Product DocumentationAppearance
sys.password_policy
This system view has one row that represents the system-level policy, with additional rows present for each user who has had their policy overwritten. In this view, the users can see their own password policies however they cannot see the system-wide policies.
Password policies are a new feature that must be enabled by setting the enable_password_policy configuration parameter, after which a full database restart is required.
| Column Name | Data Type | Description |
|---|---|---|
| object id | oid | The ID (if any) of the object the policy applies to. For the SYSTEM policy, this is NULL. |
| user_name | name | Name of the user or role. For the SYSTEM policy, this is NULL. |
| object_type | text | Either SYSTEM for the system level policy or USER if the policy is set for a user. |
| min_length | smallint | Minimum length of the password in characters. |
| min_letters | smallint | Minimum number of letters in the password. |
| min_lower_case | smallint | Minimum number of lowercase letters in the password. If larger than min_letter, this takes precedence. |
| min_upper_case | smallint | Minimum number of uppercase letters in the password. |
| min_symbols | smallint | Minimum number of special characters that are not letters or digits. |
| min_digits | smallint | Minimum number of digits in the password. |
| max_reuse | integer | Maximum number of times a password can be reused by the user. If set to zero, any number of reuses are allowed. |
| max_attempts | integer | Maximum number of failed login attemps before the account is locked out. |
| unlock_after_s | integer | How many seconds until the account automatically unlocks if locked out? If set to zero, unlocking must be done manually. Note: This interval starts from the time of a successful login. |
| expire_user_after_s | integer | How long, in seconds, a user will be automatically expired after a successful login. |
| expire_password_after_s | integer | How long, in seconds, a password will be automatically expired after it was last set. |
Note: Existing users will be able to use their current passwords until they expire. After a password expires, the new password must comply with the policy. Yellowbrick does not verify passwords that are already in use when the new password policy is implemented.
The largest of min_length, min_letters, min_lower_case, min_upper_case, min_symbols and min_digits take precendence when determining the smallest allowed password.
Access Control
The following rules apply to the rows in this view:
superuserandsysadmincan read all rows.- Users with
CREATE USER,ALTER ANY USERorCONTROL ANY USERprivilege can read all rows withobject_type= USER
Data Retention
The system-level data in this table does not expire. If a user is deleted, the entry (if any) for that user in this view is deleted too.
Replication and Backup/Restore
The rows with object_type = USER are replicated or backed up if that user is backed up. The row with object_type = SYSTEM is not replicated. This means that the primary and secondary system can have different password policies.
See also: Password Policies for a general overview of the feature. enable_password_policy for enabling the feature.