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

Ответ 1

Использование гранта/выбор одной таблицы

Если вы предоставляете CONNECT только базе данных, пользователь может подключиться, но не имеет других привилегий. Вы должны предоставить ИСПОЛЬЗОВАНИЕ на пространствах имен (схемы) и SELECT на таблицах и представлениях индивидуально так:

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;

Несколько таблиц/представлений (PostgreSQL 9.0 +)

В последних версиях PostgreSQL вы можете предоставлять разрешения для всех таблиц/представлений/etc в схеме, используя одну команду, вместо того, чтобы вводить их по одному:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx;

Это касается только тех таблиц, которые уже созданы. Более мощно, вы можете автоматически роли по умолчанию, назначенные новым объектам в будущем:

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

Обратите внимание, что по умолчанию это влияет только на объекты (таблицы), созданные пользователем, который выдал эту команду: хотя он также может быть установлен для любой роли, членом которой является выдающий пользователь. Тем не менее, вы не получаете привилегии по умолчанию для всех ролей, в которых вы участвуете при создании новых объектов... так что все еще есть кое-какие неприятности. Если вы примете подход, в котором база данных имеет свою роль, а изменения схемы выполняются в качестве этой роли, вам следует назначить привилегии по умолчанию для этой роли. IMHO, это все немного запутанно, и вам может потребоваться поэкспериментировать, чтобы придумать функциональный рабочий процесс.

Несколько таблиц/представлений (версии PostgreSQL до 9.0)

Чтобы избежать ошибок в длинных изменениях с несколькими таблицами, рекомендуется использовать следующий "автоматический" процесс для создания требуемого GRANT SELECT для каждой таблицы/представления:

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

Это должно выводить соответствующие команды GRANT для GRANT SELECT для всех таблиц, представлений и последовательностей для публики, для любви к copy-n-paste. Естественно, это будет применяться только к уже созданным таблицам.

Ответ 2

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

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

Ответ 3

Вот лучший способ найти пользователей только для чтения (используя 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 + read-slave (s)/hot-standby (s) и т.д.) и запустите:

$ 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

Ответ 4

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

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

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;

Ответ 5

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

Чтобы создать истинного пользователя только для чтения с 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;

Ответ 6

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

Ответ 7

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

Ответ 8

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

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

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

Ответ 9

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

[email protected]:~$ sudo su - postgres

создать пользователя PSQL:

[email protected]:~$ 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 и установите пароль для созданного пользователя:

[email protected]:~$ 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

Ответ 10

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

Postgres 9.x, как представляется, имеет возможность делать то, что запрашивается. См. Пункт "Предоставление по объектам базы данных":

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

Где он говорит: "Существует также возможность предоставлять привилегии для всех объектов одного и того же типа в одной или нескольких схемах. Эта функциональность в настоящее время поддерживается только для таблиц, последовательностей и функций (но обратите внимание, что ВСЕ ТАБЛИЦЫ для включения представлений и внешних таблиц).

На этой странице также обсуждается использование ROLE и PRIVILEGE под названием "ВСЕ ПРИВИЛЕГИИ".

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

Ответ 11

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