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 and pasting 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
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
