PostgreSQL User management

user_management.sql

-- REVOKE WRIGHTS TO USER
REVOKE CONNECT ON DATABASE "db" FROM user;

-- Revoke all privileges on tables
DO $$ 
DECLARE 
   r RECORD;
BEGIN
   FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP
      EXECUTE 'REVOKE ALL PRIVILEGES ON TABLE ' || quote_ident(r.tablename) || ' FROM user';
   END LOOP;
END $$;

-- Revoke all privileges on sequences
DO $$ 
DECLARE 
   r RECORD;
BEGIN
   FOR r IN (SELECT sequence_name FROM information_schema.sequences WHERE table_schema = 'public') LOOP
      EXECUTE 'REVOKE ALL PRIVILEGES ON SEQUENCE ' || quote_ident(r.sequence_name) || ' FROM user';
   END LOOP;
END $$;

-- Revoke all privileges on functions
DO $$ 
DECLARE 
   r RECORD;
BEGIN
   FOR r IN (SELECT proname FROM pg_proc WHERE pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')) LOOP
      EXECUTE 'REVOKE ALL PRIVILEGES ON FUNCTION ' || quote_ident(r.proname) || ' FROM user';
   END LOOP;
END $$;

-- GIVE WRIGHTS TO USER
-- Grant connect privilege on the database
GRANT CONNECT ON DATABASE "db" TO user;

-- Grant usage on all sequences in the schema
DO $$ 
DECLARE 
   r RECORD;
BEGIN
   FOR r IN (SELECT sequence_name FROM information_schema.sequences WHERE table_schema = 'public') LOOP
      EXECUTE 'GRANT USAGE ON SEQUENCE ' || quote_ident(r.sequence_name) || ' TO user';
   END LOOP;
END $$;

-- Grant select, insert, update, and delete privileges on all tables in the schema
DO $$ 
DECLARE 
   r RECORD;
BEGIN
   FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP
      EXECUTE 'GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE ' || quote_ident(r.tablename) || ' TO user';
   END LOOP;
END $$;

GRANT CREATE ON SCHEMA public TO user;