Wie erstellen Sie einen schreibgeschützten Benutzer in PostgreSQL?

Ich möchte einen Benutzer in PostgreSQL erstellen, der nur SELECTs von einer bestimmten database tun kann. In MySQL wäre der Befehl:

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

Was ist der entsprechende Befehl oder die Reihe von Befehlen 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 in einer database gewähren können, CREATE, CONNECT, TEMPORARY und TEMP sind.

   

Nutze / wähle eine einzelne Tabelle aus

Wenn Sie CONNECT nur einer database erteilen, kann der Benutzer eine Verbindung herstellen, hat jedoch keine anderen Berechtigungen. Sie müssen USAGE für Namespaces (Schemas) und SELECT für Tabellen und Views individuell wie folgt gewähren:

 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 / Ansichten (PostgreSQL 9.0+)

In den neuesten Versionen von PostgreSQL können Sie Berechtigungen für alle Tabellen / Sichten / 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 wirkungsvoller können Sie in Zukunft automatisch neue Rollen für neue Objekte festlegen:

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

Beachten Sie, dass dies standardmäßig nur für Objekte (Tabellen) gilt, die von dem Benutzer erstellt wurden, der diesen Befehl ausgegeben hat. Es kann jedoch auch für jede Rolle festgelegt werden, der der ausgebende Benutzer angehört. Sie erhalten jedoch keine Standardberechtigungen für alle Rollen, an denen Sie beteiligt sind, wenn Sie neue Objekte erstellen. Wenn Sie den Ansatz übernehmen, dass eine database eine eigene Rolle hat und Schemaänderungen als diese besitzende Rolle ausgeführt werden, sollten Sie dieser besitzenden Rolle Standardberechtigungen zuweisen. IMHO das ist alles ein bisschen verwirrend und Sie müssen vielleicht experimentieren, um mit einem funktionalen Workflow zu kommen.

Mehrere Tabellen / Ansichten (PostgreSQL-Versionen vor 9.0)

Um Fehler bei langwierigen Änderungen an mehreren Tabellen zu vermeiden, wird empfohlen, den folgenden ‘automatischen’ process zu verwenden, um die erforderliche GRANT SELECT für jede Tabelle / View zu generieren:

 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 in GRANT SELECT für alle Tabellen, Ansichten und Sequenzen in der Öffentlichkeit ausgeben, für copy-n-paste love. Dies wird natürlich nur auf Tabellen angewendet, die bereits erstellt wurden.

Beachten Sie, dass PostgreSQL 9.0 (heute im Betatest) einen einfachen Weg dazu bietet :

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

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

 $ sudo -upostgres psql postgres postgres=# CREATE ROLE readonly WITH LOGIN ENCRYPTED PASSWORD ' 

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

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

Referenz aus diesem Blog:

Skript zum Erstellen eines schreibgeschützten Benutzers:

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

Weisen Sie diesem schreibgeschützten Benutzer die Berechtigung 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; 

Ich habe dafür ein praktisches Skript erstellt; pg_grant_read_to_db.sh . Dieses Skript gewährt einer bestimmten Rolle in allen Tabellen, Sichten und Sequenzen in einem databaseschema schreibgeschützte Berechtigungen und legt diese als Standard fest.

Standardmäßig haben neue Benutzer die Berechtigung zum Erstellen von Tabellen. Wenn Sie einen schreibgeschützten Benutzer erstellen möchten, ist dies wahrscheinlich nicht das, was Sie möchten.

Führen Sie die folgenden Schritte aus, um einen echten schreibgeschützten Benutzer mit PostgreSQL 9.0+ zu erstellen:

 # This will prevent default users from creating tables REVOKE CREATE ON SCHEMA public FROM public; # If you want to grant a write user permission to create tables # note that superusers will always be able to create tables anyway GRANT CREATE ON SCHEMA public to writeuser; # Now create the read-only user CREATE ROLE readonlyuser WITH LOGIN ENCRYPTED PASSWORD 'strongpassword'; GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonlyuser; 

Wenn Ihr schreibgeschützter Benutzer keine Berechtigung zum Auflisten von Tabellen hat (dh, \d gibt keine Ergebnisse zurück), liegt dies wahrscheinlich daran, dass Sie keine USAGE Berechtigungen für das Schema besitzen. USAGE ist eine Berechtigung, mit der Benutzer die ihnen zugewiesenen Berechtigungen tatsächlich verwenden können. Was ist der Sinn? Ich bin mir nicht sicher. Reparieren:

 # You can either grant USAGE to everyone GRANT USAGE ON SCHEMA public TO public; # Or grant it just to your read only user GRANT USAGE ON SCHEMA public TO readonlyuser; 

Wenn sich Ihre database im öffentlichen Schema befindet, ist dies einfach (vorausgesetzt, Sie haben den readonlyuser bereits erstellt).

 db=> GRANT SELECT ON ALL TABLES IN SCHEMA public to readonlyuser; GRANT db=> GRANT CONNECT ON DATABASE mydatabase to readonlyuser; GRANT db=> GRANT SELECT ON ALL SEQUENCES IN SCHEMA public to readonlyuser; GRANT 

Wenn Ihre database customschema , führen Sie die obigen customschema , fügen Sie jedoch einen weiteren Befehl hinzu:

 db=> ALTER USER readonlyuser SET search_path=customschema, public; ALTER ROLE 

Die nicht geradlinige Art, dies zu tun, wäre das Gewähren von Select für jede Tabelle der database:

 postgres=# grant select on db_name.table_name to read_only_user; 

Sie könnten dies automatisieren, indem Sie Ihre Berechtigungsanweisungen aus den Metadaten der database generieren.

Genommen von einem Link, der als Antwort auf den Link von despesz gepostet wurde.

Postgres 9.x scheint die erforderlichen functionen ausführen zu können. Siehe den Abschnitt “Grant On Database Objects” von:

http://www.postgresql.org/docs/current/interactive/sql-grant.html

Wo es heißt: “Es gibt auch eine Option, um Berechtigungen für alle Objekte desselben Typs in einem oder mehreren Schemata zu gewähren. Diese functionalität wird derzeit nur für Tabellen, Sequenzen und functionen unterstützt (beachten Sie jedoch, dass ALLE Tabellen Ansichten enthalten) und ausländische Tabellen). ”

Diese Seite behandelt auch die Verwendung von ROLEs und eines PRIVILEGE namens “ALL PRIVILEGES”.

Ebenfalls vorhanden sind Informationen darüber, wie sich GRANT-functionalitäten mit SQL-Standards vergleichen lassen.