Managing Roles with Password Profiles: Part 1

What is a Password Profile?

Have you seen pop-up messages saying, "You are left with two more attempts" when you enter a wrong password? Also, have you seen a dialog box saying "Account locked due to too many failed logins"? Likewise, you might have also seen the alert to change your password because it is too old. And when you try to change your password, it won't allow you to change it to the previous one and wants a stronger password containing characters, digits, special characters, etc. 

This kind of security is provided by most financial websites, and others as well, to protect your account from an unknown person trying to break into your account. And in most cases, this is done by the application layer, due to the lack of these features in the backend database these sites are using.

But with the features of EDB Postgres Advanced Server (EPAS) v9.5, your application can now simply push these responsibilities onto the database layer by creating a password profile and then associating that with the user.

Password profiles define rules for managing and reusing user passwords. With these profiles, a superuser can enforce some restrictions, like locking an account after a certain number of failed logins or can enforce certain constraints on creating a new password.

In this blog, I will illustrate various profiling parameters available in EPAS v9.5 and their use-cases. Like users and tablespaces, profiles are global objects too, which means they are shared across various databases within a single cluster.

A profile is created using the CREATE PROFILE syntax, where we can specify the limit to various password parameters. In EPAS v9.5, you will see the following password-related parameters:

    FAILED_LOGIN_ATTEMPTS
    PASSWORD_LOCK_TIME
    PASSWORD_LIFE_TIME
    PASSWORD_GRACE_TIME
    PASSWORD_VERIFY_FUNCTION
    PASSWORD_REUSE_TIME
    PASSWORD_REUSE_MAX
    PASSWORD_ALLOW_HASHED (Added in v11)

Let's look over these parameters in more detail. But before that, let's create one simple profile and associate that to a user. Note that only a superuser can CREATE or ALTER a profile and associate it to the user.

-- Create profile and a user
CREATE PROFILE myprofile;
CREATE USER myuser IDENTIFIED BY mypassword;

-- Assign profile to a user
ALTER USER myuser PROFILE myprofile;

-- Check the user-profile mapping
SELECT rolname, rolprofile FROM pg_roles WHERE rolname = 'myuser';
 rolname | rolprofile
---------+------------
 myuser  | myprofile
(1 row)

Now we have one profile and one user associated with that profile.

FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME

FAILED_LOGIN_ATTEMPTS specifies the number of failed login attempts that a user might make before their account gets locked. The locking period is then specified by the PASSWORD_LOCK_TIME parameter. Now let's alter this profile so that after three failed login attempts, the user account will be locked for two days.

ALTER PROFILE myprofile LIMIT
  FAILED_LOGIN_ATTEMPTS 3
  PASSWORD_LOCK_TIME 2;

-- Check the details in the catalog table (edb_profile is the catalog table which stores all
-- details related to the user profiles). Note that time displayed here is in seconds.
SELECT prfname, prffailedloginattempts, prfpasswordlocktime FROM edb_profile
WHERE prfname = 'myprofile';
  prfname  | prffailedloginattempts | prfpasswordlocktime
-----------+------------------------+---------------------
 myprofile |                      3 |              172800
(1 row)

SELECT rolname, rolprofile, edb_get_role_status(oid), rolfailedlogins, rollockdate FROM pg_roles
WHERE rolname = 'myuser';
 rolname | rolprofile | edb_get_role_status | rolfailedlogins | rollockdate
---------+------------+---------------------+-----------------+-------------
 myuser  | myprofile  | OPEN                |               0 |
(1 row)

Since there is no failed login yet, you can see that the role status is OPEN and rolfailedlogins = 0 with no lock date mentioned.

Now, try one failed login and run the same SELECT query again

\c - myuser
Password for user myuser:
FATAL:  password authentication failed for user "myuser"
Previous connection kept

SELECT rolname, rolprofile, edb_get_role_status(oid), rolfailedlogins, rollockdate FROM pg_roles
WHERE rolname = 'myuser';
 rolname | rolprofile | edb_get_role_status | rolfailedlogins | rollockdate
---------+------------+---------------------+-----------------+-------------
 myuser  | myprofile  | OPEN                |               1 |
(1 row)

Here you can see that role status is still OPEN, but now rolfailedlogins is increased to 1. Now, if you have more failed logins you will end up with a locked account, such as below:

\c - myuser
Password for user myuser:
FATAL:  role "myuser" is locked
Previous connection kept

SELECT rolname, rolprofile, edb_get_role_status(oid), rolfailedlogins, rollockdate FROM pg_roles
WHERE rolname = 'myuser';
 rolname | rolprofile | edb_get_role_status | rolfailedlogins |           rollockdate            
---------+------------+---------------------+-----------------+----------------------------------
 myuser  | myprofile  | LOCKED(TIMED)       |               0 | 13-NOV-18 12:25:50.811022 +05:30
(1 row)

Because of too many failed logins, the user account is now locked. See the role status: it is LOCKED(TIMED) and failed login attempt counter is reset to 0. TIMED, because it will be automatically unlocked after two days (the time period specified by the PASSWORD_LOCK_TIME parameter). We also record the timestamp when the user account is locked. Note that after the locking period elapses, the user should be able to login again and the status will be reset to OPEN. If the user is able to log on successfully after a few failed login attempts but before the max limit (specified by FAILED_LOGIN_ATTEMPTS), then the failed login attempt counter gets reset to 0, as well.

\c - myuser
Password for user myuser:
You are now connected to database "edb" as user "myuser".

SELECT rolname, rolprofile, edb_get_role_status(oid), rolfailedlogins, rollockdate FROM pg_roles
WHERE rolname = 'myuser';
 rolname | rolprofile | edb_get_role_status | rolfailedlogins |           rollockdate            
---------+------------+---------------------+-----------------+----------------------------------
 myuser  | myprofile  | OPEN                |               0 | 13-NOV-18 12:25:50.811022 +05:30
(1 row)

Note that the last locked date is preserved.

A superuser can explicitly lock an account by the following command:

ALTER USER myuser ACCOUNT LOCK;

SELECT rolname, rolprofile, edb_get_role_status(oid), rolfailedlogins, rollockdate FROM pg_roles
WHERE rolname = 'myuser';
 rolname | rolprofile | edb_get_role_status | rolfailedlogins |           rollockdate            
---------+------------+---------------------+-----------------+----------------------------------
 myuser  | myprofile  | LOCKED              |               0 | 13-NOV-18 12:26:00.023422 +05:30
(1 row)

In this case, the status is just LOCKED. If the user account is explicitly locked, then that user will never be able to log on to the system. A superuser has to unlock that using:

ALTER USER myuser ACCOUNT UNLOCK;

Note: this feature is added to EPAS v9.5 and thus it is available from v9.5 onwards.

I will explain the rest of the password profile parameters in subsequent blogs. Please look for my upcoming posts.