Thread: Database level read only user

Hi,

Today a client ask me for help to create a read only user for a postgresql 8.2 database, i ended up reading, copying
andpasting and finally creating a function i'm sharing with the list: 

CREATE OR REPLACE FUNCTION db_grant(usuario text, privilegio text)
RETURNS INTEGER AS $$
DECLARE
  db RECORD;
BEGIN
  FOR db IN
    SELECT nspname
        FROM pg_namespace
        WHERE has_schema_privilege(nspname, 'USAGE') and nspname !~ '^pg_'
  LOOP
    EXECUTE 'GRANT USAGE ON schema ' || db.nspname || ' to ' || usuario;
  END LOOP;


  FOR db IN
        SELECT *
          FROM pg_tables
          WHERE tableowner = current_user
  LOOP
    EXECUTE 'GRANT ' || privilegio || ' ON '  || db.schemaname || '.' || db.tablename || ' TO ' || usuario;
  END LOOP;


  RETURN 0;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION db_grant (text, text)
IS 'Give permissions at database level, Hans Poo, Santiago Julio de 2011';


-- This CREATE must be run by the owner of the database and will be created in the default schema usually public.

-- This is the call:

select db_grant('usuario1','select');


Bye
Hans