PostgreSQL - пользователю БД следует разрешать только функции вызова

В настоящее время я использую PostgreSQL для своего приложения. Поскольку я пытаюсь поместить каждый SQL, который содержит транзакцию (например, вставить, обновить, удалить) в функции, я наткнулся на эту проблему:

Возможно ли, чтобы пользователю базы данных разрешалось только вызывать функции и Select-Statement, пока он не может вызывать SQL-заявления, содержащие транзакцию? Под "функциями вызова" подразумевается любая функция. Независимо от того, содержит ли он транзакцию или нет.

Я уже пытался создать пользователя, который может вызывать только функции и Select-Statement. Но я всегда получаю ошибку при вызове функций, содержащих транзакции. Насколько я понимаю, dbuser нуждается в разрешениях на запись, если он вызывает функцию, которая использует инструкцию insert, update или delete.

Я что-то упустил? Этот сценарий действительно невозможен? В безопасности это было бы действительно здорово, потому что вы в значительной степени предотвращаете SQL-инъекцию в первую очередь.

Ответ 1

Нет "привилегии на SELECT". Все, что вам нужно, это привилегия для функций EXECUTE. Соответствующая функция может работать с SECURITY DEFINER, чтобы наследовать все привилегии владельца. Чтобы ограничить возможную эскалацию привилегий до минимума априори, сделайте роль соответствующего демона собственными соответствующими функциями только с необходимыми привилегиями - не суперпользователем!

Рецепт

Как суперпользователь...

Создайте роль не суперпользователя myuser.

CREATE ROLE myuser PASSWORD ...;

Создайте групповую роль mygroup и создайте в ней член myuser.

CREATE ROLE mygroup;
GRANT mygroup TO myuser;

Возможно, вы захотите добавить больше пользователей, например, как myuser позже.

Do не предоставлять никаких привилегий вообще на myuser.
Предоставьте только mygroup:

GRANT CONNECT ON DATABASE mydb TO mygroup;
GRANT USAGE ON SCHEMA public TO mygroup;
GRANT EXECUTE ON FUNCTION foo() TO mygroup;

Удалите все привилегии для public, которые не должны иметь myuser.

REVOKE ALL ON ALL TABLES IN SCHEMA myschema FROM public;

Там может быть больше. Я цитирую руководство:

PostgreSQL предоставляет привилегии по умолчанию для некоторых типов объектов public. По умолчанию PUBLIC не предоставляет привилегии по таблицам, столбцов, схем или табличных пространств. Для других типов значение по умолчанию привилегии, предоставленные PUBLIC, следующие: CONNECT и CREATE TEMP TABLE для баз данных; EXECUTE привилегия для функций; и USAGEпривилегия для языков. Владелец объекта может, конечно, REVOKE как по умолчанию и явно предоставил привилегии. (Для максимальной безопасности, выпуск REVOKE в той же транзакции, которая создает объект; то есть это не окно, в котором другой пользователь может использовать объект.) Кроме того, эти начальные настройки привилегий по умолчанию могут быть изменены с помощью команды ALTER DEFAULT PRIVILEGES.

Создайте роль демона , чтобы иметь соответствующие функции.

CREATE ROLE mydaemon;

Предоставлять только привилегии, необходимые для выполнения этих функций, в mydaemon (включая EXECUTE ON FUNCTION, чтобы разрешить вызов другой функции). Опять же, вы можете использовать групповые роли для связывания привилегий и предоставить их mydaemon

GRANT bundle1 TO mydaemon;

Кроме того, вы можете использовать DEFAULT PRIVILEGES, чтобы автоматически предоставлять определенные привилегии для будущих объектов в пакет или демон прямо:

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES    TO bundle1;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE  ON SEQUENCES TO bundle1;

Это относится только к той роли, для которой она выполнена. В документации:

Если FOR ROLE опущен, предполагается, что текущая роль.

Также для покрытия существующих объектов в схеме (см. комментарий об отказе):

GRANT SELECT ON ALL TABLES    IN SCHEMA public TO bundle1;
GRANT USAGE  ON ALL SEQUENCES IN SCHEMA public TO bundle1;

Сделайте mydaemon собственные соответствующие функции. Может выглядеть так:

CREATE OR REPLACE FUNCTION foo()
  ...
SECURITY DEFINER SET search_path = myschema, pg_temp;

ALTER FUNCTION foo() OWNER TO mydaemon;
REVOKE EXECUTE ON FUNCTION foo() FROM public;
GRANT  EXECUTE ON FUNCTION foo() TO mydaemon;
GRANT  EXECUTE ON FUNCTION foo() TO mygroup;
-- possibly others ..

### Примечание
Из-за эта ошибка в текущей версии 1.16.1 pgAdmin необходимую команду

REVOKE EXECUTE ON FUNCTION foo() FROM public;

отсутствует в обратном инженерном DDL script. Не забудьте добавить его при воссоздании.
Эта ошибка исправлена в текущей версии pgAdmin 1.18.1.