Grant privileges
This tutorial demonstrates how to grant privileges in YSQL using the scenario of a company with an engineering organization that has three sub-teams: developers, QA, and DB admins.
Here is what you want to achieve from a role-based access control (RBAC) perspective:
- All members of engineering should be able to read data from any database and table.
- Both developers and QA should be able to modify data in existing tables in the database
dev_database. - QA should be able to alter the
integration_teststable in the databasedev_database. - DB admins should be able to perform all operations on any database.
The exercise assumes you have enabled authentication for YSQL.
1. Create role hierarchy
Connect to the cluster using a superuser role. For this tutorial, use the default yugabyte user and connect to the cluster using ysqlsh as follows:
$ ./bin/ysqlsh
Create a database dev_database.
yugabyte=# CREATE database dev_database;
Switch to the dev_database.
yugabyte=# \c dev_database
Create the integration_tests table:
dev_database=# CREATE TABLE integration_tests (
id UUID PRIMARY KEY,
time TIMESTAMP,
result BOOLEAN,
details JSONB
);
Next, create roles engineering, developer, qa, and db_admin.
dev_database=# CREATE ROLE engineering;
CREATE ROLE developer;
CREATE ROLE qa;
CREATE ROLE db_admin;
Grant the engineering role to developer, qa, and db_admin roles, as they are all a part of the engineering organization.
dev_database=# GRANT engineering TO developer;
GRANT engineering TO qa;
GRANT engineering TO db_admin;
2. List privileges for roles
You can list all privileges granted to the various roles with the \du meta-command:
dev_database=# \du
You should see something like the following output.
List of roles
Role name | Attributes | Member of
--------------+------------------------------------------------------------+---------------
db_admin | Cannot login | {engineering}
developer | Cannot login | {engineering}
engineering | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
qa | Cannot login | {engineering}
yb_extension | Cannot login | {}
yb_fdw | Cannot login | {}
yugabyte | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
This shows the various role attributes of the yugabyte role. Because yugabyte is a superuser, it has all privileges on all databases.
3. Grant privileges to roles
In this section, you grant permissions to each role.
Grant read access
All members of engineering should be able to read data from any database and table. Use the GRANT statement to grant SELECT (or read) access on the existing table (integration_tests) to the engineering role. This can be done as follows:
dev_database=# GRANT SELECT ON integration_tests to engineering;
dev_database=# GRANT USAGE ON SCHEMA public TO engineering;
Verify that the engineering role has SELECT privilege on the table using the \z meta-command, which lists tables with their associated access privileges:
dev_database=# \z
The output should look similar to below.
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-------------------+-------+---------------------------+-------------------+----------
public | integration_tests | table | yugabyte=arwdDxt/yugabyte+| |
| | | engineering=r/yugabyte +| |
The access privileges "arwdDxt" include all privileges for the user yugabyte (superuser), while the role engineering has only "r" (read) privileges. For details on the GRANT statement and access privileges, see GRANT.
Granting the role engineering to any other role causes all those roles to inherit the specified privileges. Thus, developer, qa, and db_admin all inherit the SELECT and USAGE privileges, giving them read-access.
Grant data modify access
Both developers and qa should be able to modify data existing tables in the database dev_database. They should be able to execute statements such as INSERT, UPDATE, DELETE or TRUNCATE to modify data on existing tables. This can be done as follows:
dev_database=# GRANT INSERT, UPDATE, DELETE, TRUNCATE ON table integration_tests TO developer;
dev_database=# GRANT INSERT, UPDATE, DELETE, TRUNCATE ON table integration_tests TO qa;
Verify that the developer and qa roles have the appropriate privileges.
dev_database=# \z
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-------------------+-------+---------------------------+-------------------+----------
public | integration_tests | table | yugabyte=arwdDxt/yugabyte+| |
| | | engineering=r/yugabyte +| |
| | | developer=awdD/yugabyte +| |
| | | qa=awdD/yugabyte | |
Now developer and qa roles have the access privileges awdD (append/insert, write/update, delete, and truncate) for the table integration_tests.
Grant alter table access
QA (qa) should be able to alter the table integration_tests in the database dev_database. This can be done as follows.
dev_database=# ALTER TABLE integration_tests OWNER TO qa;
Run the following command to verify the privileges.
dev_database=# \z
Owner has changed from yugabyte to qa and qa has all access privileges (arwdDxt) on the table integration_tests.
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-------------------+-------+-------------------+-------------------+----------
public | integration_tests | table | qa=arwdDxt/qa +| |
| | | engineering=r/qa +| |
| | | developer=awdD/qa | |
Grant all privileges
DB admins should be able to perform all operations on the database. You can do this by granting DB admins the superuser privilege. Doing this gives the DB admins all privileges over all roles as well. Only superusers can grant the superuser privilege.
To grant superuser, do the following:
dev_database=# ALTER USER db_admin WITH SUPERUSER;
Run the following command to verify the privileges:
dev_database=# \du
List of roles
Role name | Attributes | Member of
--------------+------------------------------------------------------------+---------------
db_admin | Superuser, Cannot login | {engineering}
developer | Cannot login | {engineering}
engineering | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
qa | Cannot login | {engineering}
yb_extension | Cannot login | {}
yb_fdw | Cannot login | {}
yugabyte | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
4. Revoke privileges from roles
To revoke superuser from the DB admins so that they can no longer change privileges for other roles, do the following:
dev_database=# ALTER USER db_admin WITH NOSUPERUSER;
Run the following command to verify the privileges:
dev_database=# \du
You should see the following output.
List of roles
Role name | Attributes | Member of
--------------+------------------------------------------------------------+---------------
db_admin | Cannot login | {engineering}
developer | Cannot login | {engineering}
engineering | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
qa | Cannot login | {engineering}
yb_extension | Cannot login | {}
yb_fdw | Cannot login | {}
yugabyte | Superuser, Create role, Create DB, Replication, Bypass RLS | {}