Создать настоящего пользователя только для чтения в PostgreSQL

Я создал базу данных PostgreSQL в Digital Ocean. Теперь мне нужно прийти с набором пользователей и баз данных, для которых я подумал о создании нескольких баз данных (production, staging и т. д.) и наличии 2 связанных ролей для каждой базы данных с разрешениями только на чтение и чтение-запись (production_ro, production_rw , staging_ro, staging_rw и др.). Моя идея заключается в том, что, имея эти роли, я теперь могу создавать отдельных пользователей и назначать им одну из ролей, чтобы я мог быстро изменить/удалить их в случае нарушения.

Я изучал этот вопрос, и на всех страницах, которые я смог найти, есть набор инструкций, похожих на те, что здесь:

-- Revoke privileges from 'public' role
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;

-- Read-only role
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE mydatabase TO readonly;
GRANT USAGE ON SCHEMA myschema TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;

-- Read/write role
CREATE ROLE readwrite;
GRANT CONNECT ON DATABASE mydatabase TO readwrite;
GRANT USAGE, CREATE ON SCHEMA myschema TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;

-- Users creation
CREATE USER reporting_user1 WITH PASSWORD 'some_secret_passwd';
CREATE USER reporting_user2 WITH PASSWORD 'some_secret_passwd';
CREATE USER app_user1 WITH PASSWORD 'some_secret_passwd';
CREATE USER app_user2 WITH PASSWORD 'some_secret_passwd';

-- Grant privileges to users
GRANT readonly TO reporting_user1;
GRANT readonly TO reporting_user2;
GRANT readwrite TO app_user1;
GRANT readwrite TO app_user2;

Я тщательно следовал этим инструкциям и следил за тем, чтобы ни одна из них не потерпела неудачу, но после успешного выполнения всех у меня остались предположительно пользователи только для чтения, которые на самом деле могут создавать таблицы, а не видеть таблицы, созданные другими пользователями. и переключать базы данных.

Что я делаю не так?

--- Редактировать ---

Это результат команды \dn+:

                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         |

--- Редактировать 2 ---

Вот что я делаю (из соображений безопасности я буду редактировать пользователей как <USER_A>, <USER_B> и т. д. эти отредактированные пользователи будут соответствовать 1 к 1 реальным):

$ psql "postgresql://USER_A:<PASSWORD>@<DOMAIN>:<PORT>/<DEFAULT_DB>?sslmode=require"
psql (15.1, server 14.6)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

<DEFAULT_DB>=> \connect production
psql (15.1, server 14.6)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
You are now connected to database "production" as user "USER_A"
production=> \du
                                                                      List of roles
    Role name    |                         Attributes                         |                                 Member of
-----------------+------------------------------------------------------------+---------------------------------------------------------------------------
 USER_B          | Superuser, Replication                                     | {}
 USER_A          | Create role, Create DB, Replication, Bypass RLS            | {pg_read_all_stats,pg_stat_scan_tables,pg_signal_backend,r_production_ro}
 postgres        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 production_ro   | Cannot login                                               | {}

production=> REVOKE CREATE ON SCHEMA public FROM PUBLIC;
WARNING:  no privileges could be revoked for "public"
REVOKE
production=>

--- Редактировать 3 ---

Связался с DigitalOcean. Это их ответ:

Просто чтобы вы знали, что мы изучаем этот вопрос, пока я удалось воспроизвести поведение. Кажется, что для того, чтобы удалить создать таблицу из общедоступной схемы от пользователя, который нам потребуется "ОТМЕНИТЬ СОЗДАНИЕ НА СХЕМЕ общедоступной ИЗ ОБЩЕСТВЕННОЙ;" что не разрешено как использование doadmin не является суперпользователем, и отзыв этой привилегии воздействовать на другие роли.

