CYBERTEC опять интересную статью опубликовали, не всё из нее мне было известно — поэтому читать было интересно.

Ссылка на оригинал: gexec in psql: PostgreSQL poweruser practice

Автоматизация работы становится для опытных пользователей PostgreSQL всё более необходимой. И команда gexec может в этом помочь. Эта статья покажет как использовать оператор конкатенации || и команду \gexec и избежать ненужных повторений лишних действий.

Инструмент командной строки (CLI), поставляемый с PostgreSQL, называется psql. Как и многие другие CLI-клиенты, про psql сразу же забывают после установки сервера и заменяют его на какую-нибудь графическую утилиту управления. Или используют psql только для простых операций, выполняя более сложные с помощью других средств. Однако psql — очень мощный инструмент с большим количеством полезных функций.

Периодически бывает необходимо запускать одинаковую команду с разными параметрами. Чаще всего пользователи переписывают команду снова и снова. Или могут один раз написать команду в текстовом редакторе, а потом копировать её, вставлять и менять только параметры.

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

Для этого мы можем добавить в начало или конец любого результата запроса произвольный текст с помощью оператора ||

Упражнение 1: использование оператора ||

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

Мы можем сделать это вручную или попросить базу данных автоматизировать это.

1. Давайте получим таблицы с именами, начинающимися на pgbench

postgres=# SELECT tablename FROM pg_tables WHERE tablename~'^pgbench';
tablename
------------------
pgbench_accounts
pgbench_branches
pgbench_history
pgbench_tellers
(4 rows)

2. А теперь воспользуемся || для добавления начального и конечного фрагментов чтобы создать корректную команду с именем таблицы в качестве параметра.

postgres=# SELECT 'GRANT SELECT ON TABLE ' || tablename || ' TO someuser;' FROM pg_tables WHERE tablename~'^pgbench';
?column?
-----------------------------------------------------
GRANT SELECT ON TABLE pgbench_accounts TO someuser;
GRANT SELECT ON TABLE pgbench_branches TO someuser;
GRANT SELECT ON TABLE pgbench_history TO someuser;
GRANT SELECT ON TABLE pgbench_tellers TO someuser;
(4 rows)

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

Имейте в виду что, несмотря на удобство использования || для объединения (конкатенации) текста, это не считается хорошей практикой, поскольку он уязвим для SQL-инъекций, как показано в комментарии ниже:

НЕ объединяйте слепо имена таблиц с запросами. Вместо этого используйте quote_ident() или format() с %I. Они применяют правильное экранирование по мере необходимости.

Более безопасный подход для получения тех же результатов будет примерно таким:

postgres=# SELECT format('GRANT SELECT ON TABLE %I TO someuser;', tablename) FROM pg_tables WHERE tablename~'^pgbench';
format
-----------------------------------------------------
GRANT SELECT ON TABLE pgbench_accounts TO someuser;
GRANT SELECT ON TABLE pgbench_branches TO someuser;
GRANT SELECT ON TABLE pgbench_history TO someuser;
GRANT SELECT ON TABLE pgbench_tellers TO someuser;
(4 rows)

Теперь эти команды можно скопировать, а затем вставить прямо в строку приглашения (???).

Я даже видел, как люди берут такие строки, сохраняют их в файл, а затем заставляют psql выполнять все команды из файла.

Но, к счастью, существует способ намного проще.

\gexec

В psql есть много способов и внутренних инструментов для быстрого сбора информации о базах данных, схемах, таблицах, привилегиях и многом другом.

psql позволяет работать с входными и выходными буферами, и это свойство можно использовать вместе с \gexec чтобы psql выполнял каждую команду из выходного буфера.

Упражнение 2: вызов \gexec

Вернувшись к запросу для генерации необходимых команд, мы можем вызвать \gexec для выполнения каждой строки из вывода запроса.

postgres=# SELECT 'GRANT SELECT ON TABLE ' || tablename || ' TO someuser;' FROM pg_tables WHERE tablename~'^pgbench';
?column?
-----------------------------------------------------
GRANT SELECT ON TABLE pgbench_accounts TO someuser;
GRANT SELECT ON TABLE pgbench_branches TO someuser;
GRANT SELECT ON TABLE pgbench_history TO someuser;
GRANT SELECT ON TABLE pgbench_tellers TO someuser;
(4 rows)

