Что такое схема в postgresql

SQL-Ex blog

Схемы в PostgreSQL. Изучаем PostgreSQL вместе с Grant Fritchey

Добавил Sergey Moiseenko on Среда, 9 августа. 2023

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

В тестовой базе данных, которую я использую для примеров к этой серии статей, была создана пара схем с таблицами в каждой из них. Вы можете посмотреть на эту базу данных в скрипте CreateDatabase.sql. Остальной код для этой статьи находится в папке 08_Schema.

Обслуживание схемы

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

Когда вы создаете объект типа таблицы, он автоматически приписывается схеме по умолчанию, если не задано обратное. По умолчанию все логины в базе данных имеют доступ к схеме public (PostgreSQL 15 изменил это поведение по умолчанию, поэтому теперь пользователи не имеют прав на создание объектов в схеме public). Помимо этого поведения по умолчанию, схема public является просто одной из схем в базе данных, и большинство функций и правил, которые будут далее обсуждаться, применимы к этой схеме.

Для начала создадим свои собственные схемы. Синтаксис очень простой:

CREATE SCHEMA mytestschema;

Этот оператор создает схему с именем mytestschema. Для создания таблицы в этой схеме вы просто используете имя таблицы из двух частей (имя_схемы.имя_таблицы) в операторе CREATE TABLE, например, так:

create table mytestschema.testtable 
(id int, 
somevalue varchar(50));

Так же и при любых запросах:

select id from mytestschema.testtable;

Вы можете думать о схеме как о владельце таблицы (владелец схемы технически является владельцем таблицы). Указание владельца повсюду в вашем коде является гарантией, что ничего неожиданного не произойдет. Поскольку, когда вы начинаете использовать схему, то можете давать объектам имена, которые существуют в других схемах. Давать уникальные имена — это хорошая практика, но иногда одно и то же имя в различных схемах является лучшим вариантом):

create schema secondschema: 
 
create table secondschema.testtable 
(insertdate date, 
someothervalue varchar(20));

Это совершенно допустимо. Если бы я написал то, что я считаю плохим кодом, например:

select * from testtable;

то, вероятно, получил бы следующую ошибку:

ERROR: relation «testtable» does not exist
(отношение «testtable» не существует)
LINE 2: select * from testtable;

Сначала кажется, что ошибка возникает потому, что PostgreSQL не может решить, из какой из двух таблиц брать данные. Скорее, потому, что логины имеют схему по умолчанию. Когда я выполняю запрос, подобный последнему, без указания схемы, где находится таблица, PostgreSQL смотрит путь по умолчанию. Если таблицы там нет, значит ее не существует. Это справедливо, хотя у меня есть две таблицы с этим именем. PostgreSQL не проверяет другие схемы «на всякий случай».

Ниже в этой статье я расскажу, как управлять схемами по умолчанию.

Если схема пустая, вы можете ее удалить:

drop table if exists secondschema.testtable; 
drop schema if exists secondschema;

Если я сначала не удалю таблицу, возникнет ошибка:

SQL Error [2BP01]: ERROR: cannot drop schema mytestschema because other objects depend on it
(нельзя удалить схему mytestschema, поскольку от нее зависят другие объекты)
Detail: table mytestschema.testtable depends on schema mytestschema
(таблица mytestschema.testtable зависит от схемы mytestschema)
Hint: Use DROP . CASCADE to drop the dependent objects too.
(используйте DROP . CASCADE для удаления также и зависимых объектов)

В сообщении об ошибке дается совет, как исправить ситуацию. Я мог бы переписать запрос следующим образом:

drop schema if exists mytestschema cascade;

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

В каждой базе данных создается схема по умолчанию с именем public. Однако это только по умолчанию и, как в большинстве настроек по умолчанию, ее можно изменить. Фактически вы даже можете удалить схему public, если захотите. Я начал этот раздел с объяснения, как создать свою собственную схему, которой вы непосредственно управляете, в противоположность принимаемой по умолчанию.

Управление путями поиска по умолчанию

Помимо помощи в организации объектов вашей базы данных, схема помогает контролировать доступ к этим объектам. Я еще не углублялся в тему безопасности в этой серии и, вероятно, до нее еще далеко. Однако я немного расскажу о том, как схема помогает управлять безопасностью базы данных. (Мой коллега Ryan Booz недавно опубликовал статью на эту тему ).

В этом разделе я хочу более детально обсудить некоторые способы управления схемой по умолчанию.

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

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

show search_path;

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

