Appendix: User and schema management

Last Updated: Feb 14, 2017

Since superuser is not generally available during use of the RDS, you are advised to create a user separately and manage the user's private space through schema when using the database.

Operation procedure

Note: In this example, myuser is the management account created together with the instance, and newuser is the account to be created at present.

  1. Create a user with the login permission.

    1. CREATE USER newuser LOGIN PASSWORD password’;

    Parameters are described as follows:

    • USER: The user name to be created, for example, newuser
    • password: The password corresponding to the user name, for example, password
  2. Create a schema for the new user.

    1. CREATE SCHEMA newuser;
    2. GRANT newuser to myuser;
    3. ALTER SCHEMA newuser 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 problem with permission will occur:
      1. ERROR: must be member of role "newuser"
    • In consideration of security, please remove newuser from the myuser role to improve security after the authorization of OWNER is handled.
  3. Use newuser to log in 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.