Учетная запись для резервного копирования
Для выполнения резервного копирования лучше всего подключаться к базе с минимальными привилегиями.
Сначала создаем роль, которую будем использовать для выполнения резервного копирования:
=# CREATE USER bkpuser WITH PASSWORD ‘bkppasswd’;
* мы создадим учетную запись bkpuser с паролем bkppasswd.
Предоставляем права на подключения к базе
=# GRANT CONNECT ON DATABASE database TO bkpuser;
* в данном примере к базе database.
Подключаемся к базе (в нашем примере database):
=# \c database
Даем права на все последовательности в схеме:
=# GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO bkpuser;
* мы дали права для схемы public. Это схема является схемой по умолчанию, но в вашем случае она может быть другой. В таком случае, подставляем свое значение.
Выдача и отзыв привилегий
Выдать привилегию можно с помощью команды GRANT:
GRANT <привилегии> ON <объект> to <роль>;
Забрать привилегию можно с помощью команды REVOKE:
REVOKE <привилегии> ON <объект> FROM <роль>;
Выданной привилегией можно пользоваться, но нельзя передавать другим ролям. Но владелец или суперпользователь может вместе с привилегией выдать дополнительную опцию, которая разрешит передавать привилегию другим ролям. Выдача привилегии с правом её передачи выполняется с помощью WITH GRAND OPTION:
GRANT <привилегии> ON <объект> to <роль> WITH GRAND OPTION;
Если мы дали привилегию вместе с правом её передачи. А затем роль воспользовалась своим правом и передала привилегию другим ролям. То забрать эту привилегию можно только каскадно у этой роли и у других ролей с помощью CASCADE:
REVOKE <привилегии> ON <объект> FROM <роль> CASCADE;
Можно не отбирать привилегию, а только отобрать право её передачи. Это делается следующим способом:
REVOKE GRANT OPTION FOR <привилегии> ON <объект> FROM <роль>;
Создание нового пользователя
Для того, чтобы была возможность подключения к СУБД PostgreSQL от нового пользователя, необходимо создать данного пользователя, назначить ему права, выполнить настройку файла pg_hba.conf.
1. Создание пользователя
а) Добавление новой роли (пользователя) из оболочки SQL:
=# CREATE USER dmosk WITH PASSWORD ‘myPassword’;
* в примере создана роль dmosk с паролем myPassword.
б) Добавление новой роли (пользователя) из командной строки Linux:
createuser -P dmosk
2. Назначение прав на использование базы данных
Даем права на базу командой:
=# GRANT ALL PRIVILEGES ON DATABASE «database1» to dmosk;
Теперь подключаемся к базе, к которой хотим дать доступ:
=# \c database1
* в примере подсоединимся к базе с названием database1.
а) Так мы добавим все права на использование всех таблиц в базе database1 учетной записи dmosk:
database1=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO «dmosk»;
* в большинстве случаев, используется схема по умолчанию public. Но администратор может создать новую схему. Это нужно учитывать при назначении прав.
б) Также можно дать доступ к базе для определенных таблиц:
database1=# GRANT ALL PRIVILEGES ON TABLE table1 IN SCHEMA public TO «dmosk»;
* в данном примере мы даем права на таблицу table1.
Выходим из SQL-оболочки:
database1=# \q
3. Настройка файла pg_hba.conf
Для возможности подключиться к СУБД от созданного пользователя, необходимо проверить настройки прав в конфигурационном файле pg_hba.conf.
Для начала смотрим путь расположения данных для PostgreSQL:
=# SHOW config_file;
В ответ мы получим, что-то на подобие:
——————————————
/var/lib/pgsql/9.6/data/postgresql.conf
(1 row)
* в данном примере /var/lib/pgsql/9.6/data/ — путь расположения конфигурационных файлов.
Открываем pg_hba.conf:
vi /var/lib/pgsql/9.6/data/pg_hba.conf
Добавляем права на подключение нашему созданному пользователю:
…
# IPv4 local connections:
host all dmosk 127.0.0.1/32 md5
…
* в данном примере мы разрешили подключаться пользователю dmosk ко всем базам на сервере (all) от узла 127.0.0.1 (localhost) с требованием пароля (md5).
* необходимо, чтобы данная строка была выше строки, которая прописана по умолчаниюhost all all 127.0.0.1/32 ident.
После перезапускаем службу:
systemctl restart postgresql-9.6
* в данном примере установлен postgresql версии 9.6, для разных версий на разных операционных системах команды для перезапуска сервиса могут быть разные.
2 ответа
Лучший ответ
Уловка с не работает , поскольку он принимает не * текстовую строку * (значение), как , а действительный оператор (код).
Чтобы преобразовать данные в исполняемый код , вам необходимо использовать динамический SQL, то есть в функции plpgsql или инструкции . Это работает без проблем, пока тип возвращаемого значения не зависит от результата первой функции . В противном случае вы вернетесь, чтобы поймать 22, как указано в моем предыдущем ответе:
Как выполнить строковый результат хранимой процедуры в postgres
Важнейшая часть — как-то объявить возвращаемый тип (в данном случае тип строки). Вы можете создать , или для этой цели. Или вы можете использовать подготовленный оператор или рефкурсор.
Решение с подготовленным заявлением
Вы были очень близки. Отсутствует часть головоломки — подготовить сгенерированный запрос с помощью динамического SQL .
Функция для динамической подготовки оператора
Создайте эту функцию . Это оптимизированная и безопасная версия вашей функции :
Я использую для параметра имени таблицы , чтобы сделать его однозначным и безопасным для SQLi. Подробности:
Имя таблицы как параметр функции PostgreSQL
Информационная схема не включает столбец oid системных каталогов, поэтому я переключился на вместо . Это тоже быстрее. У этого есть свои плюсы и минусы:
Как проверить, существует ли таблица в данной схеме
Если подготовленный оператор с именем уже существует, вызовет исключение. Если это приемлемо, снимите флажок в системном представлении и следующие . Возможны более сложные алгоритмы для управления несколькими подготовленными операторами за сеанс или использования имени подготовленного оператора в качестве дополнительного параметра или даже использования хеша MD5 строки запроса в качестве имени, но это выходит за рамки этого вопроса.
Имейте в виду, что работает за пределами транзакций , после успешного выполнения подготовленный оператор существует в течение всего времени существования сеанса. Если транзакция упаковки прерывается, не затрагивается. не может удалить подготовленные операторы.
Выполнение динамического запроса
Два запроса, но только один вызов к серверу. И очень эффективно.
Проще и намного эффективнее для большинства простых случаев использования, чем создание временной таблицы или курсора и выбор / выборка из них (это были бы другие варианты).
2
Community
20 Июн 2020 в 09:12
Сначала давайте создадим функцию, которая создает динамический оператор :
Теперь давайте создадим вторую функцию, которая может выполнять строковый ТЕКСТ-вывод первой функции :
Вызов:
1
Erwin Brandstetter
8 Янв 2015 в 01:03
Привилегии по умолчанию
Привилегии по умолчанию – это такие привилегии, которые добавятся к каким-то ролям на объект при его создании. Например роль Алиса хочет чтобы при создании новой таблицы доступ к ней сразу же получала роль Боб.
Привилегии по умолчанию создаются командой ALTER DEFAULT PRIVILEGES:
ALTER DEFAULT PRIVILEGES GRANT <привилегии> ON <класс_объектов> TO <роль>;
В примере выше <класс_объектов> это может быть, например таблица, функция, представление и т.п. То есть создаём мы какой-то объект из этого класса и сразу срабатывает команда выдачи привилегий: .
Аналогично можно удалять такие привилегии:
ALTER DEFAULT PRIVILEGES REVOKE <привилегии> ON <класс_объектов> FROM <роль>;
Например сделаем так, чтобы при создании функций (любым пользователем), право их выполнять забиралось у роли public:
ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM public;
Затем вам придется отдельным ролям давать эту привилегию вручную. Или можете сделать отдельную групповую роль, которая сможет выполнять функции, и включать неё другие роли.
Групповые привилегии
Роль получает привилегии своих групповых ролей. Нужно ли ей будет для получения привилегий выполнять SET ROLE зависит от атрибута роли, который мы можем указать при создании роли, как было показано на предыдущем уроке:
- INHERIT – атрибут роли, который включает автоматическое наследование привилегий;
- NOINHERIT – атрибут роли, который требует явное выполнение SET ROLE.
В 13 PostgreSQL при инициализации кластера создаются следующие роли вместе с суперпользователем postgres:
- pg_signal_backend – право посылать сигналы обслуживающим процессам, например можно вызвать функцию pg_reload_conf() или завершить процесс с помощью функции pg_terminate_backend();
- pg_read_all_settings – право читать все конфигурационные параметры, даже те, что обычно видны только суперпользователям;
- pg_read_all_stats – право читать все представления pg_stat_* и использовать различные расширения, связанные со статистикой, даже те, что обычно видны только суперпользователям;
- pg_stat_scan_tables – право выполнять функции мониторинга, которые могут устанавливать блокировки в таблицах, возможно, на длительное время;
- pg_monitor – право читать и выполнять различные представления и функции для мониторинга. Эта роль включена в роли pg_read_all_settings, pg_read_all_stats и pg_stat_scan_tables;
- pg_read_server_files – право читать файлы в любом месте файловой системы, куда имеет доступ postgres на сервере. А также выполняя копирование и другие функции работы с файлами;
- pg_write_server_files – право записывать файлы в любом месте файловой системы, куда имеет доступ postgres на сервере. А также выполнять копирование и другие функции работы с файлами.
- pg_execute_server_program – право выполнять программы на сервере (от имени пользователя, запускающего СУБД).
Пример — использование оператора NOT
Теперь давайте посмотрим, как вы можете использовать оператор NOT с подстановочными символами.
Давайте использовать подстановочный символ % с оператором NOT. Вы также можете использовать PostgreSQL условие LIKE, чтобы найти записи таблицы employees, чье last_name не начинается с ‘J’.
Например:
PgSQL
SELECT first_name, last_name
FROM employees
WHERE last_name NOT LIKE ‘J%’;
|
1 |
SELECTfirst_name,last_name FROMemployees WHERElast_nameNOTLIKE’J%’; |
Поместив оператор NOT перед PostgreSQL условием LIKE, вы сможете получить все записи employees, чье last_name не начинается с ‘J’.
Синтаксис
Синтаксис для условия LIKE в PostgreSQL:
expression LIKE pattern
Параметры или аргументы
- expression
- Символьное выражение, такое как поле или столбец.
- pattern
- Символьное выражение, которое содержит сопоставляемый шаблон. Шаблоны, которые вы можете выбрать из:
Подстановочный символ Пояснение % Соответствует любой строке любой длины (в том числе нулевой длины) _ Соответствует одному символу - escape_character
- Необязательный. Это позволяет вам проверять наличие буквенных символов, таких как % или _. Если вы не предоставите escape_character, PostgreSQL предполагает, что является escape_character.
Пример — использование Escape-символов
Важно понимать, как «экранировать символы» когда pattern соответствует. Эти примеры имеют дело с экранированием символов в PostgreSQL.
Допустим, вы хотели найти символ % или _ в условии PostgreSQL LIKE
Вы можете сделать это с помощью символа Escape.
Обратите внимание, что вы можете определить только escape-символ как один символ (длина 1).
Например:
PgSQL
SELECT *
FROM employees
WHERE last_name LIKE ‘G\%’;
|
1 |
SELECT* FROMemployees WHERElast_nameLIKE’G\%’; |
Поскольку мы не указали escape-символ, PostgreSQL предполагает, что \ это escape-символ. Затем PostgreSQL предполагает, что escape-символ является \ в результате PostgreSQL рассматривает символ % как литерал вместо подстановочного символа. Этот оператор затем возвращает все записи из employees, чье last_name равно G%.
Мы можем переопределить escape-символ по умолчанию в PostgreSQL, предоставив модификатор ESCAPE следующим образом:
PgSQL
SELECT *
FROM employees
WHERE last_name LIKE ‘G!%’ ESCAPE ‘!’;
|
1 |
SELECT* FROMemployees WHERElast_nameLIKE’G!%’ESCAPE’!’; |
Этот пример PostgreSQL условия LIKE определяет ! как escape-символ. ! escape-символ приведет к тому, что PostgreSQL будет рассматривать символ % как литерал. В результате этот оператор также вернет все записи из таблицы employees, last_name которых равно G%.
Вот еще один более сложный пример использования escape-символов в PostgreSQL условии LIKE.
PgSQL
SELECT *
FROM employees
WHERE last_name LIKE ‘M%\%’;
|
1 |
SELECT* FROMemployees WHERElast_nameLIKE’M%\%’; |
Этот пример PostgreSQL условия LIKE возвращает всех employees, чье last_name начинается с ‘M’ и заканчивается на ‘%’. Например, он вернул бы значение, такое как «Mathison%». Поскольку в условии LIKE мы не указывали escape-символ, PostgreSQL предполагает, что escape-символ является \ что приводит к тому, что PostgreSQL рассматривает второй символ % как литерал вместо подстановочного символа.
Мы могли бы изменить это условие LIKE, указав escape-символ следующим образом:
PgSQL
SELECT *
FROM employees
WHERE last_name LIKE ‘M%!%’ ESCAPE ‘!’;
|
1 |
SELECT* FROMemployees WHERElast_nameLIKE’M%!%’ESCAPE’!’; |
Этот пример PostgreSQL условия LIKE возвращает все записи из employees, чье last_name начинается с ‘M’ и заканчивается литеральным ‘%’. Например, он будет возвращать значение, такое как «Mathison%».
Вы также можете использовать escape-символ с символом _ в условии PostgreSQL LIKE.
Например:
PgSQL
SELECT *
FROM employees
WHERE last_name LIKE ‘M%\_’;
|
1 |
SELECT* FROMemployees WHERElast_nameLIKE’M%\_’; |
Опять же, поскольку модификатор ESCAPE не предоставлен, PostgreSQL использует \ как escape-символ, приводящий к символу _, который будет трактоваться как литерал вместо подстановочного символа. В этом примере будут возвращаться все поля из employees, чье last_name начинается с ‘M’ и заканчивается на ‘_’. Например, он вернул бы значение, такое как ‘Mathison_’.
12 ответов
Лучший ответ
В PostgreSQL такой возможности нет. Вы можете сделать это только в pl / PgSQL (или другом pl / *), но не в простом SQL.
Исключением является запрос , который может работать как переменная или даже как переменные. Это позволяет вам вернуть таблицу временных значений.
147
J.Wincewicz
9 Авг 2018 в 12:39
Я достиг той же цели, используя предложение , он далеко не такой элегантный, но может делать то же самое. Хотя для этого примера это действительно перебор. Я тоже не особо рекомендую это.
285
a_horse_with_no_name
21 Фев 2018 в 18:51
Вы также можете попробовать это в PLPGSQL:
Для указанного выше требуется Postgres 9.0 или более поздней версии.
104
elixenide
15 Ноя 2016 в 15:38
Параметры динамической конфигурации
Вы можете «злоупотребить» настройками динамической конфигурации для этого:
Параметры конфигурации всегда являются значениями varchar, поэтому при их использовании вам необходимо привести их к правильному типу данных. Это работает с любым клиентом SQL, тогда как работает только в
Для указанного выше требуется Postgres 9.2 или более поздней версии.
Для предыдущих версий переменная должна была быть объявлена в перед использованием, поэтому это несколько ограничивало ее удобство использования. На самом деле не переменная полностью, а конфиг «class», который по сути является префиксом. Но как только префикс был определен, любую переменную можно было использовать без изменения
75
Rob Bednark
30 Авг 2019 в 04:41
Это зависит от вашего клиента.
Однако, если вы используете клиент psql , вы можете использовать следующее:
Если вы используете текстовые переменные, вам нужно указать кавычки.
63
Kemin Zhou
6 Июн 2019 в 22:17
Использование таблицы Temp вне pl / PgSQL
Помимо использования pl / pgsql или другого языка pl / *, как было предложено, это единственная другая возможность, о которой я мог подумать.
23
I Support The Boycott
1 Ноя 2016 в 18:16
Это решение основано на решении, предложенном fei0x, но его преимущества заключаются в том, что нет необходимости присоединяться к списку значений константы в запросе и константы можно легко перечислить в начале запроса. Он также работает в рекурсивных запросах.
По сути, каждая константа представляет собой таблицу с одним значением, объявленную в предложении WITH, которую затем можно вызвать в любом месте оставшейся части запроса.
Базовый пример с двумя константами:
В качестве альтернативы вы можете использовать вместо , что может быть недопустимо для других языков запросов, отличных от postgresql.
21
Jorge Luis
28 Янв 2019 в 12:30
Вот пример использования операторов PREPARE. Вы по-прежнему не можете использовать , но можете использовать обозначение :
10
Martin Zinovsky
30 Янв 2018 в 16:54
Правда, не существует наглядного и однозначного способа объявить однозначную переменную, что вы можете сделать, это
Затем, чтобы получить доступ к значению, хранящемуся в этой конструкции, вы делаете
Например
9
Michael.Medvedskiy
6 Май 2019 в 14:44
Вы можете прибегнуть к специальным возможностям инструмента. Как и для собственного синтаксиса DBeaver:
5
gavenkoa
11 Дек 2019 в 15:56
В DBeaver вы можете использовать параметры в запросах так же, как и в коде, поэтому это будет работать:
Когда вы запустите запрос, DBeaver запросит у вас значение для: myvar и выполнит запрос.
2
The Coder
27 Фев 2020 в 21:57
Я хочу предложить улучшение @ DarioBarrionuevo answer, чтобы упростить использование временных таблиц.
16
Community
23 Май 2017 в 12:26
