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

Системный каталог - это набор системных таблиц и представлений, которые хранят метаданные о всех объектах базы данных. Это "мозг" PostgreSQL, содержащий информацию о:

  • Структуре базы данных

  • Правах доступа

  • Настройках сервера

  • Состоянии системы

Системные таблицы (pg_catalog)
Таблица Описание
pg_class Хранит информацию о таблицах, индексах, последовательностях
pg_attribute Содержит данные о столбцах таблиц
pg_index Информация об индексах
pg_database Список всех баз данных
pg_user/pg_roles Данные о пользователях и ролях
pg_tablespace Информация о табличных пространствах
Системные представления
Представление Описание
pg_tables Список всех таблиц
pg_views Список всех представлений
pg_indexes Информация об индексах
pg_stat_activity Активные подключения

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

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

Получить список всех таблиц:
SELECT tablename FROM pg_tables WHERE schemaname = 'public';
Просмотреть структуру таблицы:
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'your_table';
'your_table' - название любой вашей таблицы.

Найти все индексы таблицы:

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'your_table';
Просмотреть активные подключения:
SELECT pid, usename, application_name, state FROM pg_stat_activity;

Администрирование через системный каталог

Поиск "мусорных" объектов:
SELECT n.nspname AS schema_name, -- Наименование схемы c.relname AS object_name -- Наименование объекта FROM pg_class c -- Таблица, содержащая информацию о всех объектах (таблицах, индексах и т.д.) LEFT JOIN pg_namespace n ON n.oid = c.relnamespace -- Соединение со схемами WHERE c.relowner IN ( -- Фильтр по владельцам объектов SELECT oid FROM pg_roles WHERE rolname NOT IN ('postgres', 'rdsadmin') -- Исключение системных пользователей ) AND n.nspname NOT IN ('pg_catalog', 'information_schema') -- Исключение системных схем
Ключевые компоненты

  1. pg_class - системная таблица, содержащая:

    • Все таблицы

    • Индексы

    • Последовательности

    • Представления

    • Составные типы

  2. pg_namespace - системная таблица со списком всех схем

  3. pg_roles - системная таблица с информацией о пользователях/ролях

  4. Фильтры:

    • rolname NOT IN ('postgres', 'rdsadmin') - исключает объекты, принадлежащие системным пользователям

    • n.nspname NOT IN ('pg_catalog', 'information_schema') - исключает объекты в системных схемах

Интерпретация результатов

Результат показывает:

  • Имя схемы (nspname)

  • Имя объекта (relname)

Типы объектов, которые могут быть найдены:

  • Пользовательские таблицы

  • Индексы

  • Последовательности

  • Представления

  • Составные типы

Дополнительные рекомендации
Для более точного анализа можно добавить:
SELECT n.nspname AS schema_name, c.relname AS object_name, CASE c.relkind WHEN 'r' THEN 'table' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 'v' THEN 'view' WHEN 'c' THEN 'composite type' ELSE c.relkind::text END AS object_type, pg_size_pretty(pg_total_relation_size(c.oid)) AS size FROM ...
Перед удалением обнаруженных объектов:

  • Проверьте их использование (последнее время доступа)

  • Убедитесь в отсутствии зависимостей

  • Сделайте резервную копию

Для автоматизации можно создать представление:

CREATE OR REPLACE VIEW orphaned_objects AS SELECT ... [ваш запрос] ...

Этот запрос особенно полезен при:

  • Очистке базы данных после тестирования

  • Анализе унаследованных баз данных

  • Поиске потенциально неиспользуемых объектов

Анализ прав доступа:
SELECT grantee, -- Кому выданы права (пользователь/роль) privilege_type, -- Тип привилегии (SELECT, INSERT и т.д.) table_name -- Имя таблицы, к которой выданы права FROM information_schema.role_table_grants -- Системное представление о правах на таблицы

Данный SQL-запрос позволяет проанализировать права доступа к таблицам в базе данных.
Разберем его работу подробно:

Запрос помогает администратору:

  1. Просмотреть все выданные привилегии на таблицы

  2. Контролировать права доступа пользователей

  3. Выявлять потенциальные проблемы с безопасностью

  4. Аудитировать систему разрешений

Ключевые компоненты

  1. information_schema.role_table_grants - стандартное представление, содержащее:

    • Все выданные привилегии на таблицы

    • Информацию о том, кто и какие права имеет

  2. Основные столбцы в результате:

    • grantee - имя пользователя или роли, которой выданы права

    • privilege_type - тип привилегии:

      • SELECT - чтение данных

      • INSERT - вставка новых строк

      • UPDATE - изменение данных

      • DELETE - удаление данных

      • TRUNCATE - очистка таблицы

      • REFERENCES - создание внешних ключей

      • TRIGGER - создание триггеров

    • table_name - имя таблицы

    • table_schema - схема, в которой находится таблица (можно добавить в SELECT)

Дополненная версия запроса

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

SELECT grantee, table_schema, table_name, string_agg(privilege_type, ', ') AS privileges, is_grantable FROM information_schema.role_table_grants WHERE table_schema NOT IN ('pg_catalog', 'information_schema') -- Исключаем системные схемы GROUP BY grantee, table_schema, table_name, is_grantable ORDER BY table_schema, table_name, grantee;
Интерпретация результатов

  1. Группировка прав:

    • Запрос группирует одинаковые комбинации (пользователь+таблица)

    • Объединяет привилегии через запятую

  2. Дополнительные фильтры:

    • Исключение системных схем

    • Сортировка по схеме и таблице

  3. Полезные модификации:

    -- Права конкретного пользователя WHERE grantee = 'username' -- Права на конкретную таблицу WHERE table_name = 'your_table' -- Права с возможностью передачи (WITH GRANT OPTION) WHERE is_grantable = 'YES'
