Skip to content

Instantly share code, notes, and snippets.

@sahil1

sahil1/read-access.sql

Forked from oinopion/read-access.sql
Last active August 8, 2019 17:13
Show Gist options
  • Save sahil1/d1d7bec423eb72e9e01c4fab3c155a79 to your computer and use it in GitHub Desktop.
Save sahil1/d1d7bec423eb72e9e01c4fab3c155a79 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. sahil1 revised this gist Aug 8, 2019. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion read-access.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -17,7 +17,7 @@ GRANT ALL privileges ON database mydatabasename TO sahil;
    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO sahil;

    -- Change password
    ALTER USER sahil WITH ENCRYPTED PASSWORD 'newsecret'
    ALTER USER sahil WITH ENCRYPTED PASSWORD 'newsecret';
    ---------------------------------------
    ---------------------------------------
    -- List all users
  2. sahil1 revised this gist Aug 8, 2019. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions read-access.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -16,6 +16,8 @@ GRANT readaccess TO sahil;
    GRANT ALL privileges ON database mydatabasename TO sahil;
    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO sahil;

    -- Change password
    ALTER USER sahil WITH ENCRYPTED PASSWORD 'newsecret'
    ---------------------------------------
    ---------------------------------------
    -- List all users
  3. sahil1 revised this gist Aug 8, 2019. 1 changed file with 5 additions and 1 deletion.
    6 changes: 5 additions & 1 deletion read-access.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -9,9 +9,13 @@ GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readaccess;

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

    -- Grant all priviliges on all tables
    GRANT ALL privileges ON database mydatabasename TO sahil;
    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO sahil;

    ---------------------------------------
    ---------------------------------------
    -- List all users
  4. sahil1 revised this gist Aug 8, 2019. 1 changed file with 4 additions and 2 deletions.
    6 changes: 4 additions & 2 deletions read-access.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -14,8 +14,10 @@ GRANT readaccess TO sahil;

    ---------------------------------------
    ---------------------------------------
    -- List all users
    SELECT * FROM pg_user;

    -- Query Users
    -- Query user details
    SELECT u.usename AS "User name",
    u.usesysid AS "User ID",
    CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('superuser, create database' AS pg_catalog.text)
    @@ -26,7 +28,7 @@ SELECT u.usename AS "User name",
    FROM pg_catalog.pg_user u
    ORDER BY 1;

    -- Query Roles
    -- Query roles
    SELECT r.rolname, r.rolsuper, r.rolinherit,
    r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
    r.rolconnlimit, r.rolvaliduntil,
  5. sahil1 revised this gist Aug 8, 2019. 1 changed file with 14 additions and 0 deletions.
    14 changes: 14 additions & 0 deletions read-access.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -24,4 +24,18 @@ SELECT u.usename AS "User name",
    ELSE CAST('' AS pg_catalog.text)
    END AS "Attributes"
    FROM pg_catalog.pg_user u
    ORDER BY 1;

    -- Query Roles
    SELECT r.rolname, r.rolsuper, r.rolinherit,
    r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
    r.rolconnlimit, r.rolvaliduntil,
    ARRAY(SELECT b.rolname
    FROM pg_catalog.pg_auth_members m
    JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
    WHERE m.member = r.oid) AS memberof
    , r.rolreplication
    , r.rolbypassrls
    FROM pg_catalog.pg_roles r
    WHERE r.rolname !~ '^pg_'
    ORDER BY 1;
  6. sahil1 revised this gist Aug 8, 2019. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions read-access.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -9,8 +9,8 @@ GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;
    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;
    CREATE USER sahil WITH PASSWORD 'secret';
    GRANT readaccess TO sahil;

    ---------------------------------------
    ---------------------------------------
  7. sahil1 revised this gist Aug 8, 2019. 1 changed file with 14 additions and 0 deletions.
    14 changes: 14 additions & 0 deletions read-access.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -11,3 +11,17 @@ 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;

    ---------------------------------------
    ---------------------------------------

    -- Query Users
    SELECT u.usename AS "User name",
    u.usesysid AS "User ID",
    CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('superuser, create database' AS pg_catalog.text)
    WHEN u.usesuper THEN CAST('superuser' AS pg_catalog.text)
    WHEN u.usecreatedb THEN CAST('create database' AS pg_catalog.text)
    ELSE CAST('' AS pg_catalog.text)
    END AS "Attributes"
    FROM pg_catalog.pg_user u
    ORDER BY 1;
  8. @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;