Manage permissions in an ApsaraDB RDS for PostgeSQL instance - ApsaraDB RDS

Principles of permission management

A role is created as a permission set. You can use roles to manage permissions at fine-grained levels. Roles do not have logon permissions. You can create users to which you can grant logon permissions. The management model of ApsaraDB RDS for PostgeSQL specifies that the permissions of a user consist of the permissions of the role that is associated with the user and the logon permissions. The permissions of a user vary based on the permissions of the associated role.

Model of permission management

The management model of ApsaraDB RDS for PostgeSQL is easy to use, effective, and suitable for most business scenarios.

A privileged account can be created for your RDS instance. The privileged account has all permissions on your RDS instance and can be used only by a few database administrators.
You can create one owner and two roles named {project}_role_readwrite and {project}_role_readonly. You can use the owner and the roles to manage a team or a project.

Note If you want to manage permissions at more fine-grained levels, you can create roles based on your business requirements.
You can create users. The permissions of a user consist of the permissions of the role that is associated with the user and the logon permissions.
Multiple schemas can be defined for a team or a project. We recommend that you grant permissions at the schema level or the role level.
Do not place tables in the schema named public. By default, all users have the CREATE permission and the USAGE permission on the public schema.

Example of permission management

This section provides an example on how to manage permissions at the project level. You can also follow the instructions that are provided in the example to manage permissions at the team level.

A database administrator can use the privileged account named dbsuperuser of your RDS instance.
Your project is named rdspg, and two schemas named rdspg and rdspg_1 are created.

The following table describes the permissions of the owner and roles that you created in your project.


Owner or Role	Permission on tables	Permission on stored procedures
An owner named rdspg_owner	DDL: the permissions to perform CREATE, DROP, and ALTER operations. Data Query Language (DQL): the permissions to perform SELECT operations. DML: the permissions to perform UPDATE, INSERT, and DELETE operations.	DDL: the permissions to perform CREATE, DROP, and ALTER operations. DQL: the permissions to perform SELECT operations and the permissions to call stored procedures.
A role named rdspg_role_readwrite	DQL: the permissions to perform SELECT operations. DML: the permissions to perform UPDATE, INSERT, and DELETE operations.	DQL: the permissions to perform SELECT operations and the permissions to call stored procedures. If DDL operations are found in stored procedures, an error message that is related to permissions is displayed.
A role named rdspg_role_readonly	DQL: the permissions to perform SELECT operations.	DQL: the permissions to perform SELECT operations and the permissions to call stored procedures. If DDL operations are found in stored procedures, an error message that is related to permissions is displayed.

You can grant permissions to the users that you create based on your business requirements.

The permissions of the rdspg_readwrite user consist of the permissions of the rdspg_role_readwrite role and the logon permissions.
The permissions of the rdspg_readonly user consist of the permissions of the rdspg_role_readonly role and the logon permissions.

Procedure

Create an owner named rdspg_owner and two roles named rdspg_role_readwrite and rdspg_role_readonly for your project.

Use the privileged account named dbsuperuser of your RDS instance to perform the following operations as a database administrator:

--- rdspg_owner is the username of the owner. The password in this example is for reference only. Replace the password with the actual password of the owner. 
CREATE USER rdspg_owner WITH LOGIN PASSWORD 'asdfy181BASDfadasdbfas';

CREATE ROLE rdspg_role_readwrite;
CREATE ROLE rdspg_role_readonly;

--- Grant the permissions to perform DQL SELECT operations and DML UPDATE, INSERT, and DELETE operations on tables that are created by using the credentials of the rdspg_owner owner to the rdspg_role_readwrite role. 
ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT ALL ON TABLES TO rdspg_role_readwrite;

--- Grant the permissions to perform DQL SELECT operations and DML UPDATE, INSERT, and DELETE operations on sequences that are created by using the credentials of the rdspg_owner owner to the rdspg_role_readwrite role. 
ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT ALL ON SEQUENCES TO rdspg_role_readwrite;

--- Grant the permission to perform DQL SELECT operations on tables that are created by using the credentials of the rdspg_owner owner to the rdspg_role_readonly role. 
ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT SELECT ON TABLES TO rdspg_role_readonly;

Create two users named rdspg_readwrite and rdspg_readonly.

Use the privileged account named dbsuperuser of your RDS instance to perform the following operations as a database administrator:

--- Grant the permissions to perform DQL SELECT operations and DML UPDATE, INSERT, and DELETE operations to the rdspg_readwrite user. 
CREATE USER rdspg_readwrite WITH LOGIN PASSWORD 'dfandfnapSDhf23hbEfabf';
GRANT rdspg_role_readwrite TO rdspg_readwrite;

