1. Как устроен PostgreSQL: кластер, базы, схемы, роли

Новичку PostgreSQL кажется просто местом, куда кладут таблицы. Но у него есть несколько уровней вложенности, и без них непонятны сообщения вроде «database does not exist», «permission denied for schema» или «relation does not exist», хотя таблица точно есть. Эта глава показывает, как всё устроено, чтобы дальше вы не путались в адресах объектов.

Матрёшка: сервер, база, схема, таблица

Объекты в PostgreSQL вложены друг в друга:

кластер (один запущенный сервер)
└── база данных (shop, analytics, postgres)
    └── схема (public, billing, reports)
        └── таблица, индекс, представление, функция

Кластер — это один запущенный сервер PostgreSQL с общим каталогом данных на диске и одним портом (по умолчанию 5432). Слово «кластер» здесь не про несколько машин, а про набор баз под одним сервером.

База данных — изолированный набор объектов. Подключение всегда идёт к одной конкретной базе. Запрос из базы shop не может прочитать таблицу из базы analytics: для этого нужны расширения вроде postgres_fdw или отдельное подключение.

Схема — пространство имён внутри базы, примерно как папка. Через схемы разделяют модули приложения (billing, reports) и избегают конфликтов имён. В отличие от баз, запрос свободно обращается к таблицам из разных схем одной базы.

Таблица живёт в схеме. Полный адрес таблицы: схема.таблица.

Схемы на практике

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

CREATE SCHEMA shop;
CREATE SCHEMA archive;

CREATE TABLE shop.items (id int, title text);
CREATE TABLE archive.items (id int, title text);

INSERT INTO shop.items VALUES (1, 'Клавиатура'), (2, 'Мышь');
INSERT INTO archive.items VALUES (99, 'Снятый с продажи монитор');

Две таблицы с именем items не конфликтуют, потому что лежат в разных схемах. А если написать просто items, откуда PostgreSQL возьмёт таблицу? Это решает параметр search_path — список схем, которые сервер просматривает по порядку:

SET search_path = shop, archive;
SELECT * FROM items;

 id |   title
----+------------
  1 | Клавиатура
  2 | Мышь

Найдена первая таблица items: из схемы shop. Если поменять порядок, ответ будет другим:

SET search_path = archive, shop;
SELECT * FROM items;

 id |          title
----+--------------------------
 99 | Снятый с продажи монитор

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

По умолчанию search_path равен "$user", public: сначала схема с именем текущей роли (если такая есть), потом public. Схема public создаётся в каждой новой базе, и в неё попадают таблицы, если схема не указана.

Что лежит в системных схемах

В каждой базе есть служебные схемы. pg_catalog хранит системные каталоги: описания всех таблиц, колонок, индексов, ролей. information_schema показывает то же самое в виде, заданном стандартом SQL. Спросим у системы, какие таблицы есть в текущей схеме. Создадим пару таблиц и посмотрим на них через каталог:

 tablename
-----------
 items
 tags

Функция current_schema() возвращает первую схему из search_path, то есть ту, куда сейчас попадают новые таблицы.

То же самое можно получить через information_schema.tables. Системные каталоги пригодятся, когда нужно написать запрос «покажи все таблицы без первичного ключа» или «найди колонки с таким-то именем»: для этого не нужно ничего устанавливать, всё уже есть в базе.

Роли: пользователи и группы в одном

В PostgreSQL нет отдельных «пользователей» и «групп». Есть роли. Роль с правом LOGIN может подключаться, то есть работает как пользователь. Роль без LOGIN служит группой: ей выдают права, а потом включают в неё других.

CREATE ROLE analyst NOLOGIN;                   -- группа
GRANT SELECT ON ALL TABLES IN SCHEMA shop TO analyst;

CREATE ROLE anna LOGIN PASSWORD '...';         -- пользователь
GRANT analyst TO anna;                          -- Анна получает права группы

Ещё два свойства ролей стоит знать сразу. Роль-владелец объекта (OWNER) может делать с ним что угодно, включая удаление. Суперпользователь (SUPERUSER) обходит все проверки прав. Приложение не должно подключаться суперпользователем: ошибка в запросе или SQL-инъекция тогда получит полный доступ к серверу.

Подробнее о правах, pg_hba.conf и политиках строк — в платной главе про роли.

psql: консоль, которая всегда под рукой

psql — штатный клиент командной строки. Подключение:

psql -h localhost -p 5432 -U anna -d shop

или одной строкой:

psql "postgresql://anna@localhost:5432/shop"

Кроме SQL, psql понимает метакоманды с обратной косой чертой. Они не отправляются на сервер, а обрабатываются самим клиентом. Эти запомнить стоит:

Метакоманды заменяют запросы к системным каталогам. Например, \dt — это обёртка над запросом к pg_class. Чтобы увидеть, какой именно SQL за ней стоит, запустите psql с ключом -E.

Частые ошибки

• Класть всё в public. Для маленького проекта нормально, но когда таблиц сотни, схемы по модулям спасают от путаницы и позволяют раздавать права по частям.
• Ждать, что запрос увидит таблицу другой базы. Базы изолированы. Нужные данные лежат в одной базе, разные модули разводят схемами.
• Подключать приложение суперпользователем. Заведите отдельную роль с минимумом прав.
• Забывать про search_path. Если таблица «не найдена» (relation does not exist), хотя вы её видите в \dt, скорее всего, она в схеме, которой нет в search_path. Укажите схему явно.
• Путать «кластер» с кластерами из Kubernetes или с несколькими машинами. В PostgreSQL это один экземпляр сервера со всеми его базами.

Работа с базой и схемами разобрана на заданиях в курсе «PostgreSQL для разработчика».