Superuser is not generally available during use of RDS, so we recommend that you create a user separately and manage the user's private space through schema when using the database.

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

Solution 1

  1. Create a user with the logon permission.
    CREATE USER newuser LOGIN PASSWORD’password’;

    Parameters are described as follows:

    • USER: The user name to be created, for example, newuser.
    • password: The password of the user, for example, password.
  2. Create a schema for the new user.
    CREATE SCHEMA newuser;
    GRANT newuser to myuser;
    ALTER SCHEMA newuser OWNER TO newuser;
    REVOKE newuser FROM myuser;
    Note
    • If newuser is not added to the myuser role before execution of ALTER SCHEMA newuser OWNER TO newuser, the following permission problem occurs:
         ERROR: must be member of role "newuser"
    • In consideration of security, remove newuser from the myuser role after authorization of OWNER.
  3. Use newuser to log on to the database.
    psql -U newuser -h intranet4example.pg.rds.aliyuncs.com -p 3433 pg001Password for user newuser:psql.bin (9.4.4, server 9.4.1)Type “help” for help.

Solution 2

  1. Use the initial account myuser to create a user that has the logon permission.
    CREATE USER newuser CREATEDB LOGIN PASSWORD’password’;

    Parameters are described as follows:

    • USER: The user name to be created, for example, newuser.
    • password: The password of the user, for example, password.
    • CREATEDB: The permission for the user to create databases.
  2. Use newuser to log on to the database.

    psql -U <Database instancec name> -p 3433 -U newuser <Database name>

    CREATE DATABASE

  3. Create a schema for the new user.
    CREATE SCHEMA newuser;
    GRANT myuser to newuser;
    ALTER SCHEMA myuser OWNER TO newuser;
    REVOKE newuser FROM myuser;
    Note
    • If newuser is not added to the myuser role before execution ofALTER SCHEMA newuser OWNER TO newuser, the following permission problem occurs:
        ERROR: must be member of role "newuser"
    • In consideration of security, remove newuser from the myuser role after authorization of OWNER.
  4. Use newuser to log on to the database.
    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.