Стоит ли изучать PHP в 2023-2024 годах?
Стоит ли изучать PHP в 2023-2024 годах?
Привет всем, сегодня я хочу высказать свои соображения по поводу вопроса, который я уже много раз получал в своем сообществе: "Стоит ли изучать PHP в...
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
В JavaScript одним из самых запутанных понятий является поведение ключевого слова "this" в стрелочной и обычной функциях.
Приемы CSS-макетирования - floats и Flexbox
Приемы CSS-макетирования - floats и Flexbox
Здравствуйте, друзья-студенты! Готовы совершенствовать свои навыки веб-дизайна? Сегодня в нашем путешествии мы рассмотрим приемы CSS-верстки - в...
Тестирование функциональных ngrx-эффектов в Angular 16 с помощью Jest
В системе управления состояниями ngrx, совместимой с Angular 16, появились функциональные эффекты. Это здорово и делает код определенно легче для...
Концепция локализации и ее применение в приложениях React ⚡️
Концепция локализации и ее применение в приложениях React ⚡️
Локализация - это процесс адаптации приложения к различным языкам и культурным требованиям. Это позволяет пользователям получить опыт, соответствующий...
Пользовательский скаляр GraphQL
Пользовательский скаляр GraphQL
Листовые узлы системы типов GraphQL называются скалярами. Достигнув скалярного типа, невозможно спуститься дальше по иерархии типов. Скалярный тип...
0
0
70
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Если пользователь может создавать таблицы, он имеет CREATE разрешения на рассматриваемую схему. Посмотрите на эти разрешения с \dn+ public в psql. Определите соответствующие разрешения и REVOKE их.

В качестве альтернативы, если вы используете PostgreSQL версии 15 или выше, может случиться так, что пользователь вашей базы данных прямо или косвенно является членом предварительно заданной роли pg_write_all_data. Отменить это членство.

В вашем конкретном случае правильно будет отозвать привилегию CREATE по умолчанию для схемы public у PUBLIC, то есть у всех:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Вы говорите, что уже сделали это, и это вызвало предупреждение и не имело никакого эффекта. Это не то, что мог бы сделать PostgreSQL. Вам придется спросить людей, которые модифицировали PostgreSQL, в вашем случае DigitalOcean.

Привет @Laurenz, я не могу этого добиться :-(. Я запустил вашу команду и попытался REVOKE CREATE ON SCHEMA public FROM production_ro, но она распечатывает WARNING: no privileges could be revoked for "public" и все еще может создавать таблицы. Есть совет?

carlosV2 06.02.2023 14:51

Ну, вы не добавили результат \dn+ schemaname к вопросу, поэтому я не могу узнать, каковы текущие разрешения.

Laurenz Albe 06.02.2023 14:52

Извините, я думал, что важной частью этой команды было имя схемы. Я обновил исходный вопрос с его результатами.

carlosV2 06.02.2023 15:02

Спасибо за обновление, но это первая команда, которую я запускаю. Когда я запускаю его, я получаю WARNING: no privileges could be revoked for "public", что, как я начинаю подозревать, означает, что у меня нет прав на это с этим пользователем :-(

carlosV2 06.02.2023 15:19

Это невозможно в PostgreSQL. Либо вы совершаете какую-то банальную ошибку, например, вы подключаетесь к разным базам данных при запуске dn+ и при запуске REVOKE, либо вы используете взломанную версию PostgreSQL с измененной системой разрешений. Тот факт, что вы получаете предупреждение, предполагает последнее, потому что вы не получите такого предупреждения в PostgreSQL.

Laurenz Albe 06.02.2023 15:40

Это PostgreSQL по умолчанию, поставляемый Digital Ocean. Это управляемая служба, поэтому у них может быть установлена ​​​​некоторая система разрешений.

carlosV2 06.02.2023 16:06

«Это управляемая служба» = «это модифицированная версия PostgreSQL». Тогда вам, возможно, придется спросить DigitalOcean. Никто другой не может знать их код.

Laurenz Albe 06.02.2023 16:18

Несмотря на то, что я не смог проверить ваш ответ, я хочу выразить благодарность за поддержку, поэтому я принял ваш ответ. Спасибо!

carlosV2 08.02.2023 03:00

Другие вопросы по теме