Role-based access overview

Role-based access control (RBAC) consists of a collection of permissions on resources given to roles.

• YSQL
• YCQL

Roles

Roles in YSQL can represent individual users or a group of users. They encapsulate a set of privileges that can be assigned to other roles (or users). Roles are essential to implementing and administering access control on a YugabyteDB cluster. Below are some important points about roles:

• Roles which have LOGIN privilege are users. Hence, all users are roles, but not all roles are users.

• Roles can be granted to other roles, making it possible to organize roles into a hierarchy.

• Roles inherit the privileges of all other roles granted to them.

YugabyteDB inherits a number of roles from PostgreSQL, including the postgres user, and adds several new roles. View the YugabyteDB-specific roles for your clusters with the following command (or use \duS to display all roles):

yugabyte=> \du

                                     List of roles
  Role name   |                         Attributes                         | Member of
--------------+------------------------------------------------------------+-----------
 postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 yb_db_admin  | No inheritance, Cannot login                               | {}
 yb_extension | Cannot login                                               | {}
 yb_fdw       | Cannot login                                               | {}
 yugabyte     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

The following table describes the default YSQL roles and users in YugabyteDB clusters.

yb_extension

The yb_extension role allows non-superuser roles to create extensions. A user granted this role can create all the extensions that are bundled in YugabyteDB.

Create a role test and grant yb_extension to this role.

yugabyte=# create role test;
yugabyte=# grant yb_extension to test;
yugabyte=# set role test;
yugabyte=> select * from current_user;

 current_user
--------------
 test
(1 row)

Create an extension as the test user and check if it's created.

yugabyte=> create extension pgcrypto;
yugabyte=> select * from pg_extension where extname='pgcrypto';

 extname  | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
----------+----------+--------------+----------------+------------+-----------+--------------
 pgcrypto |    16386 |         2200 | t              | 1.3        |           |
(1 row)

Resources

YSQL defines a number of specific resources that represent underlying database objects. A resource can represent one object or a collection of objects. YSQL resources are hierarchical as described below:

• Databases and tables follow the hierarchy: ALL DATABASES > DATABASE > TABLE
• ROLES are hierarchical (they can be assigned to other roles). They follow the hierarchy: ALL ROLES > ROLE #1 > ROLE #2 ...

The table below lists out the various resources.

Privileges

Privileges are necessary to execute operations on database objects. Privileges can be granted at any level of the database hierarchy and are inherited downwards. The set of privileges include:

Read more about YSQL privileges.