Manage account permissions

Standard accounts in AnalyticDB for PostgreSQL have no permissions by default. Privileged accounts (including the initial account) grant access to standard accounts using two approaches:

Direct grants: Assign specific permissions on individual database objects to an account.
Role-based grants: Create a role, grant permissions to the role, then assign the role to multiple accounts. This approach simplifies managing permissions across many accounts.

Prerequisites

Before you begin, make sure you have:

A privileged account or the RDS_SUPERUSER permission
The accounts and database objects you want to manage

Grant permissions on database objects

When a database object is created — such as a database, schema, table, view, sequence, or function — all permissions on that object belong to its owner. By default, only the owner and accounts with the RDS_SUPERUSER permission can operate on the object. All other accounts need explicit grants.

The following table lists the available permissions for each object type.

Object	Permissions
Tables, views, and sequences	SELECT, INSERT, UPDATE, DELETE, RULE, ALL
Table columns	SELECT, INSERT, UPDATE
External tables	SELECT, RULE, ALL
Databases	CONNECT, CREATE, TEMPORARY \| TEMP, ALL
Functions	EXECUTE
Procedural languages	USAGE
Schemas	CREATE, USAGE, ALL

Important

Permissions are scoped to each object type. GRANT ALL on a database gives only CONNECT, CREATE, and TEMP — it does not grant access to tables within that database. Grant table permissions separately.

The following examples show common grant and revoke operations.

Grant the INSERT permission on the mytable table to jsmith:

GRANT INSERT ON mytable TO jsmith;

Grant SELECT and TRUNCATE on all existing tables in the myschema schema to jsmith:

GRANT SELECT,TRUNCATE ON ALL TABLES IN SCHEMA myschema TO jsmith;

Grant SELECT on the col1 column of mytable to jsmith:

GRANT SELECT (col1) on TABLE mytable TO jsmith;

Revoke all permissions on mytable from jsmith:

REVOKE ALL PRIVILEGES ON mytable FROM jsmith;

Grant permissions based on roles

Role-based grants are useful when multiple accounts need the same set of permissions. Grant permissions to a role once, then assign the role to any number of accounts. All assigned accounts inherit the role's permissions automatically.

The following example shows the full workflow.

Create a role named admin with the CREATEROLE and CREATEDB attributes:
```
CREATE ROLE admin CREATEROLE CREATEDB;
```
Assign the admin role to john, sally, and bob:
```
GRANT admin TO john, sally, bob;
```
To remove the role from a specific account, revoke it:
```
REVOKE admin FROM bob;
```

Grant permissions on database objects to the admin role. All accounts holding the role inherit these permissions:

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

FAQ

How do I grant the DROP TABLE permission?

The GRANT statement cannot grant DROP TABLE directly. Only the owner of a table or schema can delete it.

To let a single account drop a table, change the table's owner to that account:

-- new_owner can now drop mytable
ALTER Table mytable OWNER TO new_owner;

-- new_owner can now drop mytable and all other tables in myschema
ALTER SCHEMA myschema OWNER TO new_owner;

To let multiple accounts drop the same table, transfer ownership to a role and assign that role to each account:

-- Create a role to hold ownership
CREATE ROLE new_role;

-- Transfer ownership to the role
ALTER Table mytable OWNER TO new_role;
ALTER SCHEMA myschema OWNER TO new_role;

-- Grant the role to any account that needs drop access
GRANT new_role TO myuser;

Prerequisites

Grant permissions on database objects

Grant permissions based on roles

FAQ

How do I grant the DROP TABLE permission?

Related topics