PostgreSQL: How to create a read only user?

Suppose you are Super Admin, sometimes you need to give the read-only permission to particular user for a safety purpose, because, it may be possible a specific user don’t have sufficient  knowledge about the PostgreSQL and by mistake, he/she executes some fetal query who can get you in trouble.

PostgreSQL syntax to create a read-only user

First of all, I will create a user using the following PostgreSQL statement

CREATE USER read_only WITH PASSWORD 'Readonly#1' VALID UNTIL '2020-05-30'; --CREATE ROLE

Now you can log in with read_only user credentials, but you cannot access the table or cannot do any other activity with this credential. For check, If you run the select statement "SELECT * FROM MyTable" then you will get the following error.

"ERROR:  permission denied for table MyTable"

"SQL state: 42501"

Grant read-only permission to the single table

If you want to grant read-only permission to a user on a particular table, use the following statement, assuming we have a table employee and want to grant read-only permission to the user on this table.

GRANT USAGE ON SCHEMA public TO read_only; GRANT SELECT ON employee TO read_only;

Here, you cannot access the other table except "employee." 

Grant access to all tables of a schema

PostgreSQL 9.0 and the latest version provide the solution to grant permission to all tables/views/etc. Using the following PostgreSQL statement, you can give access permission to a user to all tables.

GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

 

Grant read-only permission on multiple tables/views (on the prior version to PostgreSQL 9.0)

Another the exciting thing I want to mention here is, if you are working on a version before PostgreSQL 9.0, then you have to write one PostgreSQL query/statement one by one for all tables/views to allow read-only. Run the following query, and it will generate the required GRANT SELECT to each table/view.

SELECT 'GRANT SELECT ON ' || relname || ' TO read_only;' FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE nspname = 'public' AND relkind IN ('r', 'v', 'S');