Использование ролей и управление доступом в PostgreSQL

Linux, Ubuntu | Amber | 1 Comment

PostgreSQL – это открытая система управления базами данных (СУБД), основанная на языке запросов SQL. PostgreSQL – очень производительный инструмент, предназначенный для систематизации и хранения данных приложения.

Данное руководство научит вас управлять правами доступа PostgreSQL.

Примечание: Руководство выполнено на облачном сервере Ubuntu 12.04, однако все инструкции, кроме раздела по установке, можно применить и в других современных дистрибутивах Linux.

Установка PostgreSQL

Если СУБД PostgreSQL не была установлена ранее, установите её сейчас. Для этого используйте команды:

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

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

sudo su - postgres

Права доступа PostgreSQL

PostgreSQL управляет доступом при помощи так называемых ролей.

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

Роли могут быть членами других ролей, что позволяет им наследовать параметры привилегий определённых ранее ролей.

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

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

psql

а затем ввести команду:

\du
List of roles
Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
postgres  | Superuser, Create role, Create DB, Replication | {}

Как видите, после установки в PostgreSQL существует всего одна роль, которая обладает широкими правами доступа.

Создание ролей PostgreSQL

Существует два базовых способа создания ролей: в командной строке PostgreSQL и в командной строке системы.

Создание роли в PostgreSQL

Проще всего создавать новые роли в командной строке PostgreSQL.

Для этого используется следующий синтаксис:

CREATE ROLE new_role_name;

Попробуйте создать новую роль (в руководстве она условно называется demo_role):

CREATE ROLE demo_role;
CREATE ROLE

Проверьте список существующих ролей:

\du
List of roles
Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
demo_role | Cannot login                                   | {}
postgres  | Superuser, Create role, Create DB, Replication | {}

Как видите, в списке появилась новая роль. Обратите внимание: на данный момент у неё нет привилегий входа.

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

Также можно создать роль при помощи команды createuser.

Закройте командную строку PostgreSQL:

\q

Чтобы создать роль в командной строке системы, введите следующую команду (в руководстве эта роль будет условно называться test_user):

createuser test_user
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

Команда задаст ряд вопросов, которые определят начальные привилегии данной роли.

Снова откройте командную строку Postgres и запросите список существующих ролей:

psql
\du
List of roles
Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
demo_role | Cannot login                                   | {}
postgres  | Superuser, Create role, Create DB, Replication | {}
test_user |                                                | {}

Как видите, роли, созданные разными методами, не идентичны. Роль, созданная в командной строке системы, имеет привилегии входа.

Удаление ролей PostgreSQL

Теперь попробуйте уровнять привилегии ролей demo_role и test_user. Это можно сделать во время создания роли (то есть нужно удалить и заново создать demo_role). Также можно просто отредактировать привилегии существующей роли.

Но прежде чем приступить к управлению привилегиями PostgreSQL, нужно научиться удалять роли.

Для этого используется следующий синтаксис:

DROP ROLE role_name;
Delete the "demo_role" role by typing:
DROP ROLE demo_role;
DROP ROLE

Если заданной в команде роли не существует, команда вернёт ошибку:

DROP ROLE demo_role;
ERROR:  role "demo_role" does not exist

Оператор IF EXISTS позволяет избежать этой ошибки; команда с таким оператором удалит роль, если она существует. Если указанной роли нет, команда не вернёт ошибку.

DROP ROLE IF EXISTS role_name;

То есть, в любом случае команда будет выполнена успешно и не вернёт ошибку.

DROP ROLE IF EXISTS demo_role;
NOTICE:  role "demo_role" does not exist, skipping
DROP ROLE

Определение привилегий во время создания роли

Теперь попробуйте снова создать роль demo_role, заранее установив её права доступа. Права роли можно указать сразу после главного оператора create.

Синтаксис выглядит так:

CREATE ROLE role_name WITH optional_permissions;

Полный список опций доступа можно просмотреть при помощи команды:

\h CREATE ROLE

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

CREATE ROLE demo_role WITH LOGIN;
CREATE ROLE

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

\du
List of roles
Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
demo_role |                                                | {}
postgres  | Superuser, Create role, Create DB, Replication | {}
test_user |                                                | {}

Чтобы роль имела права входа без аргумента login, используйте вместо CREATE ROLE такую команду:

CREATE USER role_name;

Команда CREATE USER отличается только тем, что автоматически даёт роли привилегии входа.

Управление правами роли PostgreSQL

Чтобы изменить права доступа уже существующей роли, используйте команду ALTER ROLE.

Её базовый синтаксис:

ALTER ROLE role_name WITH attribute_options;

Для примера попробуйте вернуть роли demo_role её исходные привилегии:

ALTER ROLE demo_role WITH NOLOGIN;
ALTER ROLE

Просмотрите список ролей:

\du
List of roles
Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
demo_role | Cannot login                                   | {}
postgres  | Superuser, Create role, Create DB, Replication | {}
test_user |                                                | {}

Теперь у роли demo_role нет привилегий входа.

Вернуть привилегии входа можно при помощи команды:

ALTER ROLE demo_role WITH LOGIN;

Смена пользователя PostgreSQL

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

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

Рассмотрим второй вариант.

Для начала нужно установить пароль для пользователя, в сессию которого нужно перейти.

Установите пароль для test_user:

\password test_user

Команда предложит ввести и подтвердить пароль. Затем закройте интерфейс PostgreSQL и вернитесь в сессию системного пользователя.

