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

Zauważ, że PostgreSQL 9.0 (dzisiaj w beta testach) będzie miał prosty sposób na to :

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

Oto najlepszy sposób, jaki znalazłem, aby dodać użytkowników tylko do odczytu (używając 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 run:

$ 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

Odniesienie zaczerpnięte z tego bloga:

Skrypt do utworzenia użytkownika Tylko do odczytu:

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

Przypisanie uprawnień użytkownikowi 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;

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

Aby utworzyć prawdziwego użytkownika tylko do odczytu z PostgreSQL 9.0+, wykonaj 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 twój Użytkownik Tylko do odczytu nie ma uprawnień do wyświetlania tabel (tzn. \d nie zwraca żadnych wyników), prawdopodobnie dlatego, że nie masz uprawnień USAGE dla schematu. USAGE jest uprawnieniem, które pozwala użytkownikom na rzeczywiste korzystanie z uprawnień zostali przydzieleni. Jaki to ma sens? Nie jestem pewien. Do naprawy:

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

Jeśli twoja baza danych znajduje się w publicznym schemacie, 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 twoja baza danych używa customschema, wykonaj powyższe polecenie, ale dodaj jeszcze jedną komendę:

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

Niełatwym sposobem zrobienia tego byłoby przyznanie select na każdej tabeli bazy danych:

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

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

Wzięte z linku zamieszczonego w odpowiedzi na link despesz'.

Postgres 9.x wydaje się mieć możliwość wykonania tego, co jest wymagane. Zobacz akapit Grant On database Objects:

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

Gdzie jest napisane: "istnieje również opcja nadawania uprawnień wszystkim obiektom tego samego typu w ramach jednego lub kilku schematów. Ta funkcjonalność jest obecnie obsługiwana tylko dla tabel, sekwencji i funkcje (ale należy pamiętać, że wszystkie tabele są uważane za zawierające widoki i tabele obce)."

Ta strona omawia również użycie ról i przywileju o nazwie "wszystkie przywileje".

Obecna jest również informacja o tym, jak funkcjonalności GRANT porównują się do standardów SQL.