Enable Users in YSQL

• YSQL
• YCQL
• YEDIS

YSQL authentication, the process of identifying that YSQL users are who they say they are, is based on roles. Users, groups, and roles within 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, you can add the --ysql_enable_auth flag via the --tserver_flags flag with the yb-ctl create, yb-ctl start, and yb-ctl restart commands.

When you create a local cluster, run the yb-ctl create command as follows to enable YSQL authentication in the newly-created cluster:

After your local cluster has been created, enable YSQL authentication when you start your cluster with the following yb-ctl start command:

Enable YSQL authentication when you restart your cluster with the following yb-ctl restart command:

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:

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:

You will be prompted to enter the password. Upon successful login to the YSQL shell, you will see the following:

ysqlsh (11.2-YB-2.7.0.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.

Creating 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.

CREATE ROLE

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

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:

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

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 command to see this information in a simpler, easier-to-read format:

                                   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 to ysqlsh using non-default credentials

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

You are prompted for a password.

As an example of connecting as a user, login with the credentials of the user john that you created above by running the following command and entering the password when prompted:

Edit user accounts

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

Changing password for a user

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

Granting and removing superuser privileges

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

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

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

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

                                   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:

Enable and disable login privileges

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

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

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

You can verify this as follows:

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

Trying to login as john using ysqlsh will now fail:

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.

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:

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

                                   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