shemy_v_postgresql

Схемы в Postgresql

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

Схемы

Схемы - пространства имен для объектов внутри базы данных, разделяют объекты на логические группы, тем самым предотвращая конфликты имен при работе между нескольких пользователей
Любой объект в базе данных принадлежит какой либо схеме
Пользователи кластера не обязательно будут иметь доступ ко всем базам данных в кластере, так как доступ пользователя можно ограничить только к некоторым базам данных
Схемы могут содержать таблицы, типы данных, функции, операторы и другие именованные объекты
Таким образом разные схемы могут содержать одно и то же имя объекта
Одну схему могут использовать разные пользователи
Схемы не могут быть вложенными подобно каталогам в операционной системе
Схемы с именами, начинающимися с pg_, являются системными
Для создания схемы используется команда CREATE SCHEMA myschema;
Синтаксис для обращения к объктам внутри схемы
1. схема.таблица 2. база_данных.схема.таблица


dbname=# --Создание схемы
dbname=# CREATE SCHEMA myschema ;
CREATE SCHEMA
dbname=#
dbname=# --Создание таблицы в схеме
dbname=# CREATE TABLE myschma.mytable (...
dbname=#
dbname=#
dbname=# --Удалить пустую схему
dbname=# DROP SCHEMA myschema;
DROP SCHEMA
dbname=#
dbname=#
dbname=# -- Удалить схему со всеми объектами внутри
dbname=# DROP SCHEMA myschema CASCADE;
DROP SCHEMA
dbname=#
dbname=#
dbname=# -- Создать схему, владельцем которой будет другой пользователь
dbname=# -- Можно опустить имя схемы, в этом случае именем схемы станет имя пользователя
dbname=# CREATE SCHEMA myschema AUTHORIZATION megauser;
dbname=#

По умолчанию таблицы создаются в схеме public, если явно не указать схему

Путь поиска схемы

Система определяет какая именно таблица подразумевается, используя путь поиска, который представляет собой список просматриваемых схем
Подразумеваемой таблицей считается первая подходящая таблица, найденная в схемах пути, если таблица не найдена, возникает ошибка, даже если такая таблица есть в других схемах базы данных.
Добавить в схему search_path - по сути значит доверять всем пользователям, имеющим право CREATE в этой схеме
Когда вы выполняете обычный запрос, злонамеренный пользователь может создать объекты в схеме, включённой в ваш путь поиска, и таким образом перехватывать управление и выполнять произвольные функции SQL как если бы их выполняли вы.
Первая схема в пути поиска называется текущей схемой. Эта схема будет использоваться не только при поиске, но и при создании объектов — она будет включать таблицы, созданные командой CREATE TABLE без указания схемы
Чтобы узнать текущий тип поиска, необходимо выполнить команду:


dbname=#-- Узнать текущий тип поиска
dbname=# SHOW search_path;
   search_path
-----------------
 "$user", public
(1 row)

dbname=#

Первый элемент ссылается на схему с именем текущего пользователя. Если такой схемы не существует, ссылка на неё игнорируется. Второй элемент ссылается на схему public
Неполные имена могут относиться только к объектам в схеме public
Чтобы добавить в путь новую схему, необходимо выполнить:


dbname=#
dbname=# -- В данном случае не указывается компонент $user, так как здесь в нём нет необходимости
dbname=#
dbname=# -- Теперь можно не указываться полное имя объекта для выполнения запроса
dbname=# DROP TABLE mytable;

Можно сделать так, чтобы к объектам из схемы public можно было обращаться только через полное имя, для этого необходимо выполнить команду:
SET search_path TO myschema;
Единственное, что отличает схему public от других, это то, что она существует по умолчанию, хотя её так же можно удалить
Дополнительная информация по манипулированию с путями поиска схемы по ссылке:
https://postgrespro.ru/docs/postgresql/16/functions-info.html
Как и для имён таблиц, путь поиска аналогично работает для имён типов данных, имён функций и имён операторов

Права в схемах

