AnalyticDB:Manage users and permissions

Manage users

When you create an instance, the system prompts you to specify an initial username and password. This initial user is the root user. After the instance is created, you can use the credentials of the root user to connect to a database on that instance. After you use the psql CLI client of PostgreSQL or Greenplum to connect to a database on your instance, you can run the \du+ command to view the information of all the users. Example:

postgres=> \du+
                                List of roles
  Role name   |            Attributes             | Member of |  Description
--------------+-----------------------------------+-----------+---------------
 root_user    |                                   |           | rds_superuser
 ...

AnalyticDB for PostgreSQL does not provide a superuser, which is equivalent to the RDS_SUPERUSER role. This is the same in ApsaraDB RDS for PostgreSQL. However, you can grant the RDS_SUPERUSER role to the root user, for example, the root_user created in the preceding example. You can only check whether the root user has this role based on the user description. The root user has the following permissions:

• Creates databases and accounts and logs on to databases, but does not have the credentials of a superuser.

• Views and modifies the tables created by users other than a superuser, changes the owners of tables, and performs operations such as SELECT, UPDATE, and DELETE.

• Views connections to users other than a superuser, cancels their SQL statements, and terminates their connections.

• Executes CREATE EXTENSION and DROP EXTENSION statements to create and delete extensions.

• Creates users who have the RDS_SUPERUSER role. Example:

  CRATE ROLE root_user2 RDS_SUPERUSER LOGIN PASSWORD 'xyz';

Manage permissions

You can manage permissions at the database, schema, and table levels. For example, if you want to grant read permissions on a table to a user and revoke write permissions, execute the following statements:

GRANT SELECT ON TABLE t1 TO normal_user1;
REVOKE UPDATE ON TABLE t1 FROM normal_user1;
REVOKE DELETE ON TABLE t1 FROM normal_user1;