Help Center/ GaussDB(DWS)/ Standard Data Warehouse (8.1.3.x)/ Database Security Management/ Managing Users and Their Permissions/ User-defined Password Policy
Updated on 2023-10-31 GMT+08:00
View PDF

User-defined Password Policy

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

Default GaussDB(DWS) Password Policy

By default, GaussDB(DWS) verifies the password complexity (that is, the GUC parameter password_policy is set to 1 by default). 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 GaussDB(DWS)

Password complexity check

password_policy

Specifies whether to check the password complexity when a GaussDB(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 (password_min_special)

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_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_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.

1

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 of User-defined Password Policies

Example 1: Configure the password complexity parameter password_policy.

  1. Log in to the GaussDB(DWS) management console.
  2. In the navigation pane on the left, choose Clusters.
  3. In the cluster list, find the target cluster and click the cluster name. The Cluster Information page is displayed.
  4. Click the Parameters tab, change the value of password_policy, and click Save. The password_policy parameter takes effect immediately after being modified. You do not need to restart the cluster.
Figure 1 password_policy

Example 2: Configure password_effect_time for password validity period.

  1. Log in to the GaussDB(DWS) management console.
  2. In the navigation pane on the left, choose Clusters.
  3. In the cluster list, find the target cluster and click the cluster name. The Cluster Information page is displayed.
  4. Click the Parameters tab, change the value of password_effect_time, and click Save. The modification of password_effect_time takes effect immediately. You do not need to restart the cluster.
    Figure 2 password_effect_time

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:

    The password must meet input requirements, 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:

  • 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:

    If the following information is displayed, the role setting has been modified:

    SET ROLE