По умолчанию пользователь не может обращаться к объектам в чужих схемах, но данное поведение можно изменить. Для этого владелец схемы должен дать пользователю право USAGE
Чтобы пользователь мог создавать объекты в чужих схемах, ему необходимо предоставить право CREATE
В базах данных, обновлённых с PostgreSQL 14 или более ранней версии, все имеют это право в схеме public

Схема системного каталога

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

Так обеспечивается доступность встроенных имён при любых условиях. Однако вы можете явным образом поместить pg_catalog в конец пути поиска, если вам нужно, чтобы пользовательские имена переопределяли встроенные.
С путём поиска по умолчанию неполная ссылка будет воспринята как обращение к системной таблице.) Системные таблицы будут и дальше содержать в имени приставку pg_, так что они не будут конфликтовать с неполными именами пользовательских таблиц, если пользователи со своей стороны не будут использовать приставку pg_.

Шаблоны использования

Чтобы обезопасить себя от недоверенных пользователей предлагается шаблон безопасного использования схем, но если этот шаблон не применяется в базе данных, тогда следует принять дополнительные меры в начале каждого сеанса. Для этого необходимо начинать каждый сеанс с присваивания пустой переменной search_path или удалять из переменной схемы доступные для записи

С конфигурацией по умолчанию легко реализуются следующие шаблоны использования:
1. Ограничить обычных пользователей личными схемами. Необходимо убедиться, что не у одной схемы нет права CREATE
2. Для каждго пользователя, который будет создавать объекты, необходимо создать схему с его именем, например:


CREATE SCHEMA alice AUTHORIZATION alice;

Подробнее о шаблонах использования по ссылке:
Источник: https://postgrespro.ru/docs/postgresql/16/ddl-schemas


--Список схем
\dn

--Полный список схем
\dnS

--Список схем для временных объектов текущего сеанса
SELECT pg_my_temp_schema()::regnamespace;
 -

--Создание схемы
postgres=# CREATE SCHEMA testschema;
CREATE SCHEMA

--Создадим таблицу
CREATE TABLE table1(s text);

--Список таблиц
postgres=# \dt
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | t      | table | postgres
 public | table1 | table | postgres
(2 rows)

--Перместить таблицу в другую схему
postgres=# ALTER TABLE table1 SET SCHEMA testschema;
ALTER TABLE
postgres=#

--Обращение к таблице по полному имени
postgres=# SELECT * FROM testschema.table1;


--Список представлений
\dv information_schema.*

Если имя объекта квалифицировано именем схемы, то все просто — используется явно указанная схема (как на рисунке на предыдущем слайде). Если имя использовано без квалификатора, PostgreSQL пытается найти имя в одной из схем, перечисленных в пути поиска, который определяется конфигурационным параметром search_path
Реальный путь поиска, включая неявные схемы, возвращает вызов функции current_schemas(true)


postgres=# SELECT current_schemas(true);
   current_schemas   
---------------------
 {pg_catalog,public}
(1 row)

search_path - переменная определяет порядок, в котором будут просматриваться схемы при поиске объекта
Значением search_path должен быть список имён схем через запятую.
Если список содержит специальный элемент $user, вместо него подставляется схема с именем, возвращаемым функцией CURRENT_USER
Схема системных каталогов, pg_catalog, просматривается всегда, независимо от того, указана она в пути или нет
Аналогично всегда просматривается схема временных таблиц текущего сеанса, pg_temp_nnn, если она существует. Временная схема просматривается только при поиске отношений (таблиц, представлений, последовательностей и т. д.) и типов данных, но никогда при поиске функций и операторов.

Специальные схемы

public - схема по умолчанию
Схема пользователя - по умолчанию входит в путь поиска в виде переменной, но не существует. Поскольку схема пользователя не определена, она игнорируется, и таблица создается в схеме public.
pg_catalog - схема для объекта системного каталога, если pg_catalog нет в пути, она неявно подразумевается первой
Чтобы не думать над тем, какие схемы существуют, каких нет, и какие не указаны явно, можно воспользоваться функцией current_schemas. Если функция вызывается с аргументом true, в результат включаются системные схемы, например, pg_catalog


