After you create an ApsaraDB RDS for PPAS instance, you must create databases and accounts on the instance before you can use the instance. This topic describes how to create an account on an ApsaraDB RDS for PPAS instance.

Precautions

  • The PPAS database engine requires you to create a privileged account in the ApsaraDB RDS console. Then, you can create and manage databases by using Alibaba Cloud Data Management (DMS).
  • Databases on the same RDS instance share all of the resources that belong to the instance. Each RDS instance supports one privileged account and multiple standard accounts. You can create and manage standard accounts by using SQL statements.
  • If you want to migrate data from an on-premises database to an RDS instance, you must log on to the RDS instance and create a database and an account with the same names as the on-premises database and its authorized account.
  • Follow the least privilege principle to create accounts and grant them read-only permissions or both read and write permissions on databases based on your business requirements. If necessary, you can create more than one account and grant them only the permissions on specific databases. If an account does not need to write data to a database, grant only the read-only permissions on that database to the account.
  • For security purposes, we recommend that you configure strong passwords for the accounts that are created on your RDS instance. In addition, we recommend that you change the passwords on a regular basis.
  • After you create a privileged account for your RDS instance, you cannot delete the privileged account.

Create a privileged account

  1. Log on to the ApsaraDB for RDS console.
  2. In the left-side navigation pane, click Instances. In the top navigation bar, select the region where the target RDS instance resides.
    Select a region
  3. Find the target instance and click the instance ID.
  4. In the left-side navigation pane, click Accounts.
  5. Click Create Privileged Account.
  6. Configure the following parameters.
    Parameter Description
    Database Account

    Enter the username of the account. The username of the account must meet the following requirements:

    • The username of the account must be 2 to 16 characters in length.
    • The username of the account must start with a letter and end with a letter or digit.
    • The username of the account can contain lowercase letters, digits, and underscores (_).
    • The username of the account cannot be the same as the username of an existing account.
    Password

    Enter the password of the account. The password of the account must meet the following requirements:

    • The password of the account must be 8 to 32 characters in length.
    • The password of the account must contain at least three of the following character types: uppercase letters, lowercase letters, digits, and special characters.
    • Special characters include: ! @ # $ % ^ & * ( ) _ + - =
    Confirm Password Enter the password of the account again.
  7. Click OK.

Create a standard account

To create a standard account, you must log on to the RDS instance by using DMS and then execute the following statement:

CREATE ROLE "username" CREATEDB CREATEROLE LOGIN ENCRYPTED PASSWORD 'password';

For more information about how to connect to an RDS instance, see Connect to an ApsaraDB RDS for PPAS instance.

Manage a schema

  1. Execute the following statement to create an account that is authorized to log on to the RDS instance:
    CREATE USER newuser LOGIN PASSWORD 'password';

    Parameter description:

    • USER: the username of the account. In this example, the username is newuser.
    • PASSWORD: the password of the account.
  2. Execute the following statement to create a schema for the newuser account:
    CREATE SCHEMA newuser;GRANT newuser to myuser;ALTER SCHEMA myuser OWNER TO newuser;REVOKE newuser FROM myuser;
    Note
    • If you have not granted the myuser role to the newuser account before you execute the ALTER SCHEMA myuser OWNER TO newuser statement, the following error is reported:
      ERROR: must be member of role "newuser"
    • After you grant the OWNER permissions to the newuser account, we recommend that revoke the myuser role from the newuser account. This increases the security of the RDS instance.
  3. Log on to the RDS instance by using the newuser account.
    psql -U newuser -h intranet4example.pg.rds.aliyuncs.com -p 3433 pg001
    Password for user newuser:
    psql.bin (9.4.4, server 9.4.1)
    Type "help" for help.