Практическое применение

  1. Аудит безопасности:

    • Проверка, не имеют ли пользователи избыточных прав

    • Выявление таблиц с публичным доступом

  2. Миграция прав:

    • Документирование текущей системы разрешений

    • Перенос прав между серверами

  3. Устранение проблем:

    • Анализ причин "Permission denied"

    • Поиск утерянных прав доступа

Важные замечания

  1. Для полного анализа прав нужно также проверять:

    • Права на схемы (information_schema.role_usage_grants)

    • Права на базы данных (pg_database)

    • Членство в ролях (pg_roles)

  2. Изменять права следует через стандартные команды:

    GRANT SELECT ON TABLE mytable TO username; REVOKE INSERT ON TABLE mytable FROM username;

Этот запрос особенно полезен при:

  • Проведении аудита безопасности

  • Подготовке к миграции

  • Анализе проблем с доступом

  • Оптимизации системы разрешений

Информационные схемы (information_schema)

Альтернативный, стандартизированный способ доступа к метаданным:

  • Более переносим между разными СУБД

  • Менее детализирован, чем pg_catalog

  • Пример использования:

SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_schema = 'public';

Практические задания

Цель: Научиться извлекать информацию о таблицах и их структуре

Подготовка данных:

CREATE SCHEMA hr; CREATE TABLE hr.employees ( id SERIAL PRIMARY KEY, name VARCHAR(100), department VARCHAR(50), salary NUMERIC(10,2) ); CREATE TABLE hr.departments ( id SERIAL PRIMARY KEY, name VARCHAR(50), manager_id INTEGER REFERENCES hr.employees(id) ); CREATE INDEX idx_emp_dept ON hr.employees(department);

Задания:

  1. Найти все таблицы в схеме hr и их владельцев

  2. Получить список всех столбцов таблицы employees с типами данных

  3. Определить, какие индексы существуют для таблицы employees

  4. Найти все внешние ключи в схеме hr

Цель: Научиться анализировать активные подключения и запросы

Подготовка данных:

  1. Откройте 3 разных сеанса psql

  2. В каждом выполните разные операции:

    • Сеанс 1: SELECT * FROM hr.employees;

    • Сеанс 2: BEGIN; UPDATE hr.employees SET salary = salary * 1.1; (не завершайте транзакцию)

    • Сеанс 3: CREATE TEMP TABLE temp_data (id INT);

Задания:

  1. Просмотреть список всех активных подключений

  2. Определить, какие запросы выполняются дольше всего

  3. Найти заблокированные транзакции

  4. Определить временные таблицы в текущей сессии

Цель: Научиться анализировать и изменять права доступа

Подготовка данных:

CREATE ROLE manager; CREATE ROLE analyst; GRANT USAGE ON SCHEMA hr TO manager; GRANT SELECT ON ALL TABLES IN SCHEMA hr TO analyst; GRANT ALL PRIVILEGES ON TABLE hr.departments TO manager;

Задания:

  1. Просмотреть все права доступа для роли manager

  2. Найти все таблицы, к которым имеет доступ роль analyst

  3. Определить, какие пользователи могут создавать объекты в схеме hr

  4. Проверить, есть ли у кого-то права с опцией GRANT OPTION

Цель: Научиться выявлять потенциальные проблемы

Подготовка данных:

-- Создадим "проблемные" объекты CREATE TABLE orphaned_table (id SERIAL); DROP TABLE orphaned_table; -- Оставим "висячий" объект CREATE TABLE unused_table (id SERIAL); -- Никем не используемая таблица

Задания:

  1. Найти таблицы без первичных ключей

  2. Обнаружить индексы, которые никогда не используются

  3. Выявить "висячие" объекты (например, после DROP TABLE)

  4. Найти таблицы без индексов

Цель: Научиться использовать системный каталог для тюнинга

Подготовка данных:

-- Сгенерируем тестовые данные INSERT INTO hr.employees (name, department, salary) SELECT 'Employee ' || i, CASE WHEN i % 2 = 0 THEN 'IT' ELSE 'HR' END, 1000 + (i % 10) * 100 FROM generate_series(1, 1000) i; -- Выполним несколько запросов EXPLAIN ANALYZE SELECT * FROM hr.employees WHERE department = 'IT'; EXPLAIN ANALYZE SELECT * FROM hr.employees WHERE salary > 1500;

Задания:

  1. Определить размер таблицы hr.employees

  2. Найти самые большие таблицы в базе данных

  3. Проанализировать статистику по использованию индексов

  4. Проверить, есть ли в таблицах "мертвые" строки, требующие VACUUM

Базы данных и схемы
Табличные пространства

Комментарии

Добавить комментарий

Чтобы оставить комменатрий необходимо Авторизоваться

Содежание
1. Введение 2. Установка PostgreSQL 3. Объектно-реляционные СУБД 4. Архитектура PostgreSQL 5. Базы данных и схемы 6. Системный каталог 7. Табличные пространства 8. Низкоуровневая организация данных 9. Задачи администрирования 10. Роли и атрибуты 11. Привилегии 12. Политики защиты строк 13. Подключение и аутентификация 14. Резервное копирование 15. Изоляция транзакций 16. Страницы и версии строк