Password authentication

YSQL

By default, password authentication is disabled, allowing users and clients to connect to and interact with YugabyteDB with minimal effort. For production clusters, password authentication is important for maximizing security. The password authentication methods work similarly, but differ in how user passwords are stored on the server and how the password provided by the client is sent across the connection.

YugabyteDB database passwords

YugabyteDB database passwords are separate from operating system passwords. The password for each database user is stored in the pg_authid system catalog.

Database passwords can be managed using the following:

YSQL API: CREATE ROLE and ALTER ROLE
ysqlsh meta-command: \password

The passwordcheck extension can be used to enforce strong passwords whenever they are set with CREATE ROLE or ALTER ROLE. passwordcheck only works for passwords that are provided in plain text. For more information, refer to the PostgreSQL passwordcheck documentation.

Password authentication methods

The following password authentication methods are supported by YugabyteDB.

MD5

The MD5 method (md5) prevents password sniffing and avoids storing passwords on the server in plain text, but provides no protection if an attacker obtains password hashes from the server or from clients (by sniffing, man-in-the-middle, or by brute force). MD5 is the default password encryption for YugabyteDB clusters.

The MD5 hash algorithm is not considered secure against determined attacks. Some of the security risks include:

If someone has access to a valid username/password combination, or their MD5-styled hash, they can log into any cluster where that user exists with the same username and password.
The "shared secret" is effectively shared over the wire every time the MD5 authentication method is used.

SCRAM-SHA-256

The SCRAM-SHA-256 method (scram-sh-256) performs SCRAM-SHA-256 authentication, as described in RFC 7677. This challenge-response scheme prevents password sniffing on untrusted connections and supports storing passwords on YugabyteDB clusters in the most secure cryptographically hashed form available. The SCRAM-SHA-256 method implemented here is explained in further detail in SASL Authentication (PostgreSQL documentation). This is the most secure password authentication available and is supported by most of the client drivers for the YSQL API.

Allows for two parties to verify they both know a secret without exchanging the secret.
SCRAM-SHA-256 encryption uses the SASL authentication mechanism flow to limit security risks from brute force attacks and sniffing.

Note

For additional security, SCRAM-SHA-256 password encryption can also be used with encryption in transit (TLS encryption).

Enable SCRAM-SHA-256 authentication

To configure a YugabyteDB cluster to use SCRAM-SHA-256 authentication for databases, follow these steps.

Change the password encryption to use SCRAM-SHA-256.

To change the default MD5 password encryption to use SCRAM-SHA-256, add the YB-TServer --ysql_pg_conf_csv flag and set the value to scram-sha-256:
```
--ysql_pg_conf_csv="password_encryption=scram-sha-256"
```
or in the yb-tserver.conf, add the following line:
```
--ysql_pg_conf_csv=password_encryption=scram-sha-256
```
Specify the rules for host-based authentication.

To specify rules for the use of the scram-sha-256 authentication method, add the YB-TServer --ysql_hba_conf_csv flag and specify rules that satisfy your security requirements.

In the following example, the --ysql_hba_conf_csv flag modifies the default rules that use trust to use SCRAM-SHA-256 authentication, changing the default values of trust to use scram-sha-256:

--ysql_hba_conf_csv='host all all 0.0.0.0/0 scram-sha-256,host all all ::0/0 scram-sha-256'

or in the yb-tserver.conf, add the following line:

--ysql_hba_conf_csv=host all all 0.0.0.0/0 scram-sha-256,host all all ::0/0 scram-sha-256

For details on using the --ysql_hba_conf_csv flag to specify rules that satisfy your security requirements, see Fine-grained authentication.

Create a cluster that uses SCRAM-SHA-256 password authentication

To use SCRAM-SHA-256 password authentication on a new YugabyteDB cluster, follow this procedure:

In the YB-TServer configuration file (flagfile), add the following two lines:
```
--ysql_pg_conf_csv=password_encryption=scram-sha-256
--ysql_hba_conf_csv=host all all 0.0.0.0/0 md5,host all all ::0/0 md5,host all all 0.0.0.0/0 scram-sha-256,host all all ::0/0 scram-sha-256
```
- The first line starts your YugabyteDB cluster with password encryption set to encrypt all new passwords using SCRAM-SHA-256.
- The ysql_hba_conf_csv flag above specifies rules that allow both MD5 and SCRAM-SHA-256 existing passwords to be used to connect to databases.
Start the YugabyteDB cluster.
Open the YSQL shell (ysqlsh), specifying the yugabyte user and prompting for the password.
```
$ ./ysqlsh -U yugabyte -W
```
When prompted for the password, enter the yugabyte password (default is yugabyte). You should be able to log in and see a response like this:
```
ysqlsh (11.2-YB-2.3.3.0-b0)
Type "help" for help.

yugabyte=#
```
Change the password for yugabyte to a SCRAM-SHA-256 password.

You can use either the ALTER ROLE statement or the ysqlsh \password\ meta-command to change the password. The new password is encrypted using the SCRAM-SHA-256 hashing algorithm. In the following example, the \password meta-command is used to change the password.
```
\password
```
You are prompted twice for the new password and then returned to the YSQL shell prompt.
```
Enter new password:
Enter it again:
yugabyte=#
```
Stop the YugabyteDB cluster.
Remove the MD5 rules from the --ysql_hba_conf_csv flag.

In the flagfile, the updated flag should appear like this:
```
--ysql_hba_conf_csv=host all all 0.0.0.0/0 scram-sha-256,host all all ::0/0 scram-sha-256
```
Restart the YugabyteDB cluster.
Open the YSQL shell and log in, specifying the yugabyte user and password prompt.
```
$ ./ysqlsh -U yugabyte -W
```

When prompted, the changed yugabyte user password should get you access. Any new users or roles that you create are encrypted using SCRAM-SHA-256. Access to the host and databases is determined by the rules you specify in the YB-TServer --ysql_hba_conf_csv configuration flag.

Migrate existing MD5 passwords to SCRAM-SHA-256

When you enable SCRAM-SHA-256 authentication on an existing YugabyteDB cluster that has users and roles, with their MD5 passwords), you need to be aware that:

All new, or changed, passwords will be encrypted using the SCRAM-SHA-256 hashing algorithm.
All existing passwords were encrypted using the MD5 hashing algorithm.

Because all existing passwords must be changed, you can manage the migration of these user and role passwords from MD5 to SCRAM-SHA-256 by maintaining rules in the --ysql_hba_conf_csv setting to allow both MD5 passwords and SCRAM-SHA-256 passwords to work until all passwords have been migrated to SCRAM-SHA-256. For an example, see Create a cluster that uses SCRAM-SHA-256 password authentication.

If you follow a similar approach for an existing cluster, you can enhance your cluster security, track and migrate passwords, and then remove the much weaker MD5 rules after all passwords have been updated.

Resetting user password

In PostgreSQL, if the administrator password is lost or changed to an unknown value, you can change the pg_hba.conf file to allow administrator access without a password. This is a static file that is used to control client authentication. To reset the password for the postgres user, you change the parameters in the configuration file, restart the database, and then log in as postgres without a password, and reset the password.

The same is also true for YugabyteDB, although the implementation is slightly different. YugabyteDB has a ysql_hba.conf file similar to PostgreSQL. However, unlike PostgreSQL, the contents of the file are dynamically generated using the --ysql_hba_conf_csv flag at yb-tserver startup.

To change the ysql_hba.conf file to allow administrator access without a password, you restart the yb-tserver with the following --ysql_hba_conf_csv configuration flag:

--ysql_hba_conf_csv=host all yugabyte 0.0.0.0/0 trust,host all all 0.0.0.0/0 md5,host all yugabyte ::0/0 trust,host all all ::0/0 md5

After restarting the yb-tserver, password authentication is enforced for all users except the yugabyte user. Now you can connect without a password:

$ ./bin/ysqlsh

And update the password of the user to new desired password:

ALTER ROLE yugabyte WITH PASSWORD 'new-password';

Rollback the configuration and restart the yb-tserver to enable password authentication for the yugabyte user again.