Каждый пользователь имеет собственную схему, как в SQL Server. Это и есть схема $user, которую вы видите выше. Однако, если вы не указали схему, по умолчанию будет принята первая в списке поиска, public в данном случае. Мы можем добавить схему в список поиска для текущего подключения:

SET search_path TO radio,public;

Это не только добавит схему radio в search_path, но и изменит порядок в пути поиска, поэтому схема radio ищется до схемы public. Если вы выполните отключение, а потом подключитесь вновь, вы должны будете переустановить путь с помощью команды SET.

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

ALTER ROLE scaryDba SET search_path = 'radio,public,$user';

Если вы хотите установить значение по умолчанию для сервера/кластера/базы данных, то можете изменить search_path в файле postgressql.cnf или использовать команду:

ALTER ROLE ALL SET search_path = '$user';

Это не будет иметь приоритета над индивидуальными установками путей, но сделает для каждого логина, который не имеет приоритетного пути поиска, необходимость указывать имя схемы при ссылках на любой объект. (Что, как уже отмечалось, является лучшей практикой.)

Владение и основные привилегии

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

CREATE SCHEMA secureschema AUTHORIZATION radio_admin;

Схема, которую я еще не создал ранее, secureschema, будет создана с владельцем, являющимся ролью логина radio_admin (тоже еще не определенной, поскольку я еще не разбирался с безопасностью). Это будет гарантировать, что только логин radio_admin и, конечно, любая учетная запись, определенная как суперпользователь, смогут работать в этой схеме.

Вы можете также управлять поведением по схеме. Например, поскольку я установил независимую схему в этой базе данных и намереваюсь использовать ее в этой манере, я могу запретить доступ для всех логинов на создание объектов в схеме public (это необходимо только в PostgreSQL 14 и ранее, в 15 разрешение на создание не предоставляется по умолчанию):

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Здесь используется слово “public” в двух разных значениях. В первом, ‘public’, мы ссылаемся на схему с этим именем. Во втором, ‘PUBLIC’, мы говорим о роли, которая содержит всех пользователей в базе данных. Этот механизм призван гарантировать, что ничего случайно не будет помещено в схему public. Я бы сказал, что полезно следовать этой практике, если вы собираетесь использовать другие схемы, особенно, если вы используете их для обеспечения безопасности вашей базы данных.

Вы можете предоставить различные привилегии между схемой и пользователями так, чтобы данный пользователь мог читать данные из таблиц в схеме, но не мог модифицировать данные в таблицах (доступ только на чтение). Тем самым вы можете объединить данные разных типов в одной базе данных, но изолировать их при необходимости друг от друга. Это главная причина использования схем в базе данных.

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

Заключение

Схемы представляют собой контейнеры, которые позволяют сегментировать объекты и обеспечивать безопасность на более низком уровне, чем уровень базы данных. Использование схем, отличных от public, имеет хорошие преимущества. В PostgreSQL имеется несколько методов установки схемы по умолчанию, если ваши пользователи не любят использовать двойные имена.

Если вы знакомы со схемами в SQL Server, базовая функциональность схемы примерно та же, что и там. Однако есть дополнительная функциональность, подобно возможности управлять изменением списка поиска, которой обладает PostgreSQL.

Ссылки по теме

1. Безопасность SQL Server — модель безопасности с использованием определяемых пользователем ролей
2. Типы данных в PostgreSQL: изучаем PostgreSQL с Grant Fritchey

Что такое схема в postgresql

CREATE SCHEMA — создать схему

Синтаксис

CREATE SCHEMA имя_схемы [ AUTHORIZATION указание_роли ] [ элемент_схемы [ . ] ] CREATE SCHEMA AUTHORIZATION указание_роли [ элемент_схемы [ . ] ] CREATE SCHEMA IF NOT EXISTS имя_схемы [ AUTHORIZATION указание_роли ] CREATE SCHEMA IF NOT EXISTS AUTHORIZATION указание_роли Здесь указание_роли: имя_пользователя | CURRENT_USER | SESSION_USER

Описание

CREATE SCHEMA создаёт новую схему в текущей базе данных. Имя схемы должно отличаться от имён других существующих схем в текущей базе данных.

Схема по сути представляет собой пространство имён: она содержит именованные объекты (таблицы, типы данных, функции и операторы), имена которых могут совпадать с именами других объектов, существующих в других схемах. Для обращения к объекту нужно либо « дополнить » его имя именем схемы в виде префикса, либо установить путь поиска, включающий требуемую схему. Команда CREATE , в которой указывается неполное имя объекта, создаёт объект в текущей схеме (схеме, стоящей первой в пути поиска; узнать её позволяет функция current_schema ).

