Как посмотреть структуру таблицы sql
Можно посмотреть колонки таблицы и тип данных через, например, pgAdmin .
Также через psql можно выполнить запрос вида:
SELECT column_name, column_default, data_type FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'super_table'; Этот запрос вернёт название колонки, её значение по умолчанию, а также тип данных.
PostgreSQL — Schemas
Кластер баз данных PostgreSQL содержит одну или несколько именованных баз данных. Роли и некоторые другие типы объектов являются общими для всего кластера. Клиентское соединение с сервером может получить доступ только к данным в одной базе данных, указанной в запросе на соединение.
База данных содержит одну или несколько именованных схем , которые, в свою очередь, содержат таблицы. Схемы также содержат другие типы именованных объектов, включая типы данных, функции и операторы. Одно и то же имя объекта может использоваться в разных схемах без конфликтов; например, оба schema1 и myschema могут содержать таблицы с именами mytable . В отличие от баз данных, схемы не разделены жестко: пользователь может получить доступ к объектам в любой из схем в базе данных, к которой он подключен, если у него есть соответствующие привилегии.
Есть несколько причин, по которым можно использовать схемы:
- Чтобы многие пользователи могли использовать одну базу данных, не мешая друг другу.
- Чтобы организовать объекты базы данных в логические группы, чтобы сделать их более управляемыми.
- Сторонние приложения можно поместить в отдельные схемы, чтобы они не конфликтовали с именами других объектов.
Схемы аналогичны каталогам на уровне операционной системы, за исключением того, что схемы не могут быть вложенными.
Создание схемы
Чтобы создать схему, используйте команду CREATE SCHEMA . Дайте схеме имя по вашему выбору. Например:
CREATE SCHEMA myschema;Чтобы создать или получить доступ к объектам в схеме, напишите полное имя , состоящее из имени схемы и имени таблицы, разделенных точкой:
schema.tableЭто работает везде, где ожидается имя таблицы, включая команды изменения таблицы и команды доступа к данным, обсуждаемые в следующих главах. (Для краткости мы будем говорить только о таблицах, но те же идеи применимы и к другим видам именованных объектов, таким как типы и функции.)
На самом деле, даже более общий синтаксис:
database.schema.tableтоже можно использовать, но в настоящее время это только для формального соответствия стандарту SQL. Если вы пишете имя базы данных, оно должно совпадать с именем базы данных, к которой вы подключены.
Итак, чтобы создать таблицу в новой схеме, используйте:
CREATE TABLE myschema.mytable ( . );Чтобы удалить схему, если она пуста (все объекты в ней удалены), используйте:
DROP SCHEMA myschema;Чтобы удалить схему, включающую все содержащиеся объекты, используйте:
DROP SCHEMA myschema CASCADE;Часто вам потребуется создать схему, принадлежащую кому-то другому (поскольку это один из способов ограничить действия ваших пользователей четко определенными пространствами имен). Синтаксис для этого:
CREATE SCHEMA schema_name AUTHORIZATION user_name;Вы даже можете опустить имя схемы, и в этом случае имя схемы будет таким же, как имя пользователя.
Имена схем, начинающиеся с pg_ , зарезервированы для системных целей и не могут создаваться пользователями.
Публичная схема
В предыдущих разделах мы создавали таблицы без указания имен схем. По умолчанию такие таблицы (и другие объекты) автоматически помещаются в схему с именем « public » . Каждая новая база данных содержит такую схему. Таким образом, следующие условия эквивалентны:
CREATE TABLE products ( . );CREATE TABLE public.products ( . );Путь поиска схемы
Уточненные имена утомительны для написания, и зачастую лучше вообще не связывать конкретное имя схемы с приложениями. Поэтому к таблицам часто обращаются по неполным именам , состоящим только из имени таблицы. Система определяет, какая таблица имеется в виду, следуя пути поиска , который представляет собой список схем для поиска. Первая совпадающая таблица в пути поиска считается искомой. Если в пути поиска нет соответствия, сообщается об ошибке, даже если совпадающие имена таблиц существуют в других схемах базы данных.
Возможность создавать объекты с одинаковыми именами в разных схемах усложняет написание запроса, который каждый раз ссылается на одни и те же объекты. Это также открывает для пользователей возможность изменить поведение запросов других пользователей, злонамеренно или случайно. Из-за преобладания неполных имен в запросах и их использования во внутренних компонентах PostgreSQL добавление схемы для search_path эффективного доверия всем пользователям, имеющим CREATE привилегии в этой схеме. Когда вы запускаете обычный запрос, злоумышленник, способный создавать объекты в схеме вашего пути поиска, может взять на себя управление и выполнять произвольные функции SQL, как если бы вы их выполняли.
Первая схема, названная в пути поиска, называется текущей схемой. Помимо того, что это первая искомая схема, это также схема, в которой будут созданы новые таблицы, если в CREATE TABLE команде не указано имя схемы.
Чтобы показать текущий путь поиска, используйте следующую команду:
SHOW search_path; search_path -------------- "$user", public
Первый элемент указывает, что следует искать схему с тем же именем, что и у текущего пользователя. Если такой схемы не существует, запись игнорируется. Второй элемент относится к общедоступной схеме, которую мы уже видели.
Первая существующая схема в пути поиска является расположением по умолчанию для создания новых объектов. По этой причине объекты по умолчанию создаются в общедоступной схеме. Когда на объекты ссылаются в любом другом контексте без квалификации схемы (модификация таблицы, модификация данных или команды запроса), путь поиска проходится до тех пор, пока не будет найден соответствующий объект. Следовательно, в конфигурации по умолчанию любой неквалифицированный доступ снова может ссылаться только на общедоступную схему.
Чтобы поместить нашу новую схему в путь, мы используем:
SET search_path TO myschema,public;(Мы опускаем $user здесь, потому что в этом нет непосредственной необходимости.) И тогда мы можем получить доступ к таблице без уточнения схемы:
DROP TABLE mytable;Кроме того, поскольку myschema это первый элемент пути, в нем по умолчанию будут создаваться новые объекты.
Мы могли бы также написать:
SET search_path TO myschema;Тогда у нас больше не будет доступа к общедоступной схеме без явного уточнения. В общедоступной схеме нет ничего особенного, за исключением того, что она существует по умолчанию. Его тоже можно сбросить.
Путь поиска работает так же для имен типов данных, имен функций и имен операторов, как и для имен таблиц. Имена типов данных и функций могут быть определены точно так же, как имена таблиц. Если вам нужно написать полное имя оператора в выражении, есть специальное условие: вы должны написать
OPERATOR(schema.operator)Это необходимо, чтобы избежать синтаксической двусмысленности. Пример:
SELECT 3 OPERATOR(pg_catalog.+) 4;На практике обычно полагаются на путь поиска операторов, чтобы не писать ничего столь уродливого.
Схемы и привилегии
По умолчанию пользователи не могут получить доступ ни к каким объектам в схемах, которыми они не владеют. Чтобы разрешить это, владелец схемы должен предоставить USAGE привилегию на схему. Чтобы разрешить пользователям использовать объекты в схеме, может потребоваться предоставить дополнительные привилегии, соответствующие объекту.
Пользователю также может быть разрешено создавать объекты в чужой схеме. Чтобы разрешить это, необходимо предоставить CREATE привилегию на схему. Обратите внимание, что по умолчанию у всех есть права на схему CREATE и . Это позволяет всем пользователям, которые могут подключаться к данной базе данных, создавать объекты в ее схеме. Некоторые шаблоны использования требуют отзыва этой привилегии: USAGEpublicpublic
REVOKE CREATE ON SCHEMA public FROM PUBLIC;(Первый « общедоступный » — это схема, второй « общедоступный » означает « каждый пользователь » .
Схема системного каталога
Помимо public схем, созданных пользователем, каждая база данных содержит pg_catalog схему, которая содержит системные таблицы и все встроенные типы данных, функции и операторы. pg_catalog всегда эффективно является частью пути поиска. Если он не указан явно в пути, то он неявно ищется перед поиском в схемах пути. Это гарантирует, что встроенные имена всегда будут доступны для поиска. Однако вы можете явно указать pg_catalog в конце пути поиска, если предпочитаете, чтобы определяемые пользователем имена переопределяли встроенные имена.
Поскольку имена системных таблиц начинаются с pg_ , лучше избегать таких имен, чтобы избежать конфликта, если какая-то будущая версия определит системную таблицу с тем же именем, что и ваша таблица. (При использовании пути поиска по умолчанию неполная ссылка на имя вашей таблицы будет затем разрешаться как системная таблица.) Системные таблицы будут по-прежнему следовать соглашению о том, что имена начинаются с pg_ , чтобы они не конфликтовали с неполной пользовательской таблицей . имена, пока пользователи избегают pg_ префикса.
Шаблоны использования
Схемы можно использовать для организации ваших данных разными способами. Шаблон безопасного использования схемы не позволяет ненадежным пользователям изменять поведение запросов других пользователей. Если база данных не использует безопасный шаблон использования схемы, пользователи, желающие безопасно запрашивать эту базу данных, будут предпринимать защитные действия в начале каждого сеанса. В частности, они будут начинать каждый сеанс, устанавливая search_path пустую строку или иным образом удаляя схемы, не доступные для записи суперпользователем, из файлов search_path . Есть несколько шаблонов использования, которые легко поддерживаются конфигурацией по умолчанию:
- Ограничьте обычных пользователей схемами, приватными для пользователей. Чтобы реализовать это, введите REVOKE CREATE ON SCHEMA public FROM PUBLIC и создайте схему для каждого пользователя с тем же именем, что и у этого пользователя. Напомним, что путь поиска по умолчанию начинается с $user , который разрешается в имя пользователя. Поэтому, если у каждого пользователя есть отдельная схема, по умолчанию они получают доступ к своим собственным схемам. После применения этого шаблона в базе данных, в которую уже вошли ненадежные пользователи, рассмотрите возможность аудита общедоступной схемы для объектов с именами, подобными объектам в схеме pg_catalog . Этот шаблон является безопасным шаблоном использования схемы, если только ненадежный пользователь не является владельцем базы данных или не имеет CREATEROLE привилегии, и в этом случае не существует безопасного шаблона использования схемы.
- Удалите общедоступную схему из пути поиска по умолчанию, изменив postgresql.confили создав ALTER ROLE ALL SET search_path = «$user» . Все сохраняют возможность создавать объекты в общедоступной схеме, но выбирать эти объекты будут только полные имена. Хотя квалифицированные ссылки на таблицы допустимы, вызовы функций в общедоступной схеме будут небезопасными или ненадежными . Если вы создаете функции или расширения в общедоступной схеме, вместо этого используйте первый шаблон. В противном случае, как и в первом шаблоне, это безопасно, если ненадежный пользователь не является владельцем базы данных или не имеет CREATEROLE привилегии.
- Оставьте значение по умолчанию. Все пользователи неявно обращаются к общедоступной схеме. Это моделирует ситуацию, когда схемы вообще недоступны, обеспечивая плавный переход из мира, не поддерживающего схемы. Однако это никогда не является безопасным шаблоном. Это приемлемо только тогда, когда в базе данных есть один пользователь или несколько взаимно доверяющих пользователей.
Для любого шаблона, чтобы установить общие приложения (таблицы для всех, дополнительные функции, предоставляемые третьими лицами и т. д.), поместите их в отдельные схемы. Не забудьте предоставить соответствующие привилегии, чтобы другие пользователи могли получить к ним доступ. Затем пользователи могут обращаться к этим дополнительным объектам, уточняя имена с помощью имени схемы, или они могут по своему выбору поместить дополнительные схемы в свой путь поиска.
Портативность
В стандарте SQL не существует понятия объектов одной и той же схемы, принадлежащих разным пользователям. Более того, некоторые реализации не позволяют создавать схемы с именем, отличным от имени их владельца. На самом деле концепции схемы и пользователя почти эквивалентны в системе баз данных, которая реализует только базовую поддержку схемы, указанную в стандарте. Поэтому многие пользователи считают, что полные имена на самом деле состоят из . Вот как будет эффективно вести себя PostgreSQL , если вы создадите схему для каждого пользователя. user_name.table_name
public Кроме того, в стандарте SQL отсутствует понятие схемы. Для максимального соответствия стандарту не следует использовать public схему.
Конечно, некоторые системы баз данных SQL могут вообще не реализовывать схемы или предоставлять поддержку пространств имен, разрешая (возможно, ограниченный) доступ между базами данных. Если вам нужно работать с этими системами, то максимальной переносимости можно добиться, вообще не используя схемы.
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.
Ссылки по теме
- Безопасность SQL Server — модель безопасности с использованием определяемых пользователем ролей
- Типы данных в PostgreSQL: изучаем PostgreSQL с Grant Fritchey
Как отобразить модель базы данных в postgresql?
Как сделать скриншоты таблиц в postgresql? Сделала базу данных в postgesql. Теперь надо эти таблички «увидеть» не в виде кода типа «create table «, а в виде нормальных табличек, соединенных между собой, и заскриншотить их для последующего отчета по бд.
Отслеживать
3,324 1 1 золотой знак 10 10 серебряных знаков 21 21 бронзовый знак
задан 23 апр 2014 в 8:09
31 1 1 золотой знак 1 1 серебряный знак 3 3 бронзовых знака
select * from your_table в консоли постгреса или в Вашем любимом GUI для БД, а потом Alt+PrintScreen -> word -> Ctrl+V.
23 апр 2014 в 8:36
Мне интересно, а есть свежие реализации на данный вопрос? Не добавили никаких вариантов, чтобы в самом pgadmin можно было делать это?
18 сен 2020 в 20:00
2 ответа 2
Сортировка: Сброс на вариант по умолчанию
Данную возможность поддерживает большинство средств из данного списка https://wiki.postgresql.org/wiki/GUI_Database_Design_Tools
Рекомендую средства, которые отображают не просто связь между таблицами, а между атрибутами:
- DbVisualizer
- Valentina Studio
- pgModeller, но формат не очень красивый
Отслеживать
ответ дан 6 июн 2017 в 19:46
3,324 1 1 золотой знак 10 10 серебряных знаков 21 21 бронзовый знак
Сделала базу данных в postgesql. Теперь надо эти таблички «увидеть» не в виде кода типа «create table «, а в виде нормальных табличек, соединенных между собой, и заскриншотить их для последующего отчета по бд.
Для этих целей, есть средства моделирования в нотации UML, например Lucidchart (Flow Chart Maker & Online Diagram Software) . Вы можете «посмотреть» на таблицы из консоли выполнив команду \d
Если нужны именно данные в таблицах, то делайте запрос в таблицу
SELECT * FROM my_table;