Skip to content

Instantly share code, notes, and snippets.

@jeffjohnson9046

jeffjohnson9046/create-postgres-db-and-schema.sql

Last active September 21, 2021 14:17
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save jeffjohnson9046/96b69a786bd68881e849e61288fab3c4 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save jeffjohnson9046/96b69a786bd68881e849e61288fab3c4 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. jeffjohnson9046 revised this gist Apr 3, 2021. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions create-postgres-db-and-schema.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,7 @@
    CREATE USER [database owner name] WITH CREATEROLE ENCRYTPED PASSWORD '[database owner's password]';
    CREATE DATABASE [database name] OWNER [database owner name];
    -- connect to [database name]
    REVOKE CREATE ON SCHEMA public FROM PUBLIC;
    REVOKE ALL ON DATABASE [database name] FROM PUBLIC;
    REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;
  2. jeffjohnson9046 revised this gist Apr 3, 2021. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion create-postgres-db-and-schema.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -15,7 +15,7 @@ GRANT USAGE ON SCHEMA [schema name] TO [read/write application group name];
    GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA [schema name] TO [read/write application group name];
    GRANT USAGE ON ALL SEQUENCES IN SCHEMA [schema name] TO [read/write application group name];
    ALTER DEFAULT PRIVILEGES IN SCHEMA [schema name] GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO [read/write application group name];
    ALTER DEFAULT PRIVILEGES IN SCHEMA [schema] GRANT USAGE ON SEQUENCES TO [read/write application group name];
    ALTER DEFAULT PRIVILEGES IN SCHEMA [schema name] GRANT USAGE ON SEQUENCES TO [read/write application group name];
    CREATE USER [read/write application user] WITH ENCRYPTED PASSWORD '[read/write application user password]' IN ROLE [read/write application group name];
    ALTER USER [read/write application user] SET SEARCH_PATH TO [schema name];
  3. jeffjohnson9046 revised this gist Apr 3, 2021. 1 changed file with 3 additions and 2 deletions.
    5 changes: 3 additions & 2 deletions create-postgres-db-and-schema.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,4 @@
    CREATE USER [database owner name] CREATEROLE WITH PASSWORD '[database owner's password]';
    CREATE USER [database owner name] WITH CREATEROLE ENCRYTPED PASSWORD '[database owner's password]';
    CREATE DATABASE [database name] OWNER [database owner name];
    REVOKE CREATE ON SCHEMA public FROM PUBLIC;
    @@ -7,6 +7,7 @@ REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;
    -- log in as [database owner name] to [database name] db
    CREATE SCHEMA [schema name];
    ALTER USER [database owner name] SET SEARCH_PATH TO [schema name];
    CREATE ROLE [read/write application group name] NOINHERIT;
    GRANT CONNECT ON DATABASE [database name] TO [read/write application group name];
    @@ -16,5 +17,5 @@ GRANT USAGE ON ALL SEQUENCES IN SCHEMA [schema name] TO [read/write application
    ALTER DEFAULT PRIVILEGES IN SCHEMA [schema name] GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO [read/write application group name];
    ALTER DEFAULT PRIVILEGES IN SCHEMA [schema] GRANT USAGE ON SEQUENCES TO [read/write application group name];
    CREATE USER [read/write application user] IN ROLE [read/write application group name] WITH PASSWORD '[read/write application user password]';
    CREATE USER [read/write application user] WITH ENCRYPTED PASSWORD '[read/write application user password]' IN ROLE [read/write application group name];
    ALTER USER [read/write application user] SET SEARCH_PATH TO [schema name];
  4. jeffjohnson9046 revised this gist Apr 3, 2021. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion create-postgres-db-and-schema.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -6,7 +6,7 @@ REVOKE ALL ON DATABASE [database name] FROM PUBLIC;
    REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;
    -- log in as [database owner name] to [database name] db
    CREATE SCHEMA [schema name] AUTHORIZATION [database owner name];
    CREATE SCHEMA [schema name];
    CREATE ROLE [read/write application group name] NOINHERIT;
    GRANT CONNECT ON DATABASE [database name] TO [read/write application group name];
  5. jeffjohnson9046 revised this gist Apr 3, 2021. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion create-postgres-db-and-schema.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -6,7 +6,7 @@ REVOKE ALL ON DATABASE [database name] FROM PUBLIC;
    REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;
    -- log in as [database owner name] to [database name] db
    CREATE SCHEMA [schema name];
    CREATE SCHEMA [schema name] AUTHORIZATION [database owner name];
    CREATE ROLE [read/write application group name] NOINHERIT;
    GRANT CONNECT ON DATABASE [database name] TO [read/write application group name];
  6. jeffjohnson9046 revised this gist Apr 3, 2021. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions create-postgres-db-and-schema.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -6,6 +6,7 @@ REVOKE ALL ON DATABASE [database name] FROM PUBLIC;
    REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;
    -- log in as [database owner name] to [database name] db
    CREATE SCHEMA [schema name];
    CREATE ROLE [read/write application group name] NOINHERIT;
    GRANT CONNECT ON DATABASE [database name] TO [read/write application group name];
  7. jeffjohnson9046 revised this gist Apr 3, 2021. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion create-postgres-db-and-schema.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -16,4 +16,4 @@ ALTER DEFAULT PRIVILEGES IN SCHEMA [schema name] GRANT SELECT, INSERT, UPDATE, D
    ALTER DEFAULT PRIVILEGES IN SCHEMA [schema] GRANT USAGE ON SEQUENCES TO [read/write application group name];
    CREATE USER [read/write application user] IN ROLE [read/write application group name] WITH PASSWORD '[read/write application user password]';
    ALTER USER app_user SET SEARCH_PATH TO [schema name];
    ALTER USER [read/write application user] SET SEARCH_PATH TO [schema name];
  8. jeffjohnson9046 created this gist Apr 3, 2021.
    19 changes: 19 additions & 0 deletions create-postgres-db-and-schema.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,19 @@
    CREATE USER [database owner name] CREATEROLE WITH PASSWORD '[database owner's password]';
    CREATE DATABASE [database name] OWNER [database owner name];
    REVOKE CREATE ON SCHEMA public FROM PUBLIC;
    REVOKE ALL ON DATABASE [database name] FROM PUBLIC;
    REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;
    -- log in as [database owner name] to [database name] db
    CREATE ROLE [read/write application group name] NOINHERIT;
    GRANT CONNECT ON DATABASE [database name] TO [read/write application group name];
    GRANT USAGE ON SCHEMA [schema name] TO [read/write application group name];
    GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA [schema name] TO [read/write application group name];
    GRANT USAGE ON ALL SEQUENCES IN SCHEMA [schema name] TO [read/write application group name];
    ALTER DEFAULT PRIVILEGES IN SCHEMA [schema name] GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO [read/write application group name];
    ALTER DEFAULT PRIVILEGES IN SCHEMA [schema] GRANT USAGE ON SEQUENCES TO [read/write application group name];
    CREATE USER [read/write application user] IN ROLE [read/write application group name] WITH PASSWORD '[read/write application user password]';
    ALTER USER app_user SET SEARCH_PATH TO [schema name];