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_tests
table 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 | {}