Setting Account Security Policies

Background

For data security purposes, GaussDB provides a series of security measures, such as automatically locking and unlocking accounts, manually locking and unlocking abnormal accounts, and deleting accounts that are no longer used.

Automatically Locking and Unlocking Accounts

If the number of incorrect password attempts (failed_login_attempts) of an account reaches the upper limit (10 by default), the system automatically locks the account. Smaller parameter values result in higher account security. However, if the value of this parameter is set too small, inconvenience may occur.
If the time during which a user is locked exceeds the preset value (password_lock_time, one day by default), the system automatically unlocks the user. Larger parameter values bring higher account security. However, if the value of this parameter is set too large, inconvenience may occur.
- The integer part of the password_lock_time parameter indicates the number of days, and the decimal part can be converted into hours, minutes, and seconds. For example, password_lock_time=1.5 indicates one day and 12 hours.
- If the failed_login_attempts parameter is set to 0, an account is never locked due to incorrect password attempts. If the password_lock_time parameter is set to 0, an account is quickly unlocked after it is locked due to incorrect password attempts. Therefore, only when both parameters are set to positive values, the following operations can be performed: password failure check, account locking, and account unlocking.
- The default values of the two parameters meet the security requirements. You can change the parameter values as needed for higher security. You are advised to retain the default values.
The database provides two account locking modes.
- Single CN mode: During cluster deployment, one CN is configured as an interface to provide external services.
- Multi-CN mode: During cluster deployment, multiple CNs are configured to work as external interfaces.
In security mode, a single CN provides the account locking mechanism. In high concurrency mode, each CN provides account locking independently and does not share their locking information. In this mode, you are advised to limit the number of CNs to prevent brute-force attacks. The automatic account unlocking time for each node depends on their system clocks. During cluster deployment, ensure clock synchronization between cluster nodes (for example, use the NTP). If the nodes are out of synchronization, the account unlocking time for each node will be different.

Manually Locking and Unlocking Accounts

Once detecting that an account is stolen or the account is used to access the database without being authorized, administrators can manually lock the account. Administrators can manually unlock the account if the account becomes normal again.

For details about how to create a user, see Users. To manually lock and unlock user joe, run commands in the following format:

To manually lock the account, run the following command:

      
           openGauss=# ALTER USER joe ACCOUNT LOCK;
ALTER ROLE

To manually unlock the account, run the following command:

      
           openGauss=# ALTER USER joe ACCOUNT UNLOCK;
ALTER ROLE

Deleting Accounts That Are No Longer Used

Administrators can delete an account that is no longer used. This operation cannot be rolled back.

When an account to be deleted is in the active state, it is deleted after the session is disconnected.

For example, if you want to delete account joe, run the following command:

    
     
       
       
         openGauss=# DROP USER joe  CASCADE;
DROP ROLE
In the scenario where multiple users, databases, and schemas are assigned permissions, if you drop a user, the following error message is displayed because the user has the permissions granted by other users.
openGauss=# drop user test1 cascade;
ERROR: role "test1" cannot be dropped because some objects depend on it
In this scenario, you can drop user only after querying the system catalog to find the permissions granted to the user by other users and logging in to the system as other users to manually delete the permissions. The following is an example:
Log in to the system database and check the user OID.
openGauss=#  select oid from pg_roles where rolname='test1';
  oid
-------
 16386
(1 row)
Check the pg_shdepend view to obtain the permissions granted to the user. You can find that the user has two permissions granted by other users. Handle the first permission first.
openGauss=# select * from pg_shdepend where refobjid='16386';
 dbid  | classid | objid | objsubid | refclassid | refobjid | deptype | objfile
-------+---------+-------+----------+------------+----------+---------+---------
 16394 |     826 | 16400 |        0 |       1260 |    16386 | a       |
 16394 |     2615 | 16399 |        0 |       1260 |    16386 | a       |

Check the pg_database view to obtain the database where the view is located. The test database is displayed.
openGauss=#   select * from pg_database where oid='16394'
 datname | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datcompatibility |                      datacl
             | datfrozenxid64
---------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+---------------+------------------+--------------------------------------
-------------+----------------
 test    |     10 |        7 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         16268 | 1985         |          1663 | MYSQL            | {=Tc/test,test=CTc/test,admi
n=c/test} |           1985
(1 row)
Log in to the test database.
[test@euler_phy_194 opengauss]$ gsql -p 3730 -d test
gsql((GaussDB Kernel VxxxRxxxCxx build f521c606) compiled at 2021-09-16 14:55:22 commit 2935 last mr 6385 release)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
Check the permission type using pg_class. The value of where oid is the value of classid queried in the pg_shdepend view.
test=# select * from pg_class where oid = 826;
    relname     | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | reldeltarelid | reldeltaidx | rel
