数据库锁定用户的查找与解锁

一、查看pg_user_status

PG_USER_STATUS系统表提供了访问数据库用户的状态

postgres=# select * from pg_user_status ;
 roloid | failcount |           locktime            | rolstatus | permspace | tempspace | passwordexpired 
--------+-----------+-------------------------------+-----------+-----------+-----------+-----------------
     33 |         0 | 2020-09-05 00:00:00+08        |         0 |         0 |         0 |               0
     34 |         0 | 2020-09-05 00:00:00+08        |         0 |         0 |         0 |               0
     35 |         0 | 2020-09-05 00:00:00+08        |         0 |         0 |         0 |               0
     37 |         0 | 2020-09-05 00:00:00+08        |         0 |         0 |         0 |               0
  28763 |         0 | 2024-03-27 10:54:59.369655+08 |         0 |         0 |         0 |               0
  28771 |         0 | 2024-03-27 11:00:56.373969+08 |         0 |         0 |         0 |               0
  28840 |         5 | 2024-04-28 11:42:23.830274+08 |         1 |         0 |         0 |               0

过滤出所需要的信息

postgres=# select * from pg_user_status where rolstatus !='0';
 roloid | failcount |           locktime           | rolstatus | permspace | tempspace | passwordexpired 
--------+-----------+------------------------------+-----------+-----------+-----------+-----------------
  28840 |         5 | 2024-04-28 11:48:12.60655+08 |         1 |         0 |         0 |               0
(1 行记录)

二、查看pg_user

PG_USER视图提供了访问数据库用户的信息

postgres=# select * from pg_user ;
  usename  | usesysid | usecreatedb | usesuper | usecatupd | userepl |  passwd  | valbegin | valuntil |   respool    | parent | spacelimit | useconfig | nodegroup | tempspacelimit | spillspacelimit | usemonitoradmin | useoperatoradmin | usepolicyadmin 
-----------+----------+-------------+----------+-----------+---------+----------+----------+----------+--------------+--------+------------+-----------+-----------+----------------+-----------------+-----------------+------------------+----------------
 test_lock |    28840 | f           | f        | f         | f       | ******** |          |          | default_pool |      0 |            |           |           |                |                 | f               | f                | f
 test_user |    28771 | f           | f        | f         | f       | ******** |          |          | default_pool |      0 |            |           |           |                |                 | f               | f                | f
 aiuap     |    28763 | f           | f        | f         | f       | ******** |          |          | default_pool |      0 |            |           |           |                |                 | f               | f                | f
 pwaudit   |       34 | f           | f        | f         | f       | ******** |          |          | default_pool |      0 |            |           |           |                |                 | f               | f                | f
 pwsso     |       35 | f           | f        | f         | f       | ******** |          |          | default_pool |      0 |            |           |           |                |                 | f               | f                | f
 pwadmin   |       33 | t           | t        | t         | t       | ******** |          |          | default_pool |      0 |            |           |           |                |                 | f               | f                | f
 omm       |       10 | t           | t        | t         | t       | ******** |          |          | default_pool |      0 |            |           |           |                |                 | t               | t                | t
(7 行记录)

三、通过pg_user_status查到的roloid匹配到pg_user中的usesysid列查出被锁用户

postgres=# select usename,usesysid from pg_user where usesysid='28840';
  usename  | usesysid 
-----------+----------
 test_lock |    28840
(1 行记录)

四、解锁用户

查看ALTER USER用法

postgres=# \h alter user 
Command:     ALTER USER
Description: change a database role
Syntax:
ALTER USER [ IF EXISTS ] user_name [ [ WITH ] option [ ... ] ];
ALTER USER user_name
    RENAME TO new_name;
ALTER USER user_name [ IN DATABASE database_name ]
    SET configuration_parameter {{ TO | = } { value | DEFAULT }|FROM CURRENT};
ALTER USER user_name
    [ IN DATABASE database_name ] RESET {configuration_parameter|ALL};

where option can be:
{CREATEDB | NOCREATEDB}
    | {CREATEROLE | NOCREATEROLE}
    | {INHERIT | NOINHERIT}
    | {AUDITADMIN | NOAUDITADMIN}
    | {SYSADMIN | NOSYSADMIN}
    | {MONADMIN | NOMONADMIN}
    | {OPRADMIN | NOOPRADMIN}
    | {POLADMIN | NOPOLADMIN}
    | {USEFT | NOUSEFT}
    | {LOGIN | NOLOGIN}
    | {REPLICATION | NOREPLICATION}
    | {INDEPENDENT | NOINDEPENDENT}
    | {VCADMIN | NOVCADMIN}
    | {PERSISTENCE | NOPERSISTENCE}
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD { 'password' [ EXPIRED ] | DISABLE | EXPIRED }
    | [ ENCRYPTED | UNENCRYPTED ] IDENTIFIED BY { 'password' [ REPLACE 'old_password' | EXPIRED ] | DISABLE }
    | VALID BEGIN 'timestamp'
    | VALID UNTIL 'timestamp'
    | RESOURCE POOL 'respool'
    | USER GROUP 'groupuser'
    | PERM SPACE 'spacelimit'
    | TEMP SPACE 'tmpspacelimit'
    | SPILL SPACE 'spillspacelimit'
    | RESERVE SPACE 'reservespacelimit'
    | NODE GROUP logic_cluster_name
    | ACCOUNT { LOCK | UNLOCK }
    | PGUSER

解锁成功

postgres=# alter user test_lock account unlock;
ALTER ROLE