Перейти к основному содержимому

Права на функции и процедуры

Функции и процедуры в PostgreSQL — это программные модули, содержащие многократно используемые блоки кода, которые выполняются внутри самой базы данных. Они позволяют перенести логику приложения ближе к данным, повысить производительность и обеспечить консистентность операций.

Главные отличия процедур и функций:

  • для создания функции и процедур используются разные команды;
  • процедуры не возвращают значение (функция может вернуть null);
  • вызов процедуры требует отдельной команды;
  • некоторые атрибуты функций не применяются к процедурам.

Поскольку чаще всего используются именно функции, дальше говорить будет только о них, но материал будет актуален и для процедур.

Создание функции​

Для создания функции используется команда CREATE FUNCTION. При написании функции следует соблюдать порядок использования ключевых слов и специальных символов:

  • ключевое слово CREATE FUNCTION;
  • название функции и ее аргументы;
  • ключевое слово RETURNS и тип результата;
  • ключевое слово и символ AS $$ (начало тела функции);
  • тело функции с запросом;
  • ключевой символ $$ (конец тела функции);
  • ключевое слово LANGUAGE и тип языка.

Переключимся на роль student и попробуем создать функцию.

postgres@my_db=# \c - student
You are now connected to database "my_db" as user "student".

Теперь разберемся с составляющими функции. Задача функции возвращать одну строку из столбца s таблицы suptab, поэтому аргументов (данных, которые функции принимает от пользователя для работы) не будет, сразу после имени укажем пустые скобки. В таблице suptab всего один столбец с типом данных date, поэтому тип результата тоже укажем date. Запрос под нашу задачу выглядит так: SELECT s FROM suptab LIMIT 1;. Чтобы вывелась только одна строка используется ключевое слово LIMIT 1. Наш запрос выполняется на языке SQL, поэтому и тип языка тоже будет SQL.

student@my_db=> CREATE FUNCTION suptab_1st() RETURNS date AS $$
                SELECT s FROM suptab LIMIT 1;
                $$ LANGUAGE sql;
CREATE FUNCTION

Функция успешно создана, попробуем выполнить ее. Для этого просто указываем SELECT и название функции со скобками.

student@my_db=> SELECT suptab_1st();
 suptab_1st
------------
 2024-10-26
(1 row)

Функция корректно отработала.

Привилегии для процедур и функций​

Для функций и процедур предусмотрена единственная привилегия EXECUTE. Причем при создании функции или процедуры эта привилегия устанавливается по умолчанию для псевдороли public. Следовательно, все роли могут выполнять функции или процедуры, если право EXECUTE не было отозвано.

Создадим еще одну функцию, которая будет возвращать текущую дату.

student@my_db=> CREATE FUNCTION my_date() RETURNS date AS $$
                SELECT now()::date;
                $$ LANGUAGE sql;
CREATE FUNCTION

В данной функции вместо обращения к объекту используется другая функция (получение текущей даты), и ее результат явно приводится к типу date: now()::date.

Переключимся на роль abiturient и попробуем вызвать новую функцию.

student@my_db=> \c - abiturient
You are now connected to database "my_db" as user "abiturient".
abiturient@my_db=> SELECT my_date();
my_date
------------
 2024-10-26
(1 row)

Роль abiturient смогла успешно вызвать функцию, хоть и не является ее владельцем (создателем) за счет псевдороли public, которая неявно включает в себя все роли и распространяет на них свою привилегию EXECUTE.

Установка привилегий для функций и процедур​

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

abiturient@my_db=> \c - student
You are now connected to database "my_db" as user "student".

Изъятие привилегий на выполнение функции у псевдороли проходит как и у обычной роли на любой другой объект.

student@my_db=> REVOKE EXECUTE ON FUNCTION suptab_1st FROM public;
REVOKE

Теперь выдадим данную привилегию роли aspirant.

student@my_db=> GRANT EXECUTE ON FUNCTION suptab_1st TO aspirant;
GRANT

Посмотреть, кто может вызывать функцию, можно с помощью поля proacl в системном каталоге pg_catalog.pg_proc, где хранятся записи ACL на функции и процедуры. При запросе к каталогу указываем имя интересующей функции, чтобы упростить вывод.

student@my_db=> SELECT proacl FROM pg_proc WHERE proname = 'suptab_1st';
                proacl
----------------------------------------
 {student=X/student,aspirant=X/student}
(1 row)

В ACL записи функции сначала идет владелец (создатель), а затем роли, которым предоставили разрешение на вызов функции.

Свойства функций и процедур​

В теле функции могут быть задействованы объекты с разными владельцами. В такой ситуации встает вопрос: с правами какой роли будет вызвана функция? Узнать на него ответ можно в свойствах функции.

Метакоманда \df+ предоставляет подробные сведения о функции или процедуре, в частности установленные привилегии.

Переключимся на суперпользователя.

student@my_db=> \c - postgres
You are now connected to database "my_db" as user "postgres".

Теперь выведем подробную информацию о функции, в имени которой есть 1st. Режим подробного вывода включим метакомандой \x, а в конце снова выключим.

postgres@my_db=# \x \df+ *1st \x
Expanded display is on.
                     List of functions
