Im PostgreSQL Telegram-Channel (https://t.me/pg_sql) kam kürzlich die Frage auf, warum in jeder neuen Datenbank das Schema "public" der Rolle "postgres" gehört und sie für jeden Benutzer beschreibbar ist.
Das Problem
Was meine ich damit? Hier ist eine neu angelegte Datenbank "blog", die mir ("bluthg") gehört:
bluthg@[local] blog # \l+ blog
List of databases
┌──────┬────────┬──────────┬─────────────┬─────────────┬───────────────────┬─────────┬────────────┬─────────────┐
│ Name │ Owner │ Encoding │ Collate │ Ctype │ Access privileges │ Size │ Tablespace │ Description │
├──────┼────────┼──────────┼─────────────┼─────────────┼───────────────────┼─────────┼────────────┼─────────────┤
│ blog │ bluthg │ UTF8 │ en_US.UTF-8 │ en_US.UTF-8 │ │ 7759 kB │ pg_default │ │
└──────┴────────┴──────────┴─────────────┴─────────────┴───────────────────┴─────────┴────────────┴─────────────┘
(1 row)Sie gehört zwar mir ("bluthg"), es sind aber keinerlei Zugriffsrechte gesetzt ("Access privileges" ist leer, s. https://www.postgresql.org/docs/current/ddl-priv.html).
Die neue DB enthält genau ein Schema ("namespace", daher "\dn"), nämlich "public":
bluthg@[local] blog # \dn+
List of schemas
┌────────┬──────────┬──────────────────────┬────────────────────────┐
│ Name │ Owner │ Access privileges │ Description │
├────────┼──────────┼──────────────────────┼────────────────────────┤
│ public │ postgres │ postgres=UC/postgres↵│ standard public schema │
│ │ │ =UC/postgres │ │
└────────┴──────────┴──────────────────────┴────────────────────────┘
(1 row)Warum gehört dieses Schema "postgres" und nicht "bluthg"? Und warum zeigt die Spalte "Access privileges" volle Rechte für "public"?
Wenn ich ein neues Benutzerkonto anlege
bluthg@[local] blog # CREATE ROLE blog LOGIN;
Time: 39.651 ms
bluthg@[local] blog # \du+ blog
List of roles
┌───────────┬────────────┬───────────┬─────────────┐
│ Role name │ Attributes │ Member of │ Description │
├───────────┼────────────┼───────────┼─────────────┤
│ blog │ │ {} │ │
└───────────┴────────────┴───────────┴─────────────┘
bluthg@[local] blog #und mich damit anmelde, darf ich mich tatsächlich mit dieser Datenbank verbinden und sogar Objekte anlegen:
:~$ psql blog -h localhost -U blog Password for user blog: blog@localhost blog # CREATE TABLE blogdemo (darfichdas INT); Time: 46.047 ms blog@localhost blog #
How did we get here?
Ich erinnere mich noch vage an meine ersten Gehversuche mit PostgreSQL. Damals kam MySQL (3.2x) bei jeder Linux-Distribution quasi automatisch mit auf die Festplatte, spätestens wenn man bei der Installation "LAMP" oder "DB-Server" mit angewählt hatte. Und MySQL hat dann direkt eine Datenbank angelegt und nach einem "root"-Passwort gefragt (oder aber auch einfach den UNIX-"root" ohne Passwort hineingelassen). Damit hatte man dann direkt was zum "herumspielen" und konnte sich an SQL versuchen.
Dann hatte ich irgendwann mal Lust auf Transaktionen und wollte "diese andere" Open-Source-Datenbank ausprobieren. Was war das für ein K(r)ampf!
Paket installieren (vielleicht war es sogar ein tar-Archiv?), dann einen UNIX-User anlegen, dann rausfinden, warum das nicht startet. Ok, also, "initdb" muss ausgeführt werden, um einen neuen Cluster (ui, ein Cluster?!? Ich wollte doch nur eine einfache Datenbank?!?) anzulegen! Dann eine Datei namens "pg_hba.conf" bearbeiten. Dazwischen zahllose Fehlversuche, sich mit dieser DB zu verbinden...
(Oracle(tm) war natürlich noch viel schlimmer... ich sage nur TNSNAMES.ORA...)
Das sieht heutzutage entschieden anders aus. Aktuelle Pakete (.deb/.rpm) nehmen einem diese ganze Arbeit ab. Die pg_hba.conf erlaubt auch direkt "peer"-Authentifizierung auf dem UNIX-Socket (bei Debian/Ubuntu, andere mögen abweichen), so das "root" nur zu "postgres" werden muss und losarbeiten kann.Die Paketierer haben ganze Arbeit geleistet. Mittlerweile ist die Inbetriebnahme einer PostgreSQL-Instanz etwa so komfortabel wie bei MySQL.
Aber: Komfort bedeutet nun einmal auch, dass dem (Neu-)Anwender so wenige Hindernisse wie unbedingt nötig in den Weg gelegt werden. Der Anfänger (nennen wir sie "Alice") wird irgendwann (hoffentlich bald!) lernen, dass man nicht mit dem SUPERUSER in der Datenbank herumhantieren sollte (etwas, was man bei dem weiter oben erwähnten Mitbewerber auch heute noch oft genug sieht...).
Was hat Alice bis hierher gemacht? Wahrscheinlich hat sie eine erste Datenbank angelegt:
postgres=# CREATE DATABASE meinedb; CREATE DATABASE Time: 744.782 ms postgres=#
Jetzt lernt sie das Rollenmodell kennen und legt sich einen eigenen Account (mit Passwort) an:
postgres=# CREATE ROLE alice LOGIN; CREATE ROLE Time: 61.743 ms postgres=# \password alice Enter new password: Enter it again: postgres=#
und verbindet sich mit der neuen Datenbank "meinedb". Wenn diese Datenbank jetzt konsequent abgesichert wäre, könnte "alice" sich gar nicht verbinden! Es ist Alice ja schon hoch anzurechnen, dass sie den Datenbanknamen angegeben hat, denn diese Meldung hätte Bob wahrscheinlich schwer verwirrt:
alice:~$ psql psql: error: could not connect to server: FATAL: database "alice" does not exist alice:~$
Aber Alice ist konsequent:
alice:~$ psql meinedb psql (12.0 (Ubuntu 12.0-1.pgdg18.04+1), server 10.10 (Ubuntu 10.10-1.pgdg18.04+1)) Type "help" for help. meinedb=>
In dieser neuen Datenbank kann Alice (so ziemlich) alles machen (obwohl sie doch "postgres" gehört). Bob - wenn er dann mal rausfindet, wie man den Datenbanknamen angibt - allerdings auch!
Das ist der Komfort, der als Zugeständnis vor allem an neue User vorgegeben ist.
Für Produktionssysteme ist das aber natürlich nicht geeignet!!!
How to fix it?
Einige einfache Schritte machen eine PostgreSQL-Installation (weitgehend) sicher:
1) "postgres"-Datenbank absichern:
REVOKE ALL ON DATABASE postgres FROM public;
Alternativ die Datenbank einfach löschen; sie ist letzlich nur aus Komfortgründen vorhanden!
2) Für jede neue Datenbank eine "owner"-Rolle anlegen:
CREATE ROLE blog_owner NOLOGIN;
3) Neue Datenbank anlegen und der neuen Rolle zuweisen:
CREATE DATABASE blog OWNER blog_owner;
4) Als Superuser mit der neuen DB verbinden und erst einmal "zu" machen:
REVOKE ALL ON DATABASE blog FROM public;
5) Wo wir schon da sind, auch gleich das Schema "public" der Owner-Rolle zuweisen und "zu" machen:
ALTER SCHEMA public OWNER TO blog_owner; REVOKE ALL ON SCHEMA public FROM public;
6) Jetzt die eigentliche Applikations-Rolle anlegen:
CREATE ROLE blog LOGIN;
7) Und die benötigten Rechte vergeben:
GRANT CONNECT ON DATABASE blog TO blog; GRANT USAGE ON SCHEMA public TO blog;
Hinweis: das Schema "public" zu benutzen ist zwar nicht 100% "reine Lehre", aber durchaus in Ordnung (sonst muss der search_path der Rolle angepasst oder generell voll qualifizierte Relationsnamen genutzt werden).
Ergebnis:
blog=# \l+ blog
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
------+------------+----------+-------------+-------------+---------------------------+---------+------------+-------------
blog | blog_owner | UTF8 | en_US.UTF-8 | en_US.UTF-8 | blog_owner=CTc/blog_owner+| 7759 kB | pg_default |
| | | | | blog=c/blog_owner | | |
(1 row)
blog=# \dn+
List of schemas
Name | Owner | Access privileges | Description
--------+------------+--------------------------+------------------------
public | blog_owner | blog_owner=UC/blog_owner+| standard public schema
| | blog=U/blog_owner |
(1 row)
blog=#Arbeiten mit der DB
"blog" darf in dieser Datenbank nichts machen, außer sich verbinden und "schauen" (USAGE auf dem Schema).
Objekte (Tabellen, Views, Sequenzen, ...) muss von "blog_owner" angelegt werden und kann anschließend auch nur von dieser Rolle gelesen werden:
blog=# SET ROLE blog_owner ;
SET
Time: 0.264 ms
blog=> CREATE TABLE nurichdarf (das INT);
CREATE TABLE
Time: 65.134 ms
blog=> \dp+ nurichdarf
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------------+-------+-------------------+-------------------+----------
public | nurichdarf | table | | |
(1 row)blog=> SET ROLE blog; SET Time: 0.221 ms blog=> SELECT * FROM nurichdarf ; ERROR: permission denied for relation nurichdarf Time: 0.514 ms blog=>
Also müssen explizite GRANTs her:
blog=> SET ROLE blog_owner; SET Time: 0.196 ms blog=> GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE nurichdarf TO blog; GRANT Time: 61.688 ms blog=> SET ROLE blog; SET Time: 0.227 ms blog=> INSERT INTO nurichdarf (das) VALUES (1); INSERT 0 1 Time: 61.876 ms blog=> SELECT * FROM nurichdarf ; das ----- 1 (1 row) Time: 0.316 ms blog=> blog=> TRUNCATE nurichdarf ; ERROR: permission denied for relation nurichdarf Time: 0.248 ms blog=> blog=> DELETE FROM nurichdarf WHERE das = 1; DELETE 1 Time: 43.389 ms blog=>
Um diese GRANTs nicht jedes Mal explizit machen zu müssen (was aber durchaus gängige und sinnvolle Praxis ist!), kann der Eigentümer (oder ein Superuser) sog. DEFAULT PRIVILEGES vergeben:
blog=> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT,INSERT,UPDATE,DELETE ON TABLES TO blog; ALTER DEFAULT PRIVILEGES Time: 53.569 ms blog=> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT,USAGE ON SEQUENCES TO blog; ALTER DEFAULT PRIVILEGES Time: 47.475 ms blog=>
Check, ob und wie das greift:
blog=> CREATE TABLE dudarfst (auch SERIAL);
CREATE TABLE
Time: 63.338 ms
blog=> \dp dudarfst
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+----------+-------+-------------------------------+-------------------+----------
public | dudarfst | table | blog=arwd/blog_owner +| |
| | | blog_owner=arwdDxt/blog_owner | |
(1 row)
blog=> SET ROLE blog;
SET
Time: 0.245 ms
blog=> INSERT INTO dudarfst (auch) VALUES (default);
INSERT 0 1
Time: 37.123 ms
blog=> TABLE dudarfst;
auch
------
1
(1 row)
Time: 0.285 ms
blog=>