Jak utworzyć użytkownika tylko do odczytu w PostgreSQL?

Chciałbym utworzyć użytkownika w PostgreSQL, który może wykonywać WYBÓR tylko z określonej bazy danych. W MySQL komenda wyglądałaby następująco:

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

Co to jest równoważne polecenie lub seria poleceń w PostgreSQL?

Próbowałem...

postgres=# CREATE ROLE xxx LOGIN PASSWORD 'yyy';
postgres=# GRANT SELECT ON DATABASE mydb TO xxx;

Wygląda jednak na to, że jedyne, co możesz przyznać w bazie danych, to CREATE, CONNECT, TEMPORARY i TEMP.

Przyznaj użycie / wybierz do pojedynczego stołu

Jeśli przyznasz CONNECT tylko bazie danych, użytkownik może się połączyć, ale nie ma innych uprawnień. Musisz przyznać USAGE w przestrzeniach nazw (schematach) i SELECT w tabelach i widokach indywidualnie tak:

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;

Wiele tabel / widoków (PostgreSQL 9.0+)

W najnowszych wersjach PostgreSQL możesz udzielać uprawnień do wszystkich tabel / widoków / etc w schemacie za pomocą pojedynczego polecenia zamiast wpisywać je jeden po drugim:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx;

Wpływa to tylko na tabele, które zostały już utworzone. Co więcej, w przyszłości możesz automatycznie przypisywać domyślne role do nowych obiektów :

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

Zauważ, że domyślnie będzie to miało wpływ tylko na obiekty (tabele) utworzone przez użytkownika, który wydał to polecenie: chociaż można je również ustawić na dowolnej roli, do której należy użytkownik wydający. Jednak nie odbierasz domyślnych uprawnień do wszystkich ról, do których należysz podczas tworzenia nowych obiektów ... więc wciąż jest trochę kłopotów. Jeśli przyjmiesz podejście, że baza danych pełni rolę właściciela, a zmiany schematu są wykonywane jako rola właściciela, wówczas należy przypisać domyślne uprawnienia do tej roli właściciela. IMHO to wszystko jest trochę mylące i być może będziesz musiał poeksperymentować, aby wymyślić funkcjonalny przepływ pracy.

Wiele tabel / widoków (wersje PostgreSQL przed 9.0)

Aby uniknąć błędów w przypadku długich, wielostołowych zmian, zaleca się zastosowanie następującego „automatycznego” procesu w celu wygenerowania wymaganych ustawień GRANT SELECTdla każdej tabeli / widoku:

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');

To powinno wypisać odpowiednie polecenia GRANT do GRANT SELECT we wszystkich publicznych tabelach, widokach i sekwencjach, dla miłości kopiowania i wklejania. Oczywiście będzie to miało zastosowanie tylko do tabel, które zostały już utworzone.

Pamiętaj, że PostgreSQL 9.0 (dziś w fazie testów beta) będzie miał prosty sposób :

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

Referencje zaczerpnięte z tego bloga:

Skrypt do tworzenia użytkownika tylko do odczytu:

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

Przypisz uprawnienia do tego użytkownika tylko do odczytu:

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;

Oto najlepszy sposób na dodanie użytkowników tylko do odczytu (za pomocą PostgreSQL 9.0 lub nowszego):

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

Następnie zaloguj się do wszystkich powiązanych maszyn (master + read-slave (s) / hot-standby (s) itp.) I uruchom:

$ 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

Domyślnie nowi użytkownicy będą mieli uprawnienia do tworzenia tabel. Jeśli planujesz utworzyć użytkownika tylko do odczytu, prawdopodobnie nie tego chcesz.

Aby utworzyć prawdziwego użytkownika tylko do odczytu za pomocą PostgreSQL 9.0+, uruchom następujące kroki:

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

Jeśli użytkownik tylko do odczytu nie ma uprawnień do wyświetlania tabel (tzn. Nie \dzwraca żadnych wyników), to prawdopodobnie dlatego, że nie masz USAGEuprawnień do schematu. USAGEto uprawnienie, które pozwala użytkownikom faktycznie korzystać z uprawnień, które zostały im przypisane. Jaki jest tego sens? Nie jestem pewny. Naprawić:

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

Stworzyłem do tego wygodny skrypt; pg_grant_read_to_db.sh . Ten skrypt nadaje uprawnienia tylko do odczytu określonej roli we wszystkich tabelach, widokach i sekwencjach w schemacie bazy danych i ustawia je jako domyślne.

Przeczytałem wszystkie możliwe rozwiązania, które są w porządku, jeśli pamiętasz, aby połączyć się z bazą danych przed udzieleniem rzeczy;) W każdym razie dziękuję wszystkim innym rozwiązaniom !!!

user@server:~$ sudo su - postgres

utwórz użytkownika psql:

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

uruchom psql cli i ustaw hasło dla utworzonego użytkownika:

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

połącz się z docelową bazą danych:

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".

przyznaj wszystkie potrzebne uprawnienia:

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

zmień domyślne uprawnienia dla docelowej shema db publicznej:

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

Jeśli Twoja baza danych znajduje się w schemacie publicznym, jest to łatwe (zakłada się, że już utworzyłeś 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

Jeśli używasz bazy danych customschema, wykonaj powyższe czynności, ale dodaj jeszcze jedno polecenie:

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

Niełatwym sposobem na zrobienie tego byłoby przyznanie wyboru w każdej tabeli bazy danych:

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

Można to zautomatyzować, generując instrukcje dotacji z metadanych bazy danych.

CREATE USER username SUPERUSER  password 'userpass';
ALTER USER username set default_transaction_read_only = on;

Zaczerpnięte z linku opublikowanego w odpowiedzi na link despesz .

Wydaje się, że Postgres 9.x ma możliwość robienia tego, co jest wymagane. Zobacz akapit Dotyczenie obiektów bazy danych:

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

Gdzie jest napisane: „Istnieje również możliwość przyznania uprawnień do wszystkich obiektów tego samego typu w ramach jednego lub większej liczby schematów. Ta funkcja jest obecnie obsługiwana tylko dla tabel, sekwencji i funkcji (należy jednak pamiętać, że WSZYSTKIE TABELE obejmują widoki i tabele obce) ”.

Ta strona omawia również użycie ROL i PRIVILEGE o nazwie „WSZYSTKIE UPRAWNIENIA”.

Obecne są także informacje o tym, jak funkcjonalność GRANT porównuje się ze standardami SQL.