Wie erstellt man einen Nur-Lese-Benutzer in PostgreSQL?

Question

Ich möchte einen Benutzer in PostgreSQL erstellen, der nur SELECTs aus einer bestimmten Datenbank ausführen kann. In MySQL würde der Befehl lauten:

GRANT SELECT ON mydb.* TO 'xxx'@'%' IDENTIFIED BY 'yyy';

Was ist der entsprechende Befehl oder die entsprechende Befehlsfolge in PostgreSQL?

Ich habe es versucht...

postgres=# CREATE ROLE xxx LOGIN PASSWORD 'yyy';
postgres=# GRANT SELECT ON DATABASE mydb TO xxx;

Aber es scheint, dass die einzigen Dinge, die Sie für eine Datenbank gewähren können, CREATE, CONNECT, TEMPORARY und TEMP sind.

Answer 1

Verwendung/Abwahl für eine einzelne Tabelle zulassen

Wenn Sie nur CONNECT für eine Datenbank gewähren, kann der Benutzer eine Verbindung herstellen, hat aber keine anderen Rechte. Sie müssen USAGE auf Namensräume (Schemata) und SELECT auf Tabellen und Ansichten einzeln gewähren, etwa so:

GRANT CONNECT ON DATABASE mydb TO xxx;
-- This assumes you're actually connected to mydb..
GRANT USAGE ON SCHEMA public TO xxx;
GRANT SELECT ON mytable TO xxx;

Mehrere Tabellen/Views (PostgreSQL 9.0+)

In den neuesten Versionen von PostgreSQL können Sie Berechtigungen für alle Tabellen/Views/etc im Schema mit einem einzigen Befehl erteilen, anstatt sie einzeln eingeben zu müssen:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx;

Dies betrifft nur Tabellen, die bereits erstellt wurden. Noch leistungsfähiger ist die Möglichkeit, dass Sie automatisch Standardrollen, die neuen Objekten zugewiesen werden in Zukunft:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
   GRANT SELECT ON TABLES TO xxx;

Beachten Sie, dass sich dies standardmäßig nur auf Objekte (Tabellen) auswirkt, die von dem Benutzer erstellt wurden, der diesen Befehl erteilt hat; es kann jedoch auch für jede Rolle festgelegt werden, der der erteilende Benutzer angehört. Allerdings erhalten Sie nicht für alle Rollen, in denen Sie Mitglied sind, Standardberechtigungen, wenn Sie neue Objekte erstellen... Sie müssen also noch etwas herumprobieren. Wenn Sie den Ansatz verfolgen, dass eine Datenbank eine Eigentümerrolle hat und Schemaänderungen als diese Eigentümerrolle durchgeführt werden, dann sollten Sie dieser Eigentümerrolle Standardberechtigungen zuweisen. IMHO ist das alles etwas verwirrend, und Sie müssen vielleicht ein wenig experimentieren, um einen funktionierenden Arbeitsablauf zu finden.

Mehrere Tabellen/Views (PostgreSQL-Versionen vor 9.0)

Um Fehler bei langwierigen, mehrere Tabellen umfassenden Änderungen zu vermeiden, wird empfohlen, den folgenden "automatischen" Prozess zu verwenden, um die erforderlichen GRANT SELECT zu jeder Tabelle/Ansicht:

SELECT 'GRANT SELECT ON ' || relname || ' TO xxx;'
FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE nspname = 'public' AND relkind IN ('r', 'v', 'S');

Dies sollte die relevanten GRANT-Befehle für GRANT SELECT auf alle Tabellen, Ansichten und Sequenzen in der Öffentlichkeit ausgeben, damit Sie sie kopieren und einfügen können. Natürlich wird dies nur auf Tabellen angewendet, die bereits erstellt wurden.

Answer 2

Die Referenz stammt aus diesem Blog:

Skript zum Erstellen eines Nur-Lese-Benutzers:

CREATE ROLE Read_Only_User WITH LOGIN PASSWORD 'Test1234' 
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';

Weisen Sie diesem Benutzer eine Leseberechtigung zu:

GRANT CONNECT ON DATABASE YourDatabaseName TO Read_Only_User;
GRANT USAGE ON SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO Read_Only_User;

Zuweisung von Leseberechtigungen für alle neu erstellten Tabellen in der Zukunft

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO Read_Only_User;

Answer 3

Ab PostgreSQL v14 können Sie das einfach tun, indem Sie die vordefinierte pg_read_all_data Rolle:

GRANT pg_read_all_data TO xxx;

Answer 4

Bitte beachten Sie, dass PostgreSQL 9.0 (derzeit im Betatest) eine eine einfache Möglichkeit, dies zu tun :

test=> GRANT SELECT ON ALL TABLES IN SCHEMA public TO joeuser;

Answer 5

Hier ist der beste Weg, den ich gefunden habe, um schreibgeschützte Benutzer hinzuzufügen (mit PostgreSQL 9.0 oder neuer):

$ sudo -upostgres psql postgres
postgres=# CREATE ROLE readonly WITH LOGIN ENCRYPTED PASSWORD '<USE_A_NICE_STRONG_PASSWORD_PLEASE';
postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

Melden Sie sich dann bei allen zugehörigen Rechnern an (Master + Read-Slave(s)/Hot-Standby(s) usw.) und führen Sie den Vorgang aus:

$ echo "hostssl <PUT_DBNAME_HERE> <PUT_READONLY_USERNAME_HERE> 0.0.0.0/0 md5" | sudo tee -a /etc/postgresql/9.2/main/pg_hba.conf
$ sudo service postgresql reload