This article describes the much needed functionality of a "Read-Only User/Role" in postgresql database that was introduced in Postgresql 14 release version by means of a predefined role.
Earlier to Postgresql 14 version, the read-only access had to be provided by combining several GRANT statements which had to be worked out to and fro depending on the various objects present in the database, the future objects to be created, etc.
pg_read_all_data, introduced in PG 14, has made this easier. Let us see a simple illustration below.
Consider you have created a user below that you would like to have read-only access.
CREATE USER dbreadonly WITH PASSWORD 'test123';
As a superuser, let us create a database and tables
CREATE DATABASE db01;
\c db01
CREATE TABLE emp (id int, name varchar(10));
INSERT INTO emp VALUES (generate_series(1,10),'aaa');
Now, when you try to get data from emp, it will obviously give permission denied.
\c db01 dbreadonly
db01=> SELECT * FROM emp;
ERROR: permission denied for table emp
Let’s grant the built-in role pg_read_all_data to the read-only user.
\c db01 postgres
GRANT pg_read_all_data TO dbreadonly;
Now let’s try to get the data.
\c db01 dbreadonly
db01=> SELECT * FROM emp;
id | name
1 | aaa
2 | aaa
3 | aaa
4 | aaa
5 | aaa
6 | aaa
7 | aaa
8 | aaa
9 | aaa
10 | aaa
(10 rows)
Now suppose you create a new table. Even that should be readable to the dbreadonly user. Otherwise, without pg_read_all_data setting, we have to execute ALTER DEFAULT PRIVILEGES to the user.