Поиск по сайту:

Как использовать роли и управлять разрешениями грантов в 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:

  1. sudo systemctl start postgresql.service

Затем вы можете переключиться на учетную запись postgres, набрав:

  1. sudo -i -u postgres

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

  1. psql

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

  1. \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:

  1. CREATE ROLE new_role_name;

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

  1. CREATE ROLE demo_role;

Еще раз проверьте определенных пользователей:

  1. \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 на мгновение, набрав:

  1. \q

Затем войдите в учетную запись postgres:

  1. sudo -i -u postgres

Вы можете создавать новые роли из командной строки с помощью команды createuser. При использовании флага --interactive вам будет предложено ввести имя новой роли, а также спросить, должны ли у нее быть права суперпользователя.

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

  1. createuser --interactive

Сценарий предложит вам несколько вариантов и, основываясь на ваших ответах, выполнит правильные команды Postgres в соответствии с вашими требованиями:

Output
Enter 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:

  1. psql

Затем выполните команду du, чтобы выявить различия между двумя новыми ролями. Эта команда начинается с \, потому что это специфичная для psql метакоманда, которая обрабатывается самим psql, а не PostgreSQL:

  1. \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

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

  1. DROP ROLE role_name;

Для демонстрации удалите роль demo_role, набрав:

  1. DROP ROLE demo_role;

Если вы выполните команду для несуществующего пользователя, вы получите сообщение об ошибке:

Output
ERROR: role "demo_role" does not exist

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

  1. DROP ROLE IF EXISTS role_name;

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

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

Теперь роль удалена.

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

Теперь вы готовы воссоздать demo_role с измененными разрешениями. Вы можете сделать это, указав нужные разрешения после основного предложения создания, например:

  1. CREATE ROLE role_name WITH assigned_permissions;

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

  1. \h CREATE ROLE
Output
Command: 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 возможность входа в систему, набрав:

  1. 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, он автоматически предоставляет привилегии входа в систему:

  1. CREATE USER role_name;

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

Изменение привилегий ролей в PostgreSQL

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

  1. ALTER ROLE role_name WITH attribute_options;

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

  1. ALTER ROLE demo_role WITH NOLOGIN;

Вы можете подтвердить изменение с помощью команды \du:

  1. \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 | | {}

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

  1. ALTER ROLE demo_role WITH LOGIN;

Теперь роль поменялась.

Вход в качестве другого пользователя в PostgreSQL

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

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

  1. \password test_user

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

  1. \q

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

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

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

Вот краткая разбивка каждого элемента в команде:

  • user_name следует заменить на имя пользователя, с которым вы хотите установить соединение.
  • имя_базы_данных должно быть именем существующей базы данных, к которой у вас есть доступ.
  • Раздел -h 127.0.0.1 — это часть, указывающая, что вы будете подключаться к локальному компьютеру, но через сетевой интерфейс, что позволяет вам аутентифицироваться, даже если ваше системное имя пользователя не совпадение.
  • Флаг -W указывает PostgreSQL, что вы будете вводить пароль.

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

  1. sudo psql -U test_user -d postgres -h 127.0.0.1 -W

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

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

Выйти из текущего сеанса:

  1. \q

Затем вернитесь в административный сеанс postgres:

  1. sudo u - postgres psql

Далее вы будете предоставлять разрешения.

Предоставление разрешений в PostgreSQL

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

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

  1. GRANT permission_type ON table_name TO role_name;

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

  1. CREATE TABLE demo (
  2. name varchar(25),
  3. id serial,
  4. start_date date);

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

  1. \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 с помощью следующей команды:

  1. GRANT UPDATE ON demo TO demo_role;

Вы можете предоставить полные разрешения пользователю, заменив тип разрешения словом ALL. Предоставьте это разрешение пользователю test_user с помощью этой команды:

  1. GRANT ALL ON demo TO test_user;

Если вы хотите указать разрешения для каждого пользователя в системе, вы можете использовать PUBLIC вместо конкретного пользователя:

  1. GRANT INSERT ON demo TO PUBLIC;

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

  1. \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:

  1. REVOKE permission_type ON table_name FROM user_name;

Вы также можете использовать те же сокращенные слова, ALL и PUBLIC, в команде:

  1. REVOKE INSERT ON demo FROM PUBLIC;

Разрешения, которые вы установили ранее, теперь отменены.

Использование групповых ролей в PostgreSQL

Роли достаточно гибки, чтобы можно было группировать другие роли, чтобы обеспечить широкий контроль разрешений. Например, вы можете создать новую роль с именем временные_пользователи, а затем добавить в эту группу demo_role и test_user.

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

  1. CREATE ROLE temporary_users;

Затем назначьте пользователей во вновь созданную группу временных_пользователей:

  1. GRANT temporary_users TO demo_role;
  1. GRANT temporary_users TO test_user;

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

Вы можете просмотреть информацию о членстве в роли, набрав:

  1. \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, даже если он не является членом группы временных_пользователей:

  1. SET ROLE temporary_users;

Теперь любые созданные таблицы принадлежат роли временных_пользователей:

  1. CREATE TABLE hello (
  2. name varchar(25),
  3. id serial,
  4. start_date date);

Теперь проверьте принадлежность таблицы, введя эту команду:

  1. \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)

Новая таблица и последовательность, связанная с последовательным типом данных, принадлежат роли временных_пользователей.

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

  1. RESET ROLE;

Если вы дадите пользователю свойство INHERIT с помощью команды ALTER ROLE, этот пользователь автоматически получит все привилегии ролей, к которым он принадлежит, без использования SET ROLE команда:

  1. ALTER ROLE test_user INHERIT;

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

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

Это выводит ошибку, поскольку таблица hello принадлежит временным_пользователям. Вы можете решить эту проблему, передав право собственности другой роли:

  1. ALTER TABLE hello OWNER TO demo_role;

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

  1. \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)

Теперь вы можете успешно удалить роль временных_пользователей, введя эту команду:

  1. DROP ROLE temporary_users;

Это уничтожит роль временных_пользователей. Бывшие члены временных_пользователей не удаляются.

Заключение

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

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

  • SQLite, MySQL и PostgreSQL: сравнение систем управления реляционными базами данных
  • Попрактикуйтесь в выполнении запросов с помощью SQL