Manage account permissions - AnalyticDB for PostgreSQL - Alibaba Cloud Documentation Center

This topic describes how to manage database account permissions in AnalyticDB for PostgreSQL.

Prerequisites

A database account is created. For more information, see Create a database account.

Permission management

After you create a database account, you can perform the following operations:

Execute the ALTER ROLE statement to change the database role.
Execute the GRANT statement to grant user access permissions or execute the REVOKE statement to revoke user access permissions.
Execute the GRANT statement to grant permissions on database objects.

Change the database role

Change the database role. Sample statements:

ALTER ROLE jsmith WITH PASSWORD 'passwd123';
ALTER ROLE admin VALID UNTIL 'infinity';
ALTER ROLE jsmith LOGIN;
ALTER ROLE jsmith RESOURCE QUEUE adhoc;
ALTER ROLE jsmith DENY DAY 'Sunday';

Configure server settings such as search_path for the database role.
```
ALTER ROLE admin SET search_path TO myschema, public;
```

Grant user permissions

If you execute the CREATE ROLE statement to create a role that does not have the LOGIN permission, the role is considered a group. You can use the group to manage permissions on a group of users. Create a group named admin that has the CREATEROLE and CREATEDB permissions. Sample statement:
```
CREATE ROLE admin CREATEROLE CREATEDB;
```
Add users john and sally to the group. Sample statement:
```
GRANT admin TO john, sally;
```
Remove bob from the group. Sample statement:
```
REVOKE admin FROM bob;
```

Grant database object permissions to the group. All users in the group can inherit the permissions.

GRANT ALL ON TABLE mytable TO admin;
GRANT ALL ON SCHEMA myschema TO admin;
GRANT ALL ON DATABASE mydb TO admin;

Grant permissions on database objects

When a database object such as a database, schema, table, view, sequence, or function is created, all permissions of the object belong to the owner of the object. By default, only the object owner and the RDS_SUPERUSER user have permissions to perform operations on the object. If other users want to perform operations on the object, the users must have the required permissions. The following table describes the permissions on different database objects.

Database object	Permission
Tables, views, and sequences	SELECT INSERT UPDATE DELETE RULE ALL
External tables	SELECT RULE ALL
Databases	CONNECT CREATE TEMPORARY \| TEMP ALL
Functions	EXECUTE
Procedural languages	USAGE
Schemas	CREATE USAGE ALL

Important

You must separately grant permissions to each type of objects. For example, if you grant the GRANT ALL permission on a database, you cannot access tables in the database. You have the CONNECT, CREATE, and TEMP permissions only on the database.

Grant the INSERT permission on mytable to jsmith. Sample statement:
```
GRANT INSERT ON mytable TO jsmith;
```
Grant the SELECT permission on the col1 column of table2 to jsmith. Sample statement:
```
GRANT SELECT (col1) on TABLE table2 TO jsmith;
```
Revoke all permissions on mytable from jsmith. Sample statement:
```
REVOKE ALL PRIVILEGES ON mytable FROM jsmith;
```

If you are the owner of a table or the RDS_SUPERUSER user, you can change the owner of the table or remove the permissions on the table from the owner.

REASSIGN OWNED BY sally TO bob;
DROP OWNED BY visitor;