Create a read only user in postgresql

Posted on vr 08 juni 2012 in databases

There apparently is no simpel way to create a user with only read / select rights on a Postgresql instance. For future reference the select statements that worked for me. The following statements produce SQL statements that must be executed to take effect :

select ‘GRANT SELECT ON ALL TABLES IN SCHEMA ‘||schema_name||’ TO read-user;’  from “information_schema”.”schemata”;
select ‘GRANT USAGE ON SCHEMA ‘||schema_name||’ TO read-user;’  from “information_schema”.”schemata”;
select ‘ALTER DEFAULT PRIVILEGES IN SCHEMA ‘||schema_name||’ GRANT SELECT ON TABLES TO read-user;’  from “information_schema”.”schemata”;

Messy if you ask me, but it works.