Команда CREATE SCHEMA может дополнительно включать подкоманды, создающие объекты в новой схеме. Эти подкоманды по сути воспринимаются как отдельные команды, выполняемые после создания схемы, за исключением того, что с предложением AUTHORIZATION все создаваемые объекты будут принадлежать указанному в нём пользователю.

Параметры

Имя создаваемой схемы. Если оно опущено, именем схемы будет имя_пользователя . Это имя не может начинаться с pg_ , так как такие имена зарезервированы для системных схем. имя_пользователя

Имя пользователя (роли), назначаемого владельцем новой схемы. Если опущено, по умолчанию владельцем будет пользователь, выполняющий команды. Чтобы назначить владельцем создаваемой схемы другую роль, необходимо быть непосредственным или опосредованным членом этой роли, либо суперпользователем. элемент_схемы

Оператор SQL, определяющий объект, создаваемый в новой схеме. В настоящее время CREATE SCHEMA может содержать только подкоманды CREATE TABLE , CREATE VIEW , CREATE INDEX , CREATE SEQUENCE , CREATE TRIGGER и GRANT . Создать объекты других типов можно отдельными командами после создания схемы. IF NOT EXISTS

Не делать ничего (только выдать замечание), если схема с таким именем уже существует. Когда используется это указание, эта команда не может содержать подкоманды элемент_схемы .

Замечания

Чтобы создать схему, пользователь должен иметь право CREATE в текущей базе данных. (Разумеется, на суперпользователей это условие не распространяется.)

Примеры

CREATE SCHEMA myschema;

Создание схемы для пользователя joe ; схема так же получит имя joe :

CREATE SCHEMA AUTHORIZATION joe;

Создание схемы с именем test , владельцем которой будет пользователь joe , если только схема test ещё не существует. (Является ли владельцем существующей схемы пользователь joe , значения не имеет.)

CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION joe;

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

CREATE SCHEMA hollywood CREATE TABLE films (title text, release date, awards text[]) CREATE VIEW winners AS SELECT title, release FROM films WHERE awards IS NOT NULL;

Заметьте, что отдельные подкоманды не завершаются точкой с запятой.

Следующие команды приводят к тому же результату другим способом:

CREATE SCHEMA hollywood; CREATE TABLE hollywood.films (title text, release date, awards text[]); CREATE VIEW hollywood.winners AS SELECT title, release FROM hollywood.films WHERE awards IS NOT NULL;

Совместимость

Стандарт SQL также допускает в команде CREATE SCHEMA предложение DEFAULT CHARACTER SET и дополнительные типы подкоманд, которые PostgreSQL в настоящее время не принимает.

В стандарте SQL говорится, что подкоманды в CREATE SCHEMA могут следовать в любом порядке. Однако текущая реализация в PostgreSQL не воспринимает все возможные варианты ссылок вперёд в подкомандах, поэтому иногда возникает необходимость переупорядочить подкоманды, чтобы исключить такие ссылки.

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

Указание IF NOT EXISTS является расширением PostgreSQL .

См. также

Пред.	Наверх	След.
CREATE RULE	Начало	CREATE SEQUENCE

Создание схемы в конкретной базе данных PostgreSQL 9.0

Чтобы создать схему в PostgreSQL, необходимо изменить контекст работы с помощью команды \c your_db и затем выполнить команду создания схемы CREATE SCHEMA your_schema; .

Скопировать код

\c your_db -- Меняем контекст на нужную базу данных CREATE SCHEMA your_schema; -- Создаём новую схему

Начало работы с новой базой данных

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

Скопировать код

CREATE DATABASE foo; -- Создаем новую базу данных \c foo -- Переходим в созданную базу данных CREATE SCHEMA yourschema; -- Создаём схему

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

Скопировать код

CREATE SCHEMA IF NOT EXISTS yourschema; -- Если схема не существует, она будет создана

Если положить все команды в скрипт с использованием \connect , он будет выглядеть аккуратно и удобно для выполнения:

Скопировать код

CREATE DATABASE foo; \connect foo CREATE SCHEMA yourschema;

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

Повторение действий с использованием скриптов

Для автоматизации повторяющихся действий можно использовать .sql-скрипт. В нём не забудьте указать контекст базы данных с помощью команды \connect :

Скопировать код

