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.

PostgreSQL : Move your stuff out of the public schema

Using the default public schema is not recommended. If you have created a bunch of tables and functions in the public schema, you can use the following scripts to move them to a different schema:

Move tables:

  DO$$   
  DECLARE   
   row record;   
  BEGIN   
   FOR row IN SELECT tablename FROM pg_tables WHERE schemaname = 'public'   
   LOOP   
    EXECUTE 'ALTER TABLE public.' || quote_ident(row.tablename) || ' SET SCHEMA [new_schema];';   
   END LOOP;   
  END;$$;  

Move functions:

  DO  
 $do$  
 DECLARE  
   sql text;  
 BEGIN  
   SELECT INTO sql  
      string_agg(format('ALTER FUNCTION public.%I(%s) SET SCHEMA new_schema;'  
           ,p.proname, pg_get_function_identity_arguments(p.oid)), E'\n')  
   FROM  pg_proc p  
   JOIN  pg_namespace n ON n.oid = p.pronamespace  
   WHERE nspname = 'public';  
    -- and other conditions, if needed  
   RAISE NOTICE '%', sql; -- for viewing the sql before executing it  
   -- EXECUTE sql; -- for executing the sql  
 END  
 $do$