Best Practices for User Management

System Administrator

The user dbadmin created when you start a GaussDB(DWS) cluster is a system administrator. It has the highest system permission and can perform all operations, including operations on tablespaces, tables, indexes, schemas, functions, and custom views, as well as query for system catalogs and views.

To create a database administrator, connect to the database as an administrator and run the CREATE USER or ALTER USER statement with SYSADMIN specified.

Examples:

Create user Jim as a system administrator.

1

CREATE USER Jim WITH SYSADMIN password '{Password}';

Change user Tom to a system administrator. ALTER USER can be used only for existing users.

1

ALTER USER Tom SYSADMIN;

Common User

You can run the CREATE USER SQL statement to create a common user. A common user cannot create, modify, delete, or assign tablespaces, and needs to be assigned the permission for accessing tablespaces. A common user has all permissions for its own tables, schemas, functions, and custom views, creates indexes on its own tables, and queries only some system catalogs and views.

The database cluster has one or more named databases. Users are shared within the entire cluster, but their data is not shared.

Common user operations are as follows. Replace password with the actual password.

1. Creating a user

   1	CREATE USER Tom PASSWORD '{Password}';

2. Changing a user password
   Change the login password of user Tom from password to newpassword.

   1	ALTER USER Tom IDENTIFIED BY 'newpassword' REPLACE '{Password}';

3. Assigning permissions to a user
   • Add CREATEDB when you create a user that has the permission for creating a database.

   1	CREATE USER Tom CREATEDB PASSWORD '{Password}';

   • Add the CREATEROLE permission for a user.

   1	ALTER USER Tom CREATEROLE;

4. Revoking user permissions

   1	REVOKE ALL PRIVILEGES FROM Tom;

5. Locking or unlocking a user
   • Lock user Tom.

   1	ALTER USER Tom ACCOUNT LOCK;

   • Unlock user Tom.

   1	ALTER USER Tom ACCOUNT UNLOCK;

6. Deleting a user

   1	DROP USER Tom CASCADE;

User Information Query

System views related to users, roles, and permissions include ALL_USERS, PG_USER, and PG_ROLES, and system catalogs include PG_AUTHID and PG_AUTH_MEMBERS.

• ALL_USERS displays all users in the database but does not show the details of them.
• PG_USER displays user information, including user IDs, the permission to create databases, and resource pools.
• PG_ROLES displays information about database roles.
• PG_AUTHID records information about database authentication identifiers (roles), including role permissions to log in or create databases.
• PG_AUTH_MEMBERS stores information of roles contained in a role group.

1. You can run PG_USER to query all users in the database. User ID (USESYSID) and permissions can also be queried.

   1 2 3 4 5 6 7 8 9 10 11 12

   SELECT * FROM pg_user; usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valbegin | valuntil | respool | parent | spacelimit | useconfig | nodegroup | tempspacelimit | spillspacelim it ---------+----------+-------------+----------+-----------+---------+----------+----------+----------+--------------+--------+------------+-----------+-----------+----------------+-------------- --- Ruby | 10 | t | t | t | t | ******** | | | default_pool | 0 | | | | | kim | 21661 | f | f | f | f | ******** | | | default_pool | 0 | | | | | u3 | 22662 | f | f | f | f | ******** | | | default_pool | 0 | | | | | u1 | 22666 | f | f | f | f | ******** | | | default_pool | 0 | | | | | dbadmin | 16396 | f | f | f | f | ******** | | | default_pool | 0 | | | | | u5 | 58421 | f | f | f | f | ******** | | | default_pool | 0 | | | | | (6 rows)

2. ALL_USERS displays all users in the database but does not show the details of them.

   1 2 3 4 5 6 7 8 9 10 11 12

   SELECT * FROM all_users; username | user_id ----------+--------- Ruby | 10 manager | 21649 kim | 21661 u3 | 22662 u1 | 22666 u2 | 22802 dbadmin | 16396 u5 | 58421 (8 rows)

3. PG_ROLES stores information about roles that have accessed the database.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

   SELECT * FROM pg_roles; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolauditadmin | rolsystemadmin | rolconnlimit | rolpassword | rolvalidbegin | rolv aliduntil | rolrespool | rolparentid | roltabspace | rolconfig | oid | roluseft | rolkind | nodegroup | roltempspace | rolspillspace ---------+----------+------------+---------------+-------------+--------------+-------------+----------------+---------------+----------------+--------------+-------------+---------------+----- ----------+--------------+-------------+-------------+-----------+-------+----------+---------+-----------+--------------+--------------- Ruby | t | t | t | t | t | t | t | t | t | -1 | ******** | | | default_pool | 0 | | | 10 | t | n | | | manager | f | t | f | f | f | f | f | f | f | -1 | ******** | | | default_pool | 0 | | | 21649 | f | n | | | kim | f | t | f | f | f | t | f | f | f | -1 | ******** | | | default_pool | 0 | | | 21661 | f | n | | | u3 | f | t | f | f | f | t | f | f | f | -1 | ******** | | | default_pool | 0 | | | 22662 | f | n | | | u1 | f | t | f | f | f | t | f | f | f | -1 | ******** | | | default_pool | 0 | | | 22666 | f | n | | | u2 | f | t | f | f | f | f | f | f | f | -1 | ******** | | | default_pool | 0 | | | 22802 | f | n | | | dbadmin | f | t | f | f | f | t | f | f | t | -1 | ******** | | | default_pool | 0 | | | 16396 | f | n | | | u5 | f | t | f | f | f | t | f | f | f | -1 | ******** | | | default_pool | 0 | | | 58421 | f | n | | | (8 rows)