CREATE DATABASE projectdb; -- Создание новой базы данных \connect projectdb -- Переключение на неё CREATE SCHEMA project_schema AUTHORIZATION db_user; -- Создание схемы с указанием выбранного пользователя в качестве владельца

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

Скопировать код

psql -f setup_script.sql -- Запуск скрипта

Визуализация

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

Скопировать код

Библиотека (��️): [Зал 1 (db_1), Зал 2 (db_2), Зал 3 (db_3)]

Вы решили создать новый раздел (схему) в Зале 2 (db_2) :

Скопировать код

-- Переходим в Зал 2 \c db_2 -- Создаем в нем новый раздел CREATE SCHEMA new_section;

Теперь посетители Зала 2 обнаружат следующие изменения:

Скопировать код

Зал 2 (db_2): До – [��, ��️] Зал 2 (db_2): После – [��, ��️, �� (new_section)]

Ваш вклад в организацию базы данных неоценим!

Управление схемами

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

Для просмотра текущих схем отправьте запрос к information_schema.schemata :

В Postgresql схема: зачем нужна и как я её могу использовать в своих проектах?

Здравствуйте!
Ещё не работал с Postgresql, и не знаю, как он работает, в чем он лучше, а в чем хуже других СУБД.
Недавно начал изучить Postgresql (надо было реализовать один проект и знакомые предложили Postgresql. а до этого работал с MySQL, Oracle)
В проекте такая структура, Интернет магазин(Покупатель, продавец, товары, склады, заказы и т.д) Структура интернет магазина чуть сложнее. и Есть одна задача:
— Интернет магазина можем продавать в других городах. А БД нужно реализовать так чтобы мы могли дать доступ клиенту, и он работал только со своими городом, и при этом все данные будет в одном БД но не должно смешиваться. с остальными городами.
Я прочитал о схема в Postgresql, пока не очень понял в чем фишка схем в Postgresql но я думаю. можно использовать схема Postgresql в моих проектах? и как лучше реализовать?

• Вопрос задан более трёх лет назад
• 8411 просмотров

5 комментариев

Средний 5 комментариев

Уточните зачем вам схемы впринципе нужны для мульти-магазинов на одной БД?

Если вам требуется подкорректировать проект БД, то нужно полнее описать задачу

Дилик Пулатов @dilikpulatov Автор вопроса

sim3x, я думал, может стоит использовать схему для разделение данные клиентов которые покупали наш проект? Мне просто нужно как-то проще разделить данные клиента но при этом они все должны быт в одном БД. Думаю вы поняли о чем я

Если клиент покупает магазин на вашем хостинге, то можно придумать как присобачить схему
Но удобнее использовать миграции

Если на хосте клиента — то вообще никак

Используйте механизм миграций в вашем бекенд фреймворке

Думаю вы поняли о чем я

Дилик Пулатов @dilikpulatov Автор вопроса

sim3x, Грубо говоря клиент покупает только доступ. Все данные будет только у меня на сервере.
О миграции пока не знаю, но я стараюсь чтобы все данные было в одном БД, потому что при добавление нового функции или исправление багов. ну вообщем при изменение мне не придется гулять в каждому БД клиента

Физическое разнесение клиентов по своим БД имеет свои плюсы

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

именно для автоматизации такого рода вещей и изобрели миграции

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

тогда схемы постгреса вам не нужны
Для общего развития можно почитать документацию по ним, но не более

Решения вопроса 0
Ответы на вопрос 1
PostgreSQL DBA

В оракле схем разве нет.

schema — дополнительный уровень структуризации объектов. Как namespace в программировании. И, к слову, входит в стандарт SQL.

Вы можете сделать таблицы:
user_subscriptions
user_orders
user_favorites
Вы можете сделать
user.subscriptions
user.orders
user.favorites
И в этом нет никакой разницы для СУБД. Но может быть удобно разработчику оперировать не с сотней таблиц одним списком, десятки из которых с одинаковыми префиксами (т.к. относятся к своим сущностям), а отдельные схемы по сущностях.

Пилить же одну таблицу на несколько смысла при этом не так много, зато добавляется хлопот.
Если вы хотите давать прямой доступ пользователю к базе — то зачем? Не надо так делать в разделяемой среде. Любую СУБД можно положить каким-нибудь интересным запросом. А в то что люди временами будут писать интересные и сильно творческие запросы — по опыту DBA вам гарантирую. Иногда такого наворотят. 0,5тб временный файлов одним запросом, например. Или сожрать 30гб RAM и увести базу в аварийный рестарт от OOM.