Wirres Durcheinander? Benutzer, Rollen und Schemata bei PostgreSQL

Wenn man von Oracle oder MySQL/MariaDB zu PostgreSQL wechselt, dann ist die etwas andere Verwendung der Begriffe Benutzer, Rollen und Schema einer der Punkte, über den viele Administratoren stolpern. Entwirren wir doch einmal diese babylonische Begriffsvielfalt. 

Wie ist es bei Oracle?

Rollen sind Sammlungen von Rechten. Dies können System- oder Objektrechte sein. Rollen selbst können wiederum erneut an Rollen ge-granted werden.

Benutzer sind „Benutzer“, die sich an die Datenbank anmelden können. Dafür haben sie ein dediziertes Password.

Ein einfaches Beispiel:

create user APP_USER identified by 'sicheres_password'; 
create role APP_READER_ROLE; 
grant CREATE SESSION to APP_READER_ROLE; 
grant SELECT on APP_OWNER.APP_TABLE to APP_READER_ROLE; 
grant APP_READER_ROLE to APP_USER; 

Ein Schema ist eine Sammlung von Datenbankobjekten, wie z. B. Tabellen, Views, Funktionen und weitere. Die Dokumentation definiert: „A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema.“. Kurz und vereinfacht formuliert: Ein Schema ist ein Benutzer mit Objekten.

Relativ neu bei Oracle (seit Oracle 18c) sind „schema-only-Accounts“. Dies sind „Benutzer ohne Password“.

Beispiel:

create user APP_SCHEMA NO AUTHENTICATION;

Ein „Schema-Only-Account“ wird zum normalen Benutzer, indem man ihm ein Password zuweist. Und natürlich gilt dies auch umgekehrt. Mittels „ALTER USER . NO AUTHENTICATION“ wird ein Benutzer zum „Schema-Only-Account“.

Die Anmeldung an einem Schema-Only-Account erfolgt mittels eines Proxy-Users:

alter user APP_SCHEMA grant through APP_ADMIN_USER; 
grant create session TO APP_SCHEMA; 
connect APP_ADMIN_USER[APP_SCHEMA]/password  

Für Oracle gilt also die „Funktion“: Schema = Benutzer mit Objekten. 

Und was ist mit MySQL/MariaDB?

Bei MySQL sind Benutzer und Rollen ähnlich wie bei Oracle: Rollen sind Sammlungen von Rechten und Benutzer sind „Benutzer“, die sich ebenfalls an die Datenbank anmelden können.

„Database“ und „Schema“ sind hingegen Synonyme. „CREATE SCHEMA“ bewirkt das Gleiche wie „CREATE DATABASE“. Die Nutzung dieser Begriffe als Aliasse kann man auch der unterstehenden Ausgabe entnehmen: Während beim Kommando „show databases“ die Überschrift „Database“ verwendet wird, benennt MySQL/MariaDB einige seiner System-Daten(!)banken jedoch „information_schema(!)“ und „performance_schema(!)“.

MariaDB [(none)]> create database demodb;
Query OK, 1 row affected (0.001 sec) 
MariaDB [(none)]> create schema demoschema; 
Query OK, 1 row affected (0.001 sec) 
MariaDB [(none)]> show databases; 
+--------------------+ 
| Database           | 
+--------------------+ 
| demodb             | 
| demoschema         | 
| information_schema | 
| mysql              | 
| performance_schema | 
| sys                | 
+--------------------+ 
6 rows in set (0.001 sec)  

PostgreSQL: Cluster-Ebene vs. Datenbankebene

Bei PostgreSQL müssen wir die Cluster-Ebene und die Datenbankebene unterscheiden. Benutzer, Rollen und Tablespaces werden clusterweit definiert. Damit sind, entsprechende Rechte vorausgesetzt, z. B. Tablespaces in allen Datenbanken eines PostgreSQL-Clusters nutzbar.

Schemata hingegen sind auf Datenbankebene definiert. Damit hätten wir schon die erste Besonderheit von PostgreSQL geklärt.

Benutzer und Rollen bei PostgreSQL

Benutzer und Rollen teilen sich bei PostgreSQL den gleichen Namensraum. Ein Benutzer ist eine Sonderform der Rolle, nämlich eine Rolle mit LOGIN-Recht. Das ist die nächste Eigenart von PostgreSQL.

Ansonsten gilt ähnliches wie bei den anderen Datenbanken: Rollen sind Zusammenfassungen von Rechten und natürlich können Rollen erneut an Rollen „ge-granted“ werden.

postgres=# create role demo_role; 
CREATE ROLE 
postgres=# create user demo_user; 
CREATE ROLE  

Man sieht, dass der Befehl „create user“ intern in „create role“ umgewandelt wird. Beim „create user“ wird zusätzlich das LOGIN-Recht vergeben: 

postgres=# select rolname,rolcanlogin from pg_authid where rolname like 'demo%'; 
  rolname  | rolcanlogin 
-----------+------------- 
 demo_role | f 
 demo_user | t 