\q
exit

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

Но в данном случае это не так, потому нужно явно указать опции. Для этого используйте синтаксис:

psql -U user_name -d database_name -h 127.0.0.1 -W

Примечание: Вместо user_name укажите имя пользователя, при помощи которого нужно установить соединение; вместо database_name укажите имя БД, к которой нужно подключиться.

Оператор -h 127.0.0.1 указывает, что нужно подключиться к локальной машине по сетевому интерфейсу. Это позволит проходить аутентификацию, даже если имя пользователя и имя роли не совпадают. Флаг –W значит, что при входе в PostgreSQL нужно ввести пароль.

Чтобы открыть сессию пользователя test_user, введите:

psql -U test_user -d postgres -h 127.0.0.1 -W
Password for user test_user:

Программа запросит установленный ранее пароль.

Примечание: Данная команда подключит пользователя к БД postgres, стандартной БД, созданной во время установки.

Попробуйте поработать в этой сессии; как видите, данный пользователь имеет довольно узкие привилегии.

Вернитесь в сессию администратора:

\q
sudo su - postgres
psql

Управление привилегиями PostgreSQL

Как передать привилегии

Как правило, при создании БД или таблицы права доступа к ней есть только у создавшей её роли. Но такое поведение можно изменить.

Передавать права доступа другим ролям можно при помощи команды GRANT; её базовый синтаксис:

GRANT permission_type ON table_name TO role_name;

Для примера создайте таблицу:

CREATE TABLE demo (
name varchar(25),
id serial,
start_date date);
NOTICE:  CREATE TABLE will create implicit sequence "demo_id_seq" for serial column "demo.id"
CREATE TABLE

Просмотрите результат:

\d
List of relations
Schema |    Name     |   Type   |  Owner
--------+-------------+----------+----------
public | demo        | table    | postgres
public | demo_id_seq | sequence | postgres
(2 rows)

Теперь попробуйте передать некоторые права доступа к таблице demo роли demo_role (пусть это будет право на обновление, UPDATE).

GRANT UPDATE ON demo TO demo_role;

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

GRANT ALL ON demo TO test_user;

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

GRANT INSERT ON demo TO PUBLIC;

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

\z
Access privileges
Schema |    Name     |   Type   |     Access privileges      | Column access privileges
--------+-------------+----------+----------------------------+--------------------------
public | demo        | table    | postgres=arwdDxt/postgres +|
|             |          | demo_role=w/postgres      +|
|             |          | test_user=arwdDxt/postgres+|
|             |          | =a/postgres                |
public | demo_id_seq | sequence |                            |
(2 rows)

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

Команда REVOKE отнимает привилегии.

REVOKE permission_type ON table_name FROM user_name;

Данная команда тоже может использовать операторы all и public.

REVOKE INSERT ON demo FROM PUBLIC;

Групповые роли PostgreSQL

PostgreSQL позволяет группировать роли, благодаря чему роли могут наследовать заранее установленные права доступа.

Для примера можно создать роль temporary_users и добавить в неё роли demo_role и test_user:

CREATE ROLE temporary_users;
GRANT temporary_users TO demo_role;
GRANT temporary_users TO test_user;

Теперь групповая роль temporary_users управлят привилегиями ролей demo_role и test_user.

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

\du
List of roles
Role name    |                   Attributes                   |     Member of
-----------------+------------------------------------------------+-------------------
demo_role       |                                                | {temporary_users}
postgres        | Superuser, Create role, Create DB, Replication | {}
temporary_users | Cannot login                                   | {}
test_user       |                                                | {temporary_users}

Команда set role позволяет выбрать групповую роль, права которой нужно использовать.

Например, текущий пользователь postgres имеет права суперпользователя. Даже несмотря на то, что этот пользователь не является членом роли temporary_users, он может использовать её права:

SET ROLE temporary_users;

Теперь любая созданная таблица будет принадлежать групповой роли  temporary_users.

CREATE TABLE hello (
name varchar(25),
id serial,
start_date date);

Просмотреть владельцев таблиц можно с помощью команды:

\d
List of relations
Schema |     Name     |   Type   |      Owner
--------+--------------+----------+-----------------
public | demo         | table    | postgres
public | demo_id_seq  | sequence | postgres
public | hello        | table    | temporary_users
public | hello_id_seq | sequence | temporary_users
(4 rows)

Как видите, новая таблица принадлежит роли temporary_users.

Чтобы вернуть оригинальные права текущей роли, введите:

RESET ROLE;

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

ALTER ROLE test_user INHERIT;

Чтобы удалить групповую роль (или любую роль), используйте:

DROP ROLE temporary_users;
ERROR:  role "temporary_users" cannot be dropped because some objects depend on it
DETAIL:  owner of table hello
owner of sequence hello_id_seq

Эта команда вернёт ошибку, потому что роли temporary_users принадлежит таблица. Сначала нужно передать права на таблицу другой роли:

ALTER TABLE hello OWNER TO demo_role;

Теперь роль таблица принадлежит роли demo_role.

\d
List of relations
Schema |     Name     |   Type   |   Owner
--------+--------------+----------+-----------
public | demo         | table    | postgres
public | demo_id_seq  | sequence | postgres
public | hello        | table    | demo_role
public | hello_id_seq | sequence | demo_role
(4 rows)

После этого роль temporary_users можно удалить:

DROP ROLE temporary_users;

Это удалит роль temporary_users; члены этой групповой роли не будут удалены.

Заключение

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