Установка и использование PostgreSQL в Ubuntu

PostgreSQL является одной из самых популярных и широко используемых открытых СУБД в мире. В этом руководстве покажем, как легко установить и использовать PostgreSQL в Ubuntu, предоставив пошаговые инструкции для эффективного управления данными.

PostgreSQL —  мощная система управления базами данных (СУБД), которая широко используется в различных приложениях и веб-сервисах. Она основана на реляционной модели данных, где данные организованы в виде таблиц с явными связями между ними.

Как установить PostgreSQL в Ubuntu

PostgreSQL устанавливается из официальных репозиториев Ubuntu. Для начала необходимо обновить список пакетов:

sudo apt update

Установка postgresql в ubuntu происходит при помощи команды:

sudo apt install postgresql postgresql-contrib -y

Команда установит саму программу и пакет расширений contrib. Этот пакет представляет собой коллекцию дополнительных модулей и расширений, которые не включены в основной поставке, но могут быть полезными для различных задач.

После завершения установки PostgreSQL запустится автоматически. Однако вы можете сами проверить статус службы:

sudo systemctl status postgresql.service

Если по каким-либо причинам сервис не запустился автоматически, можно сделать это вручную:

sudo systemctl start postgresql.service

Для добавления в автозагрузку необходимо ввести команду:

sudo systemctl enable postgresql.service

Установка PostgreSQL успешно завершена, и программа готова к дальнейшей настройке.

Как настроить PostgreSQL и начать работать с СУБД

Прежде чем приступать к настройке инструмента, требуется проверить возможность подключения к нему. Во время установки PostgreSQL автоматически создает аккаунт с именем postgres. Авторизуемся при помощи него и войдем в аккаунт:

sudo -i -u postgres 

Открываем консоль PostgreSQL:

psql

Обратите внимание, что данная команда выполняется без аргумента sudo. При успешной авторизации отобразится консоль:

Здесь же можно узнать статус подключения. Для этого воспользуемся командой:

\conninfo

В ответ отобразится вся информация о текущем подключении:

По умолчанию для пользователя postgres не назначен пароль, но мы рекомендуем его установить на данном этапе. Для этого требуется ввести команду:

\password

Для выхода из консоли введите:

\q

Мы проверили только локальное соединение. Для проверки удаленного подключения служит команда:

psql -h <адрес_хоста> -p <порт> -U <пользователь>

Где требуется указать:

1. Адрес хоста — это имя домена или IP-адрес вашего сервера;
2. Порт. По умолчанию используется 5432;
3. Имя пользователя. Для проверки можно использовать стандартную учетную запись — postgres.

При подключении может быть запрошен пароль, который мы установили ранее. Сразу после ввода пароля откроется консоль, свидетельствующая об успешном соединении:

Создание роли

Роли в PostgreSQL — это способ определения прав доступа пользователей к базе данных. Они указывают, какие операции можно выполнять с данными, например, чтение, запись и изменение.

В PostgreSQL существуют три основных типа ролей:

1. Роль по умолчанию. Обычно это postgres, которая имеет права суперпользователя и доступ ко всем объектам БД.
2. Роль, созданная пользователем. Персонализированная роль, созданная администратором базы данных для конкретных пользователей или групп.
3. Роль группы. Групповая роль, которая позволяет объединить нескольких пользователей в одну группу и назначить им общие полномочия.

Для создания роли используется команда:

CREATE ROLE <имя_роли> [WITH] [<атрибуты>] ...;

Атрибуты позволяют настроить различные параметры. Рассмотрим самые популярные:

1. LOGIN: Разрешить пользователю входить в систему базы данных.
2. NOSUPERUSER: Запретить пользователю становиться суперпользователем.
3. CREATEROLE: Разрешить пользователю создавать новые роли.
4. CREATEDB: Разрешить создавать новые базы данных.
5. PASSWORD ‘<пароль>’: Установить пароль для роли.

Создадим новую роль с именем userdb и паролем passwordbd. Для этого подключимся к PostgreSQL от имени пользователя postgres и введем следующую команду:

CREATE ROLE userdb WITH LOGIN PASSWORD 'passwordbd';

Существует также вариант создания роли без входа в оболочку и перехода между аккаунтами:

sudo -u postgres createuser --interactive

При такой команде программа запросит имя для новой роли и предложит установить права суперпользователя. Для примера создадим роль role1 с правами суперпользователя. Работа с БД  также требует наличия соответствующего пользователя в Ubuntu. Для создания пользователя требуется выйти из оболочки и ввести команду:

