Create a Read Only User

Jim Knicely

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:

Now the user jim can read from the two tables we created earlier…

				
					yugabyte=# CREATE ROLE read_all_data;
CREATE ROLE

yugabyte=# SELECT 'GRANT SELECT ON ALL TABLES IN SCHEMA ' || schema_name || ' TO 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 read_all_data;
 GRANT SELECT ON ALL TABLES IN SCHEMA some_schema TO read_all_data;
(2 rows)

yugabyte=# \gexec
GRANT
GRANT

yugabyte=# SELECT 'GRANT USAGE ON SCHEMA ' || schema_name || ' TO read_all_data;' FROM information_schema.schemata WHERE schema_name NOT IN ('pg_catalog', 'information_schema', 'public');
                        ?column?
--------------------------------------------------------
 GRANT USAGE ON SCHEMA some_schema TO read_all_data;
(1 row)

yugabyte=# \gexec
GRANT

yugabyte=# GRANT read_all_data TO jim;
GRANT ROLE

… and 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
Notes:
  • ● Avoid naming your read only role yb_read_all_data. Role names beginning with yb_ are reserved for internal use by YugabyteDB. Using this name can cause upgrade issues later, especially when upgrading to a version of YugabyteDB that includes this feature by default (i.e. 2025.x and above).
  • ● The commands that grant permissions (e.g. GRANT SELECT ON ALL TABLES IN SCHEMA …, GRANT USAGE ON SCHEMA …) must be executed by a role that owns the target schema/tables (or by a superuser). If you run them from a role without ownership, you’ll get permission errors.

Have Fun!

Provo, Utah!