PG WORKSHOP

Login roles

Login roles er dem, der kan logge ind. Best practice er at lade dem arve (inherit) rettigheder fra deres tilknyttede Group roles.

Definition af en simpel Login role:

CREATE ROLE andy LOGIN
  ENCRYPTED PASSWORD 'md5fa490f8d4c5783713935b8b8264b99ce'
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

  GRANT kbh_readonly TO andy;

Eksempler

-- Opret ny læse rolle
CREATE ROLE haderslev_readonly NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

--Opret ny bruger- Kamilla - med password  
CREATE USER kamilla WITH PASSWORD 'kamilla'
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
--Tilknyt Kamilla haderslev_readonly rollen
GRANT haderslev_readonly TO kamilla;
GRANT kbh_readonly TO kamilla;

 --Giv rolle haderslev_readonly SELECT adgang til tabeller samt funktioner i public schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO haderslev_readonly;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO haderslev_readonly;

--Giv rolle haderslev_readonly USAGE adgang til schema emdb
GRANT USAGE ON SCHEMA emdb TO haderslev_readonly;

 --Giv rolle haderslev_readonly SELECT adgang til tabeller i emdb schema
GRANT SELECT ON ALL TABLES IN SCHEMA emdb TO haderslev_readonly;

--Fjern kamilla fra gruppen haderslev_readonly
REVOKE haderslev_readonly FROM kamilla;

 --Giv rolle haderslev_readonly FULD (ALL) adgang til tabeller i error schema
GRANT USAGE ON SCHEMA error TO haderslev_readonly;
GRANT ALL ON ALL TABLES IN SCHEMA error TO haderslev_readonly;


--Sæt default rettigheder på et schema
ALTER DEFAULT PRIVILEGES IN SCHEMA emdb
    GRANT SELECT ON TABLES TO haderslev_readonly;

--Se hvile roller, der findes
SELECT * FROM pg_roles

--hvilke roller tilhører en bruger?
select rolname from pg_user join pg_auth_members on (pg_user.usesysid=pg_auth_members.member) join pg_roles on (pg_roles.oid=pg_auth_members.roleid) where pg_user.usename='kamilla';

--- Hvad må forskelige roller på en tabel ?
SELECT grantee, string_agg(privilege_type, ', ') AS privileges
FROM information_schema.role_table_grants
WHERE table_name='jr49003v'
GROUP BY grantee;