PostgreSQL rollen en privileges

Binnen PostgreSQL verloopt de autorisatie via rollen en het toekennen van privileges op databases aan rollen. Dit stuk is tot stand gekomen door bijdragen van Frank Heijkens, Frans Koers en Martin Simons.

Een ROLE geldt op databaseserver niveau, Privileges gelden op database niveau. Om de beheersbaarheid van de administratie van toegangsrechten te bevorderen verdient het aanbeveling om de rollen en gebruikers volgens een vaste structuur in te richten. De oude PostgreSQL term user is deprecated, de SQL standaard spreekt van ROLE.

Rollen zijn te relateren aan de life cycle momenten in de database. Life cycles in de database:

Het maken en onderhouden van de metastructuur, dit is het domein van de eigenaar. Door dit consequent te hanteren vallen alle objecten toe aan de eigenaar. Hieronder vallen het maken van tabellen, views, functies en stored procedures door de developers (in Dev dan eigenaar).

Het uitvoeren van een Failover, fail back. Deze acties hangen nauw samen met het platform waarop de database draait en passen bij de rol van de superuser

Repliceren en backup van de database kennen passende rollen

Het beheren rollen voor het gebruik, dat is de rol van de beheerder die kent permissies toe aan rollen en attributen aan gebruikers.

Een rol heeft de volgende kenmerken:

De naam

Attributen

Erfgenaam van een andere rol

Beschrijving

Rechten.

Attributen

PostgreSQL kent de volgende attributen bij het creëren van rollen:

LOGIN - De rol mag inloggen op de database server, let op dit is een rol op server niveau en geldt voor alle databases in dat cluster. Dit staat los van het recht om een connectie te maken met een specifieke database. Iedere rol heeft dus een login en een grant nodig.

SUPERUSER - De rol is een superuser

CREATEDB - De rol mag databases creëren

CREATEROLE - De rol mag rollen creëren

INHERIT - De default is dat een role altijd mag overerven van andere rollen. We laten het attribuut weg tenzij we de inherit willen voorkomen, dan gebruiken we NOINHERIT

REPLICATION - De rol mag streaming replication initieren

PASSWORD - het password van de role, in het algemeen hebben alleen gebruikers een password. Gebruikers zijn: Individuen, applicaties en bijvoorbeeld de replicatie user. Vanaf de development omgeving voegen we het keyword ENCRYPTED aan PASSWORD toe.

BYPASSRLS - De rol mag een ‘bypass Row Level Security checks’ doen

VALID UNTIL - Het password verloopt per deze datum

De toevoeging ‘NO’ betekent het expliciet onthouden van het betreffende recht aan de rol. De beheerder moet attributen voor elke rol specificeren, omdat ze niet overerfbaar zijn.

Host based access (pg_hba.conf)

Het bestand pg_hba.conf biedt de mogelijkheid om per server of per database toegang te verlenen aan alle of specifieke gebruikers. In onze opzet gebruiken wij dit bestand vooral om de database in algemene zin beschikbaar te maken voor toegang, de aanscherping vindt plaats in de definitie van de rollen en de permissies.

De rol van de replicator is belangrijk in een cluster, daarom regelen we deze toegang in pg_hba.conf.

1. TYPE DATABASE USER ADDRESS METHOD
2. "local" is for Unix domain socket connections only

local all all peer

1. IPv4 local connections:

host all all 127.0.0.1/32 scram-sha-256

1. IPv6 local connections:

host all all  ::1/128 scram-sha-256

1. Allow replication connections from localhost, by a user with the
2. replication privilege.

local replication all peer host replication all 127.0.0.1/32 scram-sha-256 host replication all  ::1/128 scram-sha-256 local replication all peer local all all peer

1. host all pgpool 172.17.12.0/24 scram-sha-256

host all pgpool 172.17.12.0/24 md5 host all postgres 172.17.12.0/24 md5 host replication repuser 172.17.12.0/24 scram-sha-256 host replication all 127.0.0.1/32 scram-sha-256 host replication all ::1/128 scram-sha-256

pg_hba.conf is dynamisch aan te passen en te activeren met pg_ctrl reload.

Privileges

PostgreSQL biedt de mogelijkheid om rollen te overerven, echter bovenstaande attributen vallen daarbuiten. Alles wat met een grant of revoke is ingesteld, dat zijn dus privileges, is overerfbaar.

Overerven

Als voorbeeld nemen we de rollen connect en applicatie. Elke rol, die een database connectie nodig heeft, heeft ten minste het attribuut login nodig en het privilege connect. Wij adviseren de volgende opzet.

Wij maken gebruik van overerving door per database een rol connect te benoemen en vervolgens de andere te laten erven.

CREATE ROLE bam_connect; CREATE ROLE bam_app; GRANT CONNECT ON DATABASE bam TO bam_connect; GRANT bam_connect TO bam_app;

Het voordeel van deze werkwijze is dat we het veerbinden met database op één plek beheren. Bijvoorbeeld de functie ‘noodrem’, in geval van een aanval of een database release. In dat geval doen we:

REVOKE CONNECT ON DATABASE bam FROM bam_connect;

In een database kennen wij verschillende soorten van gebruik en verschillende toepassingen. In deze structuur onderkennen wij de volgende types rollen:

Eigenaar

Beheer, superuser (incidenteel), replicator, admin, backup

Gebruik

De eigenaar

De eigenaar, of de owner, is de eigenaar van de database en alle objecten die zich in de database bevinden. De eigenaar heeft de volledige beschikking over elk object in de database. Om redenen van beveiliging splitsen wij de eigendom af van het gebruik van objecten, de eigenaar mag niet inloggen. In de productie omgeving heeft de eigenaar alleen bij database wijzigingen tijdelijk login rechten. Dit draagt bij aan de beveiliging van de database. De eigenaar definieert de abstracte rollen.

Alleen de eigenaar mag functies, tabellen, view, indexes, cast aanmaken. PostgreSQL maakt een object aan of manipuleert een object telkens als je een create, drop of alter gebruikt. De eigenaar doet ook de grant en revoke op:

schema’s

tabellen

views

functies

stored procedures

etcetera

PostgreSQL laat de overerving van rollen toe, op die wijze implementeren wij een hierarchie. De beveiliging van de gegevens in de database staat bij het toekennen van rechten centraal. De eigenaar van de database is eigenaar, maar heeft verder geen enkel recht, dit geldt in TAP. In de development omgeving is de eigenaar nodig om het geheel vorm te geven.

Beheer

De beheerder beheert de rollen,

We richten het bij Conclusion zo in, dat de rollen voor gebruikers aansluiten op RBAC uit Active Directory.

Voorbeeld

Voor NS hebben we de volgende opzet uitgewerkt:

-- Deze rechten en rollen gelden in de dev omgeving -- CREATE ROLE bam_owner; CREATE ROLE bam_dev; CREATE ROLE bam_devdba; CREATE ROLE bam_beheer; CREATE ROLE bam_app; CREATE ROLE bam_rep WITH REPLICATION PASSWORD 'bam_rep'; CREATE ROLE bam_analist;

-- Hierna koppelen we de rollen aan privileges op de database GRANT ALL ON BAM_T1 to bam_devdba; GRANT ALL ON BAM_T1 to bam_rep; GRANT USAGE ON SCHEMA bam_kern to bam_app; GRANT SELECT INSERT UPDATE DELETE ON ALL TABLES IN SCHEMA bam_kern to bam_app; GRANT USAGE ON SCHEMA bam_kern to bam_analist; GRANT SELECT ON ALL TABLES IN SCHEMA bam_kern to bam_analist;

-- Hierna komen de specifieke rechten op tabellen views en dergelijke CREATE ROLE harm WITH LOGIN PASSWORD 'harm'; GRANT bam_devdba to harm; GRANT bam_beheer to harm;

CREATE ROLE developer WITH LOGIN PASSWORD 'developer'; GRANT bam_dev to developer;

CREATE ROLE analist WITH LOGIN PASSWORD 'analist'; GRANT bam_analist to analist;

-- De bijzondere role bam_connect, voor elke database bestaat een aparte connect role CREATE ROLE bam_connect; GRANT CONNECT ON DATABASE BAM_T1 TO bam_connect; GRANT CONNECT ON DATABASE BAM_T2 TO bam_connect;

-- GRANT bam_connect to bam_owner; GRANT bam_connect to bam_dev; GRANT bam_connect to bam_devdba; GRANT bam_connect to bam_beheer; GRANT bam_connect to bam_app; GRANT bam_connect to bam_rep; GRANT bam_connect to bam_analist;

Types rollen:

Eigendom

Beheer

Gebruik, developer, tester en applicatie

Schema

Name

bam_kern

abstract_inzet

bam_kern

activiteit

bam_kern

beweging

bam_kern

databasechangelog

bam_kern

databasechangeloglock

bam_kern

dienst

bam_kern

inzet_op_beweging

bam_kern

materieel_claim

bam_kern

rit

bam_kern

rit_claim

bam_kern

werklijn

bam_kern

werklijn_begin

bam_kern

werklijn_eind

Het is de opzet om de algemene rollen bij het inrichten van de database te implementeren. We maken bij het toekennen van rechten en beperkingen aan individuele gebruikers en applicaties gebruik van het mechanisme van overerving van rechten uit rollen.

Overzicht RBAC rollen

Dev

Test

Acc

Prod

Owner

login

-

-

-

Connect

login

login

login

login

Beheerder

createrole

createrole

createrole

createrole

Applicatie

Login CRUD

Login CRUD

Login CRUD

Login CRUD

Bij installatie

Na installatie staan de privileges op het cluster te ruim. Het aanmaken van een database gaat altijd op basis van een template, default template1. Daarom voeren we voordat we de eerste database maken het volgende commando uit:

revoke connect on database template1 from public;