--- Grant the permission to perform DQL SELECT operations to the rdspg_readonly user. 
CREATE USER rdspg_readonly WITH LOGIN PASSWORD 'F89h912badSHfadsd01zlk';
GRANT rdspg_role_readonly TO rdspg_readonly;

Create a schema named rdspg and grant the permissions on the schema to the rdspg_role_readwrite role and the rdspg_role_readonly role.
Use the privileged account named dbsuperuser of your RDS instance to perform the following operations as a database administrator:
```
--- Specify the rdspg_owner owner as the owner of the rdspg schema.
CREATE SCHEMA rdspg AUTHORIZATION rdspg_owner;

--- Grant the permissions on the rdspg schema to the rdspg_role_readwrite role and the rdspg_role_readonly role. 
GRANT USAGE ON SCHEMA rdspg TO rdspg_role_readwrite;
GRANT USAGE ON SCHEMA rdspg TO rdspg_role_readonly;
```
Note The rdspg_readwrite user and the rdspg_readonly user inherit the changes to the permissions of their associated roles. You do not need to grant permissions to the rdspg_readwrite user or the rdspg_readonly user.

Scenarios

Scenario 1: Use the rdspg_owner owner to perform DDL CREATE, DROP, and ALTER operations on tables in the rdspg schema

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

Scenario 2: Use the rdspg_readwrite user or the rdspg_readonly user for business development

The user that you use for business management follows the principle of least privilege (PoLP). We recommend that you use the rdspg_readonly user for business management. Use the rdspg_readwrite user only when you must perform DML operations. PoLP facilitates read/write splitting at the business layer.

Note

Read/write splitting at the business layer helps reduce the additional cost and performance loss caused by automatic read/write splitting that is performed by the proxy middleware.
If no read-only RDS instance is attached to your RDS instance, we recommend that you grant read permissions to one client and grant read and write permissions to the other client. This configuration is for the creation of read-only RDS instances. We also recommend that you follow PoLP and use the rdspg_readonly user for the client to which you granted read permissions.
- Use the rdspg_readonly user for the client to which you granted read permissions and set the Java Database Connectivity (JDBC) URL to the endpoint of read-only RDS instance 1,the endpoint of read-only RDS instance 2,the endpoint of your RDS instance.
- Use the rdspg_readwrite user for the client to which you granted read and write permissions and set the JDBC URL to the endpoint of your RDS instance.

Use the rdspg_readwrite user to perform DQL SELECT operations and DML UPDATE, INSERT, and DELETE operations on the tables in the rdspg schema.

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

--- The rdspg_readwrite user does not have the permissions to perform DDL CREATE, DROP, and ALTER operations.
CREATE TABLE rdspg.test2(id int);
ERROR:  permission denied for schema rdspg
LINE 1: create table rdspg.test2(id int);

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

Use the rdspg_readonly user to perform DQL SELECT operations on the tables in the rdspg schema.

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)

Scenario 3: Grant permissions on a project to another project

You can grant read permissions on the tables of the rdspg project to the employee_readwrite user that belongs to the employee project. Use the privileged account named dbsuperuser of your RDS instance to perform the following operations as a database administrator:

--- Grant the permissions of the rdspg_role_readonly role to the employee_readwrite user. 
GRANT rdspg_role_readonly TO employee_readwrite;

Scenario 4: Create a schema named rdspg_2 and grant the permissions on the rdspg_2 schema to roles

The rdspg_readwrite user, the rdspg_readonly user, and the employee_readwrite user inherit the changes to the permissions of their associated roles. You do not need to grant permissions to the rdspg_readwrite user, the rdspg_readonly user, or the employee_readwrite user. Use the privileged account named dbsuperuser of your RDS instance to perform the following operations as a database administrator:

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;

Queries on permissions

If you use the management model that is described in this topic, use one of the following methods to query the permissions of the users in your RDS instance:

Use a command-line tool to connect to your RDS instance. For more information, see Connect to an ApsaraDB RDS for PostgreSQL instance. Then, run the \du command. $Run the \du command to query permissions$
The command output in the preceding figure shows that rdspg_role_readonly,employee_role_readwrite is displayed in the Member of column for the employee_readwrite user. Therefore, the permissions of DQL and DML operations are granted to the employee_readwrite user and the permissions of DQL operations are granted to tables in the rdspg project.

Use SQL to query the permissions.

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;