Skip to content

Instantly share code, notes, and snippets.

@coder4web

coder4web/postgresql_roles.sql

Last active July 17, 2018 13:24
Download ZIP

Revisions

  1. coder4web revised this gist Jul 17, 2018. 1 changed file with 17 additions and 4 deletions.
    21 changes: 17 additions & 4 deletions postgresql_roles.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -7,7 +7,20 @@ GRANT ALL ON ALL TABLES IN SCHEMA public TO admin_user;
    GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO admin_user;

    -- Read-only access
    GRANT USAGE ON SCHEMA public TO read_user;
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_user;
    GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO read_user;
    GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO read_user;
    CREATE ROLE readonly;

    -- grant access to all existing tables
    GRANT CONNECT ON DATABASE shop TO readonly;
    GRANT USAGE ON SCHEMA public TO readonly;
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
    GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
    GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly;

    -- grant access to all table which will be created in the future
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO readonly;
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO readonly;

    -- create user and grant role to this user
    CREATE USER b_readonly WITH PASSWORD 'reAdOnLy123';
    GRANT readonly TO b_readonly;
  2. coder4web revised this gist Jul 17, 2018. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions postgresql_roles.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,5 @@
    \z

    -- Full access
    GRANT ALL privileges ON DATABASE db TO admin_user;
    GRANT ALL ON schema public TO admin_user;
  3. coder4web revised this gist Jul 17, 2018. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions postgresql_roles.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,5 @@
    -- Full access
    GRANT ALL privileges ON DATABASE db TO admin_user;
    GRANT ALL ON schema public TO admin_user;
    GRANT ALL ON ALL TABLES IN SCHEMA public TO admin_user;
    GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO admin_user;
  4. coder4web renamed this gist Jul 17, 2018. 1 changed file with 0 additions and 0 deletions.
    0 postgresql_grant.sql → postgresql_roles.sql
    View file
    File renamed without changes.
  5. coder4web renamed this gist Jul 17, 2018. 1 changed file with 0 additions and 0 deletions.
    0 gistfile1.txt → postgresql_grant.sql
    View file
    File renamed without changes.
  6. coder4web created this gist Jul 17, 2018.
    10 changes: 10 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,10 @@
    -- Full access
    GRANT ALL ON schema public TO admin_user;
    GRANT ALL ON ALL TABLES IN SCHEMA public TO admin_user;
    GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO admin_user;

    -- Read-only access
    GRANT USAGE ON SCHEMA public TO read_user;
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_user;
    GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO read_user;
    GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO read_user;