Как создать пользователя только для чтения в PostgreSQL?

Обратите внимание, что PostgreSQL 9.0 (сегодня в бета-тестировании) будет иметь простой способ сделать это :

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

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

Вот лучший способ, который я нашел, чтобы добавить пользователей только для чтения (используя PostgreSQL 9.0 или новее):

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

Затем войдите на все связанные машины (master + чтение-раб (ы) / горячее резервирование (и) и т. д.) и выполнение:

$ 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

Ссылка взята из этого блога:

Скрипт для создания пользователя только для чтения:

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

Назначить разрешение этому пользователю только для чтения:

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;

Я создал удобный сценарий для этого; pg_grant_read_to_db.sh . Этот сценарий предоставляет привилегии только для чтения указанной роли для всех таблиц, представлений и последовательностей в схеме базы данных и устанавливает их по умолчанию.

Если ваша база данных находится в общедоступной схеме, это легко (предполагается, что вы уже создали 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

Если ваша база данных использует customschema, выполните вышеописанное, но добавьте еще одна команда:

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

Непростой способ сделать это - предоставить select для каждой таблицы базы данных:

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

Вы можете автоматизировать это, сгенерировав свои операторы грантов из метаданных базы данных.

По умолчанию новые пользователи будут иметь разрешение на создание таблиц. Если вы планируете создать пользователя только для чтения, это, вероятно, не то, что вам нужно.

Чтобы создать истинного пользователя только для чтения с PostgreSQL 9.0+, выполните следующие шаги:

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

Если ваш пользователь только для чтения не имеет разрешения на просмотр таблиц (т. Е. \d возвращает результатов нет), вероятно, потому что у вас нет USAGE разрешений для схемы. USAGE - это разрешение, которое позволяет пользователям фактически использовать назначенные им разрешения. какой в ​​этом смысл? Я не уверен. Исправить:

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

Я прочитал все возможные решения, и все в порядке, если вы помните, чтобы подключиться к базе данных, прежде чем предоставить вещи;) В любом случае, спасибо всем остальным решениям !!!

user@server:~$ sudo su - postgres

create psql user:

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

запустите psql cli и установите пароль для созданного пользователя:

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

подключитесь к целевой базе данных:

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

предоставьте все необходимые привилегии:

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

изменить привилегии по умолчанию для целей db public shema:

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

Взято из ссылки, размещенной в ответ на ссылку despesz '.

Postgres 9.x обладает способностью делать то, что запрашивается. См. Параграф «Предоставление объектов базы данных»:

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

Где это говорит: «Существует также возможность предоставления привилегий всем объектам одного и того же типа в одной или нескольких схемах. В настоящее время эта функция поддерживается только для таблиц, последовательностей и функций (но учтите, что ALL TABLES считается включающим представления и сторонние объекты). таблицы). "

На этой странице также обсуждается использование ролей и привилегий, называемых" все привилегии ".

Также представлена ​​информация о том, как функциональные возможности GRANT сравниваются со стандартами SQL.