更新时间:2024-08-23 GMT+08:00

用户管理优秀实践

GaussDB(DWS)集群中,常用的用户分别是系统管理员和普通用户。本节简述了系统管理员和普通用户的权限,如何创建以及如何查询用户相关信息。

系统管理员

在启动GaussDB(DWS)集群时创建的用户dbadmin是系统管理员,其拥有系统的最高权限,能够执行所有的操作(表空间,表,索引,模式,函数,自定义视图的操作权限及系统表和系统视图的查看权限)。

要创建新的数据库管理员,则以管理员用户身份连接数据库,并使用带SYSADMIN选项的CREATE USER语句或ALTER USER语句进行设置。

例如:

创建用户Jim为系统管理员。

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

修改用户Tom为系统管理员。(ALTER USER时,要求用户已存在。)

1
ALTER USER Tom SYSADMIN;

普通用户

普通用户由SQL语句CREATE USER创建。不具有对表空间创建,修改,删除,分配权限,访问需要被赋权;仅对自己创建的表/模式/函数/自定义视图有所有权限,仅可以在自己的表上建索引,仅可查看部分系统表和系统视图。

数据库集群包含一个或多个已命名数据库。用户在整个集群范围内是共享的,但是其数据并不共享。

常见用户相关操作如下,此处使用的密码需要用户自定义:

  1. 创建用户。
    1
    CREATE USER Tom PASSWORD '{Password}';
    
  2. 修改用户密码。
    将用户Tom的登录密码由password修改为newpassword。
    1
    ALTER USER Tom IDENTIFIED BY 'newpassword' REPLACE '{Password}';
    
  3. 给用户授权。
    • 要创建有“创建数据库”权限的用户,需要加CREATEDB关键字。
    1
    CREATE USER Tom CREATEDB PASSWORD '{Password}';
    
    • 为用户追加CREATEROLE权限。
    1
    ALTER USER Tom CREATEROLE;
    
  4. 撤销权限。
    1
    REVOKE ALL PRIVILEGES FROM Tom;
    
  5. 锁定或解锁用户。
    • 锁定Tom账户:
    1
    ALTER USER Tom ACCOUNT LOCK;
    
    • 解锁Tom用户:
    1
    ALTER USER Tom ACCOUNT UNLOCK;
    
  6. 删除用户。
    1
    DROP USER Tom CASCADE;
    

用户信息查询

涉及用户、角色及权限相关的系统视图有ALL_USERS、PG_USER和PG_ROLES,系统表有PG_AUTHID和PG_AUTH_MEMBERS。

  • ALL_USERS视图存储记录数据库中所有用户,但不对用户信息进行详细的描述。
  • PG_USER视图存储用户信息,包含用户ID,是否可以创建数据库以及用户所在资源池等信息。
  • PG_ROLES视图存储数据库角色的相关信息。
  • PG_AUTHID系统表存储有关数据库认证标识符(角色)的信息,包含角色是否可以登录,创建数据库等信息。
  • PG_AUTH_MEMBERS存储角色的成员关系,即某个角色组包含了哪些其他角色。
  1. 通过PG_USER可以查看数据库中所有用户的列表,还可以查看用户ID(USESYSID)和用户权限。
     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视图存储记录数据库中所有用户,但不对用户信息进行详细的描述。
     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存储访问数据库角色的相关信息。
     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. 要查看用户属性,可查询系统表PG_AUTHID,它存储有关数据库认证标识符(角色)的信息。一个集群中只有一份pg_authid,并非每个数据库一份。需要有系统管理员权限才可以访问此系统表。
    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)
    

用户资源查询

  1. 查询所有用户的资源限额和资源使用情况。
    1
    SELECT * FROM PG_TOTAL_USER_RESOURCE_INFO;
    
    例如,当前所有用户的资源使用情况如下:
    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. 查询具体某个用户的资源限额和资源使用情况。
    1
    SELECT * FROM GS_WLM_USER_RESOURCE_INFO('username');
    
    例如,查询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. 查询具体某个用户的IO资源使用情况。
    1
    SELECT * FROM pg_user_iostat('username');
    
    例如,查询Tom的IO资源使用情况:
    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)