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

Neue Benutzer haben standardmäßig die Berechtigung, Tabellen zu erstellen. Wenn Sie vorhaben, einen Nur-Lese-Benutzer zu erstellen, ist dies wahrscheinlich nicht das, was Sie wollen.

Um einen echten Nur-Lese-Benutzer mit PostgreSQL 9.0+ zu erstellen, führen Sie die folgenden Schritte aus:

# 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 Nur-Lese-Benutzer keine Berechtigung hat, Tabellen aufzulisten (d.h. \d keine Ergebnisse liefert), dann liegt das wahrscheinlich daran, dass Sie nicht über USAGE Berechtigungen für das Schema. USAGE ist eine Berechtigung, die es Benutzern ermöglicht, die ihnen zugewiesenen Berechtigungen tatsächlich zu nutzen. Wozu ist das gut? Ich bin mir nicht sicher. Zu beheben:

# 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;

Answer 2

Dafür habe ich ein praktisches Skript erstellt; pg_grant_read_to_db.sh . Dieses Skript gewährt einer bestimmten Rolle Nur-Lese-Rechte für alle Tabellen, Ansichten und Sequenzen in einem Datenbankschema und setzt sie als Standard.

Answer 3

Ich habe alle möglichen Lösungen durchgelesen, die alle in Ordnung sind, wenn man daran denkt, sich mit der Datenbank zu verbinden, bevor man die Dinge gewährt ;) Danke trotzdem an alle anderen Lösungen!!!

user@server:~$ sudo su - postgres

psql-Benutzer erstellen:

postgres@server:~$ createuser --interactive 
Enter name of role to add: readonly
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

Starten Sie psql cli und setzen Sie ein Passwort für den erstellten Benutzer:

postgres@server:~$ psql
psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1), server 9.5.14)
Type "help" for help.

postgres=# alter user readonly with password 'readonly';
ALTER ROLE

mit der Zieldatenbank verbinden:

postgres=# \c target_database 
psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1), server 9.5.14)
You are now connected to database "target_database" as user "postgres".

alle erforderlichen Privilegien gewähren:

target_database=# GRANT CONNECT ON DATABASE target_database TO readonly;
GRANT

target_database=# GRANT USAGE ON SCHEMA public TO readonly ;
GRANT

target_database=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly ;
GRANT

alter default privileges for targets db public shema:

target_database=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
ALTER DEFAULT PRIVILEGES

Answer 4

Wenn sich Ihre Datenbank im öffentlichen Schema befindet, ist es einfach (dies setzt voraus, dass Sie bereits die readonlyuser )

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 Datenbank Folgendes verwendet customschema Führen Sie die obigen Schritte aus, fügen Sie aber einen weiteren Befehl hinzu:

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

Answer 5

Die nicht ganz einfache Art, dies zu tun, wäre, select auf jede Tabelle der Datenbank zu gewähren:

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

Sie könnten dies automatisieren, indem Sie Ihre Förderungsbescheide aus den Metadaten der Datenbank generieren.