Manage privileges for backup

From PostgreSQL wiki

Revision as of 06:13, 18 July 2011 by Chronos (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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!

Personal tools