Сайт FSA
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. Методы аутентификации клиентского приложения

Обратите внимание, что заметки могут обновляться со временем. Это может быть как исправление найденных ошибок, так и доработка содержания с целью более полного раскрытия темы. Информация об изменениях доступна в репозитории на github. Там же вы можете оставить в Issue ваши замечания по данной заметке.


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