Manage privileges for backup

Snippets

Manage privileges for backup

Works with PostgreSQL

any

Written in

SQL

Depends on

Nothing

by 3manuek

Suppose you want to have a 'backup' user, that only have perms when the backup is running. So, here a function that can manage privileges for each schema (Grant and Revoke).

CREATE OR REPLACE FUNCTION manage_backup_privileges(p_user text, p_schema text, action boolean)
RETURNS void AS
$BODY$
DECLARE
  objeto text;
BEGIN
  FOR objeto IN
     SELECT tablename FROM pg_tables WHERE schemaname = p_schema
     UNION
     SELECT relname FROM pg_statio_all_sequences WHERE schemaname =
p_schema
  LOOP
     IF action = true THEN
         RAISE NOTICE 'Asignando todos los privilegios a % sobre %.%', p_user,p_schema, objeto;
         EXECUTE 'GRANT ALL PRIVILEGES ON ' || p_schema || '.' || objeto || ' TO ' || p_user ;
     ELSE
         RAISE NOTICE 'Quitando todos los privilegios a % sobre %.%', p_user,p_schema, objeto;
         EXECUTE 'REVOKE ALL PRIVILEGES ON ' || p_schema || '.' || objeto || ' FROM ' || p_user ;
     END IF;
  END LOOP;

END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE;

Run it, is very simple. If you want active the privileges use this form:

select manage_backup_privileges('usuario','esquema', true); --look the third parametter, is true

If you want to revoke them:

select manage_backup_privileges('usuario','esquema', false);

Enjoy it!