Изначально эта статья была написана как краткое внутреннее руководство по разрешениям 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.
Если что-то здесь не описано - смело обратитесь к руководству :)