-[ RECORD 1 ]-------+----------------------------------
Schema              | public
Name                | suptab_1st
Result data type    | date
Argument data types |
Type                | func
Volatility          | volatile
Parallel            | unsafe
Owner               | student
Security            | invoker
Access privileges   | student=x/student                +
                    | aspirant=x/student
Language            | sql
Source code         |                                  +
                    | SELECT s FROM suptab LIMIT 1;+
                    |
Description         |
Expanded display is off.

У функции свойств не меньше, чем у любого другого объекта в PostgreSQL, но сейчас обратим внимание только на свойство Security. Именно его значение показывает, чьи права будут использованы по отношению к объектам, с которыми взаимодействует функция.

Всего есть два варианта использования прав при вызове:

  • SECURITY INVOKER (устанавливается по умолчанию) — выполнение кода с правами вызвавшего;
  • SECURITY DEFINER — работа кода от имени владельца функции или процедуры.

В первом случае (SECURITY INVOKER) при выполнении кода необходим доступ ко всем упомянутым в нем объектам с правами роли, вызвавшей функцию или процедуру. Характеристика безопасности SECURITY DEFINER позволяет выполнять код от имени роли, которой принадлежит код. И, конечно, при такой характеристике обращение ко всем объектам в этом коде будет производиться так, как будто владелец функции этот код запустил.

Вызов функции с правами вызывающего​

Наиболее безопасный вариант — вызов функции с правами вызывающего. Посмотрим на него на примере роли aspirant.

Для начала переключимся на эту роль.

postgres@my_db=# \c - aspirant
You are now connected to database "my_db" as user "aspirant".

Роль aspirant не состоит в групповой роли, соответственно, не наследует владение какими-либо объектами или привилегии группы. Убедиться в этом можно, посмотрев на список ролей.

aspirant@my_db=> \du aspirant
          List of roles
 Role name|Attributes| Member of
----------+----------+------------
aspirant  |          | {}

Попробуем вызвать функцию suptab_1st().

aspirant@my_db=> SELECT suptab_1st();
ERROR: permission denied for table suptab
CONTEXT: SQL function "suptab_1st" statement 1

Вызов завершился ошибкой, поскольку в теле функции прописан запрос, который требует привилегии SELECT для таблицы suptab. Роль aspirant не владеет таблицей и не имеет привилегий на нее, поэтому прав на сам вызов функции у роли хватает, а вот на выполнение запроса в функции — нет.

Вызов функции с правами владельца​

При определении функции ролью student все требуемые права на объекты, упомянутые в теле функции, были в наличии (для student). Чтобы избежать проблемы нехватки привилегий при вызове функции другими пользователями, можно использовать характеристику безопасности функции SECURITY DEFINER. Данную характеристику можно задать сразу при создании функции, или в любой другой момент с помощью команды ALTER FUNCTION.

Переключимся на роль student, так как именно она владелец функции.

aspirant@my_db=> \c - student
You are now connected to database "my_db" as user "student".

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

student@my_db=> ALTER FUNCTION suptab_1st SECURITY DEFINER;
ALTER FUNCTION

Теперь переключимся на aspirant и снова попробуем вызвать функцию.

student@my_db=> \c - aspirant
You are now connected to database "my_db" as user "aspirant".
aspirant@my_db=> SELECT suptab_1st();
 suptab_1st
------------
 2024-10-26
(1 row)

Теперь вызов функции завершился успешно. При этом у роли aspirant все еще нет прав на таблицу suptab, но избежать ошибки позволяет измененная характеристика безопасности, которая берет нужные для работы привилегии у владельца.

Итоги​

  • Для функций и процедур предусмотрена одна привилегия — EXECUTE;
  • При создании функции привилегия EXECUTE устанавливается по умолчанию для псевдороли public;
  • Команда REVOKE EXECUTE ON FUNCTION … FROM public изымает право выполнения у всех ролей;
  • Команда GRANT EXECUTE ON FUNCTION … TO <роль> выдает право выполнения конкретной роли;
  • ACL-запись функции хранится в поле proacl системного каталога pg_proc;
  • Метакоманда \df+ выводит подробную информацию о функции, включая привилегии и свойства;
  • Свойство Security определяет, с правами какой роли выполняется код функции;
  • SECURITY INVOKER (по умолчанию) — функция выполняется с правами вызывающей роли;
  • SECURITY DEFINER — функция выполняется с правами владельца, что позволяет обойти нехватку привилегий у вызывающего.

Самопроверка​

Вопрос 1

Какая привилегия по умолчанию выдается псевдороли public при создании функции?

Вопрос 2

Какое поведение функции PostgreSQL по умолчанию, если при создании не указано ни SECURITY INVOKER, ни SECURITY DEFINER?

Вопрос 3

Функция suptab_1st создана с настройкой по умолчанию SECURITY INVOKER. Роль aspirant не имеет прав на таблицу suptab, используемую в теле функции. Что произойдет?

aspirant@my_db=> SELECT suptab_1st();

Вопрос 4

Роль student изменяет функцию:

student@my_db=> ALTER FUNCTION suptab_1st SECURITY DEFINER;

После этого aspirant (без прав на suptab) вызывает функцию. Что произойдет?

Вопрос 5

В каком поле системного каталога хранятся ACL записи для функций?