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