sudo adduser role1

Для просмотра ролей можно воспользоваться командой \du в командной строке psql. Она выведет список всех ролей, включая их полномочия. Обратите внимание на разницу в атрибутах разных ролей: 

Если вам требуется больше информации о возможных параметрах при создании ролей, вы можете ознакомиться с ними в мануале:

man createuser

Создание базы данных

По умолчанию инструмент подключается к БД, соответствующей имени роли. Иными словами, при авторизации от имени role1 будет происходить подключение к базе данных role1.

Для создания БД переключаемся на аккаунт, имеющий соответствующие права:

sudo -i -u role1 

И создаем базу данных:

createdb role1

Если вы хотите произвести этот процесс без переключения между аккаунтами, воспользуйтесь командой:

sudo -u postgres createdb role1

Где role1 — имя вашей роли, пользователя, а также базы данных.

Посмотреть список баз данных можно из консоли psql, выполнив команду \l .

Наша база данных успешно создана. Осталось лишь настроить привилегии для соответствующего пользователя.

Настройка привилегий

Изначально пользователи PostgreSQL имеют только возможность подключения к серверу базы данных. Для предоставления им прав на работу с данными необходимо назначить соответствующие привилегии. Условно их можно разделить на 2 вида:

1. Объектные привилегии. Они управляют доступом к определенным объектам базы данных, таким как таблицы, индексы и процедуры.
2. Системные привилегии. Эти привилегии предоставляют разрешения на выполнение определенных административных задач, например, создание и удаление баз данных.

Предлагаем рассмотреть каждый вид более подробно. Начнем с системных привилегий, которые мы уже рассматривали в качестве атрибутов при создании ролей. В рамках статьи рассмотрим только основные команды.

1. GRANT. Позволяет предоставлять привилегии другим пользователям.
2. REVOKE. Позволяет отзывать привилегии у других пользователей.

Синтаксис команды GRANT:

GRANT <привилегии> ON <объект> TO <пользователь|роль>

Чтобы предоставить пользователю role1 привилегию SELECT для таблицы role1table, выполните следующий запрос:

GRANT SELECT ON role1table TO role1;

Синтаксис команды REVOKE:

REVOKE <привилегии> ON <объект> FROM <пользователь|роль>

Чтобы отозвать у пользователя role1 привилегию SELECT для таблицы role1table, выполните следующий запрос:

REVOKE SELECT ON role1table FROM role1;

В качестве объектных привилегий можно рассмотреть:

1. SELECT. Дает права на чтение данных из объекта.
2. INSERT. Позволяет пользователю вставлять новые данные в объект.
3. UPDATE. Дает возможность обновлять данные в объекте.
4. DELETE. Обеспечивает возможность удалять данные из объекта.
5. TRUNCATE. Разрешает пользователю очистить объект (удалить все данные).

Помимо выборочной установки, можно прибегнуть к разовой установке всех привилегий:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA role1table TO role1;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA role1table TO role1;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA role1table TO role1;

Эти команды предоставят роли role1 все привилегии на таблицы, последовательности и функции в схеме role1table. Пожалуйста, убедитесь, что вы действительно хотите предоставить такие широкие привилегии, так как это может быть небезопасно с точки зрения безопасности данных. Лучше предоставлять минимально необходимые привилегии для выполнения задачи. Например,

GRANT INSERT ON table_name TO user_name;

Позволяет вставлять новые записи в указанную таблицу.

GRANT DELETE ON table_name TO user_name;

Позволяет пользователю удалять записи из указанной таблицы.

С полным списком возможных привилегий можно ознакомиться при помощи команды, введенной в терминале psql:

\h GRANT

В ответ отобразится справка по всем доступным привилегиям:

Работа с данными в PostgreSQL

Работа с данными в PostgreSQL включает в себя множество операций от создания таблиц и добавления данных в них до выполнения запросов для извлечения, обновления или удаления информации. Мы разберем лишь основные команды.

Для создания таблицы используется команда CREATE TABLE:

CREATE TABLE role1table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    department VARCHAR(100)
);

В этом примере создается таблица "role1table" с четырьмя колонками: "id", "name", "age" и "department":

Для добавления данных в таблицу используется команда INSERT INTO:

INSERT INTO role1table (name, age, department) VALUES 
    ('John Doe', 30, 'HR'),
    ('Jane Smith', 35, 'Finance'),
    ('Michael Johnson', 40, 'Marketing');

