Manage privileges for backup
From PostgreSQL wiki
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!
