Как использовать роли и управлять правами доступа в PostgreSQL

PostgreSQL — это СУБД с открытым исходным кодом, которая, в свою очередь, использует SQL. Это очень мощный инструмент, который используется для управления приложениями и размещения веб-данных на VPS. В этом руководстве вы узнаете, как управлять правами доступа в PostgreSQL. Это поможет вам предоставить ролям вашего приложения необходимые разрешения. Мы выполним это руководство, используя PostgreSQL на Ubuntu 18.04 облачном сервере на CloudSigma.

Установка PostgreSQL

Чтобы загрузить и установить PostgreSQL, выполните следующие команды:

Установщик создаст пользователя по умолчанию – “Postgres” для работы. Мы будем использовать этого пользователя в большей части этого руководства. Вы можете войти под этим пользователем с помощью следующей команды:

Теперь, когда PostgreSQL установлен, вы узнаете, как он обрабатывает права доступа. Вы также можете ознакомиться с нашим руководством по настройке PostgreSQL на Ubuntu для получения подробного пошагового руководства по процессу установки.

Концепции прав доступа в PostgreSQL

Postgres управляет правами доступа с помощью концепции “ролей”. В случае ролей нет различий между пользователями и группами. Роли более гибки, чем традиционная система пользователей и групп в Unix. Например, роль может выступать в качестве члена другой роли, наследуя ее права доступа.

Просмотр ролей в PostgreSQL

Чтобы просмотреть роли в PostgreSQL, вам сначала нужно войти в командную строку с помощью следующей команды:

Затем выполните следующее, чтобы просмотреть роли:

На данный момент существует только одна роль по умолчанию со всеми привилегиями.

Как создавать роли в PostgreSQL

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

Как создавать роли изнутри PostgreSQL

Самый простой способ создать роли — из командной строки Postgres. Вы можете сделать это с помощью следующей команды:

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

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

Как создавать роли из командной строки

Альтернативой вышеописанному методу является создание пользователя из командной строки. Чтобы выйти из командной строки PostgreSQL, введите следующее:

Вы можете создать роль с именем “test” с помощью следующей команды:

После выполнения вышеуказанной команды войдите в psql и выведите список ролей с помощью следующих команд:

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

How to Delete Roles In PostgreSQL

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

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

Если мы попытаемся удалить несуществующую роль, это просто вызовет ошибку. Чтобы убедиться в этом, вы можете попробовать удалить ту же роль еще раз:

Если вы хотите, чтобы команда не выдавала ошибку, когда роль не существует, вы можете добавить ‘IF EXISTS’ в команду:

Вы можете повторить попытку удаления роли с помощью этой команды:

Как определять привилегии при создании роли

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

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

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

Как мы видели ранее, если вы хотите создать роли с разрешением на вход (login), вы также можете использовать следующую команду:

Единственная разница между этими двумя командами заключается в том, что ‘CREATE USER’ по умолчанию дает роли разрешение на вход.

Как изменить привилегии ролей в PostgreSQL

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

Например, при желании вы можете изменить разрешения на вход для demo_role на ‘log in’ с помощью этой команды:

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

Как войти в систему под другим пользователем в PostgreSQL

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

В этом разделе вы попробуете второй вариант. Для начала вы можете установить пароль пользователя, чтобы была возможна аутентификация.

Установите пароль для ‘demo_user’ с помощью следующей команды:

При появлении запроса введите и подтвердите пароль. После этого вы можете выйти из интерфейса PostgreSQL и вернуться к обычному пользователю вашей машины:

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

user: Имя пользователя, под которым вы хотите подключиться
DB: Существующая база данных, к которой у вас есть доступ
-h 127.0.0.1: Указывает PostgreSQL, что вы будете подключаться через сетевой интерфейс к локальной машине. Этот параметр позволяет пройти аутентификацию, даже если имя пользователя не совпадает с системным именем пользователя.
-W: Указывает PostgreSQL, что вы будете вводить пароль

Чтобы войти под нашим “test_user,” мы можем выполнить следующую команду:

После ввода пароля вы войдете в оболочку. В нашем примере мы используем базу данных ‘postgres’ — базу данных по умолчанию, которая автоматически создается при установке.

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

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

Управление правами в PostgreSQL: Как предоставить права в PostgreSQL

В PostgreSQL, если база данных или таблица созданы определенной ролью, только эта роль имеет права на их изменение. Исключением являются роли с правами суперпользователя. В этом разделе вы предоставите права другим ролям. Вы можете предоставить права другим ролям с помощью оператора ‘GRANT’. Синтаксис следующий:

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

Чтобы увидеть созданную таблицу, выполните команду:

Теперь, когда у вас есть таблица demo, вы можете предоставить привилегии для таблицы ‘demo’ роли ‘demo_role’. Вы можете дать роли привилегию ‘UPDATE’ с помощью этой команды:

Вы можете предоставить все права роли, указав разрешение как ‘ALL’:

Если вы хотите предоставить определенное разрешение всем ролям в системе, вы можете использовать ключевое слово ‘public’ вместо имени роли, как в следующей команде:

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

Управление правами в PostgreSQL: как удалить права в PostgreSQL

Вы можете легко удалить права роли с помощью команды ‘REVOKE’. Она очень похожа на команду предоставления прав (GRANT):

Как и в случае с GRANT, мы можем использовать ключевые слова – ‘ALL’ и ‘PUBLIC’, чтобы предоставить все права или предоставить права всем ролям:

Как использовать групповые роли в PostgreSQL

Для обеспечения широкого контроля прав роли достаточно гибки, чтобы позволить группировку с другими ролями. Например, вы можете создать роль – ‘temporary_role’, а затем добавить ‘demo_role’ и ‘test_user’ в эту роль:

Теперь вы можете управлять правами этих двух ролей, просто управляя правами роли ‘temporary_role’. Это упрощает управление правами. Вы можете просмотреть информацию о членстве в ролях с помощью этой команды:

Любой член группы может действовать от имени группы, используя команду ‘set role’. Поскольку пользователь ‘postgres’ является суперпользователем, вы можете использовать ‘set role’, даже если вы не являетесь членом группы:

Все таблицы, которые вы создадите сейчас, будут принадлежать temporary_role:

To check the ownerships of the table, run the following command:

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

Если вы хотите, чтобы роль обладала всеми привилегиями роли, к которой она принадлежит, вы можете указать свойство ‘inherit’ с помощью команды ‘alter role’:

С помощью приведенной выше команды ‘test_user’ получает все привилегии ролей, к которым он принадлежит. Чтобы удалить роль, вы можете использовать команду ‘drop role’:

Приведенная выше команда выдает ошибку, так как у нас есть объекты, такие как таблица ‘hello’, которые зависят от нее. Чтобы удалить ее, вы можете передать владение таблицей другой роли:

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

Теперь вы можете удалить ‘temporary_role’, так как у нее больше нет зависимостей:

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

Заключение

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

Приятной работы!