ApsaraDB RDS for PostgreSQL does not provide superuser accounts. We recommend that you create separate accounts for each database. Then, you can manage the objects of each account by using the schema of the account.

Note In this example, the myuser privileged account that is created when you create an ApsaraDB RDS instance is used to create an account named newuser.

Solution 1

  1. Use the privileged account myuser to create an account named newuser that has the permissions to log on to a database. 
    CREATE USER newuser LOGIN PASSWORD 'password';

    Description:

    • USER: the username of the account.
    • password: the password of the account.
  2. Create a schema for the newuser account. 
    CREATE SCHEMA newuser;
GRANT newuser to myuser;
ALTER SCHEMA newuser OWNER TO newuser;
REVOKE newuser FROM myuser;
    Note

    • If the myuser role is not attached to the newuser account before you execute the ALTER SCHEMA newuser 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 you detach the myuser role from the newuser account. This ensures the security of the RDS instance.

  3. Log on to the database 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.

Solution 2

  1. Use the privileged account myuser to create an account named newuser that has the permissions to log on to a database. 
    CREATE USER newuser CREATEDB LOGIN PASSWORD 'password';

    Description:

    • USER: the username of the account.
    • password: the password of the account.
    • CREATEDB: grants the newuser account the permissions to create a database.
  2. Log on to the database by using the newuser account. 
    psql -U <Endpoint of the RDS instance> -p 3433 -U newuser <Database name>
CREATE DATABASE
  3. Create a schema for the newuser account. 
    CREATE SCHEMA newuser;
GRANT myuser to newuser;
ALTER SCHEMA myuser OWNER TO newuser;
REVOKE newuser FROM myuser;
    Note

    • If the myuser role is not attached 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 you detach the myuser role from the newuser account. This ensures the security of the RDS instance.

  4. Log on to the database 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.