Wie erstellen Sie einen schreibgeschützten Benutzer in PostgreSQL?
Ich möchte in PostgreSQL einen Benutzer erstellen, der nur SELECTs aus einer bestimmten Datenbank ausführen kann. In MySQL lautet der Befehl:
GRANT SELECT ON mydb.* TO 'xxx'@'%' IDENTIFIED BY 'yyy';
Was ist der entsprechende Befehl oder die Befehlsserie in PostgreSQL?
Ich habe es versucht...
postgres=# CREATE ROLE xxx LOGIN PASSWORD 'yyy';
postgres=# GRANT SELECT ON DATABASE mydb TO xxx;
Es scheint jedoch, dass die einzigen Dinge, die Sie für eine Datenbank gewähren können, CREATE, CONNECT, TEMPORARY und TEMP sind.
Verwendung/Auswahl für eine einzelne Tabelle gewähren
Wenn Sie CONNECT nur einer Datenbank gewähren, kann der Benutzer eine Verbindung herstellen, hat aber keine anderen Berechtigungen. Sie müssen USAGE für Namespaces (Schemas) und SELECT für Tabellen und Views einzeln 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/Views (PostgreSQL 9.0+)
In den neuesten Versionen von PostgreSQL können Sie Berechtigungen für alle Tabellen/Ansichten/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. Stärker können Sie Standardrollen automatisch neuen Objekten zuweisen in der Zukunft:
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO xxx;
Beachten Sie, dass dies standardmäßig nur die Objekte (Tabellen) betrifft, die von dem Benutzer erstellt wurden, der diesen Befehl ausgegeben hat. Es kann jedoch auch für jede Rolle festgelegt werden, der der ausstellende Benutzer angehört. Sie erhalten jedoch nicht die Standardberechtigungen für alle Rollen, denen Sie beim Erstellen neuer Objekte angehören. Wenn Sie davon ausgehen, dass eine Datenbank über eine Eigentümerrolle verfügt und Schemaänderungen als diese Eigentümerrolle ausgeführt werden, sollten Sie dieser Eigentümerrolle Standardberechtigungen zuweisen. IMHO ist dies alles etwas verwirrend und Sie müssen möglicherweise experimentieren, um einen funktionalen Workflow zu erstellen.
Mehrere Tabellen/Views (PostgreSQL-Versionen vor 9.0)
Um Fehler bei langwierigen Änderungen an mehreren Tabellen zu vermeiden, wird empfohlen, den folgenden automatischen Prozess zu verwenden, um den erforderlichen GRANT SELECT für jede Tabelle/Sicht 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');
Auf diese Weise sollten die entsprechenden GRANT-Befehle für alle Tabellen, Ansichten und Sequenzen in der Öffentlichkeit für GRANT SELECT ausgegeben werden, um die Copy-n-Paste-Funktion zu nutzen. Dies wird natürlich nur auf bereits erstellte Tabellen angewendet.
Beachten Sie, dass PostgreSQL 9.0 (heute im Betatest) eine einfache Möglichkeit, dies zu erreichen hat:
test=> GRANT SELECT ON ALL TABLES IN SCHEMA public TO joeuser;
GRANT
Hier habe ich die beste Methode gefunden, 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 Maschinen an (Master + Read-Slave (s)/Hot-Standby (s) usw.) und führen Sie Folgendes 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
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;
Standardmäßig haben neue Benutzer die Berechtigung zum Erstellen von Tabellen. Wenn Sie planen, einen schreibgeschützten Benutzer zu erstellen, 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 (d. H. \d gibt keine Ergebnisse zurück), liegt dies wahrscheinlich daran, dass Sie keine USAGE-Berechtigungen für das Schema haben. USAGE ist eine Berechtigung, mit der Benutzer tatsächlich die ihnen zugewiesenen Berechtigungen verwenden können. Was ist der Sinn davon? 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;
Ich habe dafür ein praktisches Skript erstellt. pg_grant_read_to_db.sh . Dieses Skript gewährt einer angegebenen Rolle schreibgeschützte Berechtigungen für alle Tabellen, Ansichten und Sequenzen in einem Datenbankschema und legt sie als Standard fest.
Wenn sich Ihre Datenbank im öffentlichen Schema befindet, ist es einfach (dies setzt voraus, dass Sie bereits die Variable readonlyuser erstellt haben).
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 customschema verwendet, führen Sie den obigen Befehl aus, fügen jedoch einen weiteren Befehl hinzu:
db=> ALTER USER readonlyuser SET search_path=customschema, public;
ALTER ROLE
Der unkomplizierte Weg wäre es, select für jede Tabelle der Datenbank zu gewähren:
postgres=# grant select on db_name.table_name to read_only_user;
Sie können dies automatisieren, indem Sie Ihre Grant-Anweisungen aus den Datenbank-Metadaten generieren.
CREATE USER username SUPERUSER password 'userpass';
ALTER USER username set default_transaction_read_only = on;
Entnommen aus einem Link, der als Antwort auf despesz ' link gepostet wurde.
Postgres 9.x scheint die Fähigkeit zu haben, das zu tun, was angefordert wird. Siehe den Abschnitt "Gewähren für Datenbankobjekte" von:
http://www.postgresql.org/docs/current/interactive/sql-grant.html
Dort heißt es: "Es gibt auch eine Option zum Erteilen von Berechtigungen für alle Objekte desselben Typs innerhalb eines oder mehrerer Schemas. Diese Funktionalität wird derzeit nur für Tabellen, Sequenzen und Funktionen unterstützt (beachten Sie jedoch, dass ALL TABLES Ansichten enthält und ausländische Tabellen). "
Diese Seite behandelt auch die Verwendung von ROLEs und eines PRIVILEGE mit dem Namen "ALL PRIVILEGES".
Außerdem sind Informationen darüber enthalten, wie GRANT-Funktionen mit SQL-Standards verglichen werden.
Ich lese durch alle möglichen Lösungen, die alle in Ordnung sind, wenn Sie daran denken, sich mit der Datenbank zu verbinden, bevor Sie die Dinge zulassen;) Vielen Dank an alle anderen Lösungen !!!
[email protected]:~$ Sudo su - postgres
psql-Benutzer erstellen:
[email protected]:~$ 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 legen Sie ein Kennwort für den erstellten Benutzer fest:
[email protected]:~$ 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
verbindung zur Zieldatenbank herstellen:
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".
gewähren Sie alle erforderlichen Berechtigungen:
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
standardberechtigungen für Ziele ändern db public shema:
target_database=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
ALTER DEFAULT PRIVILEGES