-- Generated from user.pdf at https://momjian.us/presentations
-- This is intended to be run by psql so backslash commands are processed.

-- setup
\pset footer off
\pset null (null)




SELECT CURRENT_USER;

\du
-- CREATE USER defaults to LOGIN ability, while the others are 
CREATE USER demo_user;

CREATE GROUP demo_group;

CREATE ROLE demo_role;

\du demo_*
\c - demo_user
SELECT CURRENT_USER;

SELECT CURRENT_ROLE;

\h create role
CREATE ROLE name [ [ WITH ] option [ ... ] ]
where option can be:
      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | SYSID uid
URL: https://www.postgresql.org/docs/devel/sql-createrole.html;

\c - postgres
DO $$
DECLARE i iNTEGER;
BEGIN
        FOR i IN 1..15 LOOP
                EXECUTE format('CREATE TABLE single_owner_%s (x 
INTEGER)',
                               right('0' || i, 2));
        END LOOP;
END;
$$ LANGUAGE plpgsql;

\dt single_owner_*
DO $$
DECLARE i iNTEGER;
BEGIN
        FOR i IN 1..15 LOOP
                EXECUTE format('CREATE USER user_%s',
                               right('0' || i, 2));
                EXECUTE format('CREATE TABLE multi_owner_%s (x 
INTEGER)',
                               right('0' || i, 2));
                EXECUTE format('ALTER TABLE multi_owner_%s 
OWNER TO user_%s',
                               right('0' || i, 2), right('0' || 
i, 2));
        END LOOP;
END;
$$ LANGUAGE plpgsql;

\dt multi_owner_*
CREATE DATABASE db_01 OWNER user_01;

\l db_01
\c - user_01
GRANT ALL ON SCHEMA public TO PUBLIC;

CREATE EXTENSION fuzzystrmatch;

\c db_01 user_01
SELECT current_user;

-- Starting in PostgreSQL 15, by default the public schema only 
-- by the database owner.
GRANT ALL ON SCHEMA public TO PUBLIC;

CREATE EXTENSION fuzzystrmatch;

\c - postgres
ALTER USER user_01 CONNECTION LIMIT 8;

\! psql --username user_01 -c 'SELECT pg_sleep(3)' test &
\! psql --username user_02 -c 'SELECT pg_sleep(3)' test &
\! psql --username user_03 -c 'SELECT pg_sleep(3)' test &
-- give background processes time to start
SELECT pg_sleep(1);

SELECT usename, current_timestamp - query_start, query
FROM pg_stat_activity
WHERE usename IS NOT NULL AND
      state = 'active' AND
      pid != pg_backend_pid();

ALTER USER user_01 SET work_mem = '6MB';

ALTER USER user_02 SET work_mem = '8MB';

ALTER USER user_03 SET work_mem = '10MB';

\c - user_01
SHOW work_mem;

\c - user_02
SHOW work_mem;

\c - user_03
SHOW work_mem;

\c - postgres
CREATE ROLE user_01a IN ROLE user_01 LOGIN;

SELECT rolname, roleid::regrole AS "Is member of role"
FROM  pg_roles, pg_auth_members
WHERE pg_roles.oid = member AND
      rolname NOT LIKE 'pg_%'
ORDER BY 1, 2;

\c - user_01a
SELECT current_user;

SET ROLE user_01;

SELECT current_user;

GRANT ALL ON SCHEMA public TO PUBLIC;

\c - user_01
CREATE TABLE drop_test(x INTEGER);

\c - user_01a
DROP TABLE drop_test;

\c - postgres
CREATE ROLE user_01b IN ROLE user_01 LOGIN NOINHERIT;

\c - user_01
CREATE TABLE drop_test1 (x INTEGER);

\c - user_01b
DROP TABLE drop_test1;

SET ROLE user_01;

DROP TABLE drop_test1;

SELECT rolname, roleid::regrole AS "Is member of role", 
inherit_option
FROM  pg_roles, pg_auth_members
WHERE member = pg_roles.oid AND
      rolname NOT LIKE 'pg_%'
ORDER BY 1, 2;

\c - postgres
REVOKE INHERIT OPTION FOR user_01 FROM user_01a;

