Postgres角色和用户 - 对表的权限被拒绝

Question

我按照https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/中的说明配置了运行在RDS上的Postgres 11.2数据库。

1. 我以在创建RDS期间创建的主用户身份登录。
2. 执行了CREATE SCHEMA myschema;
3. 执行了上述链接中的脚本

-- 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;

之后，我以app_user1身份连接并创建了一个新表，并向其中添加了一行数据。然后，我使用reporting_user1身份连接，并尝试对该新表执行SELECT * FROM操作，但在控制台上看到以下消息：

ERROR:  permission denied for table first_table
SQL state: 42501

我的配置中缺少什么？我期望reporting_user1对myschema中由app_user1创建的所有表有读取权限。

- Domajno

Answer 1

根据 ALTER DEFAULT PRIVILEGES文档:

您只能更改由您自己或您所属的角色创建的对象的默认权限。权限可以全局设置(即为在当前数据库中创建的所有对象)，也可以仅适用于在指定模式下创建的对象。按模式指定的默认权限将添加到特定对象类型的全局默认权限。

因此，使用 master 运行 ALTER DEFAULT PRIVILEGES 没有影响由 app_user1 创建的表的默认权限。

要解决这个问题，您必须执行以下命令：

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;

也作为app_user1。

- Leon

---

如果我没有做这个，app_user2 仍然可以读取表格，因为它具有 readwrite 角色，对吗？ - Domajno

不行，因为除非在创建表之前app_user1执行了SET ROLE readwrite，否则表的所有者是app_user1，而且app_user1和app_user2可以扮演相同的角色readwrite并不能使app_user2窥视app_user1的个人表。 - Leon

Answer 2

我相信这是Postgres的行为，它仅在现有实体上应用授权，不会将权限应用于未来的表格。您需要通过重新运行readonly用户的授权查询来更新新创建的表格的权限。或者，您可以设置触发器自动执行此操作，如此处所述：

授予用户对在PostgreSQL中创建的任何新表的权限

- Juned Ahsan

---

我没有任何现有的对象（实体），所以我不知道。授予新表明确的权限会导致错误。这是作为DB的主用户执行的。GRANT SELECT ON myschema.first_table TO reporting_user1; ERROR: permission denied for table first_table。 - Domajno

尝试授予角色，例如只读权限。重新运行此操作应该有所帮助。GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly; - Juned Ahsan

相同的 ERROR: permission denied for table first_table - Domajno

你在授予权限时使用的用户和最初设置时使用的用户是同一个吗？ - Juned Ahsan

是的，当创建RDS实例时，会创建初始主用户。 - Domajno