Инструкция по развертываю СУБД PostgreSQL в Debian и его форках.
Обновляем список пакетов:
sudo apt update
Ставим необходимые пакеты:
sudo apt -y install gnupg2 wget
PostgreSQL существует в репозиториях Ubuntu по умолчанию, однако доступные версии не являются актуальными. Доступную версию можно проверить с помощью команды:
sudo apt-cache search postgresql | grep postgresql
В этом руководстве нас интересует версия PostgreSQL 14, которая не входит в репозитории по умолчанию, поэтому добавим репозиторий, предоставляющий PostgreSQL 14:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
Импортируем ключ подписи GPG для репозитория:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
Обновляем список пакетов:
sudo apt -y update
Теперь, когда мы добавили репозиторий в нашу систему, приступаем к установке PostgreSQL 14:
sudo apt -y install postgresql-14
Проверяем работу службы:
systemctl status postgresql
Проверяем установленную версию PostgreSQL:
sudo -u postgres psql -c "SELECT version();"
Пробуем подключиться к PostgreSQL:
sudo -u postgres psql
Настраиваем PostgreSQL 14 для удаленного подключения
Чтобы к экземпляру можно было получить удаленный доступ, нам необходимо внести изменения в файл /etc/postgresql/14/main/pg_hba.conf , как показано ниже.
Разрешить вход по паролю:
sudo sed -i '/^host/s/ident/md5/' /etc/postgresql/14/main/pg_hba.conf
Теперь измените метод идентификации с однорангового на доверительный, как показано ниже:
sudo sed -i '/^local/s/peer/trust/' /etc/postgresql/14/main/pg_hba.conf
Разрешаем доступ к экземпляру отовсюду:
sudo nano /etc/postgresql/14/main/pg_hba.conf
Находим в конце файла параметры подключения IPv4 и IPv6 и приводим их к следующему виду:
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
host all all 0.0.0.0/0 md5
# IPv6 local connections:
host all all ::1/128 scram-sha-256
host all all 0.0.0.0/0 md5
Теперь убедитесь, что служба прослушивает запросы со всех IP-адресов, отредактировав файл конфигурации /etc/postgresql/14/main/postgresql.conf , как показано ниже:
sudo nano /etc/postgresql/14/main/postgresql.conf
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
.......
listen_addresses='*'
Теперь перезапустите и включите PostgreSQL, чтобы изменения вступили в силу:
sudo systemctl restart postgresql
sudo systemctl enable postgresql
Управление пользователями в базе данных PostgreSQL 14
Нам нужно создать суперпользователя, который сможет управлять другими пользователями и заполнять базы данных. Подключитесь к экземпляру Posgres:
sudo -u postgres psql
Создайте суперпользователя с именем admin. Вы можете изменить Passw0rd на предпочитаемый вами пароль:
CREATE ROLE admin WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'Passw0rd';
Убедитесь, что пользователь создан с необходимыми привилегиями.
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
admin | Superuser, Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=#
В Postgres вы также можете создавать пользователей БД. Здесь мы создали пользователя с именем user1 и назначили необходимые привилегии для доступа к testdb:
create database testdb;
create user user1 with encrypted password 'Passw0rd';
grant all privileges on database testdb to user1;
Подключитесь к экземпляру PostgreSQL 14 с удаленного хоста.
Убедитесь, что служба прослушивает порт 5432.
$ ss -tunelp | grep 5432
tcp LISTEN 0 244 0.0.0.0:5432 0.0.0.0:* uid:123 ino:292971 sk:a <->
tcp LISTEN 0 244 [::]:5432 [::]:* uid:123 ino:292972 sk:d v6only:1 <->
Разрешите порт через брандмауэр, если у вас включен UFW:
sudo ufw allow 5432/tcp
Теперь используйте приведенный ниже синтаксис для подключения к вашему экземпляру с удаленного компьютера:
psql 'postgres://<username>:<password>@<host>:<port>/<db>?sslmode=disable'