Эта команда добавляет новые записи в таблицу "role1table" с указанными значениями:

Для извлечения данных из таблицы используется команда SELECT:

SELECT * FROM role1table;

Этот запрос извлекает все записи из таблицы " role1table ":

Для обновления данных в таблице используется команда UPDATE:

UPDATE role1table SET age = 31 WHERE name = 'John Doe';

Запрос обновляет возраст сотрудника с именем "John Doe" на 31:

Для удаления данных из таблицы используется команда DELETE:

DELETE FROM role1table WHERE age > 39;

Команда удаляет всех сотрудников, чей возраст больше 39 лет:

Для изменения структуры таблицы используются команды ALTER TABLE. Например, чтобы добавить новую колонку:

ALTER TABLE role1table ADD COLUMN salary DECIMAL(10, 2);

Эта команда добавляет новую колонку "salary" в таблицу "role1table":

Для сортировки данных используется оператор ORDER BY. Например,

SELECT * FROM role1table ORDER BY age DESC;

Этот запрос извлекает все записи из таблицы "role1table" и сортирует их по убыванию возраста:

Как подключить графический интерфейс к PostgreSQL

Рассмотрим процесс подключения к PostgreSQL с помощью pgAdmin4. Данный инструмент позволяет настроить подключение к PostgreSQL при помощи Desktop, а также web-версии. Иными словами, необязательно иметь графическую оболочку на сервере для настройки программы, достаточно иметь доступное SSH-подключение.

Первым делом установите открытый ключ для репозитория PgAdmin4:

curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add

Создайте файл конфигурации репозитория:

sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/focal/ pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

После этого можно переходить к установке. На текущем этапе необходимо выбрать тип инсталляции. Всего доступно 3 варианта:

1. Только для режима рабочего стола. Удобно, когда имеется графическая оболочка на сервере.

   sudo apt install pgadmin4-web

2. Только для режима веб-интерфейса. Мы будем использовать данный вариант. Он позволяет установить удаленное подключение.

   sudo apt install pgadmin4-web

3. Для обоих режимов — рабочего стола и веб-интерфейса.

   sudo apt install pgadmin4

Как только процесс установки завершен, можно переходить к настройке веб-окружения. Необходимо ввести команду:

sudo /usr/pgadmin4/bin/setup-web.sh

Откроется мастер настройки, который запросит адрес электронной почты, пароль и согласие с установкой веб-сервера Apache. В рамках статьи рассмотрим вариант с автоматической установкой Apache, но также существует альтернативный вариант ручной установки и интеграции с окружением Nginx. Указанные адрес электронной почты и пароль будут использоваться при дальнейшей авторизации в веб-окружении pgadmin.

 Программа сообщает, что сразу после настройки доступен веб-интерфейс по локальному адресу. При работе с удаленным доступом необходимо настроить брандмауэр для веб-сервера. Для этого введите команду:

sudo ufw allow 'Apache'

Активируйте брандмауэр:

sudo ufw enable

Убедиться в правильности настройки можно при помощи команды:

sudo ufw status

Мы рассмотрели вариант настройки стандартного Firewall для службы Apache. Независимо от того, используете ли вы Apache или Nginx, вам нужно открыть соответствующие порты: 80 и 443. Например, при использовании iptables команда будет выглядеть следующим образом:

sudo iptables -A INPUT -p tcp --dport 80 -j ACCEPT

sudo iptables -A INPUT -p tcp --dport 443 -j ACCEPT

Для доступа к веб-окружению открываем браузер и переходим по адресу http://вашдомен/pgadmin4.

Вводим адрес электронной почты и пароль, который указывали при установке программы, выбираем язык интерфейса и авторизуемся. Нажимаем «добавить новый сервер»:

 Указываем желаемое имя:

И настраиваем соединение:

PgAdmin автоматически загрузит существующие базы данных, роли и прочие сопутствующие данные:

Интерфейс программы интуитивно понятен. Для работы с каким-либо объектом (базой данных, ролью и т. д) необходимо кликнуть на него правой кнопкой мыши, в ответ отобразится весь доступный функционал, связанный с этим объектом.

Подводя итог

Установка и использование PostgreSQL на сервер с ОС Ubuntu доступны даже для неопытных пользователей. Отличная документация и активное сообщество делают процесс настройки и эксплуатации этой базы данных плавным и эффективным. Независимо от вашего уровня опыта, PostgreSQL в Ubuntu предоставляет мощный инструмент для управления данными и разработки приложений.