Я создал базу данных 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 не является суперпользователем, и отзыв этой привилегии воздействовать на другие роли.
Если пользователь может создавать таблицы, он имеет CREATE
разрешения на рассматриваемую схему. Посмотрите на эти разрешения с \dn+ public
в psql
. Определите соответствующие разрешения и REVOKE
их.
В качестве альтернативы, если вы используете PostgreSQL версии 15 или выше, может случиться так, что пользователь вашей базы данных прямо или косвенно является членом предварительно заданной роли pg_write_all_data
. Отменить это членство.
В вашем конкретном случае правильно будет отозвать привилегию CREATE
по умолчанию для схемы public
у PUBLIC
, то есть у всех:
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
Вы говорите, что уже сделали это, и это вызвало предупреждение и не имело никакого эффекта. Это не то, что мог бы сделать PostgreSQL. Вам придется спросить людей, которые модифицировали PostgreSQL, в вашем случае DigitalOcean.
Ну, вы не добавили результат \dn+ schemaname
к вопросу, поэтому я не могу узнать, каковы текущие разрешения.
Извините, я думал, что важной частью этой команды было имя схемы. Я обновил исходный вопрос с его результатами.
Спасибо за обновление, но это первая команда, которую я запускаю. Когда я запускаю его, я получаю WARNING: no privileges could be revoked for "public"
, что, как я начинаю подозревать, означает, что у меня нет прав на это с этим пользователем :-(
Это невозможно в PostgreSQL. Либо вы совершаете какую-то банальную ошибку, например, вы подключаетесь к разным базам данных при запуске dn+
и при запуске REVOKE
, либо вы используете взломанную версию PostgreSQL с измененной системой разрешений. Тот факт, что вы получаете предупреждение, предполагает последнее, потому что вы не получите такого предупреждения в PostgreSQL.
Это PostgreSQL по умолчанию, поставляемый Digital Ocean. Это управляемая служба, поэтому у них может быть установлена некоторая система разрешений.
«Это управляемая служба» = «это модифицированная версия PostgreSQL». Тогда вам, возможно, придется спросить DigitalOcean. Никто другой не может знать их код.
Несмотря на то, что я не смог проверить ваш ответ, я хочу выразить благодарность за поддержку, поэтому я принял ваш ответ. Спасибо!
Привет @Laurenz, я не могу этого добиться :-(. Я запустил вашу команду и попытался
REVOKE CREATE ON SCHEMA public FROM production_ro
, но она распечатываетWARNING: no privileges could be revoked for "public"
и все еще может создавать таблицы. Есть совет?