Как сделать связи в postgresql
Обновлено: 07.07.2024
PostgreSQL – это опенсорсная реляционная СУБД. В статье будет рассматриваться процесс установки, настройки / управления, а также базовые операции с БД.
Если на вашей машине стоит MacOS, то процесс установки можно запустить командой:
На Linux СУБД устанавливается так:
Если у вас другая ОС, есть непонятные моменты или вопросы – обращайтесь в официальный хелп.
После того, как все загружено и установлено, можно проверить, все ли в порядке, и какая стоит версия PostgreSQL. Для этого выполните следующую команду:
Работа с PostgreSQL может быть произведена через командную строку (терминал) с использованием утилиты psql – инструмент командной строки PostgreSQL. Попробуйте ввести следующую команду:
Этой командой вы запустите утилиту psql. Хотя есть много сторонних инструментов для администрирования PostgreSQL, нет необходимости их устанавливать, т. к. psql удобен и отлично работает.
Если вам нужна помощь, введите \help (или -h) в psql-терминале. Появится список всех доступных параметров справки. Вы можете ввести \help [имя команды], если вам нужна помощь по конкретной команде. Например, если ввести \help UPDATE в консоли psql, вы увидите синтаксис команды update.
Если у вас возникает много вопросов – не стоит отчаиваться. Поиск в интернете предоставит массу примеров, ну и официальную документацию psql никто не отменял.
Первым делом необходимо проверить наличие существующих пользователей и баз данных. Выполните следующую команду, чтобы вывести список всех баз данных:
На рисунке выше вы видите три базы данных по умолчанию и суперпользователя postgres, которые создаются при установке PostgreSQL.
Чтобы вывести список всех пользователей, выполните команду \du. Атрибуты пользователя postgres говорят нам, что он суперпользователь.
Чтобы выполнять базовые действия в СУБД, нужно знать Structured Query Language (SQL).
Создание базы данных
Для создания базы данных используется команда create database. В приведенном ниже примере создается база данных с именем proglib_db.
На картинке нам сообщают об ошибке из-за того, что в нашем случае база уже создана. Вы поймете, что к чему, когда начнете писать более длинные запросы.
Создание нового юзера
Для создания пользователя существует команда create user. В приведенном ниже примере создается пользователь с именем author.
Вы можете установить пароль для существующего пользователя. С этой задачей справится команда \password:
Чтобы задать пароль при создании пользователя, можно использовать следующую команду:
Удаление базы или пользователя
Для этой операции используется команда drop: она умеет удалять как пользователя, так и БД.
Данную команду нужно использовать очень осторожно, иначе удаленные данные будут потеряны, а восстановить их можно только из бэкапа (если он был).
Если вы укажете psql postgres (без имени пользователя), то postgreSQL пустит вас под стандартным суперюзером (postgres). Чтобы войти в базу данных под определенным пользователем, можно использовать следующую команду:
Давайте войдем в базу proglib_db под пользователем author. Нажмите \q, чтобы выйти из текущей БД, а затем выполните следующую команду:
Умение создать таблицу — важный, даже можно сказать фундаментальный навык в работе с SQL.
В этом руководстве я покажу вам синтаксис инструкции CREATE TABLE на примерах с PostgreSQL и MySQL.
Базовый синтаксис CREATE TABLE
Начинаем с самой инструкции CREATE TABLE. Сразу за ней идет имя таблицы, которую вы хотите создать.
Предположим, я хочу создать таблицу с информацией о преподавателях. Я могу написать следующий запрос:
Точка с запятой после скобок сообщает о том, что это конец SQL-инструкции.
Движки хранения данных MySQL
MySQL использует эти движки для осуществления CRUD-операций (создание, чтение, обновление и удаление данных) в базе данных.
В MySQL можно указать тип движка, который вы хотите использовать для вашей таблицы. Для этого используется предложение ENGINE. Если вы его опустите, будет применен дефолтный движок — InnoDB.
От редакции Techrocks. Возможно, вам также будут интересны следующие статьи:
Что такое IF NOT EXISTS?
Вы можете добавить в запрос опциональное предложение IF NOT EXISTS. Оно нужно для проверки, не существует ли уже в базе данных та таблица, которую вы хотите создать. Это предложение можно поместить непосредственно перед именем таблицы.
Если такая таблица существует, новая не будет создана.
В этом примере я создала таблицу teachers. Попытавшись создать такую же таблицу снова, я получила ошибку.
Как создавать столбцы в таблице
В инструкции CREATE TABLE, в круглых скобках после имени таблицы, перечисляются имена столбцов, которые вы хотите создать, а также их типы данных и ограничения.
В этом примере мы добавим в таблицу teachers четыре столбца: school_id, name, email и age. Имена столбцов разделяются запятыми.
Впрочем, если вы работаете над маленькими личными проектами, маловероятно, что вам понадобится больше столбцов, чем это разрешено.
Согласно документации PostgreSQL, в этой СУБД установлен лимит в 1600 столбцов на таблицу. Так же, как и в MySQL, максимальное число столбцов может варьироваться в зависимости от количества места на диске или ограничений производительности.
Типы данных в SQL
При создании столбцов таблицы нужно указать их тип данных. Типы данных описывают значения, которые будут храниться в столбцах.
В SQL есть шесть популярных категорий типов данных:
- числа (int, float, serial, decimal)
- дата и время (timestamp, data, time)
- символы и строки (char, varchar, text)
- Unicode (ntext, nvarchar)
- бинарные данные (binary)
- смешанные (xml, table и др.)
Здесь мы не будем разбирать все типы, затронем только самые популярные. Полный список возможных типов данных можно посмотреть в документации: для PostgreSQL и для MySQL.
Что такое SERIAL и AUTO_INCREMENT?
В PostgreSQL тип данных SERIAL — это целое число, которое будет автоматически увеличиваться на единицу при каждом создании новой строки.
Мы можем указать тип данных SERIAL сразу после имени столбца school_id в нашей таблице teachers.
В MySQL вместо SERIAL используется инструкция AUTO_INCREMENT. В примере ниже мы использовали ее с типом данных INT, представляющим целые числа.
Если мы добавим в таблицу teachers пять строк и выведем содержимое столбца school_id, будут показаны числа 1, 2, 3, 4, 5. Число автоматически возрастает для каждой новой строки.
Что из себя представляет тип данных VARCHAR?
VARCHAR — это строковые данные переменной длины, где можно установить максимальную длину символов.
Ниже показан пример использования типа данных VARCHAR для столбцов name и email в таблице teachers. Здесь установлена максимальная длина в 30 символов.
Ограничения столбцов
Ограничения — это правила, которые должны соблюдаться относительно данных в столбцах таблицы.
Вот список нескольких самых распространенных ограничений столбцов:
- PRIMARY KEY — если для столбца установлено это ограничение, данные в нем становятся уникальными идентификаторами строк в таблице
- FOREIGN KEY — этот ключ связывает данные в одной таблице с данными в другой
- UNIQUE — все значения в столбце должны быть уникальными
- NOT NULL — значения не могут быть NULL. NULL — это отсутствие значения
- CHECK — проверка значения на соответствие логическому выражению
Примеры PRIMARY и FOREIGN ключей
Давайте добавим ограничение PRIMARY KEY (первичный ключ) для столбца school_id в нашей таблице teachers.
В PostgreSQL код будет выглядеть так:
Первичный ключ может быть составным, т. е. строка может уникально идентифицироваться двумя столбцами. Чтобы установить PRIMARY KEY для нескольких столбцов, эту инструкцию нужно добавить сразу после создания самих столбцов:
Если вы хотите связать одну таблицу с другой, следует использовать FOREIGN KEY (внешний ключ).
Допустим, у нас есть таблица district_employees с первичным ключом district_id. Вот как будет выглядеть код в PostgreSQL:
В таблице teachers мы можем установить связь с таблицей district_employees при помощи внешнего ключа:
Примеры NOT NULL, CHECK и UNIQUE
Если бы нам нужно было обеспечить отсутствие значений NULL в столбцах, мы могли бы прописать ограничение NOT NULL.
При помощи ограничения CHECK можно гарантировать, что все учителя в таблице будут старше 18 лет. CHECK проверяет значение на соответствие логическому выражению.
Ограничение UNIQUE мы можем использовать, чтобы обеспечить уникальность всех электронных адресов.
В сети много руководств по PostgreSQL, которые описывают основные команды. Но при погружении глубже в работу возникают такие практические вопросы, для которых требуются продвинутые команды.
Такие команды, или сниппеты, редко описаны в документации. Рассмотрим несколько на примерах, полезных как для разработчиков, так и для администраторов баз данных.
Получение информации о базе данных
Размер базы данных
Чтобы получить физический размер файлов (хранилища) базы данных, используем следующий запрос:
Результат будет представлен как число вида 41809016 .
current_database() — функция, которая возвращает имя текущей базы данных. Вместо неё можно ввести имя текстом:
Для того, чтобы получить информацию в человекочитаемом виде, используем функцию pg_size_pretty :
В результате получим информацию вида 40 Mb .
Перечень таблиц
Иногда требуется получить перечень таблиц базы данных. Для этого используем следующий запрос:
information_schema — стандартная схема базы данных, которая содержит коллекции представлений (views), таких как таблицы, поля и т.д. Представления таблиц содержат информацию обо всех таблицах баз данных.
Запрос, описанный ниже, выберет все таблицы из указанной схемы текущей базы данных:
В последнем условии IN можно указать имя определенной схемы.
Размер таблицы
По аналогии с получением размера базы данных размер данных таблицы можно вычислить с помощью соответствующей функции:
Функция pg_relation_size возвращает объём, который занимает на диске указанный слой заданной таблицы или индекса.
Имя самой большой таблицы
Для того, чтобы вывести список таблиц текущей базы данных, отсортированный по размеру таблицы, выполним следующий запрос:
Для того, чтобы вывести информацию о самой большой таблице, ограничим запрос с помощью LIMIT :
relname — имя таблицы, индекса, представления и т.п.
relpages — размер представления этой таблицы на диске в количествах страниц (по умолчанию одна страницы равна 8 Кб).
pg_class — системная таблица, которая содержит информацию о связях таблиц базы данных.
Перечень подключенных пользователей
Чтобы узнать имя, IP и используемый порт подключенных пользователей, выполним следующий запрос:
Активность пользователя
Чтобы узнать активность соединения конкретного пользователя, используем следующий запрос:
Работа с данными и полями таблиц
Удаление одинаковых строк
Если так получилось, что в таблице нет первичного ключа (primary key), то наверняка среди записей найдутся дубликаты. Если для такой таблицы, особенно большого размера, необходимо поставить ограничения (constraint) для проверки целостности, то удалим следующие элементы:
- дублирующиеся строки,
- ситуации, когда одна или более колонок дублируются (если эти колонки предполагается использовать в качестве первичного ключа).
Рассмотрим таблицу с данными покупателей, где задублирована целая строка (вторая по счёту).
Удалить все дубликаты поможет следующий запрос:
Уникальное для каждой записи поле ctid по умолчанию скрыто, но оно есть в каждой таблице.
Последний запрос требователен к ресурсам, поэтому будьте аккуратны при его выполнении на рабочем проекте.
АльфаСтрахование-Жизнь , Москва , По итогам собеседования
Теперь рассмотрим случай, когда повторяются значения полей.
Если допустимо удаление дубликатов без сохранения всех данных, выполним такой запрос:
Если данные важны, то сначала нужно найти записи с дубликатами:
Перед удалением такие записи можно перенести во временную таблицу или заменить в них значение customer_id на другое.
Общая форма запроса на удаление описанных выше записей выглядит следующим образом:
Безопасное изменение типа поля
Может возникнуть вопрос о включении в этот список такой задачи. Ведь в PostgreSQL изменить тип поля очень просто с помощью команды ALTER . Давайте для примера снова рассмотрим таблицу с покупателями.
Но в результате выполнения получим ошибку:
ERROR: column “customer_id” cannot be cast automatically to type integer
SQL state: 42804
Hint: Specify a USING expression to perform the conversion.
В результате всё прошло без ошибок:
Обратите внимание, что при использовании USING кроме конкретного выражения возможно использование функций, других полей и операторов.
Например, преобразуем поле customer_id обратно в varchar , но с преобразованием формата данных:
В результате таблица примет следующий вид:
Будьте внимательны при использовании последовательностей (sequence) в качестве первичного ключа (primary key): при назначении некоторые элементы последовательности случайно пропускаются, в результате работы с таблицей некоторые записи удаляются. Такие значения можно использовать снова, но найти их в больших таблицах сложно.
Рассмотрим два варианта поиска.
В результате получим значения: 5 , 9 и 11 .
Если нужно найти не только первое вхождение, а все пропущенные значения, используем следующий (ресурсоёмкий!) запрос:
В результате видим следующий результат: 5 , 9 и 6 .
Второй способ
Получаем имя последовательности, связанной с customer_id :
И находим все пропущенные идентификаторы:
Подсчёт количества строк в таблице
Количество строк вычисляется стандартной функцией count , но её можно использовать с дополнительными условиями.
Общее количество строк в таблице:
Количество строк при условии, что указанное поле не содержит NULL :
Количество уникальных строк по указанному полю:
Использование транзакций
Транзакция объединяет последовательность действий в одну операцию. Её особенность в том, что при ошибке в выполнении транзакции ни один из результатов действий не сохранится в базе данных.
Начнём транзакцию с помощью команды BEGIN .
Для того, чтобы откатить все операции, расположенные после BEGIN , используем команду ROLLBACK .
А чтобы применить — команду COMMIT .
Просмотр и завершение исполняемых запросов
Для того, чтобы получить информацию о запросах, выполним следующую команду:
Для того, чтобы остановить конкретный запрос, выполним следующую команду, с указанием id процесса (pid):
Для того, чтобы прекратить работу запроса, выполним:
Работа с конфигурацией
Поиск и изменение расположения экземпляра кластера
Изменим расположение на другое с помощью команды:
Но для того, чтобы изменения вступили в силу, требуется перезагрузка.
Получение перечня доступных типов данных
Получим перечень доступных типов данных с помощью команды:
typname — имя типа данных.
typlen — размер типа данных.
Изменение настроек СУБД без перезагрузки
Настройки PostgreSQL находятся в специальных файлах вроде postgresql.conf и pg_hba.conf . После изменения этих файлов нужно, чтобы СУБД снова получила настройки. Для этого производится перезагрузка сервера баз данных. Понятно, что приходится это делать, но на продакшн-версии проекта, которым пользуются тысячи пользователей, это очень нежелательно. Поэтому в PostgreSQL есть функция, с помощью которой можно применить изменения без перезагрузки сервера:
Но, к сожалению, она применима не ко всем параметрам. В некоторых случаях для применения настроек перезагрузка обязательна.
Мы рассмотрели команды, которые помогут упростить работу разработчикам и администраторам баз данных, использующим PostgreSQL. Но это далеко не все возможные приёмы. Если вы сталкивались с интересными задачами, напишите о них в комментариях. Поделимся полезным опытом!
Шпаргалка по PostgreSQL содержит общие команды, которые позволят вам быстро и эффективно работать с PostgreSQL.
Ниже предоставляем вам 3-страничную шпаргалку по PostgreSQL в формате PDF. Вы можете скачать и распечатать ее для быстрого ознакомления с наиболее часто используемыми операторами в PostgreSQL:
Команды PostgreSQL (Шпаргалка по PostgreSQL)
Доступ к серверу PostgreSQL через psql с определенным пользователем:
Например, следующая команда использует пользователя postgres для доступа к серверу базы данных PostgreSQL:
Подключение к определенной базе данных:
Подключение к базе данных dvdrental:
Чтобы выйти из psql:
Список всех баз данных на сервере PostgreSQL
Список всех схем:
Список всех хранимых процедур и функций:
Список всех представлений
Список всех таблиц в текущей базе данных.
Или для получения дополнительной информации о таблицах в текущей базе данных:
Получение подробной информации о таблице.
Показывает хранимую процедуру или код функции:
Показывает вывод запроса в красивом формате:
Список всех пользователей:
Создает новую роль:
Создает новую роль с: username и password :
Изменяет роль для текущей сессии на new_role :
Разрешить role_1 установить свою роль как role_2 :
Управление базами данных
Создание новой базы данных:
Удаление базы данных навсегда:
Управление таблицами
Создание новой или временной таблицы
Добавление нового столбца в таблицу:
Удаление столбца в таблице:
Установите или удалите значение по умолчанию для столбца:
Добавление первичного ключа к таблице.
Удаление первичного ключа из таблицы.
Удаление таблицы и зависимых от нее объектов:
Управление представлениями
Создаем рекурсивное представление:
Создайте детализированное представление:
Обновление детализированного представления:
Удаление существующего представления.
Удаление детализированного представления:
Управление индексами
Создание индекса с указанным именем для таблицы
Удаление указанного индекса из таблицы
Запрос данных из таблиц
Запросить все данные из таблицы:
Запрос данных из указанных столбцов всех строк таблицы:
Запрашивает данные и выбирает только уникальные строки:
Запрашивает данные из таблицы с помощью фильтра:
Назначение псевдонима столбцу в наборе результатов:
Запрос данных с помощью оператора LIKE
Запрос данных с помощью оператора BETWEEN:
Запрос данных с помощью оператора IN:
Ограничение возвращаемых строк с помощью условия LIMIT:
Запрос данных из множества с использованием inner join, left join, full outer join, cross join и natural join:
Возвращает количество строк таблицы.
Сортировка строк в порядке возрастания или убывания:
Группировка строк с помощью GROUP BY.
Фильтруйте группы, используя HAVING.
Операции
Объединение набора результатов двух или более запросов с помощью оператора UNION :
Минус результат используя оператор EXCEPT :
Получаем пересечение наборов результатов двух запросов:
Изменение данных
Добавляет новую строку в таблицу:
Добавляет несколько строк в таблицу:
Обновление данных для всех строк:
Обновление данных для набора строк, заданных условием в предложении WHERE.
20 Фев 2018 21:02:09 | 1 comment
Начало работы с PostgreSQL 10
В данной статье я расскажу о базовых операциях PostgreSQL 10: основные команды в оболочке psql, создание и удаление базы данных, работа с табличными пространствами, создание и удаление ролей, создание простых таблиц, работа со схемами и т.д.
Исходные данные: Debian 9.3 (Stretch), PostgreSQL 10.2
Задача: Научиться базовым навыкам работы с PostgreSQL
Давайте зайдем под пользователем postgres, запустим оболочку psql и попробуем поработать:
Перед нами открылась оболочка с приглашением ввести команды:
Первым делом давайте посмотрим информацию о версии PostgreSQL:
Давайте пройдемся по списку наиболее нужных команд:
1. Список баз данных.
Список БД можно посмотреть 3-мя способами:
Ключ -l командной строки приложения psql, метакоманда \l или select запрос к системному каталогу pg_database, например
2. Создание баз данных.
Базу данных можно создать 2-мя способами: Через утилиту createdb или с помощью команды CREATE DATABASE имя;
Утилита createdb не делает ничего волшебного, она просто подключается к базе данных postgres и выполняет SQL-команду CREATE DATABASE.
Пример этих команд и вывод списка БД:
Так же следует упомянуть про шаблоны баз данных, при инициализации основной БД создаются так же 2 базы-шаблоны — это template0 и template1.
По факту команда CREATE DATABASE выполняет копирование существующей базы данных. По умолчанию копируется стандартная системная база template1. Таким образом, template1 это шаблон, на основе которого создаются новые базы. Если добавить объекты в template1, то впоследствии они будут копироваться в новые базы данных.
В системную базу template0 не желательно вносить какие либо изменения.
В таблице pg_database есть два полезных флага для каждой базы данных: datistemplate и datallowconn.
datistemplate — Если true, базу данных сможет клонировать любой пользователь с правами CREATEDB, то есть использовать в качестве шаблона, в противном случае, клонировать эту базу смогут только суперпользователи и её владелец.
datallowconn — Если false, никто не сможет подключаться к этой базе данных. Это позволяет защитить базу данных template0 от модификаций.
Для создания базы данных на основе template0 нужно выполнить из среды SQL:
или из командной оболочки под пользователем postgres:
Просмотр флагов datistemplate и datallowconn:
3. Удаление баз данных.
Тут так же как и при создании, есть 2 подхода: Через утилиту dropdb или с помощью команды DROP DATABASE имя;
Пример:
Пример этих команд и вывод списка БД:
4. Работа с табличными пространствами.
Табличные пространства в PostgreSQL позволяют администраторам организовать логику размещения файлов объектов базы данных в файловой системе. К однажды созданному табличному пространству можно обращаться по имени на этапе создания объектов.
Табличные пространства позволяют администратору управлять дисковым пространством для инсталляции PostgreSQL. Это полезно минимум по двум причинам. Во-первых, это нехватка места в разделе, на котором был инициализирован экземпляр Pg или кластера Pg и невозможность его расширения. Табличное пространство можно создать в другом разделе и использовать его до тех пор, пока не появится возможность переконфигурирования системы. Во-вторых, табличные пространства позволяют администраторам оптимизировать производительность согласно бизнес-процессам, связанным с объектами базы данных. Например, часто используемый индекс можно разместить на очень быстром и надёжном, но дорогом SSD-диске. В то же время таблица с архивными данными, которые редко используются и скорость к доступа к ним не важна, может быть размещена в более дешёвом и медленном хранилище.
Для создания табличного пространства используется команда CREATE TABLESPACE.
Каталог нового табличного пространства должен существовать, быть пустым и принадлежать пользователю ОС, под которым запущен PostgreSQL.
Создаем каталог и выставляем права:
Подключаемся к Pg и создаем новое табличное пространство:
Посмотреть список табличных пространств можно 2-мя способами: метакоманда \db или \db+ или select запрос к системному каталогу pg_tablespace;
Создавать табличное пространство должен суперпользователь базы данных, но после этого можно разрешить обычным пользователям его использовать. Для этого необходимо предоставить привилегию CREATE на табличное пространство.
Таблицы, индексы и целые базы данных могут храниться в отдельных табличных пространствах. Для этого пользователь с правом CREATE на табличное пространство должен указать его имя в качестве параметра соответствующей команды.
Например, далее создаётся база данных в табличном пространстве fast:
В колонке Tablespace мы видим что база test2 находится в новом табличном пространстве fast.
Табличное пространство, связанное с базой данных, также используется для хранения её системных каталогов. Более того, это табличное пространство используется по умолчанию для таблиц, индексов и временных файлов, создаваемых в базе данных, если не указано иное в выражении
TABLESPACE, или переменной default_tablespace, или temp_tablespaces (соответственно). Если база данных создана без указания конкретного табличного пространства, то используется пространство, к которому принадлежит копируемый шаблон.
При инициализации экземпляра Pg или кластера Pg автоматически создаются два табличных пространства. Табличное пространство pg_global используется для общих системных каталогов. Табличное пространство pg_default используется по умолчанию для баз данных template1 и template0 (в свою очередь, также является пространством по умолчанию для других баз данных, пока не будет явно указано иное в выражении TABLESPACE команды CREATE DATABASE).
5. Работа с ролями.
В PostgreSQL используется концепция ролей (roles) для управления разрешениями на доступ к базе данных. Роль можно рассматривать как пользователя базы данных или как группу пользователей, в зависимости от того, как роль настроена. Роли могут владеть объектами базы данных (например, таблицами и функциями) и выдавать другим ролям разрешения на доступ к этим объектам, управляя тем, кто имеет доступ и к каким объектам. Кроме того, можно предоставить одной роли членство в другой роли, таким образом одна роль может использовать привилегии других ролей.
Концепция ролей включает в себя концепцию пользователей (user) и групп (groups). До версии 8.1 в PostgreSQL пользователи и группы были отдельными сущностями, но теперь есть только роли. Любая роль может использоваться в качестве пользователя, группы, и того и другого.
Роли базы данных концептуально полностью отличаются от пользователей операционной системы.На практике поддержание соответствия между ними может быть удобным, но не является обязательным. Роли базы данных являются глобальными для всего инстана Pg или кластера Pg базы данных (не для отдельной базы данных). Для создания роли используется SQL-команда CREATE ROLE, а для удаления DROP ROLE.
5.1 Создание ролей.
Роль можно создать/удалить 2-мя путями: через консольную утилиту createuser или dropuser и через psql с помощью CREATE ROLE или DROP ROLE.
Давайте попробуем обя способа создания роли:
Для просмотра списка ролей можно использовать метакоманду \du или сделать select запрос к системному каталогу pg_roles.
Для каждой роли можно назначит определенный атрибуты, определяющие её полномочия и взаимодействие с системой аутентификации клиентов.
Доступны следующие атрибуты (в скобках указаны столбцы из каталогу pg_roles):
1) Право подключения (rolcanlogin):
Только роли с атрибутом LOGIN могут использоваться для начального подключения к базе данных. Роль с атрибутом LOGIN можно рассматривать как пользователя базы данных. Для создания роли такой роли можно использовать любой из вариантов:
CREATE ROLE имя LOGIN;
или
CREATE USER имя;
или использование консольной утилиты createuser.
Как мы уже сказали выше, команда CREATE USER эквивалентна CREATE ROLE за исключением того, что CREATE USER по умолчанию предполагает атрибут LOGIN, в то время как CREATE ROLE нет.)
2) Статус суперпользователя (rolsuper):
Суперпользователь базы данных обходит все проверки прав доступа, за исключением права на вход в систему. Это опасная привилегия и она не должна использоваться небрежно. Лучше всего выполнять большую часть работы не как суперпользователь. Для создания нового супер пользователя используется CREATE ROLE имя SUPERUSER. Это нужно выполнить из под роли, которая также является суперпользователем.
3) Создание базы данных (rolcreatedb):
Роль должна явно иметь разрешение на создание базы данных (за исключением суперпользователей, которые пропускают все проверки). Для создания такой роли используется CREATE ROLE имя CREATEDB.
4) Создание роли (rolcreaterole):
Роль должна явно иметь разрешение на создание других ролей (за исключением суперпользователей, которые пропускают все проверки). Для создания такой роли используется CREATE ROLE имя CREATEROLE. Роль с привилегией CREATEROLE может также изменять и удалять другие
роли, а также выдавать и отзывать членство в ролях. Однако, для создания, изменения, удаления суперпользовательских ролей, а также изменения в них членства, требуется иметь статус суперпользователя, привилегии CREATEROLE в таких случаях недостаточно.
5) Запуск репликации (rolreplication):
Роль должна иметь явное разрешение на запуск потоковой репликации (за исключением суперпользователей, которые пропускают все проверки). Роль, используемая для потоковой репликации, также должна иметь атрибут LOGIN. Для создания такой роли используется CREATE ROLE имя REPLICATION LOGIN.
6) Пароль (rolpassword):
Пароль имеет значение, если метод аутентификации клиентов требует, чтобы пользователи предоставляли пароль при подключении к базе данных. Методы аутентификации password и md5 используют пароли. База данных и операционная система используют раздельные пароли. Пароль указывается при создании роли: CREATE ROLE имя PASSWORD ‘строка’.
7) Игнорировать систему защиты строк (rolbypassrls):
В дополнение к стандартной системе прав SQL, управляемой командой GRANT, на уровне таблиц можно определить политики защиты строк, ограничивающие для пользователей наборы строк, которые могут быть возвращены обычными запросами или добавлены, изменены и удалены командами, изменяющими данные. Это называется также защитой на уровне строк (RLS, Row-Level Security). Суперпользователи и роли с атрибутом BYPASSRLS всегда обращаются к таблице, минуя систему защиты строк. Более детально о RLS читайте в официальной документации или в переводе на русский.
Атрибуты ролей могут быть изменены после создания командой ALTER ROLE. Для получения более детальной информации Вам следует обратиться к справке по командам CREATE ROLE и ALTER ROLE.
Давайте добавим право LOGIN и CREATEDB для нашей роли utest2:
А теперь отберем у роли utest2 право CREATEDB и добавим CREATEROLE и REPLICATION:
Отдельно нужно упомянуть про такую вещь как членство в роли.
Часто бывает удобным сгруппировать пользователей для упрощения администрирования привилегий: привилегии выдаются или отзываются на всю группу. В PostgreSQL для этого создаётся роль, которая представляет группу, а затем членство (membership) в этой группе выдаётся ролям
индивидуальных пользователей.
Сделаем групповую роль, добавим членов и создадим новую БД в табличном пространстве fast и укажем эту групповую роль владельцем БД:
Теперь мы можем попробовать подключиться к базе group_db под пользователем utest2 из консоли:
После ввода пароля mysecretpwd нас пустят в базу group_db. База пока у нас пустая, чуть ниже мы попробуем создать там простую таблицу.
Так же хочу обратить внимание на такую вещь как владелец табличного пространства, чуть выше когда мы создавали табличное пространство fast, мы его создали под пользователем postgres и его владельцем стал он, см. столбец Owner при выводе информации метакомандой \db
Поменять владельца табличного пространства можно командой ALTER TABLESPACE OWNER TO ;
Например:
5.2 Удаление ролей.
Так как роли могут владеть объектами баз данных и иметь права доступа к объектам других, удаление роли не сводится к немедленному действию DROP ROLE. Сначала должны быть удалены и переданы другим владельцами все объекты, принадлежащие роли, также должны быть отозваны все права, данные роли.
То есть наша роль является владельцем базы group_db. Чтобы решить эти проблемы нужно поменять владельца БД и только после этого удалить роль:
Как мы видим и вывода \l+ у нашей БД group_db поменялся владелец и роль group_role1 была удалена (вывод \du).
6. Работа с таблицами.
Посмотрим список таблиц в БД с помощью метакоманды \dt
Теперь удалим табличку my_first_table с помощью DROP TABLE
Попытка удаления несуществующей таблицы считается ошибкой, поэтому рекомендуется использовать конструкцию DROP TABLE IF EXISTS
После удаления вывод \dt скажет нам, что в БД нет таблиц.
7. Работа со схемами.
Схема — это способ объединения таблиц, функций, операторов, типов данных и других объектов в БД.
Одно и то же имя объекта можно свободно использовать в разных схемах, например и schema1, и myschema могут содержать таблицы с именем mytable. В отличие от баз данных, схемы не ограничивают доступ к данным: пользователи могут обращаться к объектам в любой схеме текущей базы данных, если им назначены соответствующие права.
Для создания схемы используется команда CREATE SCHEMA ;
Чтобы создать объекты в схеме или обратиться к ним, указывайте полное имя, состоящее из имён схемы и объекта, разделённых точкой:
Этот синтаксис работает везде, где ожидается имя таблицы, включая команды модификации таблицы и команды обработки данных, обсуждаемые в следующих главах. (Для краткости мы будем говорить только о таблицах, но всё это распространяется и на другие типы именованных объектов, например, типы и функции.)
Есть ещё более общий синтаксис
При создании схемы так же как и при создании таблиц её владельцем становится текущий пользователь, об этом не стоит забывать!
Создадим схему и таблицу в ней:
Посмотрим список схем (метакоманда \dn):
Чтобы найти нашу таблицу мы должны будем обращаться к ней по полному пути, но вывод списка таблиц через метакоманду \dt не выведет нашу новую таблицу из схемы sc_test, чтобы он вывел таблицу мы должны добавить путь поиска схемы, это делается командой:
Чтобы не вводить SET search_path TO каждый раз мы можем задать search_path для конкретного пользователя на постоянной основе командой:
После этого мы сможем вывести список наших таблиц из схемы public и новой sc_test:
А что же за схема public? А дело вот в чем, до этого мы создавали таблицы, не указывая никакие имена схем, думаю Вы помните. По умолчанию такие таблицы (и другие объекты) автоматически помещаются в схему ‘public’. Она содержится во всех создаваемых базах данных. Таким образом, до того как мы указали путь поиска нашей новой схемы, команда:
Указав путь поиска схемы мы можем выполнить DROP TABLE my_first_table; без указания схемы, но тут есть нюанс:
ВНИМАНИЕ! Т.к. мы можем создавать одинаковые по именам объекты в разных схемах, то порядок поиска схем в путь будет иметь значение при разных операциях (создание, модификацию, удаления и др.) над объектами, вывести список путей поиска схем можно командой:
Таким образом DROP TABLE my_first_table; первым делом удалит таблицу из схемы sc_test если она там есть, а повторный вызов DROP TABLE my_first_table; уже удалит таблицу из схемы public, при её наличии там.
Вот поэтому если Вы используете схемы, то и все операции над объектами должны выполнятся с указанием полного имени объекта.
Для удаления схемы нужно воспользоваться командой DROP SCHEMA ;
Попытка удаления несуществующей схемы считается ошибкой, поэтому рекомендуется использовать конструкцию DROP SCHEMA IF EXISTS ;
Если в схеме есть объекты, то при попытке её удаление будет выведена ошибка, пример:
В таком случае нужно воспользоваться доп. опцией CASCADE, например:
Как мы видим, схема sc_test и таблица my_first_table были удалены.
На этом все, до скорых встреч.
Если у Вас возникли вопросы или Вы хотите чтобы я помог Вам, то Вы всегда можете связаться со мной разными доступными способами.
Читайте также: