Here in this blog, I’ll explain some new parameters for password profiles like PASSWORD_REUSE_TIME, PASSWORD_REUSE_MAX, and PASSWORD_ALLOW_HASHED (Added in v11). In the end, we will touch upon the DEFAULT profile.
PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX
To restrict the user from setting a password that is either the same as that of the last few passwords or that has been used too recently, PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX are used. PASSWORD_REUSE_TIME specifies the number of days a user must wait before reusing a password whereas PASSWORD_REUSE_MAX specifies the number of password changes that must occur before a password can be reused.
Let's assume you want to prevent the user from reusing the same password within three days and want to require the user to change the password at least two times before reusing the same password. The ALTER PROFILE command will look like:
ALTER PROFILE myprofile LIMIT
PASSWORD_REUSE_TIME 3
PASSWORD_REUSE_MAX 2;
-- Check the details in the catalog table
SELECT prfname, prfpasswordreusetime, prfpasswordreusemax
FROM edb_profile WHERE prfname = 'myprofile';
prfname | prfpasswordreusetime | prfpasswordreusemax
-----------+----------------------+---------------------
myprofile | 259200 | 2
(1 row)
Let's get back to the original password and — for simplicity’s sake — let's clear the password verify function.
ALTER USER myuser IDENTIFIED BY mypassword REPLACE mynewpassword;
ALTER PROFILE myprofile LIMIT PASSWORD_VERIFY_FUNCTION null;
Now, if a user myuser tries to change the password to mypassword again, (s)he won't be allowed to do so.
ALTER USER myuser IDENTIFIED BY mypassword;
ERROR: password cannot be reused
DETAIL: The password_reuse_time constraint failed.
DETAIL in the error message above says that the PASSWORD_REUSE_TIME constraint has failed. That's because we have set the PASSWORD_REUSE_TIME to three days, which means for the next three days the same password cannot be reused.
So let's change it to a different password:
ALTER USER myuser IDENTIFIED BY mypassword1; -- First password change
This does work. However, even after three days, we cannot change the password back to mypassword. Try it and see what error message you get.
ALTER USER myuser IDENTIFIED BY mypassword;
ERROR: password cannot be reused
DETAIL: The password_reuse_max constraint failed.
The DETAIL in the error message above says that PASSWORD_REUSE_MAX constraint is failed. Remember that we need at least two different password changes before we can go back and reuse the older one since we have set this parameter to 2 in the profile. So let’s test that.
ALTER USER myuser IDENTIFIED BY mypassword2; -- Second password change
ALTER USER myuser IDENTIFIED BY mypassword;
The above two commands work as expected. We now can reuse the mypassword password as two password changes have successfully been made.
Note that the PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX parameters are intended to be used together. If you specify a finite value for one of these parameters while the other value is UNLIMITED, old passwords can never be reused. If both parameters are set to UNLIMITED, there are no restrictions on password reuse (i.e., password reuse constraints are disabled).
PASSWORD_ALLOW_HASHED
This new parameter is introduced in EDB Postgres Advanced Server (EPAS) v11.
PASSWORD_ALLOW_HASHED specifies whether an encrypted password is allowed to be used or not. If true, then the system allows the user to change passwords by specifying a hash computed (encrypted password) on the client side, or else it generates an error message if the user attempts to do this. Thus, when false, the password must be specified in plaintext, so it can be effectively validated by the remainder of the password validation machinery.
So, let's test this. First set the value to FALSE and then provide a hashed (md5 encrypted) password.
ALTER PROFILE myprofile LIMIT
PASSWORD_ALLOW_HASHED false;
-- Check the details in the catalog table
SELECT prfname, prfpasswordallowhashed::bool
FROM edb_profile WHERE prfname = 'myprofile';
prfname | prfpasswordallowhashed
-----------+------------------------
myprofile | f
(1 row)
ALTER USER myuser IDENTIFIED BY md589333188e3fec5107b4b73377453f459;
ERROR: must specify password in plaintext format
DETAIL: The current user's password profile does not permit specifying an encrypted password.
HINT: The profile's PASSWORD_ALLOW_HASHED setting can be changed to true to allow this.
As expected, we got an error because we don't allow hashed passwords. So, now change the setting to TRUE and try altering the password with a hashed one.
ALTER PROFILE myprofile LIMIT
PASSWORD_ALLOW_HASHED true;
ALTER USER myuser IDENTIFIED BY md589333188e3fec5107b4b73377453f459;
This works just fine.
DEFAULT Profile
The DEFAULT profile is a system-generated profile that is associated with a new role if that role has no profile assigned explicitly. DEFAULT profile defines the default limits to all the parameters. Only the superuser can alter these limits. Since DEFAULT profile is assigned implicitly to the role, that role is bound to follow the rules set by it. For example, if FAILED_LOGIN_ATTEMPTS is set to one, then every user with the DEFAULT profile will be locked out on his first invalid login attempt. Also, if you create a new profile you can set any of its profile parameters to DEFAULT which means that it will use the corresponding parameter value from the DEFAULT profile. By default, a newly created profile's parameter values are set to DEFAULT unless explicitly specified.
Note that neither you nor the superuser can drop the DEFAULT profile or rename it.
Also, to view the profiles and their parameter limits, you can use the DBA_PROFILES view, which is accessible only to the superuser.
Let's see a few examples of the DEFAULT profile and check its limits using the DBA_PROFILES:
-- ALTER DEFAULT profile to have FAILED_LOGIN_ATTEMPTS set to 1
ALTER PROFILE default LIMIT FAILED_LOGIN_ATTEMPTS 1;
-- Check the values in DEFAULT profile using DBA_PROFILES
SELECT * FROM DBA_PROFILES WHERE profile = 'DEFAULT';
profile | resource_name | resource_type | limit | common
---------+--------------------------+---------------+-----------+--------
DEFAULT | FAILED_LOGIN_ATTEMPTS | PASSWORD | 1 | NO
DEFAULT | PASSWORD_ALLOW_HASHED | PASSWORD | YES | NO
DEFAULT | PASSWORD_GRACE_TIME | PASSWORD | UNLIMITED | NO
DEFAULT | PASSWORD_LIFE_TIME | PASSWORD | UNLIMITED | NO
DEFAULT | PASSWORD_LOCK_TIME | PASSWORD | UNLIMITED | NO
DEFAULT | PASSWORD_REUSE_MAX | PASSWORD | UNLIMITED | NO
DEFAULT | PASSWORD_REUSE_TIME | PASSWORD | UNLIMITED | NO
DEFAULT | PASSWORD_VERIFY_FUNCTION | PASSWORD | NULL | NO
(8 rows)
-- Create a profile with no limits specified to any of the parameters and then see the values
CREATE PROFILE myprf;
SELECT * FROM DBA_PROFILES WHERE profile = 'MYPRF';
profile | resource_name | resource_type | limit | common
---------+--------------------------+---------------+---------+--------
MYPRF | FAILED_LOGIN_ATTEMPTS | PASSWORD | DEFAULT | YES
MYPRF | PASSWORD_ALLOW_HASHED | PASSWORD | DEFAULT | YES
MYPRF | PASSWORD_GRACE_TIME | PASSWORD | DEFAULT | YES
MYPRF | PASSWORD_LIFE_TIME | PASSWORD | DEFAULT | YES
MYPRF | PASSWORD_LOCK_TIME | PASSWORD | DEFAULT | YES
MYPRF | PASSWORD_REUSE_MAX | PASSWORD | DEFAULT | YES
MYPRF | PASSWORD_REUSE_TIME | PASSWORD | DEFAULT | YES
MYPRF | PASSWORD_VERIFY_FUNCTION | PASSWORD | DEFAULT | YES
(8 rows)
In the above newly created profile, all of the parameters are set to DEFAULT since no explicit limits have been specified. This means that any user associated with profile myprf will be using all DEFAULT values from the DEFAULT profile.
We have set FAILED_LOGIN_ATTEMPTS to 1, so let's test the user behavior associated with that. But, first, let's create another user who is associated with a default profile.
CREATE USER mynewuser IDENTIFIED BY mynewpassword;
SELECT rolname, rolprofile, edb_get_role_status(oid), rolfailedlogins, rollockdate
FROM pg_roles WHERE rolname = 'mynewuser';
rolname | rolprofile | edb_get_role_status | rolfailedlogins | rollockdate
-----------+------------+---------------------+-----------------+-------------
mynewuser | default | OPEN | 0 |
(1 row)
You can see that the profile associated with this new role is DEFAULT and role status is OPEN.
Now, try to log on using mynewuser with the wrong password.
\c - mynewuser
Password for user mynewuser:
FATAL: password authentication failed for user "mynewuser"
Previous connection kept
SELECT rolname, rolprofile, edb_get_role_status(oid), rolfailedlogins, rollockdate
FROM pg_roles WHERE rolname = 'mynewuser';
rolname | rolprofile | edb_get_role_status | rolfailedlogins | rollockdate
-----------+------------+---------------------+-----------------+----------------------------------
mynewuser | default | LOCKED(TIMED) | 0 | 25-NOV-18 09:39:37.356111 +05:30
(1 row)
Since FAILED_LOGIN_ATTEMPTS is set to 1, the user account is locked after the first invalid login. Now, let's see whether we get the same behavior with the myprf profile, which has all default values set to refer to the DEFAULT profile. However, before testing that, make sure to unlock this user account as we are going to use it again.
ALTER USER mynewuser ACCOUNT unlock PROFILE myprf;
SELECT rolname, rolprofile, edb_get_role_status(oid), rolfailedlogins, rollockdate
FROM pg_roles WHERE rolname = 'mynewuser';
rolname | rolprofile | edb_get_role_status | rolfailedlogins | rollockdate
-----------+------------+---------------------+-----------------+----------------------------------
mynewuser | myprf | OPEN | 0 | 25-NOV-18 09:39:37.356111 +05:30
(1 row)
So now you can see that the account status is back to OPEN and the user's profile has been changed to myprf. Note that, as we saw earlier, rollockdate is not cleared — and that's expected. So now try to log on with the invalid password again.
\c - mynewuser
Password for user mynewuser:
FATAL: password authentication failed for user "mynewuser"
Previous connection kept
SELECT rolname, rolprofile, edb_get_role_status(oid), rolfailedlogins, rollockdate
FROM pg_roles WHERE rolname = 'mynewuser';
rolname | rolprofile | edb_get_role_status | rolfailedlogins | rollockdate
-----------+------------+---------------------+-----------------+----------------------------------
mynewuser | myprf | LOCKED(TIMED) | 0 | 25-NOV-18 09:44:57.487991 +05:30
(1 row)
As expected, the account is locked on the first failed login.
Note that if the DEFAULT profile's parameter is altered then that new value will be applicable to all the other profiles whose parameter limit refers to the DEFAULT profile.
To conclude, by using profiles in EPAS v9.5 and above, you can quickly and easily manage your user's logins and passwords at a database level.