4. To view user properties, query the system catalog PG_AUTHID, which stores information about database authorization identifiers (roles). Each cluster, not each database, has only one PG_AUTHID system catalog. Only users with system administrator permissions can access the catalog.

   1 2 3 4 5 6 7

   SELECT * FROM pg_authid; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolauditadmin | rolsystemadmin | rolconnlimit | rolpassword | rolvalidbegin | rolvaliduntil | rolrespool | roluseft | rolparentid | roltabspace | rolkind | rolnodegroup | roltempspace | rolspillspace | rolexcpdata | rolauthinfo ----------+----------+------------+---------------+-------------+--------------+-------------+----------------+---------------+----------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+---------------+--------------+----------+-------------+-------------+---------+--------------+--------------+---------------+-------------+------------- Ruby | t | t | t | t | t | t | t | t | t | -1 | sha256366f1e665be208e6015bc3c5795d13e4dc297a148dca6c60346018c80e5c04c9ba170384ce44609b31baa741f09a3ea5bedc7dadb906286ca994067c3fbf672dc08c981929e326ca08c005d8df942994e146ed3302af47000b36e9852b50e39dmd585de11aafebd90ec620b201fc36f07a5ecdficefade3a1456ec0aca9a0ee01e3bf2971d1dbafd604e596149e2e2928be4060dec2bd8688776588b4cd8c64fd38f1b0beab1603129fa396556ba8aa4c7d6e137a04623 | | | default_pool | t | 0 | | n | 0 | | | | sysadmin | f | t | f | f | f | t | f | f | t | -1 | sha256ecaa7f0ca4436143af43074f16cdd825783ad1a5d659fd94f5e2fa5124e7da44045ecf40bda1a97975fcf5920dca0c8be375be5c71b51cb1eeeba0851fb3648cfa49f55989f83fd9baf1a9d5853ce19125f4fc29a7c709c095ed02d00638410dmd556d6e2dcc41594dc7ad8ee909ef81637ecdficefadefd7d9704ee06affef9581cd6a50a546607f88891198e96a5e84e7e83dccf56c5cd20a500bbc5248e8ea51f0bca70c5a8dcf00953f8b62c7a181368153abce760 | | | default_pool | f | 0 | | n | | | | | Tom | f | t | f | t | f | t | f | f | f | -1 | sha256f43c4f52ac51e297bc4dbdbc751fcf05319c15681dbf5a9c5777d2edce45cb592a948b25457a728e99a3e0608592f33b0a4312eba6124936522304ba298caa2002a04578860fecb0286d7c7baec09365eafd049b2b99f74f21a08864dd7d3f2amd515ee49f0b18ef8e7d0cd27d91ce2fa9decdficefade16bab5f05b6d7c86a19ae6406cc59c437506c3f6187bfdf3eefc7a7c7033afa076361b255cc8b6ccb6e19d4767effaec654b3308cc72cebb891d00a4a10362da | | | default_pool | f | 0 | | n | | | | | (3 rows)

User Resource Query

1. Querying the resource quota and usage of all users

   1	SELECT * FROM PG_TOTAL_USER_RESOURCE_INFO;

   Example of the resource usage of all users:

   1 2 3 4 5 6 7

   username | used_memory | total_memory | used_cpu | total_cpu | used_space | total_space | used_temp_space | total_temp_space | used_spill_space | total_spill_space | read_kbytes | write_kbytes | read_counts | write_counts | read_speed | write_speed ----------+-------------+--------------+----------+-----------+------------+-------------+-----------------+------------------+------------------+-------------------+-------------+--------------+-------------+--------------+------------+------------- perfadm | 0 | 17250 | 0 | 0 | 0 | -1 | 0 | -1 | 0 | -1 | 0 | 0 | 0 | 0 | 0 | 0 usern | 0 | 17250 | 0 | 48 | 0 | -1 | 0 | -1 | 0 | -1 | 0 | 0 | 0 | 0 | 0 | 0 userg | 34 | 15525 | 23.53 | 48 | 0 | -1 | 0 | -1 | 814955731 | -1 | 6111952 | 1145864 | 763994 | 143233 | 42678 | 8001 userg1 | 34 | 13972 | 23.53 | 48 | 0 | -1 | 0 | -1 | 814972419 | -1 | 6111952 | 1145864 | 763994 | 143233 | 42710 | 8007 (4 rows)

2. Querying the resource quota and usage of a specified user

   1	SELECT * FROM GS_WLM_USER_RESOURCE_INFO('username');

   Example of the resource usage of user Tom:

   1 2 3 4 5

   SELECT * FROM GS_WLM_USER_RESOURCE_INFO('Tom'); userid | used_memory | total_memory | used_cpu | total_cpu | used_space | total_space | used_temp_space | total_temp_space | used_spill_space | total_spill_space | read_kbytes | write_kbytes | read_counts | write_counts | read_speed | write_speed -------+-------------+--------------+----------+-----------+------------+-------------+-----------------+------------------+------------------+-------------------+-------------+--------------+-------------+--------------+------------+------------- 16523 | 18 | 2831 | 0 | 19 | 0 | -1 | 0 | -1 | 0 | -1 | 0 | 0 | 0 | 0 | 0 | 0 (1 row)

3. Querying the I/O usage of a specified user

   1	SELECT * FROM pg_user_iostat('username');

   Example of the I/O usage of user Tom:

   1 2 3 4 5

   SELECT * FROM pg_user_iostat('Tom'); userid | min_curr_iops | max_curr_iops | min_peak_iops | max_peak_iops | io_limits | io_priority -------+---------------+---------------+---------------+---------------+-----------+------------- 16523 | 0 | 0 | 0 | 0 | 0 | None (1 row)