Create and configure login profiles in YSQL

To enhance the security of your database, you can enable login profiles to lock accounts after a specified number of login attempts. This prevents brute force exploits.

When enabled, database administrators with superuser (or in YugabyteDB Aeon, yb_db_admin) privileges can create login profiles and assign roles to the profiles.

There is no default profile for roles; you must explicitly assign all roles with login privileges to the profile if you want the policy to apply to all users. Users not associated with a profile continue to have unlimited login attempts.

When creating a profile, you must specify the number of failed attempts that are allowed before the account with the profile is locked.

The number of failed attempts increments by one every time authentication fails during login. If the number of failed attempts is equal to the preset limit, then the account is locked. For example, if the limit is 3, a user is locked out on the third failed attempt.

If authentication is successful, or if an administrator unlocks a locked account, the number of failed attempts resets to 0.

Enable login profiles

Start local clusters

To enable login profiles in your local YugabyteDB clusters, include the YB-TServer --ysql_enable_profile flag with the yugabyted start command --tserver_flags flag, as follows:

./bin/yugabyted start --tserver_flags="ysql_enable_profile=true"

Start YB-TServer services

To enable login profiles in deployable YugabyteDB clusters, you need to start your YB-TServer services using the --ysql_enable_profile flag. Your command should look similar to the following:

./bin/yb-tserver \
  --tserver_master_addrs <master addresses> \
  --fs_data_dirs <data directories> \
  --ysql_enable_auth=true \
  --ysql_enable_profile=true \
  >& /home/centos/disk1/yb-tserver.out &

You can also enable YSQL login profiles by adding the --ysql_enable_profile=true to the YB-TServer configuration file (tserver.conf).

For more information, refer to Start YB-TServers.

Manage login profiles

When profiles are enabled, you can manage login profiles using the following commands:

• CREATE PROFILE
• DROP PROFILE
• ALTER ROLE

Only superusers can create or drop profiles, and assign profiles to roles.

Create and drop profiles

To create a profile, do the following:

CREATE PROFILE myprofile LIMIT
  FAILED_LOGIN_ATTEMPTS <number>;
  [PASSWORD_LOCK_TIME <days>];

Note that PASSWORD_LOCK_TIME is optional, and timed locking is not currently supported.

You can drop a profile as follows:

DROP PROFILE myprofile;

Assign roles to profiles

You can assign a role to a profile as follows:

ALTER ROLE myuser PROFILE myprofile;

You can remove a role from a profile as follows:

ALTER ROLE myuser NOPROFILE;

Note that you should remove the association between a role and its profile using ALTER ROLE ... NOPROFILE before dropping a role.

Lock and unlock roles

You can unlock a role that has been locked out as follows:

ALTER ROLE myuser ACCOUNT UNLOCK;

You can lock a role so that it can't log in as follows:

ALTER ROLE myuser ACCOUNT LOCK;

Recover from complete lockout

If you lock out all roles including administrator roles, you must restart the cluster with the --ysql_enable_profile flag disabled.

While disabling login profiles allows users back in, you won't be able to change any profile information, as profile commands can't be run when the profile flag is disabled.

To re-enable accounts, do the following:

1. Restart the cluster without profiles enabled.
2. Create a new superuser.
3. Restart the cluster with profiles enabled.
4. Connect as the new superuser and issue the profile commands to unlock the accounts.

View profiles

The pg_yb_profile table lists profiles and their attributes.

To view profiles, execute the following statement:

SELECT * FROM pg_yb_profile;

You should see output similar to the following:

  prfname  | prfmaxfailedloginattempts | prfpasswordlocktime 
-----------+---------------------------+---------------------
 myprofile |                         3 |                   0
(1 row)

The following table describes the columns and their values:

View role profile information

The pg_yb_role_profile table lists role profiles and their attributes.

To view profiles, execute the following statement:

SELECT * FROM pg_yb_role_profile;

You should see output similar to the following:

 rolprfrole | rolprfprofile | rolprfstatus | rolprffailedloginattempts | rolprflockeduntil 
------------+---------------+--------------+---------------------------+-------------------
      13287 |         16384 | o            |                         0 | 
(1 row)

The following table describes the columns and their values:

Limitations and caveats

• A profile can't be modified using ALTER PROFILE. If a profile needs to be modified, create a new profile.

• Currently a role is locked indefinitely unless an administrator unlocks the role.

• Login profiles are only applicable to challenge-response authentication methods. YugabyteDB also supports authentication methods that are not challenge-response, and login profiles are ignored for these methods as the authentication outcome has already been determined. The authentication methods are as follows:

  • Reject
  • ImplicitReject
  • Trust
  • YbTserverKey
  • Peer

  For more information on these authentication methods, refer to Client Authentication in the PostgreSQL documentation.

• If the cluster SSL mode is allow or prefer, a single user login attempt can trigger two failed login attempts. For more information on SSL modes in PostgreSQL, refer to SSL Support in the PostgreSQL documentation.

• The \h and \dg meta commands do not currently provide information about PROFILE and ROLE PROFILE catalog objects.

Enhancements to login profiles are tracked in GitHub issue #15676.