Create and manage users-AnalyticDB(AnalyticDB)-阿里云帮助中心

Database accounts are used for permission management in AnalyticDB for PostgreSQL. An account can have permissions to perform operations on database objects. Before you connect to a database or operate on its objects, you must create a database account.

Account types

AnalyticDB for PostgreSQL provides two types of database accounts:

Privileged account: This type includes the initial account created in the console and accounts created using SQL statements that have the RDS_SUPERUSER role. A privileged account has all operational permissions on all databases. The permissions include:
- The CREATEROLE, CREATEDB, and LOGIN permissions. This means the account can create databases and database accounts, and log in to databases. However, it does not have the SUPERUSER permission.
- The permission to view and modify data tables of other accounts using SELECT, UPDATE, and DELETE statements, or by changing the owner.
- The permission to view other accounts' connection information, cancel their SQL statements, or terminate their connections.
- The permission to run the CREATE EXTENSION and DROP EXTENSION commands to create and delete extensions.
- The permission to create other accounts with the RDS_SUPERUSER role.
Note
AnalyticDB for PostgreSQL does not grant the SUPERUSER permission. The corresponding permission is RDS_SUPERUSER, which is consistent with the permission system of ApsaraDB RDS for PostgreSQL.
Standard account: By default, a standard account has no permissions. A privileged account or an account with the GRANT permission must grant it permissions on one or more database objects.

Recommendations

A privileged account has all operational permissions on all databases. To ensure data security, we recommend avoiding using a privileged account for routine business operations. Instead, create standard accounts and grant them only the required permissions on specific database objects. This helps prevent unauthorized access and modifications.

Create an account

Create an initial account

The initial account is a privileged account that has the RDS_SUPERUSER role. You must create an initial account first. After logging in to the database with the initial account, you can create other privileged or standard accounts.

Important

An initial account cannot be deleted. If you forget the password for the initial account, you can reset it.

You can create an initial account in the AnalyticDB for PostgreSQL console or by calling an API operation.

Console

Log in to the AnalyticDB for PostgreSQL console. From the top of the page, select the region of your instance. Then, find your instance and click its ID.
In the left-side navigation pane, click Account Management.

Click Create Account. In the Create Account dialog box, enter an account name and a password. Then, click OK.

Parameter

Description

Account

The initial account name must meet the following requirements:

Must contain only lowercase letters, digits, and underscores (_).
Must start with a lowercase letter and end with a lowercase letter or digit.
Cannot start with gp.
Must be 2 to 16 characters long.

New Password and Confirm Password

The password must meet the following requirements:

Must contain characters from at least three of the following categories: uppercase letters, lowercase letters, digits, and special characters.
The supported special characters are !@#$%^&*()_+-=.
Must be 8 to 32 characters long.

Important

For data security, change your password regularly and do not reuse previous passwords.

API

Call the CreateAccount API operation to create an initial account.

Create a privileged RDS_SUPERUSER account

To create a privileged account, grant the RDS_SUPERUSER role when you create the account. For example:

CREATE ROLE username WITH LOGIN ENCRYPTED PASSWORD 'userPassword' RDS_SUPERUSER;

Create a standard account

A standard account is a database account that does not have the RDS_SUPERUSER role. You cannot create a standard account in the AnalyticDB for PostgreSQL console or by calling an API operation.

You can create a standard account by running an SQL statement. For example:

CREATE ROLE username WITH LOGIN ENCRYPTED PASSWORD 'userPassword';

For the complete SQL syntax for creating privileged and standard accounts, see CREATE ROLE.

Query database accounts

The AnalyticDB for PostgreSQL console displays only the initial account. It does not display other privileged or standard accounts that are created using SQL statements. You can query all privileged accounts (including the initial account) and standard accounts in an instance using an SQL statement or by calling an API operation.

SQL

SELECT * FROM pg_roles;

The adbpgadmin and aurora accounts in the query results are system accounts.

API

Call the DescribeAccounts API operation to query all database account names, their types (privileged or standard), and their creation status.
Call the DescribeDiagnosisDimensions API operation to query all database accounts and databases. This operation does not return the account types or creation status.
Call the DescribeRoles API operation to query all database accounts. This operation does not return the account types or creation status.

Reset a password

If you forget the password for a database account, you can reset it in the AnalyticDB for PostgreSQL console, by running an SQL statement, or by calling an API operation.

Console

In the console, you can only reset the password for the initial account. To reset the password for a standard account, you must use an SQL statement.

Log in to the AnalyticDB for PostgreSQL console. From the top of the page, select the region of your instance. Then, find your instance and click its ID.
In the left-side navigation pane, click Account Management.
Find the account and click Reset Password. In the dialog box, enter a new password.

SQL

ALTER ROLE username WITH PASSWORD 'userPassword';

For the complete SQL syntax for modifying the password and other attributes of a database account, see ALTER ROLE.

API

Call the ResetAccountPassword API operation to reset an account password.

Next steps

After a standard account is created, it has no permissions to operate on or access any databases. You must grant it the required permissions on database objects. For more information, see Manage account permissions.