SQL-Ex blog

Имеется два типа ролей в PostgreSQL.

• Роль логина - роли, которые могут осуществлять вход. Они эквивалентны пользователям в других системах баз данных.


• Роль группы - когда роли содержат другие роли, они называются групповыми ролями.

Создать роль

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

Базовый синтаксис:

CREATE ROLE role_name;

Этот оператор создает роль в базе данных. Здесь важно отметить, что эта роль не обладает никакими разрешениями, и даже не может использоваться для входа. Рисунок ниже объясняет этот сценарий. При создании роли она действительна для всех баз данных на сервере баз данных (или кластере).

Пример:

CREATE ROLE admin1;

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



Как говорилось выше, роль не зависит от базы данных, что мы тут в точности наблюдаем. Она доступна для двух баз данных, 'postgres', а также 'demo'.

Замечание: Роли admin1 присвоен атрибут 'cannot login', что означает, что она не имеет прав на вход в базу (базы) данных. Однако есть способы дать разрешения роли. Например, мы хотим дать роли права на вход. Давайте рассмотрим разные способы в разделе ниже.

Изменить роль

Предложение ALTER ROLE в сочетании с ключевым словом WITH позволяет обновить роль, дав ей необходимое разрешение.

Пример

ALTER ROLE "admin1" WITH LOGIN;

Второй вариант - это удалить существующую роль и снова создать ее с необходимым разрешением.

Базовый синтаксис:

Drop role role_name;

CREATE ROLE role_name WITH OPTION;

Во втором варианте ключевое слово WITH не обязательно. Другими опциями, доступными для использования, являются SUPERUSER, CREATEDB, CREATEROLE и т.д., что видно для других ролей на скриншоте ниже:

Создать роль с паролем

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



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

Базовый синтаксис:

CREATE ROLE role_name 

LOGIN 

PASSWORD 'password';

Пароль должен заключаться в одинарные кавычки ('). Давайте рассмотрим пример для этого сценария:

CREATE ROLE admin3

LOGIN 

PASSWORD 'test123';

Используя оператор 'select current_user', можно узнать текущего пользователя. Роль и пользователь - одно и то же в PostgreSQL.

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

\c database_name role_name

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

Создать роль с привилегиями суперпользователя (superuser)

Роль 'SUPERUSER' обходит все проверки разрешений, за исключением права на вход. Это опасная привилегия и не должна использоваться беззаботно; лучше всего выполнять большую часть работы с ролью, которая не является суперпользователем. Для создания нового суперпользователя базы данных используйте следующий синтаксис. Суперпользователь может переопределить все ограничения доступа в базе данных и, следовательно, должен создаваться только в случае необходимости. Другой момент - вы должны быть суперпользователем, чтобы создать другую роль superuser.

CREATE ROLE shivayan 

SUPERUSER 

LOGIN 

PASSWORD 'test123';

Поскольку по умолчанию роль 'superuser' не имеет прав на вход, здесь эта возможность явно указывается во время создания роли.

Создание роли с привилегией создания баз данных

Эти предложения определяют возможность роли создавать базы данных. Если указано 'CREATEDB', создаваемой роли будет позволено конструировать новые базы данных. Указание 'NOCREATEDB' будет запрещать роли создавать базы данных. Если ничего не указано, 'NOCREATEDB' принимается по умолчанию.

CREATE ROLE sysdba 

CREATEDB 

LOGIN 

PASSWORD 'test123';

Роль 'CREATE DB' дает права на создание базы данных.

Доступ к ролям посредством pgAdmin

В pgAdmin вы можете выполнить навигацию к узлу 'Login/Group Roles', как показано ниже, чтобы получить список всех имеющихся ролей.



Обратите внимание, что роли с префиксом 'pg_' являются системными ролями. Системные роли - это набор ролей по умолчанию, которые предоставляют доступ к определенным, часто необходимым, специальным возможностям и информации. Администраторы могут представлять (GRANT) эти роли пользователям и/или другим ролям в своей среде, обеспечивая пользователям доступ к специальным возможностям и информации.

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



Ссылки по теме
Привилегии и роли в SQL Server, Oracle и PostgreSQL. Часть 1
Основы PostgreSQL: роли и привилегии
Основы PostgreSQL: начала работы с psql
Основы PostgreSQL: владение объектами и привилегии по умолчанию
Основы PostgreSQL: шаблон для управления привилегиями базы данных