我按照https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/中的说明配置了在RDS上运行的Postgres 11.2数据库
我以在RDS创建期间创建的主用户身份登录
已执行 CREATE SCHEMA myschema;
从上面的链接执行的脚本
-- 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;
之后,我以as身份连接app_user1
并创建了一个新表,并向其中添加了一行。然后,我使用report_user1进行连接并尝试连接到SELECT * FROM
该新表,但在控制台上看到以下消息:
ERROR: permission denied for table first_table SQL state: 42501
我的配置中缺少什么?我希望reporting_user1对myschema中的app_user1创建的所有表具有读取权限。