postgresql
Rollenverwaltung

Syntax

• CREATE ROLE name [ [ WITH ] option [ ... ] ]

• CREATE USER name [ [ WITH ] option [ ... ] ]

• where option can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | CREATEUSER | NOCREATEUSER | INHERIT | NOINHERIT | LOGIN | NOLOGIN | CONNECTION LIMIT connlimit | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp' | IN ROLE role_name [, ...] | IN GROUP role_name [, ...] | ROLE role_name [, ...] | ADMIN role_name [, ...] | USER role_name [, ...] | SYSID uid

Erstellen Sie einen Benutzer mit einem Kennwort

Im Allgemeinen sollten Sie die Verwendung der Standarddatenbankrolle (häufig postgres ) in Ihrer Anwendung vermeiden. Sie sollten stattdessen einen Benutzer mit niedrigeren Berechtigungen erstellen. Hier machen wir einen Namen namens niceusername und geben ihm ein very-strong-password

CREATE ROLE niceusername with PASSWORD 'very-strong-password' LOGIN;

Das Problem dabei ist, dass Abfragen, die in die psql Konsole eingegeben werden, in der History-Datei .psql_history im Home-Verzeichnis des Benutzers gespeichert werden und .psql_history im PostgreSQL-Datenbankserverprotokoll protokolliert werden können, wodurch das Kennwort angezeigt wird.

Um dies zu vermeiden, verwenden Sie den Befehl \password , um das Benutzerkennwort festzulegen. Wenn der Benutzer, der den Befehl ausgibt, ein Superuser ist, wird das aktuelle Kennwort nicht abgefragt. (Muss ein Superuser sein, um die Passwörter der Superuser zu ändern.)

CREATE ROLE niceusername with LOGIN;
\password niceusername

Erstellen Sie eine Rollen- und passende Datenbank

Um eine bestimmte Anwendung zu unterstützen, erstellen Sie häufig eine neue Rolle und Datenbank, die übereinstimmen soll.

Die auszuführenden Shell-Befehle lauten wie folgt:

$ createuser -P blogger
Enter password for the new role: ********
Enter it again: ********

$ createdb -O blogger blogger

Dies setzt voraus, dass pg_hba.conf richtig konfiguriert wurde, was wahrscheinlich so aussieht:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    sameuser        all             localhost               md5
local   sameuser        all                                     md5

Gewähren und Widerruf von Berechtigungen.

Angenommen, wir haben drei Benutzer:

1. Der Administrator der Datenbank> admin
2. Die Anwendung mit vollem Zugriff auf ihre Daten> read_write
3. Der Nur-Lese-Zugriff> read_only

--ACCESS DB
REVOKE CONNECT ON DATABASE nova FROM PUBLIC;
GRANT  CONNECT ON DATABASE nova  TO user;

Mit den obigen Abfragen können nicht vertrauenswürdige Benutzer keine Verbindung zur Datenbank mehr herstellen.

--ACCESS SCHEMA
REVOKE ALL     ON SCHEMA public FROM PUBLIC;
GRANT  USAGE   ON SCHEMA public  TO user;

Die nächste Gruppe von Abfragen sperrt alle Berechtigungen von nicht authentifizierten Benutzern und bietet eingeschränkte Berechtigungen für den Benutzer read_write .

--ACCESS TABLES
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC ;
GRANT SELECT                         ON ALL TABLES IN SCHEMA public TO read_only ;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write ;
GRANT ALL                            ON ALL TABLES IN SCHEMA public TO admin ;

--ACCESS SEQUENCES
REVOKE ALL   ON ALL SEQUENCES IN SCHEMA public FROM PUBLIC;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO read_only; -- allows the use of CURRVAL
GRANT UPDATE ON ALL SEQUENCES IN SCHEMA public TO read_write; -- allows the use of NEXTVAL and SETVAL
GRANT USAGE  ON ALL SEQUENCES IN SCHEMA public TO read_write; -- allows the use of CURRVAL and NEXTVAL
GRANT ALL    ON ALL SEQUENCES IN SCHEMA public TO admin;

Ändern Sie den Standard-Suchpfad des Benutzers

Mit den folgenden Befehlen kann der Standard-Suchpfad des Benutzers festgelegt werden.

1. Überprüfen Sie den Suchpfad, bevor Sie das Standardschema festlegen.

postgres=# \c postgres user1
You are now connected to database "postgres" as user "user1".
postgres=> show search_path;
  search_path   
----------------
 "$user",public
(1 row)

2. search_path Sie search_path mit dem Befehl alter user , um ein neues Schema my_schema

postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# alter user user1 set search_path='my_schema, "$user", public';
ALTER ROLE

3. Ergebnis nach Ausführung prüfen.

postgres=# \c postgres user1
Password for user user1: 
You are now connected to database "postgres" as user "user1".
postgres=> show search_path;
 search_path 
-------------
 my_schema, "$user", public
(1 row)

Alternative:

postgres=# set role user1;
postgres=# show search_path;
 search_path 
-------------
 my_schema, "$user", public
(1 row)

Gewähren Sie Zugriffsberechtigungen für Objekte, die in der Zukunft erstellt werden.

Angenommen, wir haben three users :

1. Der Administrator der Datenbank> admin
2. Die Anwendung mit vollem Zugriff auf ihre Daten> read_write
3. Der Nur-Lese-Zugriff> read_only

Mit den folgenden Abfragen können Sie Zugriffsberechtigungen für Objekte festlegen, die in der Zukunft in einem bestimmten Schema erstellt werden.

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT                      ON TABLES TO read_only;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT,INSERT,DELETE,UPDATE ON TABLES TO read_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT ALL                         ON TABLES TO admin;

Sie können auch Zugriffsberechtigungen für Objekte festlegen, die in der Zukunft von einem bestimmten Benutzer erstellt werden.

ALTER DEFAULT PRIVILEGES FOR ROLE admin GRANT SELECT  ON TABLES TO read_only;

Schreibgeschützten Benutzer erstellen

CREATE USER readonly WITH ENCRYPTED PASSWORD 'yourpassword';
GRANT CONNECT ON DATABASE <database_name> to readonly;

GRANT USAGE ON SCHEMA public to readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;