Skip to content

Instantly share code, notes, and snippets.

@fabiojwalter
Created February 10, 2023 19:58
Show Gist options
  • Save fabiojwalter/8d7eb26fc40776c376bed05ed5ef7ce2 to your computer and use it in GitHub Desktop.
Save fabiojwalter/8d7eb26fc40776c376bed05ed5ef7ce2 to your computer and use it in GitHub Desktop.
PostreSQL restricted user creation
--Dropping schema public
DROP SCHEMA public CASCADE;
-- Removing access from other users
REVOKE connect ON DATABASE ${dbName} FROM PUBLIC;
-- Creating schema
CREATE SCHEMA ${schemaName} AUTHORIZATION ${userName};
-- Grant privileges for user on schema
GRANT ALL ON ALL TABLES IN SCHEMA ${schemaName} TO ${userName};
GRANT ALL ON ALL SEQUENCES IN SCHEMA ${schemaName} TO ${userName};
/*=== To set default privileges for future objects ===*/
--Grant all on tables
ALTER DEFAULT PRIVILEGES FOR ROLE ${userName} IN SCHEMA ${schemaName} GRANT ALL ON TABLES TO ${userName};
--Grant all on sequences
ALTER DEFAULT PRIVILEGES FOR ROLE ${userName} IN SCHEMA ${schemaName} GRANT ALL ON SEQUENCES TO ${userName};
REVOKE ALL ON DATABASE postgres FROM ${userName};
REVOKE ALL ON DATABASE template1 FROM ${userName};
REVOKE ALL PRIVILEGES ON SCHEMA pg_catalog FROM ${userName};
REVOKE ALL PRIVILEGES ON SCHEMA pg_catalog FROM public;
REVOKE ALL PRIVILEGES ON SCHEMA information_schema FROM ${userName};
REVOKE ALL PRIVILEGES ON SCHEMA information_schema FROM public;
--===========================================================
-- READ ONLY USER
GRANT CONNECT ON DATABASE ${dbname} TO ${userName};
GRANT USAGE ON SCHEMA ${schemaName} TO ${userName};
GRANT SELECT ON ALL TABLES IN SCHEMA ${schemaName} TO ${userName};
GRANT SELECT ON ALL SEQUENCES IN SCHEMA ${schemaName} TO ${username};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment