postgresql
Zarządzanie rolami

Składnia

• 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

Utwórz użytkownika za pomocą hasła

Zasadniczo należy unikać korzystania z domyślnej roli bazy danych (często postgres ) w aplikacji. Zamiast tego należy utworzyć użytkownika o niższych poziomach uprawnień. Tutaj tworzymy nazwę niceusername i niceusername jej hasło very-strong-password

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

Problem polega na tym, że zapytania wpisane w konsoli psql są zapisywane w pliku historii .psql_history w katalogu .psql_history użytkownika i mogą być również logowane do dziennika serwera bazy danych PostgreSQL, odsłaniając w ten sposób hasło.

Aby tego uniknąć, użyj polecenia \password aby ustawić hasło użytkownika. Jeśli użytkownik wydający polecenie jest superużytkownikiem, bieżące hasło nie zostanie wyświetlone. (Musi być superużytkownikiem, aby zmieniać hasła superużytkowników)

CREATE ROLE niceusername with LOGIN;
\password niceusername

Utwórz rolę i pasującą bazę danych

Aby obsłużyć daną aplikację, często tworzysz nową rolę i bazę danych, które będą pasować.

Polecenia powłoki do uruchomienia byłyby następujące:

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

$ createdb -O blogger blogger

Zakłada się, że pg_hba.conf został poprawnie skonfigurowany, co prawdopodobnie wygląda następująco:

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

Przyznaj i cofnij uprawnienia.

Załóżmy, że mamy trzech użytkowników:

1. Administrator bazy danych> admin
2. Aplikacja z pełnym dostępem do swoich danych> read_write
3. Dostęp tylko do odczytu> tylko do odczytu

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

Dzięki powyższym zapytaniom niezaufani użytkownicy nie mogą już łączyć się z bazą danych.

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

Następny zestaw zapytań odwołuje wszystkie uprawnienia nieuwierzytelnionych użytkowników i zapewnia ograniczony zestaw uprawnień dla użytkownika 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;

Zmień domyślną ścieżkę wyszukiwania użytkownika

Za pomocą poniższych poleceń można ustawić domyślną ścieżkę wyszukiwania użytkownika.

1. Sprawdź ścieżkę wyszukiwania przed ustawieniem domyślnego schematu.

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. Ustaw search_path za alter user polecenia alter user aby dołączyć nowy schemat 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. Sprawdź wynik po wykonaniu.

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)

Alternatywny:

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

Przyznaj uprawnienia dostępu do obiektów utworzonych w przyszłości.

Załóżmy, że mamy three users :

1. Administrator bazy danych> admin
2. Aplikacja z pełnym dostępem do swoich danych> read_write
3. Dostęp tylko do odczytu> tylko do read_only

Za pomocą poniższych zapytań możesz ustawić uprawnienia dostępu do obiektów utworzonych w przyszłości w określonym schemacie.

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;

Lub możesz ustawić uprawnienia dostępu do obiektów utworzonych w przyszłości przez określonego użytkownika.

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

Utwórz użytkownika tylko do odczytu

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;