Skip to content

Instantly share code, notes, and snippets.

@dexter0201

dexter0201/rw_ro_access.sql

Forked from checco/rw_ro_access.sql
Created July 25, 2022 02:48
Show Gist options
  • Save dexter0201/fe9ef00cf90b4728cc5dc96e64fba845 to your computer and use it in GitHub Desktop.
Save dexter0201/fe9ef00cf90b4728cc5dc96e64fba845 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. Francesco Latini revised this gist Apr 8, 2020. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions rw_ro_access.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -8,6 +8,7 @@ CREATE ROLE postgres_ro_group;
    -- Grant access to existing tables
    GRANT USAGE ON SCHEMA public TO postgres_ro_group;
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO postgres_ro_group;
    GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO postgres_ro_group;

    -- Grant access to future tables
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO postgres_ro_group;
  2. Francesco Latini revised this gist Sep 27, 2017. 2 changed files with 25 additions and 13 deletions.
    13 changes: 0 additions & 13 deletions read-access.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,13 +0,0 @@
    -- Create a group
    CREATE ROLE readaccess;

    -- Grant access to existing tables
    GRANT USAGE ON SCHEMA public TO readaccess;
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;

    -- Grant access to future tables
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readaccess;

    -- Create a final user with password
    CREATE USER tomek WITH PASSWORD 'secret';
    GRANT readaccess TO tomek;
    25 changes: 25 additions & 0 deletions rw_ro_access.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,25 @@
    --
    -- Read only
    --

    -- Create a group
    CREATE ROLE postgres_ro_group;

    -- Grant access to existing tables
    GRANT USAGE ON SCHEMA public TO postgres_ro_group;
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO postgres_ro_group;

    -- Grant access to future tables
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO postgres_ro_group;

    -- Create a final user with password
    CREATE USER postgres_ro WITH PASSWORD 'secret';
    GRANT postgres_ro_group TO postgres_ro;

    --
    -- Superuser
    --

    -- Create a final user with password
    CREATE USER postgres_adm WITH PASSWORD 'secret';
    GRANT rds_superuser to postgres_adm;
  3. @oinopion oinopion created this gist Oct 5, 2016.
    13 changes: 13 additions & 0 deletions read-access.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,13 @@
    -- Create a group
    CREATE ROLE readaccess;

    -- Grant access to existing tables
    GRANT USAGE ON SCHEMA public TO readaccess;
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;

    -- Grant access to future tables
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readaccess;

    -- Create a final user with password
    CREATE USER tomek WITH PASSWORD 'secret';
    GRANT readaccess TO tomek;