Roles and profiles v18
The password_profile extension allows you to manage user roles and their associated profiles effectively. Each role can be assigned a specific profile that dictates the password management rules applicable to that role.
Attach role and profile
When a role is created, it can be associated with a password profile using the PG_ATTACH_ROLE_PROFILE function. This association ensures that the password management rules defined in the profile are enforced for the role.
PASSWORD_PROFILE.PG_ATTACH_ROLE_PROFILE('role_name', 'profile_name');
This function accepts following parameters:
role_name— Name of the role to which the profile is being attached.profile_name— Name of the password profile to attach to the role.
Detach role and profile
When a role is no longer needed or requires a different profile, it can be unassociated from its current profile using the PG_DETACH_ROLE_PROFILE function.
PASSWORD_PROFILE.PG_DETACH_ROLE_PROFILE('role_name');
This function accepts following parameters:
role_name— Name of the role from which the profile is being detached.
Role account status
You can check the status of a role's account by using the PG_GET_ROLE_STATUS function that is attached to the profile.
PASSWORD_PROFILE.PG_GET_ROLE_STATUS('role_name'); or PASSWORD_PROFILE.PG_GET_ROLE_STATUS('roleid');
This function accepts either parameter:
role_name— Name of the role whose account status is being queried.roleid— ID of the role whose account status is being queried.
Lock/unlock a role account
You can lock or unlock a role's account using the PG_ROLE_ACCOUNT_LOCK function. This action prevents the role from logging in until it is unlocked.
PASSWORD_PROFILE.PG_ROLE_ACCOUNT_LOCK('role_name', lock);
This function accepts following parameters:
role_name— Name of the role whose account is to be locked or unlocked.lock— If true, lock the role's account; if false, unlock the role's account.
Set a role password to expire
You can set a role's password to expire using the PG_ROLE_PASSWORD_EXPIRE function. This action forces the role to change its password upon the next login.
PASSWORD_PROFILE.PG_ROLE_PASSWORD_EXPIRE('role_name');
This function accepts following parameters:
role_name— Name of the role whose password is to be expired.