Как использовать роли и управлять разрешениями грантов в PostgreSQL на VPS
Введение
SQL. PostgreSQL — это мощный инструмент, который можно использовать для управления приложениями и веб-данными на виртуальном частном сервере.
В этом руководстве показано, как правильно управлять привилегиями и предоставлять разрешения пользователям. Это позволит вам предоставить вашим приложениям необходимые привилегии, не затрагивая отдельные базы данных.
Предпосылки
Чтобы следовать этому руководству, вам понадобятся:
- Один сервер Ubuntu 22.04, настроенный в соответствии с нашим руководством по начальной настройке сервера для Ubuntu 22.04. После завершения этого обязательного руководства на вашем сервере должен быть пользователь без полномочий root с разрешениями sudo и базовым брандмауэром.
- Чтобы выполнить шаг 1 нашего руководства по установке и использованию PostgreSQL в Ubuntu 22.04, установите Postgres на свой сервер.
Подготовив среду и запустив Postgres на вашем сервере, вы можете приступить к изучению того, как Postgres обрабатывает разрешения.
Просмотр ролей и разрешений в PostgreSQL
Postgres управляет разрешениями с помощью концепции ролей. Роли отличаются от традиционных разрешений в стиле Unix тем, что нет различия между пользователями и группами. Ролями можно манипулировать, чтобы они напоминали оба этих соглашения, но они также более гибкие. После установки Postgres настраивается на использование одноранговой аутентификации, что означает, что он связывает роли Postgres с соответствующей системной учетной записью Unix/Linux. Если в Postgres существует роль, имя пользователя Unix/Linux с таким же именем может войти в систему в качестве этой роли.
Процедура установки создала учетную запись пользователя с именем postgres, связанную с ролью Postgres по умолчанию. Чтобы использовать Postgres, вы можете войти в эту учетную запись.
Во-первых, убедитесь, что ваш сервер работает, используя команду systemctl start
:
- sudo systemctl start postgresql.service
Затем вы можете переключиться на учетную запись postgres, набрав:
- sudo -i -u postgres
Теперь вы можете сразу получить доступ к командной строке PostgreSQL, набрав:
- psql
Чтобы получить список ролей в вашем экземпляре Postgres, введите следующую команду:
- \du
Output List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
В настоящее время существует только одна роль по умолчанию с множеством мощных привилегий.
Создание ролей в PostgreSQL
Существует несколько различных способов создания ролей для Postgres. Роли можно создавать из Postgres или из командной строки.
Создание ролей из PostgreSQL
Один из способов создания новой роли — через интерфейс подсказок Postgres. Ниже приведен синтаксис для создания новой роли в интерфейсе приглашения Postgres:
- CREATE ROLE new_role_name;
Чтобы продемонстрировать это, создайте новую роль с именем demo_role:
- CREATE ROLE demo_role;
Еще раз проверьте определенных пользователей:
- \du
Output List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
demo_role | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
Ваш вывод покажет двух пользователей.
Создание ролей из командной строки
Альтернативный метод создания ролей — использование команды createuser
из командной строки.
Во-первых, выйдите из командной строки PostgreSQL на мгновение, набрав:
- \q
Затем войдите в учетную запись postgres:
- sudo -i -u postgres
Вы можете создавать новые роли из командной строки с помощью команды createuser
. При использовании флага --interactive
вам будет предложено ввести имя новой роли, а также спросить, должны ли у нее быть права суперпользователя.
Войдя в систему как учетная запись postgres, вы можете создать нового пользователя, набрав:
- createuser --interactive
Сценарий предложит вам несколько вариантов и, основываясь на ваших ответах, выполнит правильные команды Postgres в соответствии с вашими требованиями:
OutputEnter name of role to add: 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
Отвечая n
на все эти запросы, вы создадите пользователя, похожего на предыдущего пользователя.
Войдите снова в командную строку psql
Postgres:
- psql
Затем выполните команду du
, чтобы выявить различия между двумя новыми ролями. Эта команда начинается с \
, потому что это специфичная для psql
метакоманда, которая обрабатывается самим psql
, а не PostgreSQL:
- \du
Output List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
demo_role | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test_user | | {}
Обратите внимание, что пользователь, созданный из командной строки, не имеет атрибута Cannot login
в качестве атрибута.
Удаление ролей в PostgreSQL
Вы можете удалить роль, используя следующий синтаксис:
- DROP ROLE role_name;
Для демонстрации удалите роль demo_role, набрав:
- DROP ROLE demo_role;
Если вы выполните команду для несуществующего пользователя, вы получите сообщение об ошибке:
OutputERROR: role "demo_role" does not exist
Чтобы избежать этой ситуации и заставить команду drop удалить пользователя, если он есть, и ничего не делать, если пользователь не существует, используйте следующий синтаксис:
- DROP ROLE IF EXISTS role_name;
Если указан этот параметр, команда будет успешно завершена независимо от допустимости роли. Попытка удалить demo_role
с помощью приведенных выше команд приведет к следующему результату:
- DROP ROLE IF EXISTS demo_role;
OutputNOTICE: role "demo_role" does not exist, skipping
DROP ROLE
Теперь роль удалена.
Определение привилегий при создании роли
Теперь вы готовы воссоздать demo_role с измененными разрешениями. Вы можете сделать это, указав нужные разрешения после основного предложения создания, например:
- CREATE ROLE role_name WITH assigned_permissions;
Чтобы просмотреть полный список параметров, введите:
- \h CREATE ROLE
OutputCommand: CREATE ROLE
Description: define a new database role
Syntax:
CREATE ROLE name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid
URL: https://www.postgresql.org/docs/14/sql-createrole.html
Вы можете предоставить пользователю demo_role возможность входа в систему, набрав:
- CREATE ROLE demo_role WITH LOGIN;
Проверяя атрибуты с помощью команды \du
, два пользователя теперь имеют одинаковые привилегии:
Output List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
demo_role | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test_user | | {}
Вы можете попасть в это состояние без указания атрибута LOGIN
при каждом создании роли. Используя следующую команду CREATE USER
, он автоматически предоставляет привилегии входа в систему:
- CREATE USER role_name;
Роль создается с автоматически предоставленными правами.
Изменение привилегий ролей в PostgreSQL
Чтобы изменить атрибуты уже созданной роли, используйте команду ALTER ROLE
. Синтаксис этой команды:
- ALTER ROLE role_name WITH attribute_options;
Эта команда позволяет вам определять изменения привилегий без необходимости удалять и заново создавать пользователей, как показано ранее. Например, вы можете вернуть demo_role в прежнее состояние Не удается войти
, выполнив следующую команду:
- ALTER ROLE demo_role WITH NOLOGIN;
Вы можете подтвердить изменение с помощью команды \du
:
- \du
Output List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
demo_role | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test_user | | {}
Чтобы изменить его обратно на роль с доступом для входа, используйте следующую команду:
- ALTER ROLE demo_role WITH LOGIN;
Теперь роль поменялась.
Вход в качестве другого пользователя в PostgreSQL
По умолчанию пользователям разрешен локальный вход только в том случае, если системное имя пользователя совпадает с именем пользователя PostgreSQL. Вы можете изменить это, либо изменив тип входа, либо указав, что PostgreSQL должен использовать петлевой сетевой интерфейс. Это изменяет тип подключения на удаленный, хотя на самом деле это локальное подключение.
Сначала создайте пароль для пользователя, с которым вы хотите соединиться, чтобы он мог пройти аутентификацию. Вы можете попробовать это с test_user, который вы создали ранее, задав ему пароль:
- \password test_user
Вам будет предложено ввести и подтвердить пароль. Теперь выйдите из интерфейса PostgreSQL и вернитесь к своему обычному пользователю с помощью этой команды:
- \q
PostgreSQL предполагает, что при входе в систему вы будете использовать имя пользователя, совпадающее с именем пользователя вашей операционной системы, и что вы будете подключаться к базе данных с таким же именем.
Чтобы явно указать параметры, которые вы хотите использовать, используйте следующий синтаксис с вашими параметрами:
- psql -U user_name -d database_name -h 127.0.0.1 -W
Вот краткая разбивка каждого элемента в команде:
user_name
следует заменить на имя пользователя, с которым вы хотите установить соединение.имя_базы_данных
должно быть именем существующей базы данных, к которой у вас есть доступ.- Раздел
-h 127.0.0.1
— это часть, указывающая, что вы будете подключаться к локальному компьютеру, но через сетевой интерфейс, что позволяет вам аутентифицироваться, даже если ваше системное имя пользователя не совпадение. - Флаг
-W
указывает PostgreSQL, что вы будете вводить пароль.
Чтобы войти под своим test_user, введите следующую команду:
- sudo psql -U test_user -d postgres -h 127.0.0.1 -W
После этой команды вам нужно будет ввести пароль.
В этом примере вы используете базу данных postgres. Это база данных по умолчанию, настроенная во время установки. Если вы попытаетесь выполнить некоторые действия в этом сеансе, вы увидите, что у вас нет возможности делать многие вещи. Это связано с тем, что test_user не имеет прав администратора.
Выйти из текущего сеанса:
- \q
Затем вернитесь в административный сеанс postgres:
- sudo u - postgres psql
Далее вы будете предоставлять разрешения.
Предоставление разрешений в PostgreSQL
Когда создается база данных или таблица, обычно только роль, создавшая их, не включая роли со статусом суперпользователь, имеет разрешение на их изменение. Это поведение можно изменить, предоставив разрешения другим ролям.
Вы можете предоставить разрешения с помощью команды GRANT
со следующим общим синтаксисом:
- GRANT permission_type ON table_name TO role_name;
Вы можете создать таблицу для отработки этих концепций с помощью следующих команд:
- CREATE TABLE demo (
- name varchar(25),
- id serial,
- start_date date);
Чтобы просмотреть созданную вами таблицу, введите следующую команду:
- \d
Output List of relations
Schema | Name | Type | Owner
--------+-------------+----------+----------
public | demo | table | postgres
public | demo_id_seq | sequence | postgres
(2 rows)
Обратите внимание, что существует один тип table
и один тип sequence
. sequence
генерируется для вас, когда вы использовали команду id serial
при создании таблицы. Это генерирует автоинкрементное целое число.
Теперь вы можете предоставить некоторые привилегии для новой демонстрационной таблицы для demo_role. Для этого предоставьте пользователю demo_role привилегии UPDATE
с помощью следующей команды:
- GRANT UPDATE ON demo TO demo_role;
Вы можете предоставить полные разрешения пользователю, заменив тип разрешения словом ALL
. Предоставьте это разрешение пользователю test_user с помощью этой команды:
- GRANT ALL ON demo TO test_user;
Если вы хотите указать разрешения для каждого пользователя в системе, вы можете использовать PUBLIC
вместо конкретного пользователя:
- GRANT INSERT ON demo TO PUBLIC;
Чтобы просмотреть таблицу грантов, используйте следующую команду:
- \z
Output Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-------------+----------+----------------------------+-------------------+----------
public | demo | table | postgres=arwdDxt/postgres +| |
| | | demo_role=w/postgres +| |
| | | test_user=arwdDxt/postgres+| |
| | | =a/postgres | |
public | demo_id_seq | sequence | | |
(2 rows)
Это показывает все предоставленные разрешения, которые были назначены.
Удаление разрешений в PostgreSQL
Вы можете удалить разрешения с помощью команды REVOKE
. Команда REVOKE
использует почти такой же синтаксис, как и команда grant:
- REVOKE permission_type ON table_name FROM user_name;
Вы также можете использовать те же сокращенные слова, ALL
и PUBLIC
, в команде:
- REVOKE INSERT ON demo FROM PUBLIC;
Разрешения, которые вы установили ранее, теперь отменены.
Использование групповых ролей в PostgreSQL
Роли достаточно гибки, чтобы можно было группировать другие роли, чтобы обеспечить широкий контроль разрешений. Например, вы можете создать новую роль с именем временные_пользователи, а затем добавить в эту группу demo_role и test_user.
Сначала создайте новую роль, которая будет использоваться как группа:
- CREATE ROLE temporary_users;
Затем назначьте пользователей во вновь созданную группу временных_пользователей:
- GRANT temporary_users TO demo_role;
- GRANT temporary_users TO test_user;
Теперь эти два пользователя могут управлять своими разрешениями, манипулируя групповой ролью временных_пользователей вместо того, чтобы управлять каждым участником по отдельности.
Вы можете просмотреть информацию о членстве в роли, набрав:
- \du
Output List of roles
Role name | Attributes | Member of
-----------------+------------------------------------------------------------+-------------------
demo_role | | {temporary_users}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
temporary_users | Cannot login | {}
test_user | | {temporary_users}
Любой член групповой роли может действовать как групповая роль, членом которой он является, с помощью команды SET ROLE
. Поскольку пользователь postgres, под которым вы вошли, в настоящее время имеет привилегии суперпользователя, вы можете использовать команду SET ROLE
, даже если он не является членом группы временных_пользователей:
- SET ROLE temporary_users;
Теперь любые созданные таблицы принадлежат роли временных_пользователей:
- CREATE TABLE hello (
- name varchar(25),
- id serial,
- start_date date);
Теперь проверьте принадлежность таблицы, введя эту команду:
- \d
Output 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)
Новая таблица и последовательность, связанная с последовательным типом данных, принадлежат роли временных_пользователей.
Чтобы вернуться к исходным разрешениям роли, введите следующую команду:
- RESET ROLE;
Если вы дадите пользователю свойство INHERIT
с помощью команды ALTER ROLE
, этот пользователь автоматически получит все привилегии ролей, к которым он принадлежит, без использования SET ROLE
команда:
- ALTER ROLE test_user INHERIT;
Теперь test_user будет иметь все разрешения ролей, членом которых он является. Вы можете удалить роль группы или любую роль с помощью команды DROP ROLE
. Вы можете проверить это с помощью группы временных_пользователей, введя следующую команду:
- DROP ROLE temporary_users;
OutputERROR: role "temporary_users" cannot be dropped because some objects depend on it
DETAIL: owner of sequence hello_id_seq
owner of table hello
Это выводит ошибку, поскольку таблица hello
принадлежит временным_пользователям. Вы можете решить эту проблему, передав право собственности другой роли:
- ALTER TABLE hello OWNER TO demo_role;
Вы можете проверить, не владеет ли временный_пользователь какой-либо из таблиц, следующим образом:
- \d
Output 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)
Теперь вы можете успешно удалить роль временных_пользователей, введя эту команду:
- DROP ROLE temporary_users;
Это уничтожит роль временных_пользователей. Бывшие члены временных_пользователей не удаляются.
Заключение
Теперь у вас есть базовые навыки, необходимые для администрирования прав доступа к базе данных PostgreSQL. Важно знать, как управлять разрешениями, чтобы ваши приложения могли получать доступ к необходимым им базам данных, не нарушая при этом данные, используемые другими приложениями.
Если вы хотите узнать больше о Postgres и о том, как его использовать, мы рекомендуем вам ознакомиться со следующими руководствами:
- SQLite, MySQL и PostgreSQL: сравнение систем управления реляционными базами данных
- Попрактикуйтесь в выполнении запросов с помощью SQL