(2 rows)  

pg_authid ist die Tabelle im Systemkatalog, welche die Daten zu Benutzern und Rollen enthält. Die Views pg_user und pg_roles sind Sichten auf diese Tabelle 

postgres=# select rolname,rolcanlogin from pg_roles where rolname like 'demo%'; 
  rolname  | rolcanlogin 
-----------+------------- 
 demo_role | f 
 demo_user | t 
(2 rows) 
 
postgres=# select usename from pg_user where usename like 'demo%'; 
  usename 
----------- 
 demo_user 
(1 row)  

Schemata bei PostgreSQL

Schemata sind bei PostgreSQL Namensräume für Objekte und werden auf Datenbank-Ebene angelegt.

postgres=# create schema demo_schema authorization demo_user; 
CREATE SCHEMA  

Der angegebene Benutzer ist Eigentümer des Schemas. Wenn beim Anlegen eines Schemas kein Eigentümer angegeben wird, so wird der Benutzer, welcher das Schema anlegt, automatisch als Eigentümer definiert. 

postgres=# select n.nspname schemaname,n.nspowner,r.rolname schemaowner 
postgres-# from pg_namespace n,pg_roles r 
postgres-# where r.oid=n.nspowner and n.nspname like 'demo%'; 
 schemaname  | nspowner | schemaowner 
-------------+----------+------------- 
 demo_schema |    33468 | demo_user 
(1 row)  

Ein Benutzer kann sogar Eigentümer mehrerer Schemata sein.

Leider gibt es im PostgreSQL-Systemkatalog keine „pg-View“, welche die „Object ID“ (OID) des Eigentümers (Spalte nspowner in der Tabelle pg_namespace) in den Namen der jeweiligen Rolle übersetzt. Um diese Information zu ermitteln, muss man sich entweder des obigen Joins bedienen oder man nutzt die View Schemata im information_schema:

postgres=# select catalog_name,schema_name,schema_owner 
postgres-# from information_schema.schemata 
postgres-# where schema_name like 'demo%'; 
 catalog_name | schema_name | schema_owner 
--------------+-------------+-------------- 
 postgres     | demo_schema | demo_user 
(1 row)  

Das information_schema ist ein RDBMS-übergreifender ANSI-Standard (siehe auch im Abschnitt von MySQL/MariaDB), mit dem sichergestellt werden soll, dass die Metadaten zu Datenbankobjekten in allen RDBMS über gleichnamige Views abgefragt werden können. Leider unterstützen nicht alle RDBMS diesen Standard (weitere Informationen zu diesem Thema siehe z. B. hier).

Damit ein Benutzer, der nicht Superuser oder Eigentümer eines Schemas ist, Objekte in einem Schema nutzen kann, benötigt er das „USAGE-Recht“.

grant usage on schema demo_schema to anwendungsnutzer;

Dies repräsentiert lediglich das fundamentale Privileg, welches es grundsätzlich ermöglicht, Objekte innerhalb dieses Schemas zu verwenden. Zusätzlich bedarf es spezifischer Berechtigungen für die einzelnen Objekte im Schema. Als Veranschaulichung sei folgendes Beispiel angeführt:

grant select on table demo_schema.beispiel_tabelle to anwendungsnutzer;

Damit ein Benutzer Objekte in einem Schema anlegen kann, benötigt er das CREATE-Recht für dieses Schema.

grant create on schema demo_schema to anwendungsnutzer;

Durch dieses Konstrukt kann es dazu kommen, dass verschiedene Objekte im gleichen Schema verschiedene Eigentümer haben:

postgres=# select schemaname,tablename,tableowner 
postgres-# from pg_tables 
postgres-# where schemaname='demo_schema'; 
 schemaname  | tablename | tableowner 
-------------+-----------+------------ 
 demo_schema | t1        | demo_user 
 demo_schema | t2        | demo_user2 
(2 rows)  

Das kann alles sehr verwirrend sein. Daher ist es häufige Praxis, zu einem User ein gleichnamiges Schema anzulegen: 

postgres=# create schema demo authorization demo; 
CREATE SCHEMA 
```  

Dieses Vorgehen ergibt auch aus dem folgenden Grund Sinn. Der Suchpfad, in dem ein nicht vollqualifiziertes Objekt ermittelt wird, enthält automatisch auch das Schema, das so heißt wie der eingeloggte Benutzer. 

postgres=# show search_path; 
   search_path 
----------------- 
 "$user", public 
(1 row)  

Durch den gleichgewählten Namen für Benutzer und Schema wird es für den menschlichen Anwender einfacher. Damit verschwimmen für ihn aber auch die Unterschiede zwischen Benutzer und Schema und die Kenntnis darüber gerät vielleicht in Vergessenheit. Dies kann natürlich bei Abweichungen von diesem „Best Practice“ auch zu Irritationen führen.

Mehr zu PostgreSQL erfahren Sie in unserem Seminar „PostgreSQL-Administration“.

Seminarempfehlung