User and schema management

Last Updated: Aug 17, 2017

Since the superuser permissions are not completely released during the use of RDS, it is recommended that you create a separate account and use the schema to manage your private data space when using the database.

Operation procedures

Note: In the following example, myuser is the management account which has been created for the instance and newuser is the new account to be created.

  1. Create an account with logon permissions.

    1. CREATE USER newuser LOGIN PASSWORD 'password';

    Parameters are described as follows:

    • USER: Name of the account to be created, for example, newuser.

    • password: Password of the account, for example, password.

  2. Create a schema for the new account.

    1. CREATE SCHEMA newuser;
    2. GRANT newuser to myuser;
    3. ALTER SCHEMA myuser OWNER TO newuser;
    4. REVOKE newuser FROM myuser;

    Note:

    • If newuser is not added to the myuser role before ALTER SCHEMA newuser OWNER TO newuser, the following permission error is displayed:

      1. ERROR: must be member of role "newuser"
    • For security reasons, remove newuser from myuser role to improve security after the authorization of OWNER is handled.

  3. Use newuser to log on to the database.

    1. psql -U newuser -h intranet4example.pg.rds.aliyuncs.com -p 3433 pg001
    2. Password for user newuser:
    3. psql.bin (9.4.4, server 9.4.1)
    4. Type "help" for help.
Thank you! We've received your feedback.