\c - user_01
CREATE TABLE drop_test2 (x INTEGER);

\c - user_01a
DROP TABLE drop_test2;

SET ROLE user_01;

DROP TABLE drop_test2;

SELECT rolname, roleid::regrole AS "Is member of role", 
inherit_option
FROM  pg_roles, pg_auth_members
WHERE member = pg_roles.oid AND
      rolname NOT LIKE 'pg_%'
ORDER BY 1, 2;

\c - postgres
REVOKE SET OPTION FOR user_01 FROM user_01a;

\c - user_01
CREATE TABLE drop_test3 (x INTEGER);

\c - user_01a
DROP TABLE drop_test3;

SET ROLE user_01;

SELECT rolname, roleid::regrole AS "Is member of role", 
inherit_option, set_option
FROM  pg_roles, pg_auth_members
WHERE member = pg_roles.oid AND
      rolname NOT LIKE 'pg_%'
ORDER BY 1, 2;

\c - postgres
GRANT user_01 TO user_02;

GRANT user_01 TO user_03 WITH INHERIT FALSE;

SELECT rolname, roleid::regrole AS "Is member of role", 
inherit_option, set_option
FROM  pg_roles, pg_auth_members
WHERE member = pg_roles.oid AND
      rolname NOT LIKE 'pg_%'
ORDER BY 1, 2;

REVOKE user_01 FROM user_03;

SELECT rolname, roleid::regrole AS "Is member of role", 
inherit_option, set_option
FROM  pg_roles, pg_auth_members
WHERE member = pg_roles.oid AND
      rolname NOT LIKE 'pg_%'
ORDER BY 1, 2;

\c - postgres
GRANT user_01 TO user_01a WITH ADMIN TRUE;

\c - user_01a
GRANT user_01 TO user_04;

SELECT rolname, roleid::regrole AS "Is member of role", 
inherit_option, set_option, admin_option
FROM  pg_roles, pg_auth_members
WHERE member = pg_roles.oid AND
      rolname NOT LIKE 'pg_%'
ORDER BY 1, 2;

-- This was added to psql in PostgreSQL 16.
\drg
-- This was added in PostgreSQL 16.
\c - postgres
\drg user_01a
GRANT user_01 TO user_01a WITH INHERIT TRUE, SET TRUE, ADMIN 
FALSE;

\drg user_01a
CREATE ROLE user_20 ROLE user_07, user_08, user_09 IN ROLE 
user_11, user_12, user_13;

SELECT rolname, roleid::regrole AS "Is member of role", 
inherit_option,
       set_option, admin_option
FROM  pg_roles, pg_auth_members
WHERE member = pg_roles.oid AND
      rolname NOT LIKE 'pg_%'
ORDER BY 1, 2;

\c - user_11
CREATE TABLE drop_test4 (x INTEGER);

\c - user_07
-- user_07 is a member of user_20, and user_10 is a member of 
DROP TABLE drop_test4;

SET ROLE user_11;

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | 
REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]
 
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name 
[, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ];

\c - user_01
CREATE TABLE grant_test (x INTEGER);

\dp
GRANT SELECT ON TABLE grant_test TO user_02;

\dp
SELECT relacl FROM pg_class where relname = 'grant_test';

SELECT (aclexplode(relacl)).grantee::regrole,
       (aclexplode(relacl)).privilege_type
FROM pg_class
WHERE relname = 'grant_test'
ORDER BY 1, 2;

WITH acls AS (
    SELECT (aclexplode(relacl)).grantee::regrole AS rolename,
           (aclexplode(relacl)).privilege_type AS acl
    FROM pg_class
    WHERE relname = 'grant_test'
) SELECT rolename, array_agg(acl)
FROM acls
GROUP BY 1
ORDER BY 1, 2;

SELECT grantee,
       array_agg(privilege_type)
FROM  information_schema.role_table_grants
WHERE table_name = 'grant_test'
GROUP BY grantee
ORDER BY 1,2;

\du pg_*
\c - user_01
SELECT * FROM pg_authid;

\c - postgres
GRANT pg_read_all_data TO user_01;

\c - user_01
SELECT * FROM pg_authid;

