postgresql - Postgres。如何为新表自动授予 SELECT 权限？

Question

我正在设置一个具有多个模式的 Postgres 数据库 (AWS/RDS)，并希望进行精细的访问控制。

每个模式都与一个应用程序相关联。通常，应用程序会有一个“写入”用户(INSERT、UPDATE、DELETE 等)，但某些应用程序只需要从不同的模式中读取 (SELECT)。

受此 AWS 博客启发:https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/我的 readusers 查询 writeusers 创建的表时遇到“权限被拒绝”的问题，即使我更改了默认权限:

ALTER DEFAULT PRIVILEGES IN SCHEMA someschema GRANT SELECT ON TABLES TO some_read_role;

重现步骤:

1. 使用 masteruser postgres 创建 AWS RDS postgres (10.6) 实例

2. (DB postgres) 作为用户 postgres:

   CREATE DATABASE somedb LC_COLLATE 'da_DK.utf8' LC_CTYPE 'da_DK.utf8' ENCODING 'UTF8' TEMPLATE template0;
   

3. (DB somedb) 作为用户 postgres:

   REVOKE ALL ON DATABASE somedb FROM PUBLIC;
   CREATE SCHEMA clients;
   CREATE ROLE clients_read_role;
   GRANT CONNECT ON DATABASE somedb TO clients_read_role;
   CREATE ROLE clients_write_role;
   GRANT CONNECT ON DATABASE somedb TO clients_write_role;
   GRANT USAGE ON SCHEMA clients TO clients_read_role;
   GRANT SELECT ON ALL TABLES IN SCHEMA clients TO clients_read_role;
   ALTER DEFAULT PRIVILEGES IN SCHEMA clients GRANT SELECT ON TABLES TO clients_read_role;
   GRANT SELECT ON ALL SEQUENCES IN SCHEMA clients TO clients_read_role;
   ALTER DEFAULT PRIVILEGES IN SCHEMA clients GRANT SELECT ON SEQUENCES TO clients_read_role;
   GRANT USAGE, CREATE ON SCHEMA clients TO clients_write_role;
   GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA clients TO clients_write_role;
   ALTER DEFAULT PRIVILEGES IN SCHEMA clients GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO clients_write_role;
   GRANT USAGE ON ALL SEQUENCES IN SCHEMA clients TO clients_write_role;
   ALTER DEFAULT PRIVILEGES IN SCHEMA clients GRANT USAGE ON SEQUENCES TO clients_write_role;
   

4. (DB somedb) 作为用户 postgres:

   CREATE USER clients_read WITH PASSWORD 'xxx';
   GRANT clients_read_role TO clients_read;
   CREATE USER clients_write WITH PASSWORD 'yyy';
   GRANT clients_write_role TO clients_write;
   

5. (DB somedb) 作为用户 clients_write(通过 LiquiBase):

   CREATE TABLE clients.sometable
   (
       id   serial primary key,
       name varchar(50) not null
   );
   

6. (DB somedb) 作为用户 clients_read:

   SELECT * FROM clients.sometable;
   

   [42501] 错误:关系 sometable 的权限被拒绝

Answer 1

来自docs :

You can change default privileges only for objects that will be created by yourself or by roles that you are a member of.

换句话说，以用户 postgres 运行 ALTER DEFAULT PRIVILEGES 只会影响 postgres 创建的表。

要更改其他用户表的默认值，您需要指定哪个用户:

ALTER DEFAULT PRIVILEGES FOR ROLE clients_write ...

请注意，默认值不会被继承，因此目标角色是 clients_write(即实际运行 CREATE TABLE 命令的用户，他将成为新表的所有者) . clients_write_role 的默认值将无效，除非您的用户在创建表之前 SET ROLE clients_write_role;。