Gruppe mit Mitgliedern anlegen

-- 1. Gruppen-Rolle (NOLOGIN, also reiner Container)
CREATE ROLE devs;

-- 2. Login-Rollen (User)
CREATE USER alice PASSWORD 'changeme';
CREATE USER bob   PASSWORD 'changeme';
CREATE USER carol PASSWORD 'changeme';

-- 3. Mitgliedschaft: alle drei kommen in devs
GRANT devs TO alice, bob, carol;

-- 4. Rechte einmal an die Gruppe vergeben
GRANT USAGE ON SCHEMA app TO devs;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA app TO devs;

REVOKE devs FROM bob;

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'bob' AND pid <> pg_backend_pid();

SELECT
    r.rolname  AS member,
    g.rolname  AS member_of,
    m.admin_option
FROM pg_auth_members m
JOIN pg_roles r ON r.oid = m.member
JOIN pg_roles g ON g.oid = m.roleid
WHERE NOT g.rolname LIKE 'pg\_%'
ORDER BY g.rolname, r.rolname;

 member | member_of | admin_option
--------+-----------+--------------
 alice  | devs      | f
 bob    | devs      | f
 carol  | devs      | f

myapp=# \du+
        List of roles
 Role name |    Attributes    | Member of   | Description
-----------+------------------+-------------+-------------
 alice     |                  | {devs}      |
 bob       |                  | {devs}      |
 carol     |                  | {devs}      |
 devs      | Cannot login     | {}          | Dev-Team

COMMENT ON ROLE devs IS 'Dev-Team mit Read-Write auf app-Schema';

CREATE ROLE devs;
CREATE ROLE reporters;

-- Devs: voller Zugriff auf app-Schema
GRANT USAGE ON SCHEMA app TO devs;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO devs;

-- Reporters: nur Lesen, dafuer in app UND analytics
GRANT USAGE ON SCHEMA app, analytics TO reporters;
GRANT SELECT ON ALL TABLES IN SCHEMA app, analytics TO reporters;

-- Mitgliedschaften
CREATE USER alice PASSWORD 'changeme';
CREATE USER bob   PASSWORD 'changeme';
CREATE USER carol PASSWORD 'changeme';

GRANT devs TO alice, bob;          -- Alice + Bob entwickeln
GRANT reporters TO alice, carol;   -- Alice + Carol haben Reporting-Zugang

GRANT devs TO team_lead WITH ADMIN OPTION;

GRANT devs TO new_intern;
REVOKE devs FROM ex_employee;