postgres=# \gexec
GRANT
GRANT
GRANT
GRANT

Упражнение 3: CROSS JOIN с \gexec

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

Предположим, вам необходимо предоставить права на вставку, обновление и удаление из этих таблиц.

Простой CROSS JOIN дает нам желаемый результат (с помощью набора строк, построенного с использованием конструктора VALUES) для каждого из имен таблиц.

postgres=# SELECT action, tablename FROM pg_tables CROSS JOIN (VALUES ('INSERT'),('UPDATE'),('DELETE')) AS t(action) WHERE tablename~'^pgbench';
action | tablename
--------+------------------
INSERT | pgbench_accounts
UPDATE | pgbench_accounts
DELETE | pgbench_accounts
INSERT | pgbench_branches
UPDATE | pgbench_branches
DELETE | pgbench_branches
INSERT | pgbench_history
UPDATE | pgbench_history
DELETE | pgbench_history
INSERT | pgbench_tellers
UPDATE | pgbench_tellers
DELETE | pgbench_tellers
(12 rows)

Обратите внимание, что мы явно назначаем имя столбца «action» с помощью AS t(action) таблице, сгенерированной с помощью VALUES.

postgres=# SELECT 'GRANT ' || action || ' ON TABLE ' || tablename || ' TO someuser;' FROM pg_tables CROSS JOIN (VALUES ('INSERT'),('UPDATE'),('DELETE')) AS t(action) WHERE tablename~'^pgbench';
?column?
-----------------------------------------------------
GRANT INSERT ON TABLE pgbench_accounts TO someuser;
GRANT UPDATE ON TABLE pgbench_accounts TO someuser;
GRANT DELETE ON TABLE pgbench_accounts TO someuser;
GRANT INSERT ON TABLE pgbench_branches TO someuser;
GRANT UPDATE ON TABLE pgbench_branches TO someuser;
GRANT DELETE ON TABLE pgbench_branches TO someuser;
GRANT INSERT ON TABLE pgbench_history TO someuser;
GRANT UPDATE ON TABLE pgbench_history TO someuser;
GRANT DELETE ON TABLE pgbench_history TO someuser;
GRANT INSERT ON TABLE pgbench_tellers TO someuser;
GRANT UPDATE ON TABLE pgbench_tellers TO someuser;
GRANT DELETE ON TABLE pgbench_tellers TO someuser;
(12 rows)

Далее вывод этого запроса опять можно выполнить с помощью \gexec.

postgres=# \gexec
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT

Упражнение 4: добавление кавычек

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

postgres=# SELECT 'GRANT SELECT ON TABLE "' || tablename || '" TO someuser;' FROM pg_tables WHERE schemaname='public';
?column?
-----------------------------------------------------
GRANT SELECT ON TABLE "with spaces" TO someuser;
GRANT SELECT ON TABLE "Capitalization" TO someuser;
GRANT SELECT ON TABLE "capitalization" TO someuser;
(3 rows)

postgres=# \gexec
GRANT
GRANT
GRANT

Теперь, когда вы знаете, как использовать \gexec, почему бы не сделать следующий шаг? Посмотрите нашу статью о том, что порядке столбцов в PostgreSQL имеет значение, чтобы увидеть использование \gexec на другом практическом примере.

Если вы хотите узнать больше о безопасности в PostgreSQL, смотрите мою статью о Безопасности транспортного уровня.

Джулиан Маркворт (Julian Markwort)

Джулиан Маркворт — младший консультант и разработчик в CYBERTEC. Его участие в сообществе PostgreSQL началось примерно в 2015 году, когда Джулиан работал студентом в Мюнстерском университете, где он продолжает обучение на магистра наук, на всякий случай и, одновременно, помогая глобальным корпорациям запускать кластеры Patroni, а также разрабатывать и создавать много полезного, связанного с базами данных.

Ещё раз ссылка на оригинал: gexec in psql: PostgreSQL poweruser practice