15.06.2022

Установка PostgreSQL

Эта заметка является доработанным и расширенным вариантом моей старой заметки Установка PostgreSQL на FreeBSD.

Инициализация базы данных

При инициализации базы данных следует обратить внимание на два параметра:

После создания кластера БД эти параметры невозможно будет изменить.

Настройка сервера

В большинстве операционных систем при установке сервера из репозиториев создаётся пользователь 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 базы данных:

  1. template0 - Первоначальный шаблон для новых баз данных. Не должен изменяться ни при каких обстоятельствах!!!
  2. template1 - Шаблон базы данных. Применяется по умолчанию при создании новой базы данных, если не указан другой шаблон. Может быть изменён.
  3. 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 подразумевает передачу пароля перед аутентификацией в открытом виде. Самым надёжным методом парольной аутентификации является 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

Восстановление базы из дампа:

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
  1. Перевод официальной документации по pg_dump
  2. Перевод официальной документации по pg_restore
  3. Перевод официальной документации по CREATE USER
  4. Методы аутентификации клиентского приложения

Если данная заметка оказалась вам полезной, можете поблагодарить автора финансово на сервисе Boosty или любой суммой через сервис QIWI.