Creating a Custom Password Policy for DWS

When creating or modifying a user, you need to specify a password. DWS has default password complexity requirements. You can also define database account password policies.

Default DWS Password Policy

By default, DWS verifies the password complexity. The default password policy requires that the password:

Contain 8 to 32 characters.

Contain at least three types of the following characters: uppercase letters, lowercase letters, digits, and special characters.
Cannot be the same as the user name or the user name in reverse order, case insensitive.
Cannot be the current password or the current password in reverse order.

User-defined Password Policy

The password policy includes the password complexity requirements, password validity period, password reuse settings, password encryption mode, and password retry and lock policies. Different policy items are controlled by the corresponding GUC parameters. For details, see Security and Authentication (postgresql.conf).

**Table 1** User-defined password policies and corresponding GUC parameters
Password Policy	Parameter	Description	Value Range	Default Value in DWS
Password complexity check	password_policy	Specifies whether to check the password complexity when a DWS account is created or modified.	Integer, 0 or 1 0 indicates that no password complexity policy is used. Setting this parameter to 0 leads to security risks. You are advised not to set this parameter to 0. 1 indicates that the default password complexity policy is used.	1
Password complexity requirement	password_min_length	Specifies the minimum password length.	An integer ranging from 6 to 999	8
	password_max_length	Specifies the maximum password length.	An integer ranging from 6 to 999	32
	password_min_uppercase	Minimum number of uppercase letters (A-Z)	An integer ranging from 0 to 999 0 means no requirements. 1-999 indicates the minimum number of uppercase letters in the password.	0
	password_min_lowercase	Minimum number of lowercase letters (a-z)	An integer ranging from 0 to 999 0 means no requirements. 1-999 indicates the minimum number of lower letters in the password.	0
	password_min_digital	Minimum number of digits (0-9)	An integer ranging from 0 to 999 0 means no requirements. 1-999 indicates the minimum number of digits in the password.	0
	password_min_special	Minimum number of special characters (Table 2 lists the special characters.)	An integer ranging from 0 to 999 0 means no requirements. 1-999 indicates the minimum number of special characters in the password.	0
Password validity	password_effect_time	Password validity period When the number of days in advance a user is notified that the password is about to expire reaches the value of password_notify_time, the system prompts the user to change the password when the user logs in to the database.	The value is a floating point number ranging from 0 to 999. The unit is day. 0 indicates the validity period is disabled. A floating point number from 1 to 999 indicates the validity period of the password. When the password is about to expire or has expired, the system prompts the user to change the password.	90
Password validity	password_notify_time	Specifies for how many days you are reminded of the password expiry.	The value is an integer ranging from 0 to 999. The unit is day. 0 indicates the reminder is disabled. A value ranging from 1 to 999 indicates the number of days prior to password expiration that a user will receive a notification.	7
Password reuse settings	password_reuse_time	Specifies the number of days after which the password cannot be reused.	A Floating point number ranging from 0 to 3650. The unit is day. 0 indicates that the password reuse days are not checked. A positive number indicates that the new password cannot be chosen from passwords in history that are newer than the specified number of days.	60
Password reuse settings	password_reuse_max	Specifies the number of the most recent passwords that the new password cannot be chosen from.	An integer ranging from 0 to 1000 0 indicates that the password reuse times are not checked. A positive number indicates that the new password cannot be chosen from the specified number of the most recent passwords.	0
Encryption mode	password_encryption_type	Specifies the password storage encryption mode.	0, 1, 2 0 indicates that passwords are encrypted in MD5 mode. The password is encrypted using MD5. This mode is not recommended for users. 1 indicates that passwords are encrypted with SHA-256, which is compatible with the MD5 user authentication method of the PostgreSQL client. The password is stored in ciphertext encrypted by MD5 and SHA256. 2 indicates that passwords are encrypted using SHA-256. The password is encrypted using SHA256.	1
Retry and lock	password_lock_time	Specifies the duration for a locked account to be automatically unlocked.	A Floating point number ranging from 0 to 365. The unit is day. 0 indicates that the account is not automatically locked if the password verification fails. A positive number indicates the duration after which a locked account is automatically unlocked. NOTE: The integral part of the value of the password_lock_time parameter indicates the number of days and its decimal part can be converted into hours, minutes, and seconds.	1
Retry and lock	failed_login_attempts	If the number of incorrect password attempts reaches the value of failed_login_attempts, the account is locked and will be automatically unlocked in X (which indicates the value of password_lock_time) seconds.	An integer ranging from 0 to 1000 0 indicates that the automatic locking function does not take effect. A positive number indicates that an account is locked when the number of incorrect password attempts reaches the value of failed_login_attempts.	10

**Table 2** Special characters
No.	Character	No.	Character	No.	Character	No.	Character
1	~	9	*	17	\|	25	<
2	!	10	(	18	[	26	.
3	@	11	)	19	{	27	>
4	#	12	-	20	}	28	/
5	$	13	_	21	]	29	?
6	%	14	=	22	;	-	-
7	^	15	+	23	:	-	-
8	&	16	\	24	,	-	-

Example 2: Configure password_effect_time.

Run the following command to connect to the database:

gsql -d postgres -p 25308

postgres indicates the name of the database you want to connect. 25308 indicates the CN port.

If information similar to the following is displayed, the connection succeeds:

gsql ((GaussDB x.x.x build 39137c2d) compiled at 2022-04-01 15:43:11 commit 3629 last mr 5138 release)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=#

View the configured parameter.

      
           SHOW password_effect_time;
 password_effect_time
----------------------
 90
(1 row)

If the command output is not 90, run the \q command to exit the database.

If 90 is not displayed, run the following command to set the parameter to 90 (0 is not recommended):
```
gs_guc reload -Z coordinator -Z datanode -N all -I all -c "password_effect_time = 90"
```

Setting and Changing a Password

Both system administrators and common users need to periodically change their passwords to prevent the accounts from being stolen.

For example, to change the password of the user user1, connect to the database as the administrator and run the following command:

       
            ALTER USER user1 IDENTIFIED BY 'newpassword' REPLACE 'oldpassword';

The password must meet requirements in Default DWS Password Policy, or the execution will fail.

An administrator can change its own password and other accounts' passwords. With the permission for changing other accounts' passwords, the administrator can resolve a login failure when a user forgets its password.
To change the password of the user joe, run the following command:
1

ALTER USER joe IDENTIFIED BY 'password';

System administrators are not allowed to change passwords for each other.
When a system administrator changes the password of a common user, the original password is not required.
However, when a system administrator changes its own password, the original password is required.

Password verification
Password verification is required when you set the user or role in the current session. If the entered password is inconsistent with the stored password of the user, an error is reported.

To set the password of the user joe, run the following command:
1

SET ROLE joe PASSWORD 'password';
If the following information is displayed, the role setting has been modified:
```
SET ROLE
```