ApsaraDB RDS:Best practices for permission management in ApsaraDB RDS for PostgreSQL

-- Create the owner. Replace the password with a strong password.
CREATE USER rdspg_owner WITH LOGIN PASSWORD 'asdfy181BASDfadasdbfas';

-- Create the read-write and read-only roles (no login).
CREATE ROLE rdspg_role_readwrite;
CREATE ROLE rdspg_role_readonly;

-- Grant DQL + DML permissions on tables created by rdspg_owner to rdspg_role_readwrite.
ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT ALL ON TABLES TO rdspg_role_readwrite;

-- Grant DQL + DML permissions on sequences created by rdspg_owner to rdspg_role_readwrite.
ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT ALL ON SEQUENCES TO rdspg_role_readwrite;

-- Grant DQL-only permission on tables created by rdspg_owner to rdspg_role_readonly.
ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT SELECT ON TABLES TO rdspg_role_readonly;

-- Read-write user: inherits DQL + DML permissions from rdspg_role_readwrite.
CREATE USER rdspg_readwrite WITH LOGIN PASSWORD 'dfandfnapSDhf23hbEfabf';
GRANT rdspg_role_readwrite TO rdspg_readwrite;

-- Read-only user: inherits DQL-only permission from rdspg_role_readonly.
CREATE USER rdspg_readonly WITH LOGIN PASSWORD 'F89h912badSHfadsd01zlk';
GRANT rdspg_role_readonly TO rdspg_readonly;

-- Set rdspg_owner as the owner of the rdspg schema.
CREATE SCHEMA rdspg AUTHORIZATION rdspg_owner;

-- Grant schema access to both roles.
GRANT USAGE ON SCHEMA rdspg TO rdspg_role_readwrite;
GRANT USAGE ON SCHEMA rdspg TO rdspg_role_readonly;

CREATE SCHEMA rdspg_1 AUTHORIZATION rdspg_owner;

-- Grant the access permission on the rdspg_2 schema to roles.
-- Grant the permissions to perform DDL CREATE, DROP, and ALTER operations on tables in the rdspg_1 schema.
GRANT USAGE ON SCHEMA rdspg_1 TO rdspg_role_readwrite;
GRANT USAGE ON SCHEMA rdspg_1 TO rdspg_role_readonly;

-- Grant rdspg_role_readonly permissions to employee_readwrite.
GRANT rdspg_role_readonly TO employee_readwrite;

postgres=> \du
                         List of roles
 Role name              | Attributes    | Member of
------------------------+---------------+----------------------------------------------
 rdspg_owner            | ...           | {}
 rdspg_role_readwrite   | Cannot login  | {}
 rdspg_role_readonly    | Cannot login  | {}
 rdspg_readwrite        | ...           | {rdspg_role_readwrite}
 rdspg_readonly         | ...           | {rdspg_role_readonly}
 employee_readwrite      | ...           | {rdspg_role_readonly,employee_role_readwrite}

SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;

CREATE TABLE rdspg.test(id bigserial primary key, name text);
CREATE INDEX idx_test_name on rdspg.test(name);

INSERT INTO rdspg.test (name) VALUES('name0'),('name1');
SELECT id,name FROM rdspg.test LIMIT 1;

-- DDL is blocked:
CREATE TABLE rdspg.test2(id int);
ERROR:  permission denied for schema rdspg

DROP TABLE rdspg.test;
ERROR:  must be owner of table test

ALTER TABLE rdspg.test ADD id2 int;
ERROR:  must be owner of table test

CREATE INDEX idx_test_name on rdspg.test(name);
ERROR:  must be owner of table test

INSERT INTO rdspg.test (name) VALUES('name0'),('name1');
ERROR:  permission denied for table test

SELECT id,name FROM rdspg.test LIMIT 1;
 id | name
----+-------
  1 | name0
(1 row)