Как составить список баз данных и таблиц postgresql с помощью psql 2021
Содержание:
- Соединенные таблицы
- Возможные ошибки
- Какие параметры требуют перезапуск сервера?
- А куда сохраняются мои данные?
- Получение справки
- Настройка ротации паролей
- Создание тестовой базы данных
- Групповые привилегии
- Подключение к БД PostgreSQL из консоли
- После установки
- Вы здесь
- 14 команд для управления PostgreSQL
- Настройка прав PostgreSQL
- Доступ к базам данных
- Взаимодействие psql с операционной системой
- Выдача и отзыв привилегий у пользователя
Соединенные таблицы
Предложение JOIN позволяет запрашивать данные из нескольких связанных таблиц одновременно.
Примечание: Таблицы считаются связанными, если они имеют столбец, который относится к одним и тем же данным.
К примеру, в тестовой БД таблицы country и city связаны, поскольку совместно используют некоторые данные. Чтобы увидеть, что таблица country ссылается на таблицу city, введите:
Этот вывод говорит о том, что столбец capital таблицы country ссылается на столбец id таблицы city. По сути, можно воспринимать эти две таблицы как одну большую таблицу.
В предложенном выше запросе есть часть:
Это выражение значит, что Postgres вернёт данные из обеих таблиц. Предложение JOIN здесь является стандартным оператором соединения (также известен как inner join).
INNER JOIN выводит данные, присутствующие в обеих таблицах. Например, если соединить таблицы, которые не относятся друг к другу как внешние ключи, программа вернёт ошибку, поскольку таблицы содержат не связанные между собой данные. Для такого вывода стандартное предложение join не подходит.
Часть команды после ключевого слова on указывает общий столбец таблиц в таком формате:
В данном случае запрашиваются записи с общими значениями в обеих таблицах.
Возможные ошибки
Input file appears to be a text format dump. please use psql.
Причина: дамп сделан в текстовом формате, поэтому нельзя использовать утилиту pg_restore.
Решение: восстановить данные можно командой psql <имя базы> < <файл с дампом> или выполнив SQL, открыв файл, скопировав его содержимое и вставив в SQL-редактор.
No matching tables were found
Причина: Таблица, для которой создается дамп не существует. Утилита pg_dump чувствительна к лишним пробелам, порядку ключей и регистру.
Решение: проверьте, что правильно написано название таблицы и нет лишних пробелов.
Причина: Утилита pg_dump чувствительна к лишним пробелам.
Решение: проверьте, что нет лишних пробелов.
Aborting because of server version mismatch
Причина: несовместимая версия сервера и утилиты pg_dump. Может возникнуть после обновления или при выполнении резервного копирования с удаленной консоли.
Решение: нужная версия утилиты хранится в каталоге /usr/lib/postgresql/<version>/bin/. Необходимо найти нужный каталог, если их несколько и запускать нужную версию. При отсутствии последней, установить.
No password supplied
Причина: нет системной переменной PGPASSWORD или она пустая.
Решение: либо настройте сервер для предоставление доступа без пароля в файле pg_hba.conf либо экспортируйте переменную PGPASSWORD (export PGPASSWORD или set PGPASSWORD).
Неверная команда \
Причина: при выполнении восстановления возникла ошибка, которую СУБД не показывает при стандартных параметрах восстановления.
Решение: запускаем восстановление с опцией -v ON_ERROR_STOP=1, например:
psql -v ON_ERROR_STOP=1 users < /tmp/users.dump
Теперь, когда возникнет ошибка, система прекратит выполнять операцию и выведет сообщение на экран.
Какие параметры требуют перезапуск сервера?
Чтобы это выяснить нужно посмотреть все параметры у которых context = postmaster:
postgres@postgres=# SELECT name, setting, unit FROM pg_settings WHERE context = 'postmaster';
name | setting | unit
-------------------------------------+---------------------------------------+------
archive_mode | off |
autovacuum_freeze_max_age | 200000000 |
autovacuum_max_workers | 3 |
autovacuum_multixact_freeze_max_age | 400000000 |
bonjour | off |
bonjour_name | |
cluster_name | |
config_file | /usr/local/pgsql/data/postgresql.conf |
data_directory | /usr/local/pgsql/data |
data_sync_retry | off |
dynamic_shared_memory_type | posix |
event_source | PostgreSQL |
external_pid_file | |
hba_file | /usr/local/pgsql/data/pg_hba.conf |
hot_standby | on |
huge_pages | try |
ident_file | /usr/local/pgsql/data/pg_ident.conf |
ignore_invalid_pages | off |
jit_provider | llvmjit |
listen_addresses | localhost |
logging_collector | off |
max_connections | 100 |
max_files_per_process | 1000 |
max_locks_per_transaction | 64 |
max_logical_replication_workers | 4 |
max_pred_locks_per_transaction | 64 |
max_prepared_transactions | 0 |
max_replication_slots | 10 |
max_wal_senders | 10 |
max_worker_processes | 8 |
old_snapshot_threshold | -1 | min
port | 5432 |
recovery_target | |
recovery_target_action | pause |
recovery_target_inclusive | on |
recovery_target_lsn | |
recovery_target_name | |
recovery_target_time | |
recovery_target_timeline | latest |
recovery_target_xid | |
restore_command | |
shared_buffers | 16384 | 8kB
shared_memory_type | mmap |
shared_preload_libraries | |
superuser_reserved_connections | 3 |
track_activity_query_size | 1024 | B
track_commit_timestamp | off |
unix_socket_directories | /tmp |
unix_socket_group | |
unix_socket_permissions | 0777 |
wal_buffers | 512 | 8kB
wal_level | replica |
wal_log_hints | off |
(53 rows)
Time: 0,666 ms
Сводка
Имя статьи
PostgreSQL. Конфигурирование
Описание
Сервер баз данных PostgreSQL имеет очень много параметров с помощью которых его можно настроить под любые нужды. В этой статье мы не будет рассматривать все эти параметры. Здесь мы посмотрим на различные способы настройки этого сервера.
А куда сохраняются мои данные?
Базы данных – это в первую очередь история про персистентность. И,.. Хьюстон, кажется у нас проблема… К настоящему моменту мы никак не управляем долговременным хранением нашей базы данных. Эту задачу целиком на себя берёт Docker, автоматически создавая volume для контейнера с БД. Есть целый ворох причин, почему это плохо, начиная от банальной невозможности просматривать содержимое volume’ов в бесплатной версии Docker Desktop и заканчивая лимитами дискового пространства.
Разумеется, хорошей практикой является полностью ручное управление физическим размещением создаваемых баз данных. Для этого нам нужно подмонтировать соответствующий каталог (куда будут сохраняться данные) в контейнер и при необходимости переопределить переменную окружения PGDATA:
Вариант с макросом, использующий для инициализации БД скрипты из предыдущего раздела:
С однострочниками на этом закончим. Все дальнейшие шаги будем осуществлять только через compose-файл:
При запуске этого скрипта рядом с ним создастся директория pgdata, где будут располагаться файлы БД.
Получение справки
Каждый раз когда мне нужно выполнить какую-нибудь редкую или сложную команду SQL, я лезу в справку, благо она всегда доступна через .
без аргументов покажет список доступных разделов. Это уже само по себе полезно, чтобы подсмотреть синтаксис или . Допустим, вы хотите удалить ограничение NOT NULL для столбца, но не помните точно как это делается через команду ALTER. поможет в этом и предоставит более чем подробную информацию.
psql использует пагинацию для большого вывода, так что там работает поиск. Наберите и увидите все упоминания, с помощью n и N можно переходить к следующему и предыдущему соответственно.
Поискав NULL, находится вот такая команда: . Теперь можно составить нужную команду, не заглядывая в Google.
Как видите, даёт справку только по SQL командам. Для внутренних команд psql, которые начинаются с обратного слэша, есть .
Настройка ротации паролей
Данная настройка позволит автоматически по таймеру менять пароли в СУБД PostgreSQL для определенных учетных записей.
На стороне хранилища паролей необходимо:
- Создать роль, которая будет менять пароль в базе данных.
- Проверить настройки, создав новый пароль и подключившись к базе данных.
Переходим к настройке.
Создаем роль для смены пароля
Настройка задается с помощью static-roles. В нашем Vault вводим команду:
vault write database/static-roles/postgresql-rotate \
db_name=postgresql \
rotation_statements=»ALTER USER \»`name`\» WITH PASSWORD ‘`password`’;» \
username=»dmosk» \
rotation_period=720h
* обратите внимание, что запрос rotation_statements важно написать именно так — первая кавычка двойная, вторая одинарная. ** в данном примере:
** в данном примере:
- Мы создадим роль с названием postgresql-rotate (с тем названием, которое мы использовали в allowed_roles, когда создавали конфигурацию).
- Опция db_name должна называться как настройка, которую мы создали на предыдущем шаге (database/config/postgresql).
- Наша роль будет выполнять команду rotation_statements (менять пароль для пользователя).
- Каждые 720 часов (30 дней) пароль будет меняться автоматически.
Мы должны увидеть что-то на подобие:
Success! Data written to: database/static-roles/postgresql-rotate
Готово.
Меняем пароль и проверяем доступ
Давайте посмотрим текущий пароль:
vault read database/static-creds/postgresql-rotate
Мы получим что-то на подобие:
Key Value
— ——
last_vault_rotation 2021-09-07T11:35:07.796668266+03:00
password Cai-dzsJDtKTLHSl6Bvt
rotation_period 720h
ttl 719h48m55s
username dmosk
Где Cai-dzsJDtKTLHSl6Bvt — наш пароль от учетной записи dmosk.
Перейдем на сервер с PostgreSQL и попробуем войти в СУБД с использованием этих данных:
psql -Udmosk -W template1
Мы должны подключиться с использованием пароля, который получили от Vault.
Создание тестовой базы данных
Прежде чем начать работу с запросами в SQL, создайте тестовую базу данных и пару таблиц, а затем заполните эти таблицы условными данными. Это позволит вам получить практический опыт, выполняя предложенные в мануале примеры.
В качестве БД, которую мы будем использовать в этом руководстве, мы предлагаем следующий сценарий.
Предположим, вы и несколько ваших подруг всегда празднуете дни рождения вместе. Каждый раз все члены группы направляются в местный боулинг на дружеский турнир, а затем идут к вам, где вы готовите любимое блюдо для именинницы.
Теперь, когда это стало традицией, вы решили начать отслеживать результаты этих турниров. Кроме того, чтобы упростить планирование обедов, вы хотите создать запись о днях рождения ваших подруг и их любимых блюдах, гарнирах и десертах. Вместо того чтобы хранить эту информацию в физической книге, вы можете использовать свои навыки работы с СУБД PostgreSQL.
Для начала откройте командную строку PostgreSQL:
Примечание: Если вы полностью выполнили мануал по установке PostgreSQL в Ubuntu 18.04, вероятно, вы настроили новую роль. В этом случае вы можете подключиться к командной строке Postgres с помощью следующей команды, заменив 8host своим именем пользователя:
Создайте БД:
Выберите эту БД:
Затем создайте в этой базе данных две таблицы. Первую таблицу можно использовать, чтобы отслеживать результаты в боулинге. Следующая команда создаст таблицу под названием tourneys. В ней будут столбцы с именами подруг (name), количество выигранных ими турниров (wins), их лучшие результаты за все время (best) и размер ботинок для боулинга, которые они носят (size):
Запустив эту команду и заполнив ее заголовками столбцов, вы увидите такой вывод:
Теперь добавьте в таблицу какие-нибудь данные:
Команда вернет:
После этого создайте еще одну таблицу в той же базе данных. Ее можно использовать для хранения информации о любимых блюдах подруг на день рождения. Следующая команда создает таблицу dinners, где будут столбцы с именами подруг, их датой рождения (birthdate), их любимым блюдом (entree), гарниром (side) и десертом (dessert):
После запуска команда выведет:
Теперь заполните таблицу данными:
Теперь у вас есть данные, на которых можно потренироваться.
Групповые привилегии
Роль получает привилегии своих групповых ролей. Нужно ли ей будет для получения привилегий выполнять 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 – право выполнять программы на сервере (от имени пользователя, запускающего СУБД).
Подключение к БД PostgreSQL из консоли
Все команды запускаются под пользователем postgres (postgresql-суперпользователь)
psql -l — список баз данных.
psql -d dbname — подключение к БД dbname.
psql -f file.sql — выполнение команд из файла file.sql.
psql -U postgres -d dbname -c «CREATE TABLE test(some_id serial PRIMARY KEY, some_text text);» — выполнение команды в базе dbname.
psql -d dbname -H -c «SELECT * FROM test» -o test.html — вывод результата запроса в html-файл.
Просмотр списка и путей к конфигурационным файлам
Список активных соединений с информацией о: pid процесса, выполняющегося запроса, пользователя, базы данных.
c dbname — подсоединение к БД dbname.
l — список баз данных.
dt — список всех таблиц.
d table — структура таблицы table.
du — список всех пользователей и их привилегий.
dt+ — список всех таблиц с описанием.
dt *s* — список всех таблиц, содержащих s в имени.
i FILE — выполнить команды из файла FILE.
o FILE — сохранить результат запроса в файл FILE.
a — переключение между режимами вывода: с/без выравнивания.
Бекап и восстановление таблиц
В PostgreSQL есть две утилиты для бекапа pg_dump и pg_dumpall . pg_dump используется для бекапа одной базы, pg_dumpall для бекапа всех баз и сервера в целом (необходимо запускать под postgresql-суперпользователем).
Создание бекапа базы mydb, в сжатом виде
Создание бекапа базы mydb, в виде обычного текстового файла, включая команду для создания БД
Создание бекапа базы mydb, в сжатом виде, с таблицами которые содержат в имени payments
Дамп данных только одной, конкретной таблицы. Если нужно создать резервную копию нескольких таблиц, то имена этих таблиц перечисляются с помощью ключа -t для каждой таблицы.
Создание резервной копии с сжатием в gz
Список наиболее часто используемых опций:
-h host — хост, если не указан то используется localhost или значение из переменной окружения PGHOST.
-p port — порт, если не указан то используется 5432 или значение из переменной окружения PGPORT.
-u — пользователь, если не указан то используется текущий пользователь, также значение можно указать в переменной окружения PGUSER.
-a, —data-only — дамп только данных, по-умолчанию сохраняются данные и схема.
-b — включать в дамп большие объекты (blog’и).
-s, —schema-only — дамп только схемы.
-C, —create — добавляет команду для создания БД.
-c — добавляет команды для удаления (drop) объектов (таблиц, видов и т.д.).
-O — не добавлять команды для установки владельца объекта (таблиц, видов и т.д.).
-F, —format — выходной формат дампа, custom, tar, или plain text.
-t, —table=TABLE — указываем определенную таблицу для дампа.
-v, —verbose — вывод подробной информации.
-D, —attribute-inserts — дамп используя команду INSERT с списком имен свойств.
Бекап всех баз данных используя команду pg_dumpall .
В PostgreSQL есть две утилиты для восстановления базы из бекапа.
- psql — восстановление бекапов, которые хранятся в обычном текстовом файле (plain text);
- pg_restore — восстановление сжатых бекапов (tar);
Восстановление всего бекапа с игнорированием ошибок
Восстановление всего бекапа с остановкой на первой ошибке
Для восстановления из tar-арихива нам понадобиться сначала создать базу с помощью CREATE DATABASE mydb; (если при создании бекапа не была указана опция -C ) и восстановить
Восстановление резервной копии БД, сжатой gz
Начиная с версии 9.2 можно восстановить только структуру таблиц с помощью опции —section
Перенос директории с данным (data directory)
Узнать текущий путь
Создадим новую директорию, назначим пользователя и инициализируем
Теперь надо подправить файл с сервисом, который стартует postgresql
Очищение таблицы tablename и обнуление счетчика с ID.
CASCADE нужен на случай если tablename связана с другой таблицей.
Удаление NULL у поля
pgcli утилита командной строки с авто-дополнениям и подсветкой синтаксиса.
После установки
После установки проверьте версию установленного PostgreSQL
postgres -V
postgres (PostgreSQL) 9.2.24
Расположение файлов с настройками, например,
postgresql.conf
можно получить выполнив
-bash-4.2$ su — postgres -c «psql -c ‘SHOW config_file;'»
Password:
config_file
————————————-
/var/lib/pgsql/data/postgresql.conf
(1 row)
В этом примере директория, которая содержит настройки это
Полезно изучить её содержание
ll /var/lib/pgsql/data/
total 48
drwx——. 7 postgres postgres 67 Jun 9 22:54 base
drwx——. 2 postgres postgres 4096 Jun 9 23:19 global
drwx——. 2 postgres postgres 18 Jun 9 13:54 pg_clog
-rw——-. 1 postgres postgres 4371 Jun 10 01:23 pg_hba.conf
-rw——-. 1 postgres postgres 1636 Jun 9 13:54 pg_ident.conf
drwx——. 2 postgres postgres 58 Jun 10 00:00 pg_log
drwx——. 4 postgres postgres 36 Jun 9 13:54 pg_multixact
drwx——. 2 postgres postgres 18 Jun 9 14:14 pg_notify
drwx——. 2 postgres postgres 6 Jun 9 13:54 pg_serial
drwx——. 2 postgres postgres 6 Jun 9 13:54 pg_snapshots
drwx——. 2 postgres postgres 25 Jun 10 02:06 pg_stat_tmp
drwx——. 2 postgres postgres 18 Jun 9 13:54 pg_subtrans
drwx——. 2 postgres postgres 6 Jun 9 13:54 pg_tblspc
drwx——. 2 postgres postgres 6 Jun 9 13:54 pg_twophase
-rw——-. 1 postgres postgres 4 Jun 9 13:54 PG_VERSION
drwx——. 3 postgres postgres 60 Jun 9 13:54 pg_xlog
-rw——-. 1 postgres postgres 19889 Jun 10 01:43 postgresql.conf
-rw——-. 1 postgres postgres 45 Jun 9 14:14 postmaster.opts
-rw——-. 1 postgres postgres 92 Jun 9 14:14 postmaster.pid
Вы здесь
14 команд для управления PostgreSQL
чт, 29.07.2010 — 19:41 — krak
tags:
sql
postgresql
1. Как изменить root пароль в PostgreSQL?
/usr/local/pgsql/bin/psql postgres postgres
Password: (oldpassword)
# ALTER USER postgres WITH PASSWORD ‘tmppassword’;
/usr/local/pgsql/bin/psql postgres postgres
Password: (tmppassword)
Изменение пароля для обычного пользователя происходит таким же образом. Пользователь root может поменять пароль любому пользователю.
ALTER USER username WITH PASSWORD ‘tmppassword’;
2. Как установить PostgreSQL в автозапуск?
su — root
tar xvfz postgresql-8.3.7.tar.gz
cd postgresql-8.3.7
cp contrib/start-scripts/linux /etc/rc.d/init.d/postgresql
chmod a+x /etc/rc.d/init.d/postgresql
3. Проверяем состояние сервера
/etc/init.d/postgresql status
Password:
pg_ctl: server is running (PID: 6171)
/usr/local/pgsql/bin/postgres “-D” “/usr/local/pgsql/data”
/etc/init.d/postgresql status
Password:
pg_ctl: no server running
4. Как запустить, остановить, перезапустить PostgreSQL?
service postgresql stop
Stopping PostgreSQL: server stopped
ok
service postgresql start
Starting PostgreSQL: ok
service postgresql restart
Restarting PostgreSQL: server stopped
ok
5. Как посмотреть какая версия PostgreSQL запущена?
/usr/local/pgsql/bin/psql test
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
test=# select version();
version
—————————————————————————————————-
PostgreSQL 8.3.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42)
(1 row)
test=#
5. Как создать пользователя в PostgreSQL?
Для этого существуют два метода..
Метод 1: Создаем пользователя в через PSQL шелл, командой CREATE USER.
CREATE USER ramesh WITH password ‘tmppassword’;
CREATE ROLE
Метод 2: Создаем пользователя в через шелл команду createuser.
/usr/local/pgsql/bin/createuser sathiya
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
CREATE ROLE
6. Как создать базу в PostgreSQL ?
Для этого существует 2 метода.
Метод 1: Создаем базу черезе PSQL шелл, с помощью команды CREATE DATABASE.
CREATE DATABASE mydb WITH OWNER ramesh;
CREATE DATABASE
Метод 2: Используем команду createdb.
/usr/local/pgsql/bin/createdb mydb -O ramesh
CREATE DATABASE
7. Получаем список всех баз в Postgresql?
# \l
List of databases
Name | Owner | Encoding
———-+———-+———-
backup | postgres | UTF8
mydb | ramesh | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
8. Как удалить базу в PostgreSQL?
# \l
List of databases
Name | Owner | Encoding
———-+———-+———-
backup | postgres | UTF8
mydb | ramesh | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
# DROP DATABASE mydb;
DROP DATABASE
9. Пользуемся встроенным хелпом к командам
Команда \? отобразит строку помощи для команда PSQL. \h CREATE покажет хелп для всех команд который начинаются с CREATE.
# \?
# \h CREATE
# \h CREATE INDEX
10. Как получить список всех таблиц в базе данный в Postgresql?
# \d
Для пустой базы вы получите сообщение “No relations found.”
11. Как узнать время выполнения запроса?
# \timing
— после выполения данной команды каждый последующий запрос будет показывать время выполнения.
# \timing
Timing is on.
# SELECT * from pg_catalog.pg_attribute ;
Time: 9.583 ms
12. Как посмотреть список доступных функций в PostgreSQL ?
Для того чтобы получить список доступных функций, скажите \df+
# \df
# \df+
13. Как отредактировать запрос к PostgreSQL в редакторе?
# \e
\e откроет редактор, в котором вы можете отредактировать запрос и сохранить его.
14. Где я могу найти файл истории postgreSQL?
Подобно файлу ~/.bash_history, postgreSQL хранит все sql команды в файле ~/.psql_history.
cat ~/.psql_history
alter user postgres with password ‘tmppassword’;
\h alter user
select version();
create user ramesh with password ‘tmppassword’;
\timing
select * from pg_catalog.pg_attribute;
.
Настройка прав PostgreSQL
На сервере PostgreSQL нам необходимо:
- Задать пароль для пользователя postgres.
- Создать пользователя, пароль для которого мы будем хранить в Vault.
- Разрешить подключение к базе данных для postgres и созданного пользователя.
- Открыть порт 5432 для удаленного подключения.
- Проверить доступы.
Рассмотрим действия по шагам.
1. Задаем пароль для postgres
Данные действия необходимы, если мы не задавали пароль для пользователя postgres или мы не знаем данный пароль.
Стоит иметь ввиду, что данный пользователь может использоваться другими приложениями — таким образом, смена пароля приведет к потере их работоспособности. В этом случае, стоит уточнить текущий пароль и использовать его.
Заходим под пользователем postgres и подключаемся к СУБД:
su — postgres
$ psql
Вводим:
=# ALTER USER postgres PASSWORD ‘password’;
* в данном примере мы задаем пароль password для пользователя postgres.
2. Создаем нового пользователя
На данном шаге мы создадим пользователя, для которого и будем хранить секрет в Vault. В рамках примера мы не будем его предоставлять никаких доступов. Для нас достаточно, чтобы мы могли подключиться и удостовериться, что пароль будет меняться.
В той же командной оболочке postgresql вводим:
=# CREATE USER dmosk WITH PASSWORD ‘myPassword’;
* с помощью данной команды мы создадим пользователя dmosk с паролем myPassword.
3. Разрешаем подключение к СУБД
Нам необходимо разрешить подключаться к базе данных пользователю dmosk (создали на шаге 2) с локального компьютера и пользователю postgres с сервера Vault. Это делается посредством редактирования файла pg_hba.conf.
Но размещение этого файла может быть разным — это зависит от версии установленного PostgreSQL. Вводим команду:
=# SHOW config_file;
Данная команда нам покажет место размещения конфигурационного файла postgresql.conf — в этом же каталоге находятся и другие конфигурационные файлы. Например, если команда показала:
—————————————-
/var/lib/pgsql/11/data/postgresql.conf
(1 row)
… то значит нужный нам файл в каталоге /var/lib/pgsql/11/data.
Выходим из командной оболочки psql:
=# \q
Разлогиниваемся из-под пользователя postgres:
$ exit
И вводим:
vi /var/lib/pgsql/11/data/pg_hba.conf
* где /var/lib/pgsql/11/data — путь, который мы получили с помощью sql-команды SHOW config_file.
В данном файле мы должны добавить 2 строки:
…
# «local» is for Unix domain socket connections only
local all dmosk md5
…
# IPv4 local connections:
host all postgres 192.168.1.20/32 md5
…
* где 192.168.1.20 — IP-адрес предполагаемого сервера Vault, с которого мы будем подключаться под пользователем postgres.
Открываем конфигурационный файл:
vi /var/lib/pgsql/11/data/postgresql.conf
Приводим опцию listen_addresses к виду:
listen_addresses = ‘*’
* в данном примере мы настроили, чтобы postgresql слушал запросы на всех сетевых интерфейсах. При необходимости. мы можем ограничить их число вводом конкретного адреса.
Перезапускаем службу СУБД:
systemctl restart postgresql
* команда для перезапуска PostgreSQL может отличаться и зависит от версии СУБД.
4. Настраиваем межсетевой экран
Для подключения к серверу баз данных по сети, нам необходимо открыть порт 5432.
Как правило, в CentOS используется firewalld, как средство управления брандмауэром. Вводим:
firewall-cmd —permanent —add-port=5432/tcp
firewall-cmd —reload
5. Делаем проверку
Убедимся, что наши учетные записи имеют права для подключения к базе данных.
На сервере с СУБД вводим:
psql -Udmosk -W template1
* в данном примере мы подключаемся к локальному хосту под пользователем dmosk.
Система запросит пароль — вводим тот, что задали при создании нашего пользователя. Мы должны попасть в оболочку psql.
Теперь подключаемся по SSH на сервер Vault. Нам придется установить на него клиента postgresql.
а) На системы RPM (Rocky Linux, CentOS):
yum install postgresql
б) На Deb (Ubuntu, Debian):
apt install postgresql
После установки можно подключаться к нашему серверу с помощью команды psql.
Вводим:
psql -h192.168.1.15 -Upostgres -W
* в данном примере мы подключимся к серверу 192.168.1.15 под учетной записью postgres.
Консоль у нас запросит пароль — вводим тот, что задали для пользователя postgres. В итоге мы должны подключиться к серверу:
postgres=#
Выходим из SQL-оболочки:
postgres=# \q
… и переходим к настройке Vault.
Доступ к базам данных
Выдача доступа пользователю
К базе данных можно выдать доступы нескольким пользователям.
Чтобы выдать доступ, в панели управления:
- Перейдите в раздел Облачная платформа ⟶ Базы данных.
- Перейдите на страницу нужного кластера баз данных и откройте вкладку Базы данных.
- Откройте страницу базы данных, в блоке Имеют доступ нажмите кнопку Добавить.
- В списке выберите пользователя и подтвердите изменения.
После создания пользователь может только подключиться к базе данных () и не может выполнять никакие операции с объектами БД. Чтобы дать пользователю доступ к объектам, .
Изменение владельца базы данных
Владелец базы данных назначается при ее создании. Владельца нельзя удалить (у каждой базы должен быть владелец), но его можно сменить на другого.
Чтобы изменить владельца, в панели управления:
- Перейдите в раздел Облачная платформа ⟶ Базы данных.
- Перейдите на страницу нужного кластера баз данных и откройте вкладку Базы данных.
- Откройте страницу базы данных, в списке Владелец базы выберите другого владельца и подтвердите изменения.
Удаление доступа для пользователя
Чтобы удалить доступ к базе данных, в панели управления:
- Перейдите в раздел Облачная платформа ⟶ Базы данных.
- Перейдите на страницу нужного кластера баз данных и откройте вкладку Базы данных.
- Откройте страницу базы данных, в блоке Имеют доступ удалите нужного пользователя и подтвердите изменения.
Взаимодействие psql с операционной системой
Терминал psql умеет выполнять команды операционной системы. Для этого нужно использовать команду “\!“. Например так:
postgres=# \! hostname s-pg13
Можно установить переменную окружения в систему с помощью команды \setenv:
postgres=# \setenv TEST Hello postgres=# \! echo $TEST Hello
А для того чтобы перевести вывод команд в файл нужно использовать ‘\o имя_файла’. И чтобы вернуть всё обратно используем “\o” без имени файла. Например:
postgres=# \o dba.log postgres=# SELECT schemaname, tablename, tableowner FROM pg_tables LIMIT 5; postgres=# \! cat dba.log ---------------------- schemaname | pg_catalog tablename | pg_statistic tableowner | postgres ---------------------- schemaname | pg_catalog tablename | pg_type tableowner | postgres ---------------------- schemaname | pg_catalog tablename | pg_foreign_table tableowner | postgres ---------------------- schemaname | pg_catalog tablename | pg_authid tableowner | postgres ---------------------- schemaname | pg_catalog tablename | pg_statistic_ext_data tableowner | postgres postgres=# \o postgres=# \x Expanded display is off.
В предыдущем листинге с помощью последней команды мы выключили расширенный режим.
Помимо вывода в файл psql умеет выполнять команды из файла. Это делается с помощью команды “\i имя файла”. Вот пример:
postgres=# \q postgres@s-pg13:~$ cat <<EOT >> dba1.log > SELECT 'pg_statistic: '|| count(*) FROM pg_statistic; > SELECT 'pg_type: '|| count(*) FROM pg_type; > SELECT 'pg_foreign_table: '|| count(*) FROM pg_foreign_table; > EOT postgres@s-pg13:~$ psql psql (13.3) Type "help" for help. postgres=# \! cat dba1.log SELECT 'pg_statistic: '|| count(*) FROM pg_statistic; SELECT 'pg_type: '|| count(*) FROM pg_type; SELECT 'pg_foreign_table: '|| count(*) FROM pg_foreign_table; postgres=# \a \t \pset fieldsep ' ' Output format is unaligned. Tuples only is on. Field separator is " ". postgres=# \i dba1.log pg_statistic: 402 pg_type: 411 pg_foreign_table: 0 postgres=# \a \t \pset fieldsep '|' Output format is aligned. Tuples only is off. Field separator is "|".
В примере выше мы проделали следующее:
- вышли из psql;
- создали скрипт dba1.log, который подсчитывает количество строк из:
- pg_statistic – статистическая информация о содержимом базы данных;
- pg_type – информация о типах данных;
- pg_foreign_table – дополнительная информация о сторонних таблицах.
- обратно вернулись в psql;
- прочитали файл dba1.log;
- изменили формат вывода;
- выполнили скрипт sql команд;
- вернули формат вывода в прежнее состояние.
Выдача и отзыв привилегий у пользователя
По умолчанию у пользователей нет доступа к операциям над любыми объектами баз данных (схемам, таблицам, функциям и т.д.), владельцами которой они не являются. Для этого пользователям нужно выдать привилегию (право доступа) на объект.
По умолчанию владельцы объекта имеют доступ и все права на объект.
Выдача привилегий пользователю
Выдача привилегий происходит с помощью команды , которая назначает права (привилегии) пользователям на объекты баз данных. Привилегии могут быть следующие: , , , и т.д.
Пример выдачи доступа на чтение () к таблице пользователю :
Подробное описание синтаксиса команды в документации PostgreSQL.
Пример создания пользователя схемы с правами только на чтение
Создадим пользователя с привилегиями: доступ к базе данных кластера, к таблице в схеме по умолчанию и ко всем таблицам схемы. Автоматически все новые таблицы будут создаваться с доступом только на чтение для этого пользователя.
Для создания пользователя схемы с правами только на чтение (read-only) и подключитесь к базе данных.
Затем создайте схему и таблицу:
Выдайте привилегии:
Отзыв привилегий у пользователя
Для отзыва привилегий используется команда , подробное описание синтаксиса команды в документации PostgreSQL.
Пример отзыва привилегии у пользователя на схему :
