Enable users in YSQL

• YSQL
• YCQL

YSQL authentication, the process of identifying that YSQL users are who they say they are, is based on roles. Users, groups, and roles in YugabyteDB are created using roles. Typically, a role that has login privileges is known as a user, while a group is a role that can have multiple users as members.

Users, roles, and groups allow administrators to verify whether a particular user or role is authorized to create, access, change, or remove databases, or manage users and roles.

Authentication verifies the identity of a user while authorization determines the verified user's database access privileges.

Authorization is the process of managing access control based on roles. For YSQL, enabling authentication automatically enables authorization and the role-based access control (RBAC) model, to determine the access privileges. Privileges are managed using GRANT, REVOKE, CREATE ROLE, ALTER ROLE, and DROP ROLE.

Users and roles can be created with superuser, non-superuser, and login privileges, and the roles that users have are used to determine what access privileges are available. Administrators can create users and roles using the CREATE ROLE statement (or its alias, CREATE USER). After users and roles have been created, ALTER ROLE and DROP ROLE statements are used to change or remove users and roles.

Default user and password

When you start a YugabyteDB cluster, the YB-Master and YB-TServer services are launched using the default user, named yugabyte, and then this user is connected to the default database, also named yugabyte.

Once YSQL authentication is enabled, all users (including yugabyte) require a password to log in to a YugabyteDB database. The default yugabyte user has a default password of yugabyte that lets this user sign into YugabyteDB when YSQL authentication is enabled.

Enable YSQL authentication

Start local clusters

To enable YSQL authentication in your local YugabyteDB clusters, add the --ysql_enable_auth flag with the yugabyted start command, as follows:

$ ./bin/yugabyted start --ysql_enable_auth=true

Start YB-TServer services

To enable YSQL authentication in deployable YugabyteDB clusters, you need to start your yb-tserver services using the --ysql_enable_auth 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 \
  >& /home/centos/disk1/yb-tserver.out &

You can also enable YSQL authentication by adding the --ysql_enable_auth=true to the YB-TServer configuration file (tserver.conf). For more information, refer to Start YB-TServers.

Open the YSQL shell (ysqlsh)

A YugabyteDB cluster with authentication enabled starts with the default admin user of yugabyte and the default database of yugabyte. You can connect to the cluster and use the YSQL shell by running the following ysqlsh command from the YugabyteDB home directory:

$ ./bin/ysqlsh -U yugabyte

You are prompted to enter the password. After logging in, you should see the following output:

ysqlsh (11.2-YB-2.23.1.0-b0)
Type "help" for help.

yugabyte=#

Common user authorization tasks

Here are some common authorization-related tasks. For more detailed information on authorization, refer to Role-Based Access Control.

For information on configuring authentication, refer to Authentication.

Create users

To add a new user, run the CREATE ROLE statement or its alias, the CREATE USER statement. Users are roles that have the LOGIN privilege granted to them. Roles created with the SUPERUSER option in addition to the LOGIN option have full access to the database. Superusers can run all of the YSQL statements on any of the database resources.

By default, creating a role does not grant the LOGIN or the SUPERUSER privileges — these need to be explicitly granted.

Create a regular user

To add a new regular user (with non-superuser privileges) named john, with the password PasswdForJohn, and grant him LOGIN privileges, run the following CREATE ROLE command.

yugabyte=# CREATE ROLE john WITH LOGIN PASSWORD 'PasswdForJohn';

CREATE ROLE

To verify the user account just created, you can run a query like this:

yugabyte=# SELECT rolname, rolsuper, rolcanlogin FROM pg_roles;

You should see the following output.

          rolname          | rolsuper | rolcanlogin
---------------------------+----------+-------------
 postgres                  | t        | t
 pg_monitor                | f        | f
 pg_read_all_settings      | f        | f
 pg_read_all_stats         | f        | f
 pg_stat_scan_tables       | f        | f
 pg_signal_backend         | f        | f
 pg_read_server_files      | f        | f
 pg_write_server_files     | f        | f
 pg_execute_server_program | f        | f
 yb_extension              | f        | f
 yb_fdw                    | f        | f
 yugabyte                  | t        | t
 john                      | f        | t
