In the PostgreSQL database, it's impossible to assign read-only privileges for all tables using the Grant select all table to user like we can do in Oracle. Instead, we must use a two-step process outline below to accommodate that task.
To over come this issue we have made below steps to grant read only privileges in postgresql databases in simple method.
edb=# create user read_only_user with password 'read';
edb=# \t
Tuples only is on.
edb=# \o grant.sql
edb=# select 'grant select on all TABLES in SCHEMA '||schema_name||' to read_only_user;' from information_schema.schemata;
edb=# \o
edb=# \q
[enterprisedb@localhost ~]$ psql -f grant.sql -d edb -U enterprisedb -p 5444
#####grant access to the new table in the future automatically
edb=# \t
Tuples only is on.
edb=# \o Alter.sql
edb=# select 'ALTER DEFAULT PRIVILEGES IN SCHEMA '||schema_name||' GRANT SELECT ON TABLES to read_only_user;' from information_schema.schemata;
edb=# \o
edb=# \q
[enterprisedb@localhost ~]$ psql -f Alter.sql -d edb -U enterprisedb -p 5444
Example:
edb=# create table t1 (name varchar2, type int);
CREATE TABLE
edb=#
edb=# insert into t1(name) values ('I''m OK');
INSERT 0 1
edb=#
edb=# select * from t1;
name | type
I'm OK |
(1 row)
edb=# create user read_only_user with password 'read';
CREATE ROLE
edb=#
edb=# \t
Tuples only is on.
edb=# \o grant.sql
edb=# select 'grant select on all TABLES in SCHEMA '||schema_name||' to read_only_user;' from information_schema.schemata;
edb=# \o
edb=# \q
[enterprisedb@localhost ~]$
[enterprisedb@localhost ~]$ ls -lrt grant.sql
-rw-rw-r--. 1 enterprisedb enterprisedb 669 Feb 1 21:57 grant.sql
[enterprisedb@localhost ~]$
[enterprisedb@localhost ~]$ psql -f grant.sql -d edb -U enterprisedb -p 5444
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
[enterprisedb@localhost ~]$
[enterprisedb@localhost ~]$ psql edb
psql (13.4.8, server 13.4.8)
Type "help" for help.
edb=# \c edb read_only_user ;
psql (13.4.8, server 13.4.8)
You are now connected to database "edb" as user "read_only_user".
edb=>
edb=>
edb=>
edb=> select * from t1;
name | type
I'm OK |
(1 row)
edb=>
As we can see we are able to select(read) data from table T1 which was already present in the database. Now lets try to create another table T2 :
edb=> \c edb enterprisedb
psql (13.4.8, server 13.4.8)
You are now connected to database "edb" as user "enterprisedb".
edb=#
edb=#
edb=# create table t2 (name varchar2, type int);
CREATE TABLE
edb=#
edb=# insert into t2(name) values (name 'shaikh');
INSERT 0 1
edb=#
edb=# \c edb read_only_user
psql (13.4.8, server 13.4.8)
You are now connected to database "edb" as user "read_only_user".
edb=> select * from t2;
ERROR: permission denied for table t2
edb=>
We are unable to read data from table T2 because we have not provided select privileges to “read_only_user” for the table which are going to be created in future.
Now let’s provide select privileges to “read_only_user” for future tables:
edb=> \c edb enterprisedb
psql (13.4.8, server 13.4.8)
You are now connected to database "edb" as user "enterprisedb".
edb=#
edb=# \t
Tuples only is on.
edb=# \o Alter.sql
edb=# select 'ALTER DEFAULT PRIVILEGES IN SCHEMA '||schema_name||' GRANT SELECT ON TABLES to read_only_user;' from information_schema.schemata;
edb=# \o
edb=# \q
Now create new table T3 and verify the access for user read_only_user:
edb=# \c edb read_only_user
psql (13.4.8, server 13.4.8)
You are now connected to database "edb" as user "read_only_user".
edb=>
edb=# create table t3 (name varchar2, type int);
CREATE TABLE
edb=#
edb=# insert into t3(name) values (name 'students');
INSERT 0 1
edb=#
edb=# \c edb read_only_user
psql (13.4.8, server 13.4.8)
You are now connected to database "edb" as user "read_only_user".
edb=>
edb=> select * from t3;
name | type
students |
(1 row)
edb=>
We can see that we are able to access table T3 which is created after granting privileges to read_only_user but still he can read data from table T3.