Postgresql: Выделение прав

Отступление

Здесь важно понять механизм исключения прав. Создаваемый пользователь наследует права роли PUBLIC. Поэтому, чтобы лишить его какого-либо права нужно лишить этого права PUBLIC. Или создать другую групповую роль с нужными правами и затем предоставлять в ней членство создаваемым пользователям (в данной публикации этот вопрос не рассматривается)

2. Закрываем созданному пользователю доступ к схемам information_schema, pg_catalog.

На данном этапе существуют определенные тонкости со схемой pg_catalog. Во-первых, схема хранит много служебной информации, необходимой для работы пользовательских функций. Так, если закрыть для пользователя external_user доструп к pg_catalog, то вызов даже такой простой функции как getQuery() (описанной в этой статье) будет генерировать ошибку. В частности потому, что в каталоге pg_catalog хранится информация о всеъ типах данных postgresql.

А во-вторых существует особенность доступа к объектам схемы pg_catalog. А именно при разборе sql-запроса postgresql автоматически добавляет схему pg_catalog в пути поиска. Поэтому, мы можем закрыть доступ к схеме pg_catalog и запрос типа

SELECT * FROM pg_catalog.pg_roles;

подскажет нам об этом. Но автоматическое добавление pg_catalog в путь поиска позволяет нам выполнить запрос

SELECT * FROM pg_roles;

который вернет нам зарегистрированных в кластере пользователей.

Поэтому, если мы хотим максимально ограничить доступ пользователя к схеме pg_catalog, воспользуемся следующими командами

Забираем у групповой роли PUBLIC права на доступ к таблицам, представлениям и выполнению функций

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA pg_catalog from public;

Предоставляем права пользователю external_user только для нужных объектов (в нашем примере - таблицы pg_class, pg_type)

grant select on pg_class, pg_type to external_user

3. Закрываем доступ к подключению других баз данных (на примере бд other_db)

SET SESSION AUTHORIZATION postgres;
REVOKE connect ON database other_db FROM PUBLIC;

4. Добавляем доступ к объектам схемам config и logs

GRANT USAGE ON SCHEMA config TO external_user
GRANT USAGE ON SCHEMA logs TO external_user

При этом, после после получения доступа к схеме, наследуя права PUBLIC стали доступны следующие операции

• запуск всех функций, хранящихся в схеме,
• создание временных таблиц,
• возможность использования языков

5. Ограничиваем доступные для выполнения функции.

Если нужно запретить доступ к отдельным функциям, можно сделать так (на примере функции a())

REVOKE execute ON function config.a() FROM public;

Внимание! Если функция предусматривает входные параметры, при ее указании в скобках нужно перечислить типы этих параметров.

Если же функций много, а разрешить выполнение нужно только некоторым, делаем так (на примере функции a())

-- запрещаем для роли public выполнение всех функций
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA config from public;
--даем разрешение на выполнение только тем пользователям, которым нужно
GRANT execute ON function config.a() TO external_user;

6. Напоследок проверяем какими правами обладает наш пользовател external_user в полкюченной базе. Для этого выполняем запрос.

select * from (
SELECT
  use.usename as subject,
  nsp.nspname as schem,
  c.relname as obj,
  c.relkind as type,
  use2.usename as owner,
  c.relacl,
  (use2.usename != use.usename and c.relacl::text !~ ('({|,)' || use.usename || '=')) as public
FROM
  pg_user use
  cross join pg_class c
  left join pg_namespace nsp on (c.relnamespace = nsp.oid)
  left join pg_user use2 on (c.relowner = use2.usesysid)
WHERE
  c.relowner = use.usesysid or
  c.relacl::text ~ ('({|,)(|' || use.usename || ')=')
ORDER BY
  subject,
  schem,
  obj
 ) as res
 where
 subject = 'external_user'
 and public=false;

Запрос работает в пределах текущей подключенной базы данных. И не информативен для роли с правами суперпользователя

Запрос содержит следующие поля

• subject - имя пользователя
• schem - схема хранения объекта
• obj - название конечного объекта на который у пользователя есть права
• type - метка типа объекта
• owner - владелец объекта
• relacl - права пользователя на объект
• public - метка принадлежности к публичной схеме information_schema или pg_catalog

Метка типа объекта

• r = обычная таблица,
• i = индекс (index),
• S = последовательность (sequence),
• v = представление (view),
• m = материализованное представление(materialized view),
• c = составной тип (composite),
• t = таблица TOAST,
• f = сторонняя таблица (foreign)

Права пользоватя на объект

Записи, выводимые запросом интерпретируются так: имя_роли=xxxx -- права, назначенные роли

=xxxx -- права, назначенные PUBLIC

• r - SELECT ("read", чтение)
• w - UPDATE ("write", запись)
• a - INSERT ("append", добавление)
• d - DELETE
• D - TRUNCATE
• x - REFERENCES
• t - TRIGGER
• X - EXECUTE
• U - USAGE
• C - CREATE
• c - CONNECT
• T - TEMPORARY
• arwdDxt - ALL PRIVILEGES (все права для таблиц; для других объектов другие)
• * - право передачи заданного права
• /yyyy -- роль, назначившая это право

Запрос возвращает только те конечные объекты (таблицы, функции,... но не базы данных и схемы) у которых пользователю заданы какие-либо права. При этом, из результатов исключаются объекты, принадлежащие общим схемам information_schema и pg_catalog. Если мы хотим включить и их - нужно закомментировать условие public=false

Как было сказано ранее, запрос возвращает права доступа пользователя к объектам текущей подключенной бд. Чтобы узнать его права для объектов другой бд кластера postgresql, сначала к этой бд нужно подключиться. Для этого будем использовать расширение postgresql dblink.

sudo apt-get install postgresql-contrib

CREATE EXTENSION dblink;

(помним, что расширение создается для текущей бд)

Теперь запрос на получение прав пользователя будет выглядеть так

SELECT * FROM dblink('dbname={{database_name}}', '
        select * from (
        SELECT
          use.usename as subject,
          nsp.nspname as schem,
          c.relname as obj,
          c.relkind as type,
          use2.usename as owner,
          c.relacl,
          (use2.usename != use.usename and c.relacl::text !~ (''({|,)'' || use.usename || ''='')) as public
        FROM
          pg_user use
          cross join pg_class c
          left join pg_namespace nsp on (c.relnamespace = nsp.oid)
          left join pg_user use2 on (c.relowner = use2.usesysid)
        WHERE
          c.relowner = use.usesysid or
          c.relacl::text ~ (''({|,)(|'' || use.usename || '')='')
        ORDER BY
          subject,
          schem,
          obj
         ) as res
         where
         subject = ''{{user_name}}'';
         ')
AS r(subject name, schem name, obj name, type char, owner name, relacl aclitem[], public boolean)

запрос должен выполняться от имени суперпользователя

Узнать имена всех баз данных текущего класера можно так

select datname from pg_database