Row Level Security für postgresql

Rechte auf Datenbanken, Schemas, Views, Tabellen und sogar Spalten einer Tabelle sind alles kein Thema und mit Standard Mitteln zu lösen, was aber wenn man auf Datensatzebene den Zugriff regelen muss oder möchte?
In meinem Beispiel hier nehmen wir zur Vereinfachung nur mal die Lese Rechte her.

Als Erstes benötigen wir zwei Hilfsfunktionen, um zum einen die system id eines Benutzers anhand seines Namens zu ermitteln:

CREATE OR REPLACE FUNCTION private.pg_get_userbyname(name)
RETURNS integer AS
'SELECT usesysid::INTEGER FROM pg_user WHERE usename = $1;'
LANGUAGE sql VOLATILE
COST 100;


und zum anderen ein Funktion, um die oid einer Rolle bei Ihrem Namen zu ermitteln.

CREATE OR REPLACE FUNCTION private.pg_get_rolebyname(name)
RETURNS integer AS
'SELECT oid::INTEGER FROM pg_roles WHERE rolname = $1;'
LANGUAGE sql VOLATILE
COST 100;


Dann benötigen wir eine Tabelle, wo ein Feld “owner” den Namen eine Rolle eingetragen bekommt. Benutzer die diese Rolle zugeornet haben, sollen später den Datensatz sehen also lesen dürfen, alle anderen nicht. In meinem Beispiel nehme ich einmal Objekte, wobei jedes eine id object_id und einen Namen object_name trägt.


CREATE TABLE private.object_ownership
(
object_id text NOT NULL,
object_name text NOT NULL,
"owner" text NOT NULL DEFAULT 'not defined'::text,
CONSTRAINT object_ownership_pkey PRIMARY KEY (object_id)
)
WITH (
OIDS=FALSE
);

Entziehen Sie der Rolle public alle Rechte von der Tabelle, damit ist sichergestellt, dass kein Benutzer außer dem sysadmin darauf zugreifen kann.

Über einen Trigger auf der Tabelle object_ownership stellen Sie sicher, das nur gültige Gruppenrollen durch den sysadmin eingetragen werden können. Dies geschieht mittels folgender Funktion:

CREATE OR REPLACE FUNCTION private.tri_func_validate_role_table_object_ownership()
RETURNS trigger AS
$BODY$DECLARE
v_oid oid;
BEGIN

END$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;


Aktivieren Sie den Trigger wie folgt:

CREATE TRIGGER audit_object_ownership
AFTER INSERT OR UPDATE
ON private.object_ownership
FOR EACH ROW
EXECUTE PROCEDURE private.tri_func_validate_role_table_object_ownership();


Legen wir uns nun beispielhaft 4 Gruppenrollen an. Auch die letzte “not defined” mitnehmen, da dies für unseren DEFAULT Wert unseres owner Feldes steht. Die Rolle muss vorhanden sein, da wir die Existenz über den Trigger sicherstellen.


CREATE ROLE grp_object_1111
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;

CREATE ROLE "not defined"
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;


Legen Sie Beispiel Datensätze an:

INSERT INTO private.object_ownership(object_id, object_name, "owner")
VALUES (1, 'White House', 'grp_object_1111');

INSERT INTO private.object_ownership(object_id, object_name, "owner")
VALUES (4, '', 'not defined');

Weisen Sie nun eine der neu angelegten Rollen ihrem Benutzer zu.
Danach legen Sie folgende View an:

CREATE OR REPLACE VIEW private.view_ownership_object AS
SELECT object_ownership.object_id, object_ownership.object_name
FROM private.object_ownership
WHERE pg_has_role(private.pg_get_userbyname("current_user"()::text::name)::oid, private.pg_get_rolebyname(object_ownership.owner::name)::oid, 'MEMBER'::text);


Wenn die View mit SELECT * FROM private.view_ownership_object; nun ausgeführt wird, werden nur die Zeilen ausgegeben, wo der Aktuelle Benutzer die Rolle zugewiesen bekommen hatte, welche im Feld “owner” eingetragen war. Alle anderen werden nicht angezeigt. Da der direkte Zugang zur Tabelle object_ownership durch das Entfernen der public Rolle, unterbunden ist und ein Zugriff nur über die View möglich ist, ist sichergestellt, das nur berechtigte Benutzer die Daten sehen können.

Und das ganze funktioniert einfach nur perfekt. Leider kann ich keine Aussagen zur Performanz machen, wie sich das bei vielen Daten verhält.