Thursday, September 5, 2013

Postgres grant read only

Prior to postgres 9.0 you can use this:



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

Postgres 9+ you can use this:



 GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx;  

Reference:


http://stackoverflow.com/questions/760210/how-do-you-create-a-read-only-user-in-postgresql