1.00M

Category:

database

Знакомство с СУБД. PostgreSQL

О PostgreSQL
• PostgreSQL - это мощная система объектнореляционных баз данных с открытым
исходным кодом, которая использует и
расширяет язык SQL в сочетании со
многими функциями, которые безопасно
хранят и масштабируют самые сложные
рабочие нагрузки данных.

3.

Зачем использовать PostgreSQL
• Помимо того , что PostgreSQL
является бесплатным и открытым
исходным кодом, он обладает широкими
возможностями расширения. Например, вы
можете определять свои собственные типы
данных, создавать собственные функции,
даже писать код на разных языках
программирования без перекомпиляции
базы данных.

4.

Зачем использовать PostgreSQL
• Начиная с версии 12, выпущенной в
октябре 2019 года, PostgreSQL соответствует
как минимум 160 из 179 обязательных
функций для соответствия SQL: 2016
Core. На момент написания этой
презентации ни одна реляционная база
данных не соответствовала этому
стандарту.

5.

Функции, доступные в PostgreSQL
Типы данных
• Примитивы: целое, числовое, строковое,
логическое
• Структурированные: дата / время, массив,
диапазон, UUID
• Документ: JSON / JSONB, XML, Key-value
(Hstore)
• Геометрия: точка, линия, круг, многоугольник
• Настройки: составные, пользовательские типы

6.

Установка
• Имя суперпользователя - Postgres. Пароль
задается при установке.
• В окне установщик предлагает указать порт
TCP/IP для PostgreSQL. В большинстве
случаев можно оставить тот, который
предлагается по умолчанию.

7.

8.

Определение локали

9.

Установка
• После определения указанных параметров
начинается процесс копирования файлов в
определенный ранее каталог установки
PostgreSQL.
• Сообщение в процессе установки СУБД:
«Initialising database cluster» (инициализация
кластера баз данных), означает, что
копирование файлов закончено и создается
кластер баз данных, содержащий базу данных,
которая будет использоваться как шаблон для
вновь создаваемых баз

10.

Установка
• Через некоторое время эта надпись
сменится на «starting database server», что
означает запуск службы сервера. Если
запустить «Диспетчер задач» и перейти на
вкладку «Службы», можно убедиться, что
СУБД запущена.

11.

Настройки
Работа с PostgreSQL может быть произведена
через командную строку (терминал) с
использованием утилиты psql – инструмент
командной строки PostgreSQL.

12.

Основные команды
• \connect db_name – подключиться к базе с именем
db_name
• \du – список пользователей
• \dp (или \z) – список таблиц, представлений,
последовательностей, прав доступа к ним
• \di – индексы
• \dt – список таблиц
• \dt+ — список всех таблиц с описанием
• \dt *s* — список всех таблиц, содержащих s в имени
• \dv – представления
• \dS – системные таблицы
• \d+ – описание таблицы

13.

Основные команды
• \l – список баз данных
• \d “table_name” – описание таблицы
• \pset – команда настройки параметров
форматирования
• \echo – выводит сообщение
• \? – справочник psql
• \help – справочник SQL
• \q (или Ctrl+D) – выход с программы

14.

Настройки
Если вам нужна помощь, введите \help (или -h) в psqlтерминале. Появится список всех доступных
параметров справки. Вы можете ввести \help [имя
команды], если вам нужна помощь по конкретной
команде. Например, если ввести \help UPDATE в
консоли psql, вы увидите синтаксис команды update.

15.

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

16.

Настройки
Чтобы вывести список всех пользователей,
выполните команду \du. Атрибуты
пользователя postgres говорят нам, что он
суперпользователь.

17.

Создание базы данных
Для создания базы данных используется команда create database. В
приведенном ниже примере создается база данных с именем proglib_db.
Если вы забыли точку с запятой в конце запроса, знак «=» в приглашении
postgres заменяется на «-», как показано на рисунке ниже. Это зачастую
указывает на то, что необходимо завершить (дописать) запрос.
На картинке нам сообщают об ошибке из-за того, что в нашем случае
база уже создана. Вы поймете, что к чему, когда начнете писать более
длинные запросы.

18.

19.

20.

Создание нового пользователя
• SUPERUSER NOSUPERUSER
• определяют, будет ли эта роль «суперпользователем»,
который может переопределить все ограничения доступа
в базе данных. В отсутствие этих предложений по
умолчанию подразумевается NOSUPERUSER.
• CREATEDB NOCREATEDB
• Эти предложения определяют, сможет ли роль создавать
базы данных.

