Configure Password Policies
A profile (or password policy configuration) is used to control password security policies for users in SynxDB. You can bind a profile to one or more users to control their password security policies. A profile defines the rules for managing and reusing passwords. By configuring a profile, database administrators can use SQL statements to enforce constraints, such as locking an account after a certain number of failed login attempts or controlling password reuse.
Password policy configuration is enabled by default. To disable or enable it, you can set the enable_password_profile parameter in the postgresql.conf configuration file. The default value is true. If you want to disable this feature and the related login monitor process, set the parameter to false and restart the cluster:
gpconfig -c enable_password_profile -v false
gpstop -ra
# To re-enable it, you can run gpconfig -c enable_password_profile -v true and restart the cluster.
How it works
Similar to the Autovacuum mechanism, profiles introduce a Login Monitor Launcher and Login Monitor Worker processes. When a user fails to log in, SynxDB sends a signal to the postmaster. Upon receiving the signal, the postmaster sends a signal to the launcher process. The launcher process then notifies the postmaster to start a worker process. The worker process writes back metadata and, upon completion, notifies the user process and the launcher process.
Set password policies using SQL syntax
Currently, database administrators can use SQL statements to set up profiles. The following password-related parameters are commonly used in the statements:
Parameter |
Description |
|---|---|
|
|
|
|
|
|
CREATE PROFILE
Create a profile and set the corresponding password policies.
CREATE PROFILE profile LIMIT
password_parameters ... ;
password_parameters:
{ { FAILED_LOGIN_ATTEMPTS
| PASSWORD_REUSE_MAX
| PASSWORD_LOCK_TIME
}
expr
}
ALTER PROFILE
Update a profile to modify its password policies.
ALTER PROFILE profile LIMIT
password_parameters ... ;
DROP PROFILE
Drop a profile.
DROP PROFILE profile;
CREATE USER … PROFILE
Create a user and set its profile at the same time.
CREATE USER user PROFILE profile;
ALTER USER … PROFILE
Modify the profile for a user.
ALTER USER user PROFILE profile;
CREATE USER … ENABLE/DISABLE PROFILE
Create a user and specify whether the user is allowed to use a profile. ENABLE PROFILE allows it, and DISABLE PROFILE disallows it. By default, a newly created user is not allowed to use a profile.
CREATE USER user
{ ENABLE | DISABLE }
PROFILE;
ALTER USER … ENABLE/DISABLE PROFILE
Modify whether a user is allowed to use a profile.
ALTER USER user
{ ENABLE | DISABLE }
PROFILE;
CREATE USER … ACCOUNT LOCK/UNLOCK
Create a user and specify whether the user account is locked. ACCOUNT LOCK locks the user account, preventing login. ACCOUNT UNLOCK unlocks it, allowing normal login. By default, a newly created user account is not locked.
CREATE USER user ACCOUNT
{ LOCK | UNLOCK };
ALTER USER … ACCOUNT LOCK/UNLOCK
Modify whether a user account is locked.
ALTER USER user ACCOUNT
{ LOCK | UNLOCK };
View password policy information in system tables
After applying a profile, SynxDB makes some changes to the database metadata. Specifically, it adds two new system tables, pg_profile and pg_password_history, and adds some columns to the pg_authid and pg_roles system tables/views. For example:
pg_catalog.pg_roles
In
pg_roles, the columnsrolprofile,rolaccountstatus, androlfailedloginsare added to record the database user to which the profile is applied, the account status, and the number of failed login attempts, respectively.1View "pg_catalog.pg_roles" 2 Column | Type | Collation | Nullable | Default | Storage | Description 3-------------------+--------------------------+-----------+----------+---------+----------+------------- 4rolname | name | | | | plain | 5rolsuper | boolean | | | | plain | 6rolinherit | boolean | | | | plain | 7rolcreaterole | boolean | | | | plain | 8rolcreatedb | boolean | | | | plain | 9rolcanlogin | boolean | | | | plain | 10rolreplication | boolean | | | | plain | 11rolconnlimit | integer | | | | plain | 12rolprofile | name | | | | plain | 13rolaccountstatus | smallint | | | | plain | 14rolfailedlogins | integer | | | | plain | 15rolpassword | text | | | | extended | 16rolvaliduntil | timestamp with time zone | | | | plain | 17rolbypassrls | boolean | | | | plain | 18rolconfig | text[] | C | | | extended | 19rolresqueue | oid | | | | plain | 20oid | oid | | | | plain | 21rolcreaterextgpfd | boolean | | | | plain | 22rolcreaterexthttp | boolean | | | | plain | 23rolcreatewextgpfd | boolean | | | | plain | 24rolresgroup | oid | | | | plain |
pg_catalog.pg_authid
In
pg_authid, the columnsrolprofile,rolaccountstatus,rolfailedlogins,rolpasswordsetat,rollockdate, androlpasswordexpireare added to record the database user to which the profile is applied, the account status, the number of failed login attempts, the time the password was set, the time the account was locked, and the password expiration time, respectively.1Table "pg_catalog.pg_authid" 2 Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description 3-------------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+------------- 4oid | oid | | not null | | plain | | | 5rolname | name | | not null | | plain | | | 6rolsuper | boolean | | not null | | plain | | | 7rolinherit | boolean | | not null | | plain | | | 8rolcreaterole | boolean | | not null | | plain | | | 9rolcreatedb | boolean | | not null | | plain | | | 10rolcanlogin | boolean | | not null | | plain | | | 11rolreplication | boolean | | not null | | plain | | | 12rolbypassrls | boolean | | not null | | plain | | | 13rolconnlimit | integer | | not null | | plain | | | 14rolenableprofile | boolean | | not null | | plain | | | 15rolpassword | text | C | | | extended | | | 16rolvaliduntil | timestamp with time zone | | | | plain | | | 17rolprofile | oid | | not null | | plain | | | 18rolaccountstatus | smallint | | not null | | plain | | | 19rolfailedlogins | integer | | not null | | plain | | | 20rolpasswordsetat | timestamp with time zone | | | | plain | | | 21rollockdate | timestamp with time zone | | | | plain | | | 22rolpasswordexpire | timestamp with time zone | | | | plain | | | 23rolresqueue | oid | | | | plain | | | 24rolcreaterextgpfd | boolean | | | | plain | | | 25rolcreaterexthttp | boolean | | | | plain | | | 26rolcreatewextgpfd | boolean | | | | plain | | | 27rolresgroup | oid | | | | plain | | | 28Indexes: 29 "pg_authid_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global" 30 "pg_authid_rolname_index" UNIQUE CONSTRAINT, btree (rolname), tablespace "pg_global" 31 "pg_authid_rolprofile_index" btree (rolprofile), tablespace "pg_global" 32 "pg_authid_rolresgroup_index" btree (rolresgroup), tablespace "pg_global" 33 "pg_authid_rolresqueue_index" btree (rolresqueue), tablespace "pg_global" 34Tablespace: "pg_global" 35Access method: heap
pg_catalog.pg_profile
The new
pg_profilesystem table is as follows:Table "pg_catalog.pg_profile" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description -------------------------+---------+-----------+----------+---------+---------+-------------+--------------+------------- oid | oid | | not null | | plain | | | prfname | name | | not null | | plain | | | prffailedloginattempts | integer | | not null | | plain | | | prfpasswordlocktime | integer | | not null | | plain | | | prfpasswordlifetime | integer | | not null | | plain | | | prfpasswordgracetime | integer | | not null | | plain | | | prfpasswordreusetime | integer | | not null | | plain | | | prfpasswordreusemax | integer | | not null | | plain | | | prfpasswordallowhashed | integer | | not null | | plain | | | prfpasswordverifyfuncdb | oid | | | | plain | | | prfpasswordverifyfunc | oid | | | | plain | | | Indexes: "profile_name_index" UNIQUE CONSTRAINT, btree (prfname), tablespace "pg_global" "profile_oid_index" UNIQUE CONSTRAINT, btree (oid), tablespace "pg_global" "profile_password_verify_function_index" btree (prfpasswordverifyfuncdb, prfpasswordverifyfunc), tablespace "pg_global" Tablespace: "pg_global" Access method: heap
Note
The columns in the
pg_profiletable are described as follows:oid- Uniquely identifies each profile record.prfname- The name of the profile.prffailedloginattempts- The number of allowed failed login attempts before the account is locked.prfpasswordlocktime- The duration of the password lock. If an account is locked due to failed login attempts, this field defines how long the lock lasts.prfpasswordreusemax- The number of new passwords that must be set before an old password can be reused.Other columns in this table are not yet effective.
pg_catalog.pg_password_history
Table "pg_catalog.pg_password_history" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description -----------------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+------------- passhistroleid | oid | | not null | | plain | | | passhistpasswordsetat | timestamp with time zone | | not null | | plain | | | passhistpassword | text | C | not null | | extended | | | Indexes: "pg_password_history_role_password_index" UNIQUE CONSTRAINT, btree (passhistroleid, passhistpassword), tablespace "pg_global" "pg_password_history_role_passwordsetat_index" btree (passhistroleid, passhistpasswordsetat), tablespace "pg_global" Tablespace: "pg_global" Access method: heap
Note
The columns in the
pg_password_historytable are described as follows:passhistroleid- The unique ID of the user or role associated with this password history record.passhistpasswordsetat- A timestamp with time zone field that records the exact time when the password was set or last modified.passhistpassword- Stores the ciphertext of the historical password.
Default password policy
When creating a new user without specifying a password policy, SynxDB applies the Default Profile to the user by default. The Default Profile is the default password policy generated during system initialization. In SynxDB, the Default Profile corresponds to the pg_default row in the pg_profile table. pg_default defines the default values for the profile parameters, and only superusers can update these limits.
If a user-defined profile has parameters with a value of -1 (which means using the default value), these parameters will take their values from pg_default. The default values for pg_default are shown below. For how to use the Default Profile, see Scenario 3: Use the settings of the default profile.
1\x
2Expanded display is on.
3
4-- Checks the values in the default password policy profile from pg_profile.
5SELECT * FROM pg_profile WHERE prfname = 'pg_default';
6-[ RECORD 1 ]-----------+-----------
7oid | 10140
8prfname | pg_default
9prffailedloginattempts | -2
10prfpasswordlocktime | -2
11prfpasswordlifetime | -2
12prfpasswordgracetime | -2
13prfpasswordreusetime | -2
14prfpasswordreusemax | -2
15prfpasswordallowhashed | 1
16prfpasswordverifyfuncdb |
17prfpasswordverifyfunc |
Attention
pg_default cannot be renamed or dropped by any user, including superusers.
Usage examples
This section provides examples of how to use profiles.
Create a password policy
First, you need to create a simple profile and bind a database user to it. For example:
-- Creates a profile and a user myuser.
CREATE PROFILE myprofile;
CREATE USER myuser PASSWORD 'mypassword';
-- Binds the profile to the user myuser.
ALTER USER myuser PROFILE myprofile;
-- Views the mapping between the user myuser and the profile.
SELECT rolname, rolprofile FROM pg_roles WHERE rolname = 'myuser';
rolname | rolprofile
---------+------------
myuser | myprofile
(1 row)
Scenario 1: Set the maximum number of failed login attempts and the password lock time
Modify the profile to set the maximum number of failed login attempts to 3 and the password lock time to 2 hours.
Attention
When multiple users fail to log in, the response speed might slow down.
ALTER PROFILE myprofile LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 2;
-- Allows the user myuser to use the profile.
ALTER USER myuser ENABLE PROFILE;
-- Views the details in the catalog table (pg_profile is the catalog table that stores all details related to user profiles).
-- Note that the time displayed here is in hours.
SELECT prfname, prffailedloginattempts, prfpasswordlocktime
FROM pg_profile
WHERE prfname = 'myprofile';
prfname | prffailedloginattempts | prfpasswordlocktime
-----------+------------------------+---------------------
myprofile | 3 | 2
(1 row)
SELECT rolname, rolprofile, get_role_status('myuser'), rolfailedlogins, rollockdate
FROM pg_roles
WHERE rolname = 'myuser';
rolname | rolprofile | get_role_status | rolfailedlogins | rollockdate
---------+------------+-----------------+-----------------+-------------
myuser | myprofile | OPEN | 0 |
(1 row)
Because the user myuser has not yet failed to log in, querying the pg_roles system table shows that the user’s status is OPEN, and rolfailedlogins in the pg_roles system table is 0. Now, myuser attempts a failed login and executes the same query:
\c - myuser
Password for user myuser:
FATAL: password authentication failed for user "myuser"
Previous connection kept
SELECT rolname, rolprofile, get_role_status('myuser'), rolfailedlogins, rollockdate
FROM pg_roles
WHERE rolname = 'myuser';
rolname | rolprofile | get_role_status | rolfailedlogins | rollockdate
---------+------------+-----------------+-----------------+-------------
myuser | myprofile | OPEN | 1 |
(1 row)
The result above shows that the user status is still OPEN, but rolfailedlogins has increased to 1. If login attempts continue to fail, rolfailedlogins will continue to increase until the account is locked, as shown below:
\c - myuser
Password for user myuser:
FATAL: role "myuser" is locked
Previous connection kept
SELECT rolname, rolprofile, get_role_status('myuser'), rolfailedlogins, rollockdate
FROM pg_roles
WHERE rolname = 'myuser';
rolname | rolprofile | get_role_status | rolfailedlogins | rollockdate
---------+------------+-----------------+-----------------+----------------------------------
myuser | myprofile | LOCKED(TIMED) | 4 |13-MAR-23 12:25:50.811022 +08:00
(1 row)
Due to too many failed login attempts, the user account is locked. The user status has changed to LOCKED(TIMED), and the user account will be automatically unlocked after 2 hours (controlled by the PASSWORD_LOCK_TIME parameter).
The system also records the timestamp information of when the user account was locked. After the lock time expires, the user account status will change back to OPEN, and login will be allowed. If a user has a few failed login attempts that do not exceed the FAILED_LOGIN_ATTEMPTS limit and then successfully logs in, the system will reset rolfailedlogins to 0, as shown below:
\c - myuser
Password for user myuser:
You are now connected to database "postgres" as user "myuser".
SELECT rolname, rolprofile, get_role_status('myuser'), rolfailedlogins, rollockdate
FROM pg_roles
WHERE rolname = 'myuser';
rolname | rolprofile | get_role_status | rolfailedlogins | rollockdate
---------+------------+-----------------+-----------------+----------------------------------
myuser | myprofile | OPEN | 0 |13-MAR-23 12:25:50.811022 +08:00
(1 row)
Attention
If you manually set PASSWORD_LOCK_TIME to 0, the user account will never be locked.
Scenario 2: Set the number of historical password reuses
By using the PASSWORD_REUSE_MAX parameter, you can prevent users from setting recently used passwords. Suppose you want to prevent users from using the last two historical passwords. You can use this parameter in the ALTER PROFILE command to make this change, as shown in the example below:
ALTER PROFILE myprofile LIMIT
PASSWORD_REUSE_MAX 2;
-- Check the catalog table, and you will find that the number of historical password reuses has been set to two.
SELECT prfname, prfpasswordreusemax
FROM pg_profile
WHERE prfname = 'myprofile';
prfname | prfpasswordreusemax
-----------+---------------------
myprofile | 2
(1 row)
ALTER USER myuser PASSWORD 'mynewpassword';
ALTER USER myuser PASSWORD 'mypassword';
ERROR: The new password should not be the same with latest 2 history password
The result shows that because mypassword has been used before being set as the new password, the system does not allow mypassword to be reused. To set a new password, you must ensure that two different passwords have been set before the target password, as shown below:
ALTER USER myuser PASSWORD 'mypassword2'; -- Second password change
ALTER USER myuser PASSWORD 'mypassword';
Attention
If PASSWORD_REUSE_MAX is set to 0, the password can never be changed. If it is set to -2 (UNLIMITED), a historical password can be reused only after 9999 new passwords have been set.
Scenario 3: Use the settings of the default profile
When creating a new profile without explicitly specifying parameter values, the corresponding parameter values for that profile in the pg_profile table will be -1, which means SynxDB will get the actual values for these parameters from pg_default.
The following example uses FAILED_LOGIN_ATTEMPTS:
-- Creates a profile without explicitly specifying any parameter values.
CREATE PROFILE myprf;
-- Views the default parameter values of myprf in pg_profile.
SELECT * FROM pg_profile WHERE prfname = 'myprf';
-[ RECORD 1 ]-----------+------
oid | 16386
prfname | myprf
prffailedloginattempts | -1
prfpasswordlocktime | -1
prfpasswordlifetime | -1
prfpasswordgracetime | -1
prfpasswordreusetime | -1
prfpasswordreusemax | -1
prfpasswordallowhashed | -1
prfpasswordverifyfuncdb |
prfpasswordverifyfunc |
The example above creates a new profile named myprf. Because no parameter values were explicitly specified in the creation statement, all parameters are set to -1, which means any user bound to this profile will use the parameter values from pg_default.
Next, set the FAILED_LOGIN_ATTEMPTS of pg_default to 1 and create a user for testing, as shown in the example below:
-- Sets the default value of FAILED_LOGIN_ATTEMPTS in pg_default to 1.
-- All users without a specified profile will have their accounts locked after one failed login attempt.
ALTER PROFILE pg_default LIMIT FAILED_LOGIN_ATTEMPTS 1;
\x
Expanded display is on.
-- Views the default values of pg_default in pg_profile.
SELECT * FROM pg_profile WHERE prfname = 'pg_default';
-[ RECORD 1 ]-----------+-----------
oid | 10140
prfname | pg_default
prffailedloginattempts | 1
prfpasswordlocktime | -2
prfpasswordlifetime | -2
prfpasswordgracetime | -2
prfpasswordreusetime | -2
prfpasswordreusemax | -2
prfpasswordallowhashed | 1
prfpasswordverifyfuncdb |
prfpasswordverifyfunc |
CREATE USER mynewuser PASSWORD 'mynewpassword' ENABLE PROFILE;
SELECT rolname, rolprofile, get_role_status('mynewuser'), rolfailedlogins, rollockdate
FROM pg_roles
WHERE rolname = 'mynewuser';
rolname | rolprofile | get_role_status | rolfailedlogins | rollockdate
-----------+------------+-----------------+-----------------+-------------
mynewuser | pg_default | OPEN | 0 |
(1 row)
You can see that the user’s profile is pg_default and the user account status is OPEN. Next, try to log in to the account with an incorrect password.
\c - mynewuser
Password for user mynewuser:
FATAL: password authentication failed for user "mynewuser"
Previous connection kept
SELECT rolname, rolprofile, get_role_status('mynewuser'), rolfailedlogins, rollockdate
FROM pg_roles
WHERE rolname = 'mynewuser';
rolname | rolprofile | get_role_status | rolfailedlogins | rollockdate
-----------+------------+---------------------+-----------------+----------------------------------
mynewuser | pg_default | LOCKED(TIMED) | 1 | 12-MAR-23 09:36:42.132231 +08:00
(1 row)
Because FAILED_LOGIN_ATTEMPTS for pg_default is 1, the user’s account was locked after one failed login attempt.
Next, we will set the user’s profile to myprf and then test the same operation to observe the results. Before the test, the user must be unlocked, as shown below:
ALTER USER mynewuser ACCOUNT unlock PROFILE myprf;
SELECT rolname, rolprofile, get_role_status('mynewuser'), rolfailedlogins, rollockdate
FROM pg_roles
WHERE rolname = 'mynewuser';
rolname | rolprofile | get_role_status | rolfailedlogins | rollockdate
-----------+------------+---------------------+-----------------+----------------------------------
mynewuser | myprf | OPEN | 0 | 12-MAR-23 09:36:42.132231 +08:00
(1 row)
The result shows that the user status has changed back to OPEN, and the profile has changed to myprf. rollockdate still exists, which is expected. Next, try to log in again with an incorrect password and observe the result.
\c - mynewuser
Password for user mynewuser:
FATAL: password authentication failed for user "mynewuser"
Previous connection kept
SELECT rolname, rolprofile, get_role_status('mynewuser'), rolfailedlogins, rollockdate
FROM pg_roles
WHERE rolname = 'mynewuser';
rolname | rolprofile | get_role_status | rolfailedlogins | rollockdate
-----------+------------+---------------------+-----------------+----------------------------------
mynewuser | myprf | LOCKED(TIMED) | 1 | 12-MAR-23 09:38:26.753832 +08:00
(1 row)
As expected, the user account was locked again.
Scenario 4: Superuser locks or unlocks user accounts
A superuser can explicitly lock or unlock user accounts, as shown below:
ALTER USER myuser ACCOUNT LOCK;
SELECT rolname, rolprofile, get_role_status('myuser'), rolfailedlogins, rollockdate
FROM pg_roles
WHERE rolname = 'myuser';
rolname | rolprofile | get_role_status | rolfailedlogins | rollockdate
---------+------------+---------------------+-----------------+----------------------------------
myuser | myprofile | LOCKED | 0 | 13-MAR-23 12:25:50.811022 +08:00
(1 row)
ALTER USER myuser ACCOUNT UNLOCK;
SELECT rolname, rolprofile, get_role_status('myuser'), rolfailedlogins, rollockdate
FROM pg_roles
WHERE rolname = 'myuser';
rolname | rolprofile | get_role_status | rolfailedlogins | rollockdate
---------+------------+---------------------+-----------------+----------------------------------
myuser | myprofile | OPEN | 0 | 13-MAR-23 13:23:50.83928 +08:00
(1 row)
Scenario 5: Enable profile for regular users
By default, all newly created regular users do not use the Profile feature. To use a profile, it must be explicitly enabled, as shown below:
CREATE USER myuser1;
SELECT rolname, rolprofile, get_role_status('myuser1'), rolfailedlogins, rollockdate, rolenableprofile
FROM pg_roles
WHERE rolname like 'myuser1';
rolname | rolprofile | get_role_status | rolfailedlogins | rollockdate | rolenableprofile
---------+------------+-----------------+-----------------+-------------+------------------
myuser1 | pg_default | OPEN | 0 | | f
(1 row)
CREATE USER myuser2 ENABLE PROFILE;
SELECT rolname, rolprofile, get_role_status('myuser2'), rolfailedlogins, rollockdate, rolenableprofile
FROM pg_roles
WHERE rolname = 'myuser2';
rolname | rolprofile | get_role_status | rolfailedlogins | rollockdate | rolenableprofile
---------+------------+-----------------+-----------------+-------------+------------------
myuser2 | pg_default | OPEN | 0 | | t
(1 row)
CREATE USER myuser3 DISABLE PROFILE;
SELECT rolname, rolprofile, get_role_status('myuser3'), rolfailedlogins, rollockdate, rolenableprofile
FROM pg_roles
WHERE rolname = 'myuser3';
rolname | rolprofile | get_role_status | rolfailedlogins | rollockdate | rolenableprofile
---------+------------+-----------------+-----------------+-------------+------------------
myuser3 | pg_default | OPEN | 0 | | f
(1 row)
After a user is created, you can use the ALTER USER ENABLE/DISABLE PROFILE statement to modify whether to use a profile.
ALTER USER myuser1 ENABLE PROFILE;
SELECT rolname, rolprofile, get_role_status('myuser1'), rolfailedlogins, rollockdate, rolenableprofile
FROM pg_roles
WHERE rolname = 'myuser1';
rolname | rolprofile | get_role_status | rolfailedlogins | rollockdate | rolenableprofile
---------+------------+-----------------+-----------------+-------------+------------------
myuser1 | pg_default | OPEN | 0 | | t
(1 row)
ALTER USER myuser1 DISABLE PROFILE;
SELECT rolname, rolprofile, get_role_status('myuser1'), rolfailedlogins, rollockdate, rolenableprofile
FROM pg_roles
WHERE rolname = 'myuser1';
rolname | rolprofile | get_role_status | rolfailedlogins | rollockdate | rolenableprofile
---------+------------+-----------------+-----------------+-------------+------------------
myuser1 | pg_default | OPEN | 0 | | f
(1 row)