Manage users and roles - YSQL

Roles in YSQL can represent individual users or a group of users. Users are a role that has login permissions.

You manage roles and users using the CREATE ROLE, GRANT, REVOKE, and DROP ROLE statements.

{{< note title="YSQL and case sensitivity" >}}

Like SQL, YSQL is case-insensitive by default. When specifying an identifier, such as the name of a table or role, YSQL automatically converts the identifier to lowercase. For example, CREATE ROLE Alice creates the role "alice". To use a case-sensitive name for an identifier, enclose the name in quotes. For example, to create the role "Alice", use CREATE ROLE "Alice".

{{< /note >}}

Create roles

You can create roles with the CREATE ROLE statement.

For example, to create a role engineering for an engineering team in an organization, do the following:

yugabyte=# CREATE ROLE engineering;

Roles that have LOGIN privileges are users. For example, create a user john as follows:

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

Read about how to create users in YugabyteDB in the Authentication section.

Grant roles

You can grant a role to another role (which can be a user), or revoke a role that has already been granted. Executing the GRANT and the REVOKE operations requires the AUTHORIZE privilege on the role being granted or revoked.

For example, you can grant the engineering role you created above to the user john as follows:

yugabyte=# GRANT engineering TO john;

Read more about granting privileges.

Create a hierarchy of roles

In YSQL, you can create a hierarchy of roles. The privileges of any role in the hierarchy flows downward.

For example, you can create a developer role that inherits all the privileges from the engineering role.

First, create the developer role.

yugabyte=# CREATE ROLE developer;

Next, GRANT the engineering role to the developer role.

yugabyte=# GRANT engineering TO developer;

List roles

You can list all the roles by running the following statement:

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

You should see the following output:

 rolname     | rolcanlogin | rolsuper |
-------------+-------------+----------+
 john        | t           | f        |
 developer   | f           | f        |
 engineering | f           | f        |
 yugabyte    | t           | t        |

(4 rows)

In the table, note the following:

• The yugabyte role is the built-in superuser.
• The role john can log in, and hence is a user. Note that john is not a superuser.
• The roles engineering and developer cannot log in.

You can also list roles using the du meta-command:

yugabyte=# \du

                                       List of roles
  Role name   |                         Attributes                         |   Member of
--------------+------------------------------------------------------------+---------------
 developer    | Cannot login                                               | {engineering}
 engineering  | Cannot login                                               | {}
 john         |                                                            | {engineering}
 yugabyte     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Note how both john and developer inherit the role engineering.

Revoke roles

Revoke roles using the REVOKE statement.

For example, you can revoke the engineering role from the user john as follows:

yugabyte=# REVOKE engineering FROM john;

Listing all the roles now shows that john no longer inherits from the engineering role:

yugabyte=# \du

                                       List of roles
  Role name   |                         Attributes                         |   Member of
--------------+------------------------------------------------------------+---------------
 developer    | Cannot login                                               | {engineering}
 engineering  | Cannot login                                               | {}
 john         |                                                            | {}
 yugabyte     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Drop roles

Drop roles using the DROP ROLE statement.

For example, you can drop the developer role with the following statement:

yugabyte=# DROP ROLE developer;

The developer role is no longer present when listing the roles:

yugabyte=# \du

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