(11 rows)

Create a user with SUPERUSER privileges

The SUPERUSER privilege should be given only to a limited number of users. Applications should generally not access the database using an account that has the superuser privilege.

Only a role with the SUPERUSER privilege can create a new role with the SUPERUSER privilege, or grant it to an existing role.

To create a superuser admin with the LOGIN privilege, run the following command using a superuser account:

yugabyte=# CREATE ROLE admin WITH LOGIN SUPERUSER PASSWORD 'PasswdForAdmin';

To verify the admin account just created, run the following query:

yugabyte=# SELECT rolname, rolsuper, rolcanlogin FROM pg_roles;

You should see a table output similar to the following:

          rolname          | rolsuper | rolcanlogin
---------------------------+----------+-------------
 postgres                  | t        | t
 ...
 yugabyte                  | t        | t
 john                      | f        | t
 admin                     | t        | t
(12 rows)

(To see all of the information available in the pg_roles table, run SELECT * from pg_roles.)

In this table, you can see that postgres, admin, and yugabyte users can log in and have SUPERUSER status.

As an easier alternative, you can run the \du meta-command to see this information in a simpler format:

yugabyte=# \du

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 admin     | Superuser                                                  | {}
 john      |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 yugabyte  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Users with SUPERUSER status display "Superuser" in the list of attributes for each role.

Connect using non-default credentials

You can connect to a cluster with authentication enabled as follows:

$ ysqlsh -U <username>

You are prompted for a password.

For example, to log in with the credentials of the user john that you created, you would run the following command and enter the password when prompted:

$ ysqlsh -U john

Edit user accounts

You can edit existing user accounts using the ALTER ROLE command. The role making the change must have sufficient privileges to modify the target role.

Changing password for a user

To change the password for john, enter the following command:

yugabyte=# ALTER ROLE john PASSWORD 'new-password';

Granting and removing superuser privileges

To verify that john is not a superuser, use the following SELECT statement:

yugabyte=# SELECT rolname, rolsuper, rolcanlogin FROM pg_roles WHERE rolname='john';

 rolname | rolsuper | rolcanlogin
---------+----------+-------------
 john    | f        | t
(1 row)

To grant SUPERUSER privileges to john, log in as a superuser and run the following ALTER ROLE command:

yugabyte=# ALTER ROLE john SUPERUSER;

Verify that john is now a superuser by running the \du command.

yugabyte=# \du

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 admin     | Superuser                                                  | {}
 john      | Superuser                                                  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 yugabyte  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Similarly, you can revoke superuser privileges by running:

yugabyte=# ALTER ROLE john WITH NOSUPERUSER;

Enable and disable login privileges

To verify that john can log in to the database, do the following:

yugabyte=# SELECT rolname, rolcanlogin FROM pg_roles WHERE rolname='john';

 rolname | rolcanlogin
---------+-------------
 john    |  t
(1 rows)

To disable login privileges for john, run the following command:

yugabyte=# ALTER ROLE john WITH NOLOGIN;

You can verify this as follows:

yugabyte=# SELECT rolname, rolcanlogin FROM pg_roles WHERE rolname='john';

 rolname | rolcanlogin
---------+-------------
 john    | f
(1 row)

Trying to log in as john using ysqlsh now fails:

$ ./bin/ysqlsh -U john

After entering the correct password, you see the following message:

Password for user john:
ysqlsh: FATAL:  role "john" is not permitted to log in

To re-enable login privileges for john, run the following command.

yugabyte=# ALTER ROLE john WITH LOGIN;

Delete a user

You can delete a user with the DROP ROLE statement.

For example, to drop the user john, run the following command as a superuser:

yugabyte=# DROP ROLE john;

To verify that the john role was dropped, run the \du command:

yugabyte=# \du

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 admin     | Superuser                                                  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 yugabyte  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Related topics

• CREATE ROLE
• ALTER ROLE
• DROP ROLE
• GRANT
• REVOKE