Изначально эта статья была написана как краткое внутреннее руководство по разрешениям PostgreSQL, но затем я решил опубликовать ее для всеобщего ознакомления. Наслаждайтесь!
Да, я знаю, что существует уже миллиард руководств и руководств по PostgreSQL в целом и управлению его пользователями в частности. И да, стоит отбросить их все и просто прочитать инструкцию.
Но я знаю, что вы ищете! Есть база данных PostgreSQL, которую вы раньше не трогали, и внезапно возникают некоторые проблемы с разрешениями. Вам просто нужно быстрое решение! Больше не беспокойтесь, вот решение для всего:
ALTER USER my_user WITH SUPERUSER;
Это, очевидно, худшее решение. Если у вас есть еще несколько минут, прочтите, и я покажу вам, как понять, что происходит с вашими существующими разрешениями базы данных, и разобраться во всем этом.
Ниже приводится 3-минутное руководство по ролям и разрешениям PostgreSQL, которые вы искали!
Перво-наперво. Несколько основных вещей, которые вам нужно знать:
0. Есть пользователи и есть роли. И это одно и то же (начиная с PostgreSQL 8.1). Ага. Каждый пользователь - это роль, и одна роль может быть членом другой роли. Давайте теперь просто называть все ролью.
1. Право собственности и Привилегии - это разные понятия. Права на чтение / создание / изменение данных в таблице отделены от права изменять (ALTER
) саму таблицу (которое есть только у владельца таблицы).
Это часто сбивает людей с толку: когда вы выполняете GRANT ALL ON my_table TO joe;
, это фактически не дает joe
права на ALTER
таблицу my_table
(если вы попытаетесь, вы получите ERROR: must be owner of relation my_table
). Чтобы дать joe
права на ALTER TABLE my_table
, вам нужно сделать joe
владельцем таблицы my_table
.
2. Лучшим методом является создание групповых ролей с определенными привилегиями, а затем предоставление этих групповых ролей пользователям (вместо предоставления индивидуальных привилегий пользователям). Например:
CREATE ROLE read_group; GRANT SELECT ON my_table TO read_group; CREATE ROLE write_group; GRANT INSERT, UPDATE, DELETE ON my_table TO write_group; CREATE ROLE owner_group; ALTER TABLE my_table OWNER TO owner_group;
Теперь, если вы хотите предоставить joe
права на чтение и запись данных:
GRANT read_group, write_group TO joe;
Для изменения таблицы:
GRANT owner_group TO joe;
3. Прежде чем получить доступ к каким-либо таблицам в этой схеме, пользователь должен иметь доступ к схеме. По умолчанию существует только одна public
схема, и каждый имеет к ней доступ. Но если у вас несколько схем, рекомендуется также проверить права доступа к схеме. Есть два типа:
1) USAGE
- это право доступа к объектам (например, таблицам) в схеме;
2) CREATE
это право ну создавать новые объекты.
Вот как можно проверить права доступа к схеме для пользователя joe
:
SELECT pg_catalog.has_schema_privilege('joe', 'my_schema', 'USAGE') AS has_usage, pg_catalog.has_schema_privilege('joe', 'my_schema', 'CREATE') AS has_create;
Наконец, вот несколько полезных запросов, которые помогут вам понять текущие права доступа к базе данных, с которой вы работаете:
- Список всех ролей, предоставленных конкретному пользователю. Например, для
joe
:
SELECT pg_user.usename, pg_roles.* FROM pg_user JOIN pg_auth_members ON pg_user.usesysid = pg_auth_members.member JOIN pg_roles ON pg_roles.oid = pg_auth_members.roleid WHERE pg_user.usename = 'joe';
- Перечислите всех пользователей для определенной роли. Например, для
write_group
:
SELECT rolname, usename FROM pg_user JOIN pg_auth_members ON pg_user.usesysid = pg_auth_members.member JOIN pg_roles ON pg_roles.oid = pg_auth_members.roleid WHERE pg_roles.rolname = 'write_group';
- Посмотрите, есть ли многоуровневые ролевые отношения, которые вам следует рассмотреть (например, роль, которая является родительской для другой роли):
SELECT DISTINCT cr.rolname AS child, pr.rolname AS parent FROM pg_auth_members AS roles JOIN pg_auth_members AS parents ON roles.roleid = parents.member JOIN pg_roles cr on roles.roleid = cr.oid JOIN pg_roles pr on parents.roleid = pr.oid ORDER BY child;
- Проверьте разрешения отдельных ролей. Например, для
write_group
:
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'write_group'
Вот и все. Этого должно быть достаточно, чтобы понять и исправить даже самые сложные проблемы с разрешениями в PostgreSQL.
Если что-то здесь не описано - смело обратитесь к руководству :)