PRODAT PUBLIC

h1. PG Authentication Delegation

* *Option 1*

* <pre><code class="sql">

--create a user that you want to use the database as:

create role neil;

--create the user for the web server to connect as:

create role webgui noinherit login password 's3cr3t';

--let webgui set role to neil:

grant neil to webgui; --this looks backwards but is correct.

</code></pre>

* webgui is now in the neil group, so webgui can call set role neil . However, webgui did not inherit neil's permissions.

* Later, login as webgui:

<pre><code class="sql">

psql -d some_database -U webgui

(enter s3cr3t as password)

set role neil;

</code></pre>

* webgui does not need superuser permission for this.

* *die Variante beinhaltet enorme Sicherheitslücke:*

** <pre><code class="sql">

RESET ROLE;

SET ROLE admin;

</code></pre>

** da hierfür alle Rollen welche sich einlogen können sollen Mitglied der Login-Rolle sind (webgui), kann hiermit zu allen anderen Rollen gewechselt werden

* *Option 2*

* Funktionen welche als @SECURITY DEFINER@ deklariert sind, dürfen kein @SET ROLE@  (Änderungen des "current_user") und auch kein @SET AUTHORIZATION@ (Änderung des "session_user") aufrufen

* <pre><code class="sql">

CREATE OR REPLACE FUNCTION TSystem.grantrole(role_name text) RETURNS void AS $$

DECLARE

  _SQL varchar;

BEGIN

  _SQL = format('GRANT %I TO %I;', role_name, session_user);

  EXECUTE _SQL;

END;

$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION TSystem.revokerole(role_name text) RETURNS void AS $$

DECLARE

  _SQL varchar;

BEGIN

  _SQL = format('REVOKE %I FROM %I;', role_name, session_user);

  EXECUTE _SQL;

END;

$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION TSystem.switchrole(role_name text) RETURNS void AS $$

DECLARE

  _SQL varchar;

BEGIN

  -- check for permission here -> IF THEN  

  PERFORM TSystem.grantrole(role_name);

  _SQL = format('SET ROLE %I;', role_name);

  EXECUTE _SQL;

  PERFORM TSystem.revokerole(role_name);	

END;

$$ LANGUAGE plpgsql;

</code></pre>

* Niemand ausser der webui Rolle bekommt jetzt das Recht diese Funktionen auszuführen

* Für alle Tabellen mit alternativen Authentifizierungs-Feldern (llv.ll_rfid) wird RLS aktiviert @ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;@

** Lesen: SUPERUSER ODER session_user (nicht current_user !!!)

** Schreiben: session_user

** die hier nicht aufgeführte Funktion zum überprüfen der alternativen Authentifizierung (NFC/RFID oder was auch immer in Zukunft nötig wird) wird mit @SECURITY DEFINER@ definiert und alleinig @EXECUTE@ für alle gesetzt