Установка PostgreSQL
Эта заметка является доработанным и расширенным вариантом моей старой заметки Установка PostgreSQL на FreeBSD.
Инициализация базы данных
При инициализации базы данных следует обратить внимание на два параметра:
- LC_COLLATE - отвечает за то, как будут сортироваться символы при выполнении сортировки;
- LC_CTYPE - отвечает за категории типов символов, например, чтобы одинаковые слова на кириллице считались одинаковыми независимо от регистра символов в них.
После создания кластера БД эти параметры невозможно будет изменить.
Настройка сервера
В большинстве операционных систем при установке сервера из репозиториев создаётся пользователь postgres (а ранних версиях FreeBSD - pgsql). При настройках по умолчанию PostgreSQL сервер использует текущего пользователя для проверки доступа к серверу. Поэтому для подключения к серверу необходимо переключиться на пользователя postgres. Далее можно воспользоваться утилитами для командной строки (createuser, createdb, dropuser, dropdb и др.) или запустить интерактивный терминал psql.
Для запуска psql пользователем root:
su - postgres
psql
Для пользователя, обладающего достаточными правами sudo:
sudo -u postgres psql
либо
sudo -u postgres -i
psql
Просле успешного запуска клиента будет отображена его версия и приглашение для ввода команд. В приглашении указывается имя текущей базы данных.
psql (14.3 (Ubuntu 14.3-0ubuntu0.22.04.1))
Type "help" for help.
postgres=#
Подробнее ознакомиться с возможностями оболочки можно с использованием команд \h и \?.
Просмотр доступных баз данных
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 |
template0 | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
Изначально в кластере создаётся 3 базы данных:
template0- Первоначальный шаблон для новых баз данных. Не должен изменяться ни при каких обстоятельствах!!!template1- Шаблон базы данных. Применяется по умолчанию при создании новой базы данных, если не указан другой шаблон. Может быть изменён.postgres- Служебная база данных по умолчанию для пользователя postgres. При подключении пользователя к серверу, например, при помощи psql, происходит выбор базы данных с именем текущего пользователя, если не указан другой. При отсутствии базы данных будет происходить ошибка подключения. Эта база позволяет пользователя postgres успешно подключиться даже если база данных не была указана.
В примере все базы данных имеют Collate и Ctype равным ru_RU.UTF-8. На ОС Ubuntu это значение при инициализации берётся из настроек системы.
Управление ролями (пользователя)
Просмотреть список всех доступных пользователей можно выполнив команду \du:
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
Для создания новых пользователей используется команда CREATE USER или CREATE ROLE. Все доступные опции команд описаны в документации. CREATE USER является синонимом для CREATE ROLE, но имеет по умолчанию включенной опцию LOGIN, т.е. роль может быть использована непосредственно при подключении к серверу. Фактически для сервера нет сущности user, а есть только role.
Аутентификация пользователей
Для аутентификации пользователей при соединении с базой данных существует множество методов аутентификации. Наиболее часто используемые:
- trust - полное доверие для любых подключений;
- peer - используется имя текущего пользователя в системе, используется только для локального подключения;
- парольная аутентификация, такие методы как: password, md5 и scram-sha-256.
По умолчанию, многие дистрибутивы используют методы trust или peer.
Метод password подразумевает передачу пароля перед аутентификацией в открытом виде. Самым надёжным методом парольной аутентификации является scram-sha-256, который позволяет защититься от перехвата пароля через недостоверное соединение и не хранить его на сервере в виде криптографического хеша. Однако при использовании этого метода могут возникнуть проблемы со старыми клиентами. В таком случае можно использовать метод md5. Он также защищает от перехвата пароля, но при этом хеши паролей могут быть похищены с сервера.
Указать то, какие методы аутентификации должны быть использованы можно в файле [pg_hba.conf], который обычно расположен в корневом каталоге базы данных (например, /var/lib/pgsql/data), но в некоторых дистрибутивах может располагаться в /etc или иметь в нём соответствущие символические ссылки на файлы конфигурации.
Пример настройки с использованием метода аутентификации для всех клиентов scram-sha-256 и возможность подключения под чётной записью postgres локально без пароля.
local all postgres peer
local all all scram-sha-256
host all all 127.0.0.1/32 scram-sha-256
host all all ::1/128 scram-sha-256
Если вы ранее уже использовали md5, то после смены типа аутентификации пользователи не смогут подключиться со своими паролями. Чтобы дать пользователям возможность самостоятельно изменить пароль на совместимый с scram-sha-256 необходимо перед изменением типа аутентификации добавить параметр password_encryption = 'scram-sha-256' в файл postgresql.conf. В этом случае при установке нового пароля он будет сохранён в нужном формате, но при этом пользователь будет иметь возможность подключаться с использованием метода md5 и со старым паролем. После того, как все пользователи сменят свои пароли, можно изменить тип аутентификации в файле pg_hba.conf на scram-sha-256.
Создание нового пользователя
Создание пользователя с именем test и возможностью создания баз данных:
postgres=# create user test createdb;
CREATE ROLE
Убедимся, что пользователь создан:
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test | Create DB | {}
Установить пароль пользователя может как сам пользователь, так и пользователь с соответствующими привилегиями с указанием имени необходимого пользователя, например, задаём пароль пользователю test от имени пользователя postgres:
$ sudo -u postgres psql
psql (14.3 (Ubuntu 14.3-0ubuntu0.22.04.1))
Type "help" for help.
postgres=# \password test
Enter new password for user "test":
Enter it again:
postgres=#
Изменение пользователя
Изменить текущего пользователя можно с помощью команды ALTER USER, которая является синонимом для ALTER ROLE в актуальных версиях PostgreSQL. Например, отберём у пользователя test возможность создания баз данных:
postgres=# alter user test nocreatedb;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test | | {}
Удаление пользователя
Удалить ненужного пользователя можно с помощью команды DROP USER, которая также является синонимом DROP ROLE. Удалить суперпользователя может только суперпользователь. Удалить обычного пользователя может любой пользователь с правами CREATEROLE:
postgres=# drop user test;
DROP ROLE
Если у пользователя имеются во владении какие-либо объекты, то удалить его будет нельзя до тех пор, пока эти объекты не будут удалены или переданы другому пользователю.
Создание базы данных
Создать новую базу данных:
postgres=# create database test;
CREATE DATABASE
Однако в этом случае владельцем базы данных будет пользователь, который выполнил команду, а значит пользователь test, для которого предназначалась эта база, не будет иметь доступа. Исправить это можно с помощью команды ALTER DATABASE;
postgres=# alter database test owner to test;
ALTER DATABASE
Или просто указать пользователя при создании базы данных:
postgres=# create database test owner to test;
CREATE DATABASE
Для выполнения команды CREATE DATABASE также имеется скрипт с именем createdb, который можно использовать из командной строки.
Переименование базы данных
Для переименования также используется команда ALTER DATABASE:
postgres=# alter database test rename to test_old;
ALTER DATABASE
Удаление базы данных
Удаление производится с помощью команды DROP DATABASE:
postgres=# drop database test;
DROP DATABASE
Бекап базы данных и её восстановление
Для создания дампов и их восстановления отдельных БД в PostgreSQL имеются утилиты pg_dump и pg_restore. Кроме этого, сделать дамп всего кластера БД можно с помощью утилиты pg_dumpall, которая использует при своей работе pg_dump, и восстановить с помощью утилиты psql.
Наиболее полезные ключи при создании дампа базы данных
pg_dump -h hostname -U username -F format -f dumpname dbname
hostname- адрес сервер БД;username- имя пользователя БД;format- формат дампа:p(по умолчанию) - текстовый SQL-скрипт;c- архивный формат для утилиты pg_restore;d- выгрузить данные в формате каталога, также применяется сжатие;t- формат tar - архив tar, который распаковывается в каталог, аналогичный предыдущему формату;
dumpname- имя файла данных или каталога;dbname- имя базы данных.
Восстановление базы из дампа:
pg_restore -h hostname -U username -F format -d dbname dumpname
Параметры аналогичные, за исключением format, который при восстановлении может быть только c, d или t.
Для восстановления дампа из SQL-скрипта можно использовать команду psql.
Пример создания дампа и восстановления из него
pg_dump -Fc -f db_name.dump db_name
pg_restore -Fc -d db_name db_name.dump
- Перевод официальной документации по pg_dump
- Перевод официальной документации по pg_restore
- Перевод официальной документации по CREATE USER
- Методы аутентификации клиентского приложения
Обратите внимание, что заметки могут обновляться со временем. Это может быть как исправление найденных ошибок, так и доработка содержания с целью более полного раскрытия темы. Информация об изменениях доступна в репозитории на github. Там же вы можете оставить в Issue ваши замечания по данной заметке.
Если данная заметка оказалась вам полезной, можете поблагодарить автора финансово.