21.

Создание нового пользователя
• CREATEROLE NOCREATEROLE
• Эти предложения определяют, сможет ли роль создавать
новые роли (т. е. выполнять CREATE ROLEПо умолчанию
подразумевается NOCREATEROLE.
• CREATEUSER NOCREATEUSER
• Эти предложения являются устаревшими, но по-прежнему
принимаются, как альтернативное
написание SUPERUSER и NOSUPERUSER. Учтите, что
они не равнозначны паре CREATEROLE, как можно было бы
подумать!

22.

Создание нового пользователя
• INHERIT NOINHERIT
• Определяют, будет ли роль «наследовать» права
ролей, членом которых она является. Роль с
атрибутом
INHERIT
может
автоматически
использовать в базе данных любые права,
назначенные всем ролям, в которые она
включена, непосредственно или опосредованно
По умолчанию подразумевается INHERIT.

23.

Создание нового пользователя
• LOGIN NOLOGIN
• Определяют, разрешается ли новой роли вход на
сервер; то есть, может ли эта роль стать
начальным авторизованным именем при
подключении клиента. Можно считать, что роль с
атрибутом LOGIN соответствует пользователю.
Роли без этого атрибута бывают полезны для
управления доступом в базе данных, но это не
пользователи в обычном понимании.

24.

Создание нового пользователя
• REPLICATION NOREPLICATION
• Определяют, сможет ли новая роль запускать
потоковую репликацию или переключать систему
в режим резервного копирования и из него. Роль
с атрибутом REPLICATION получает очень широкие
полномочия, поэтому его следует назначать,
только если роль фактически применяется для
репликации. По умолчанию
подразумевается NOREPLICATION. Создавать роли
с атрибутом REPLICATION разрешено только
суперпользователям.

25.

Создание нового пользователя
• BYPASSRLS NOBYPASSRLS
• Эти предложения определяют, будут ли для роли
игнорироваться все политики защиты на уровне
строк (RLS). По умолчанию подразумевается
вариант NOBYPASSRLS. Создавать роли с
атрибутом NOBYPASSRLS разрешено только
суперпользователям.

26.

Создание нового пользователя
• CONNECTION LIMIT предел_подключений
• Если роли разрешён вход, этот параметр
определяет максимальное число одновременных
подключений, разрешённых для этой роли.
Значение -1 (по умолчанию) отключает
ограничение.

27.

Создание нового пользователя
• PASSWORD пароль
• Задаёт пароль роли. (Пароль полезен только для
ролей с атрибутом LOGIN, но задать его можно и
для ролей без такого атрибута.) Если проверка
подлинности по паролю не будет использоваться,
этот параметр можно опустить. При указании
пустого значения будет задан пароль NULL, что не
позволит данному пользователю пройти проверку
подлинности по паролю. При желании пароль
NULL можно установить явно, указав PASSWORD
NULL.

28.

Создание нового пользователя
• ENCRYPTED UNENCRYPTED
• Определяют, будет ли пароль храниться в
системных каталогах в зашифрованном виде.

29.

Создание нового пользователя
• VALID UNTIL 'дата_время‘
• Предложение VALID UNTIL устанавливает дату и
время, после которого пароль роли перестаёт
действовать. Если это предложение отсутствует,
срок действия пароля будет неограниченным.

30.

Создание нового пользователя
• IN ROLE имя_роли
• В предложении IN ROLE перечисляются одна или
несколько существующих ролей, в которые будет
немедленно включена новая роль.

31.

Создание нового пользователя
• ROLE имя_роли
• В предложении ROLE перечисляются одна или
несколько существующих ролей, которые
автоматически становятся членами создаваемой
роли. (По сути таким образом новая роль
становится «группой».)

32.

Создание нового пользователя
• ADMIN имя_роли
• Предложение ADMIN подобно ROLE, но
перечисленные в нём роли включаются в новую
роль с атрибутом WITH ADMIN OPTION, что даёт
им право включать в эту роль другие роли.

33.

Создание нового пользователя
Пример 1: CREATE USER tom WITH SUPERUSER;
Создаст пользовательский том с привилегиями суперпользователя.
Пример 2:
Создаем суперпользователя сроком действия до 3 апреля 2025
11:50:38
CREATE USER mytest WITH
LOGIN
SUPERUSER
CREATEDB
CREATEROLE
INHERIT
NOREPLICATION
CONNECTION LIMIT -1
VALID UNTIL '2025-04-03T11:50:38+05:30'
PASSWORD '123456';