Thursday, October 24, 2013

PostgreSQL grant permissions on tables and sequences

PostgreSQL grant permissions on tables and sequences in selected schema for selected user:

 DO $$  
 DECLARE  
     _tables text := (  
         SELECT  
          string_agg(  
             'GRANT SELECT, INSERT, UPDATE ON '  
                 || table_schema || '.' || table_name  
                 ||' to my_user;',  
             E'\n')  
         FROM information_schema.tables   
         WHERE table_type = 'BASE TABLE' AND table_schema = 'my_schema'  
     );  
     _sequences text := (  
         SELECT  
          string_agg(  
             'GRANT USAGE ON ' || n.nspname || '.' || c.relname ||' to my_user;',  
             E'\n')  
         FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace  
         WHERE relkind='S' AND n.nspname = 'my_schema'  
     );  
 BEGIN  
     RAISE NOTICE E'\n%\n%', _sequences, _tables;  
 END;$$  

Note: I personally (normally) don't like to allow deletes on my tables from apps - feel free to add a DELETE grant if you need it.

No comments:

Post a Comment