Set password policy in Cloudberry Database (New in v1.5.0)
Profile refers to the password policy configuration, which is used to control the password security policy of users in Cloudberry Database. You can bind a profile to one or more users to control the password security policy of database users. Profile defines the rules for user management and password reuse. With Profile, the database administrator can use SQL to force some constraints, such as locking accounts after login failures or controlling the number of password reuses.
- In general, Profile includes password policy and user resource usage restrictions. Profile in Cloudberry Database only supports password policy. "Profile" mentioned in this document refers to password policy configuration.
- Only superusers can create or modify Profile policies, and superusers are not restricted by any Profile policies. Profile policies will take effect only when regular users are allowed to use Profile.
The Profile feature is enabled by default. You can set the enable_password_profile
parameter in the postgresql.conf
file to disable or enable Profile. The default value of enable_password_profile
is true
.
To disable Profile and the related login monitor process, you can set enable_password_profile
to false
and restart the cluster:
gpconfig -c enable_password_profile -v false
gpstop -ra
# To re-enable Profile, execute _gpconfig -c enable_password_profile -v true_ and restart the cluster.
Implementation principle
Similar to the Autovacuum mechanism, Profile introduces the Login Monitor Launcher and Login Monitor Worker processes. When user login verification fails, Cloudberry Database will send a signal to the postmaster. After receiving the signal, the postmaster will send a signal to the launcher process. After receiving the signal, the launcher process will notify the postmaster to launch a worker process to perform the metadata write-back operation, and notify the user process and the launcher process after completion.
Set password policies using SQL
Database administrators can use SQL to set Profile. The following parameters are commonly used.
Parameter | Description |
---|---|
FAILED_LOGIN_ATTEMPTS | • The maximum number of failed logins before the user account is locked. • Valid values include -2 (unlimited), -1 (default), and 1 to 9999 .0 is an invalid value. |
PASSWORD_LOCK_TIME | • The lock time (in hours) after multiple consecutive failed login attempts. • Valid values are -2 to 9999 . 0 is a valid value. |
PASSWORD_REUSE_MAX | • The number of historical password reuses. • Valid values are -2 to 9999 . 0 is a valid value. |
CREATE PROFILE
Creates a profile and sets its password policy.
CREATE PROFILE profile LIMIT
password_parameters ... ;
password_parameters:
{ { FAILED_LOGIN_ATTEMPTS
| PASSWORD_REUSE_MAX
| PASSWORD_LOCK_TIME
}
expr
}
ALTER PROFILE
Modifies a password policy.
ALTER PROFILE profile LIMIT
password_parameters ... ;
DROP PROFILE
Deletes a profile.
DROP PROFILE profile;
CREATE USER ... PROFILE
Creates a user and sets its profile.
CREATE USER user PROFILE profile;
ALTER USER ... PROFILE
Modifies the profile of user
.
ALTER USER user PROFILE profile;
CREATE USER ... ENABLE/DISABLE PROFILE
Creates a user and specifies their permission to use Profile. ENABLE PROFILE
grants permission, while DISABLE PROFILE
denies it. By default, newly created users are not permitted to use Profile.
CREATE USER user
{ ENABLE | DISABLE }
PROFILE;
ALTER USER ... ENABLE/DISABLE PROFILE
Sets whether a user is allowed to use Profile.
ALTER USER user
{ ENABLE | DISABLE }
PROFILE;
CREATE USER ... ACCOUNT LOCK/UNLOCK
Creates a user and sets whether the user is locked. ACCOUNT LOCK
means that the user is locked and cannot log in. ACCOUNT UNLOCK
means that the user is not locked and can log in. The default created user is not locked.
CREATE USER user ACCOUNT
{ LOCK | UNLOCK };
ALTER USER ... ACCOUNT LOCK/UNLOCK
Sets whether to lock the account of a user.
ALTER USER user ACCOUNT
{ LOCK | UNLOCK };
Check password policy information in system tables
After applying the password configuration policy, Cloudberry Database will update metadata: add two system tables pg_profile
and pg_password_history
, and add some fields to the system tables/views pg_authid
and pg_roles
. For example:
-
pg_catalog.pg_roles: In
pg_roles
, therolprofile
,rolaccountstatus
, androlfailedlogins
fields are added to record database users who use Profile, the account status, and the number of failed logins.View "pg_catalog.pg_roles"
Column | Type | Collation | Nullable | Default | Storage | Description
-------------------+--------------------------+-----------+----------+---------+----------+-------------
rolname | name | | | | plain |
rolsuper | boolean | | | | plain |
rolinherit | boolean | | | | plain |
rolcreaterole | boolean | | | | plain |
rolcreatedb | boolean | | | | plain |
rolcanlogin | boolean | | | | plain |
rolreplication | boolean | | | | plain |
rolconnlimit | integer | | | | plain |
rolprofile | name | | | | plain |
rolaccountstatus | smallint | | | | plain |
rolfailedlogins | integer | | | | plain |
rolpassword | text | | | | extended |
rolvaliduntil | timestamp with time zone | | | | plain |
rolbypassrls | boolean | | | | plain |
rolconfig | text[] | C | | | extended |
rolresqueue | oid | | | | plain |
oid | oid | | | | plain |
rolcreaterextgpfd | boolean | | | | plain |
rolcreaterexthttp | boolean | | | | plain |
rolcreatewextgpfd | boolean | | | | plain |
rolresgroup | oid | | | | plain | -
pg_catalog.pg_authid: In
pg_authid
, therolprofile
,rolaccountstatus
,rolfailedlogins
,rolpasswordsetat
,rollockdata
, androlpasswordexpire
are added to record the database users who use Profile, the account status, the number of failed logins, password setting time, account lock time, and password expiration time.Table "pg_catalog.pg_authid"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
oid | oid | | not null | | plain | | |
rolname | name | | not null | | plain | | |
rolsuper | boolean | | not null | | plain | | |
rolinherit | boolean | | not null | | plain | | |
rolcreaterole | boolean | | not null | | plain | | |
rolcreatedb | boolean | | not null | | plain | | |
rolcanlogin | boolean | | not null | | plain | | |
rolreplication | boolean | | not null | | plain | | |
rolbypassrls | boolean | | not null | | plain | | |
rolconnlimit | integer | | not null | | plain | | |
rolenableprofile | boolean | | not null | | plain | | |
rolpassword | text | C | | | extended | | |
rolvaliduntil | timestamp with time zone | | | | plain | | |
rolprofile | oid | | not null | | plain | | |
rolaccountstatus | smallint | | not null | | plain | | |
rolfailedlogins | integer | | not null | | plain | | |
rolpasswordsetat | timestamp with time zone | | | | plain | | |
rollockdate | timestamp with time zone | | | | plain | | |
rolpasswordexpire | timestamp with time zone | | | | plain | | |
rolresqueue | oid | | | | plain | | |
rolcreaterextgpfd | boolean | | | | plain | | |
rolcreaterexthttp | boolean | | | | plain | | |
rolcreatewextgpfd | boolean | | | | plain | | |
rolresgroup | oid | | | | plain | | |
Indexes:
"pg_authid_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
"pg_authid_rolname_index" UNIQUE CONSTRAINT, btree (rolname), tablespace "pg_global"
"pg_authid_rolprofile_index" btree (rolprofile), tablespace "pg_global"
"pg_authid_rolresgroup_index" btree (rolresgroup), tablespace "pg_global"
"pg_authid_rolresqueue_index" btree (rolresqueue), tablespace "pg_global"
Tablespace: "pg_global"
Access method: heap -
pg_catalog.pg_profile
The newly added
pg_profile
system 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: heapinfoThe fields in the
pg_profile
table are described as follows:oid
: used to uniquely identify each profile record.prfname
: the name of the configuration file.prffailedloginattempts
: the number of failed login attempts allowed before an account is locked.prfpasswordlocktime
: the password lock time. 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 the old password can be reused.- Other fields in the table are not valid.
-
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: heapinfoThe fields in the
pg_password_history
table are described as follows:passhistroleid
: a unique ID that identifies the user or role related to this password history.passhistpasswordsetat
: a timestamp field with the time zone that records the exact time when the password was set or last modified.Passhistpassword
: the ciphertext that stores historical passwords.
Default password policy
When you create a user, Cloudberry Database applies the default Profile to the user by default if no specific password policy is specified. The default Profile is the default password policy during system initialization. The default Profile in Cloudberry Database is the pg_default
row in the pg_profile
table. The pg_default
row defines default values for the Profile parameters, and only superusers can modify these parameters.
If a user sets a parameter with the default value -1
, the parameter will get its value from pg_default
. The default values of pg_default
are as follows. Refer to Scenario 3 for how to use the default Profile.
\x
Expanded display is on.
-- Checks the values of the default Profile in pg_profile
SELECT * FROM pg_profile WHERE prfname = 'pg_default';
-[ RECORD 1 ]-----------+-----------
oid | 10140
prfname | pg_default
prffailedloginattempts | -2
prfpasswordlocktime | -2
prfpasswordlifetime | -2
prfpasswordgracetime | -2
prfpasswordreusetime | -2
prfpasswordreusemax | -2
prfpasswordallowhashed | 1
prfpasswordverifyfuncdb |
prfpasswordverifyfunc |
The pg_default
row cannot be renamed or dropped by any user (including superusers).