cudescrelid | relcudescidx | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relcmprs | relhasclusterkey
 | relrowmovement | parttype | relfrozenxid |    relacl    | reloptions | relreplident | relfrozenxid64 | relbucket | relbucketkey
----------------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+---------------+---------------+-------------+----
------------+--------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+----------+-----------------
-+----------------+----------+--------------+--------------+------------+--------------+----------------+-----------+--------------
 pg_default_acl |           11 |   11810 |         0 |       10 |     0 |       16063 |             0 |        0 |         0 |             0 |             0 |             0 |             0 |           0 |
          0 |            0 | t           | f           | p              | r       |        4 |         0 | t          | f          | f           | f              | f              | 0        | f
 | f              | n        | 0            | {=r/test} |            | n            |           1985 |           |
(1 row)
The preceding information indicates that the user has been granted the default ACL permission on the table or view by other users. Check the pg_default_acl view. The value of oid is the value of objid in the pg_shdepend table.
test=#  select * from pg_default_acl oid = 16400;;
 defaclrole | defaclnamespace | defaclobjtype |               defaclacl               |  oid
------------+-----------------+---------------+---------------------------------------+-------
      16395 |           16399 | r             | {test=arwd/admin,test1=arwd/admin} | 16400
(1 row)
According to the defaclacl column of the view, user test1 is granted with the default permission by user admin. Then, find the object (table or view) to which the permission is granted.
View the object name using pg_namespace. The value of oid is the value of defaclnamespace queried in pg_default_acl. The view shows that user test1 has been granted the schema test_schema_1 permission by user admin.
test=# select * from pg_namespace where oid = 16399;
    nspname    | nspowner | nsptimeline |                nspacl                 | in_redistribution | nspblockchain
---------------+----------+-------------+---------------------------------------+-------------------+---------------
 test_schema_1 |       10 |           0 | {test=UC/test,admin=UC/test} | n                 | f
(1 row)
Log in to the test database as user admin. To revoke the default schema permission granted by the user admin to user test1, run ALTER DEFAULT PRIVILEGES IN SCHEMA test_schema_1 revoke all privileges ON TABLES from test1;.
test=#   ALTER DEFAULT PRIVILEGES IN SCHEMA test_schema_1 revoke all privileges ON TABLES from test1;
ALTER DEFAULT PRIVILEGES

Log in to the system database and check the pg_database view to obtain the database where the view is located. The test database is displayed.
openGauss=#   select * from pg_database where oid='16394'
 datname | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datcompatibility |                      datacl
             | datfrozenxid64
---------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+---------------+------------------+--------------------------------------
-------------+----------------
 test    |     10 |        7 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         16268 | 1985         |          1663 | MYSQL            | {=Tc/test,test=CTc/test,admi
n=c/test} |           1985
(1 row)
Log in to the test database.
[test@euler_phy_194 opengauss]$ gsql -p 3730 -d test

Check the pg_class view. In the view, the value of oid is the value of classid in the second row of the pg_shdepend table.
openGauss=#  select * from pg_class where oid='2615';
   relname    | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | reldeltarelid | reldeltaidx | relcu
descrelid | relcudescidx | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relcmprs | relhasclusterkey |
 relrowmovement | parttype | relfrozenxid |    relacl    | reloptions | relreplident | relfrozenxid64 | relbucket | relbucketkey
--------------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+---------------+---------------+-------------+------
----------+--------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+----------+------------------+
----------------+----------+--------------+--------------+------------+--------------+----------------+-----------+--------------
 pg_namespace |           11 |   11787 |         0 |       10 |     0 |       15947 |             0 |        1 |        20 |             1 |             0 |             0 |             0 |           0 |
        0 |            0 | t           | f           | p              | r       |        6 |         0 | t          | f          | f           | f              | f              | 0        | f                |
 f              | n        | 0            | {=r/test} |            | n            |           1985 |           |
(1 row)

The result shows that the type is pg_namespace. Check the pg_namespace system view. The value of oid is the value of objid in the second line of pg_shdepend.
test=# select * from pg_namespace where oid='16399';
    nspname    | nspowner | nsptimeline |                        nspacl                         | in_redistribution | nspblockchain
---------------+----------+-------------+-------------------------------------------------------+-------------------+---------------
 test_schema_1 |       10 |           0 | {test=UC/test,admin=UC/test,test1=U/test} | n                 | f
(1 row)

The nspacl column shows that the test_schema_1 view of user test1 is granted permissions by user test. To revoke the permissions granted to user test1, log in to the test database as user test and run revoke all on schema test_schema_1 from test1.
test=# revoke all on schema test_schema_1 from test1;
REVOKE

By now, the permissions granted to user test1 by other users have been cleared. You only need to run the drop user command.
openGauss=# drop user test1 cascade;
DROP ROLE

        

      

    
   