postgres=#
postgres=# SELECT * FROM table1;
ERROR:  relation "table1" does not exist
LINE 1: SELECT * FROM table1;
                      ^
postgres=#
postgres=# --Добавим схему в путь поиска
postgres=# SET search_path = public, testschema;
SET
postgres=#
postgres=# --Проверим еще раз
postgres=# SELECT * FROM table1;
 s
---
(0 rows)

postgres=#

При повторном подключении значение пропадет. Устанавливать такое значение на уровне всего кластера тоже не правильно — возможно, этот путь нужен не всегда и не всем
Однако параметр можно установить и на уровне отдельной базы данных:


-- Установить путь поиска для отдельной базы данных
postgres=# ALTER DATABASE newdb SET search_path = public, testschema;
ALTER DATABASE
postgres=#

--Теперь он будет устанавливаться для всех новых подключений к базе
newdb=# \c newdb
You are now connected to database "newdb" as user "postgres".
newdb=#
newdb=# SHOW search_path;
    search_path
--------------------
 public, testschema
(1 row)

newdb=#
postgres=#
postgres=# \c newdb
You are now connected to database "newdb" as user "postgres".
newdb=#
newdb=# SHOW search_path;
    search_path
--------------------
 public, testschema
(1 row)

newdb=#
newdb=# SELECT current_schemas(true);
   current_schemas
---------------------
 {pg_catalog,public}
(1 row)

newdb=#

Временные таблицы и схема pg_temp

Временные таблицы существуют на время сеанса или транзакции, не журналируются, не попадают в общий буферный кеш
Схема pg_temp автоматически создается для временных таблиц
pg_temp — ссылка на конкретную временную схему данного сеансаесли pg_temp нет в пути, она неявно подразумевается самой первойпо окончании сеанса все объекты временной схемы удаляются,а сама схема остается и повторно используется для других сеансов
Для сеанса создается временная схема с именем pg_temp_N (pg_temp_1, pg_temp_2 и т. п.). Обращаться к ней нужно по имени pg_temp (без номера) — для каждого сеанса это имя ссылается на конкретную временную схему.


newdb=#
newdb=# --Создать временную таблицу
newdb=# CREATE TEMP TABLE temptable(s text);
CREATE TABLE
newdb=#
newdb=# \dt
            List of relations
  Schema   |   Name    | Type  |  Owner
-----------+-----------+-------+----------
 pg_temp_4 | temptable | table | postgres
(1 row)

newdb=#
newdb=# --В путь поиска: в него теперь подставлена временная схема
newdb=# SELECT current_schemas(true);
        current_schemas
-------------------------------
 {pg_temp_4,pg_catalog,public}
(1 row)

newdb=#
newdb=# INSERT INTO temptable VALUES ('Временная таблица');
INSERT 0 1
newdb=#
newdb=# SELECT * FROM temtable;
ERROR:  relation "temtable" does not exist
LINE 1: SELECT * FROM temtable;
                      ^
newdb=# SELECT * FROM temptable;
         s
-------------------
 Временная таблица
(1 row)

newdb=# SHOW search_path;
    search_path
--------------------
 public, testschema
(1 row)

newdb=#

newdb=# CREATE VIEW v AS SELECT * FROM pg_temp.temptable;
NOTICE:  view "v" will be a temporary view
CREATE VIEW
newdb=#

Временные таблицы и данные в них могут иметь различные сроки жизни (в зависимости от указания ON COMMIT DELETE, PRESERVE или DROP). В любом случае при переподключении все объекты во временной схеме уничтожаются
Схему нельзя удалить, если в ней находятся какие-либо объекты
Но можно удалить схему вместе со всеми ее объектами


--Удаление схемы и ее объектов
DROP SCHEMA app CASCADE;

temp_buffers (integer) #
Задаёт максимальный объём памяти, выделяемой для временных буферов в каждом сеансе. Эти существующие только в рамках сеанса буферы используются исключительно для работы с временными таблицами
https://postgrespro.ru/docs/postgresql/16/runtime-config-resource#GUC-TEMP-BUFFERS