Postgres 14 introduced a set of predefined roles that provide access to certain, commonly needed, privileged capabilities and information.
One of those roles, pg_read_all_data, can be granted to a user allowing that user to read all data (tables, views, sequences), as if having SELECT rights on those objects, and USAGE rights on all schemas, even without having it explicitly.
This let’s you create a read only user.
Let’s try this in YugabyteDB YSQL.
Set up:
yugabyte=# CREATE TABLE some_table(c1 INT PRIMARY KEY, c2 TEXT);
CREATE TABLE
yugabyte=# CREATE SCHEMA some_schema;
CREATE SCHEMA
yugabyte=# CREATE TABLE some_schema.some_table(c1 INT PRIMARY KEY, c2 TEXT);
CREATE TABLE
yugabyte=# INSERT INTO some_table VALUES (1, 'some_data');
INSERT 0 1
yugabyte=# INSERT INTO some_schema.some_table VALUES (1, 'some_data');
INSERT 0 1
yugabyte=# CREATE USER jim WITH LOGIN;
CREATE ROLE
yugabyte=# \! ysqlsh -h yugabytedb.tech -U jim -c "SELECT * FROM some_table;"
ERROR: permission denied for table some_table
yugabyte=# \! ysqlsh -h yugabytedb.tech -U jim -c "SELECT * FROM some_schema.some_table;"
ERROR: permission denied for schema some_schema
LINE 1: SELECT * FROM some_schema.some_table;
^
So far the jim user can not read from the tables created above.
This is where the pg_read_all_data role should help…
yugabyte=# GRANT pg_read_all_data TO jim;
ERROR: role "pg_read_all_data" does not exist
Uh oh…
Currently, YugabyteDB is compatible with Postgres 11.2, thus does not have the pg_read_all_data role built-in yet.
But we can create our own to mimic the functionality in YugabyteDB!
Example:
yugabyte=# CREATE ROLE yb_read_all_data;
CREATE ROLE
yugabyte=# SELECT 'GRANT SELECT ON ALL TABLES IN SCHEMA ' || schema_name || ' TO yb_read_all_data;' FROM information_schema.schemata WHERE schema_name NOT IN ('pg_catalog', 'pg_toast', 'information_schema');
?column?
-----------------------------------------------------------------------
GRANT SELECT ON ALL TABLES IN SCHEMA public TO yb_read_all_data;
GRANT SELECT ON ALL TABLES IN SCHEMA some_schema TO yb_read_all_data;
(2 rows)
yugabyte=# SELECT 'GRANT SELECT ON ALL TABLES IN SCHEMA ' || schema_name || ' TO yb_read_all_data;' FROM information_schema.schemata WHERE schema_name NOT IN ('pg_catalog', 'pg_toast', 'information_schema');
?column?
-----------------------------------------------------------------------
GRANT SELECT ON ALL TABLES IN SCHEMA public TO yb_read_all_data;
GRANT SELECT ON ALL TABLES IN SCHEMA some_schema TO yb_read_all_data;
(2 rows)
yugabyte=# \gexec
GRANT
GRANT
yugabyte=# SELECT 'GRANT USAGE ON SCHEMA ' || schema_name || ' TO yb_read_all_data;' FROM information_schema.schemata WHERE schema_name NOT IN ('pg_catalog', 'pg_toast', 'information_schema', 'public');
?column?
--------------------------------------------------------
GRANT USAGE ON SCHEMA some_schema TO yb_read_all_data;
(1 row)
yugabyte=# \gexec
GRANT
yugabyte=# GRANT yb_read_all_data TO jim;
GRANT ROLE
Now the user jim can read from the two tables we created earlier.
yugabyte=# \! ysqlsh -h yugabytedb.tech -U jim -c "SELECT * FROM some_table;"
c1 | c2
----+-----------
1 | some_data
(1 row)
yugabyte=# \! ysqlsh -h yugabytedb.tech -U jim -c "SELECT * FROM some_schema.some_table;"
c1 | c2
----+-----------
1 | some_data
(1 row)
Note that DML operations like INSERT, UPDATE and DELETE are not possible by the user jim.
yugabyte=# \! ysqlsh -h yugabytedb.tech -U jim -c "INSERT INTO some_table VALUES (2, 'other_data');"
ERROR: permission denied for table some_table
yugabyte=# \! ysqlsh -h yugabytedb.tech -U jim -c "DELETE FROM some_table WHERE c1 = 1;"
ERROR: permission denied for table some_table
yugabyte=# \! ysqlsh -h yugabytedb.tech -U jim -c "UPDATE some_table SET c2 = 'other_data' WHERE c1 =
1;"
ERROR: permission denied for table some_table
Have Fun!
