NetBash shop
Login

Схемы в 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 - используется по умолчанию для пользовательских объектов
    pg_catalog используется для таблиц системного каталога
    information_schema дает альтернативное представление системного каталога
    pg_toast используется для объектов, относящихся к TOAST
    pg_temp объединяет временные таблицы
    Схемы расположены внутри базы данных и все объекты базы принадлежат какой либо схеме

    Источник: Егор Рогов "PostgreSQL изнутри"

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

    Система определяет какая именно таблица подразумевается, используя путь поиска, который представляет собой список просматриваемых схем
    Подразумеваемой таблицей считается первая подходящая таблица, найденная в схемах пути, если таблица не найдена, возникает ошибка, даже если такая таблица есть в других схемах базы данных.
    Добавить в схему 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

    Источники
    Последнее изменение: 05.02.2025 18:28
    Предложить изменение
    Связанные темы

    Типы данных в Postgresql

    Буферный кэш и журнал в Postgresql

    Шаблоны template в Postgresql

    Системный каталог в Postgresql

    Определение данных в Postgresql

    Табличные пространства в Postgresql

    Основные команды psql и sql Postgresql

    Агрегирование и группировка в Postgresql

    Основные понятия реляционной модели

    Возможности шифрования

    Создание и управление кластером postgresql

    Системные каталоги в Postgresql

    Этапы запроса и получения результата в postgresql

    Низкий уровень Postgresql

    Многоверсионность в Postgresql

    Блокировки в Postgresql

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

    Авторизоваться
    Я буду рекламой
    Я тоже буду рекламой
    И я
    ВВЕРХ