It is not possible to create a user with read only access on a particular database. Instead we can do it at schema level.
1) Grant connect to database
GRANT CONNECT ON DATABASE <db name> to <user name>;
2) Grant usage on schema
GRANT USAGE ON SCHEMA <schema name> TO <user name>;
3) Grant select on all tables in a schema using
GRANT SELECT ON ALL TABLES IN SCHEMA <schema name> TO <user name>;
4) Also make this privilege available for new tables added in future using
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema name> GRANT SELECT ON TABLES TO <user name>;
So, if we have many schemas we can simply generate ‘GRANT’ statements as mentioned below :
1) SELECT 'GRANT USAGE ON SCHEMA ' || nspname || ' TO <user name>;' FROM pg_namespace WHERE nspname not like 'pg%'; 2) SELECT 'GRANT SELECT ON ALL TABLES IN SCHEMA ' || nspname || ' TO <user name>;' FROM pg_namespace WHERE nspname not like 'pg%'; 3) SELECT 'ALTER DEFAULT PRIVILEGES IN SCHEMA ' || nspname || ' GRANT SELECT ON TABLES TO <user name>;' FROM pg_namespace WHERE nspname not like 'pg%';