Связаться с намиПодключиться

Назначение привилегий и ролей пользователям PostgreSQL

Статья создана
Обновлена 22 октября 2024 г.

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

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

Важно

Для новых пользователей привилегия на создание таблиц в схеме public зависит от версии PostgreSQL:

  • 14 и ниже — привилегия выдается автоматически и отозвать ее нельзя.
  • 15 и выше — привилегию необходимо предоставить пользователю вручную.

Изменить список ролей пользователя

Для назначения роли пользователю используйте интерфейсы Yandex Cloud: назначение роли запросом GRANT отменится при следующей операции с базой.

Сервис Managed Service for PostgreSQL не дает доступа к предопределенным ролям, в том числе к роли суперпользователя. Назначить пользователю можно только специальные роли:

  • mdb_admin
  • mdb_monitor
  • mdb_replication

Максимальные привилегии при работе с кластером имеет пользователь с ролью mdb_admin. Подробнее см. в разделе Назначение ролей.

Примечание

В Managed Service for PostgreSQL не предусмотрено создание пользовательских ролей. Права пользователя определяются совокупностью назначенных ему привилегий.

  1. Перейдите на страницу каталога и выберите сервис Managed Service for PostgreSQL.
  2. Нажмите на имя нужного кластера и выберите вкладку Пользователи.
  3. В строке с именем нужного пользователя нажмите на значок и выберите пункт Настроить.
  4. Разверните список Настройки СУБД и в поле Grants выберите роли, которые хотите назначить пользователю.
  5. Нажмите кнопку Сохранить.

Если у вас еще нет интерфейса командной строки Yandex Cloud, установите и инициализируйте его.

По умолчанию используется каталог, указанный в профиле CLI. Вы можете указать другой каталог с помощью параметра --folder-name или --folder-id.

Чтобы назначить роли пользователю кластера, передайте список нужных ролей в параметре --grants. Имеющиеся роли будут полностью перезаписаны: если вы хотите дополнить или уменьшить имеющийся список, сначала запросите текущие роли с информацией о пользователе командой yc managed-postgresql user get.

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

yc managed-postgresql user update <имя_пользователя> \
       --grants=<роль1,роль2> \
       --cluster-id <идентификатор_кластера>

Имя кластера можно запросить со списком кластеров в каталоге, имя пользователя — со списком пользователей.

Чтобы назначить роли пользователю кластера:

  1. Откройте актуальный конфигурационный файл Terraform с планом инфраструктуры.

    О том, как создать такой файл, см. в разделе Создание кластера.

    Полный список доступных для изменения полей конфигурации пользователей кластера Managed Service for PostgreSQL см. в документации провайдера Terraform.

  2. Найдите ресурс yandex_mdb_postgresql_user нужного пользователя.

  3. Добавьте атрибут grants со списком нужных ролей:

    resource "yandex_mdb_postgresql_user" "<имя_пользователя>" {
  ...
  name   = "<имя_пользователя>"
  grants = [ "<роль1>","<роль2>" ]
  ...
}

  4. Проверьте корректность настроек.

    1. В командной строке перейдите в каталог, в котором расположены актуальные конфигурационные файлы Terraform с планом инфраструктуры.

    2. Выполните команду:

      terraform validate

      Если в файлах конфигурации есть ошибки, Terraform на них укажет.

  5. Подтвердите изменение ресурсов.

    1. Выполните команду для просмотра планируемых изменений:

      terraform plan

      Если конфигурации ресурсов описаны верно, в терминале отобразится список изменяемых ресурсов и их параметров. Это проверочный этап: ресурсы не будут изменены.

    2. Если вас устраивают планируемые изменения, внесите их:

      1. Выполните команду:

        terraform apply

      2. Подтвердите изменение ресурсов.

      3. Дождитесь завершения операции.

  1. Получите IAM-токен для аутентификации в API и поместите токен в переменную среды окружения:

    export IAM_TOKEN="<IAM-токен>"

  2. Чтобы проверить список текущих ролей, воспользуйтесь методом User.get и выполните запрос, например, с помощью cURL:

    curl \
  --request GET \
  --header "Authorization: Bearer $IAM_TOKEN" \
  --url 'https://mdb.api.cloud.yandex.net/managed-postgresql/v1/clusters/<идентификатор_кластера>/users/<имя_пользователя>'

    Идентификатор кластера можно запросить со списком кластеров в каталоге, а имя пользователя — со списком пользователей в кластере.

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

  3. Чтобы изменить список ролей пользователя, воспользуйтесь методом User.update и выполните запрос:

    Важно

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

    curl \
  --request PATCH \
  --header "Authorization: Bearer $IAM_TOKEN" \
  --header "Content-Type: application/json" \
  --url 'https://mdb.api.cloud.yandex.net/managed-postgresql/v1/clusters/<идентификатор_кластера>/users/<имя_пользователя>' \
  --data '{
            "updateMask": "grants",
            "grants": [
              "роль_1", "роль_2", ..., "роль_N"
            ]
          }'

    Где:

    • updateMask — перечень изменяемых параметров в одну строку через запятую.

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

    • grants — массив строк с новыми ролями. Каждая строка соответствует отдельной роли. Возможные значения:

      • mdb_admin
      • mdb_monitor
      • mdb_replication
      • mdb_superuser

  4. Убедитесь, что запрос был выполнен успешно, изучив ответ сервера.

  1. Получите IAM-токен для аутентификации в API и поместите токен в переменную среды окружения:

    export IAM_TOKEN="<IAM-токен>"

  2. Клонируйте репозиторий cloudapi:

    cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi

    Далее предполагается, что содержимое репозитория находится в директории ~/cloudapi/.

  3. Чтобы проверить список текущих ролей, воспользуйтесь вызовом UserService/Get и выполните запрос, например, с помощью gRPCurl:

    grpcurl \
  -format json \
  -import-path ~/cloudapi/ \
  -import-path ~/cloudapi/third_party/googleapis/ \
  -proto ~/cloudapi/yandex/cloud/mdb/postgresql/v1/user_service.proto \
  -rpc-header "Authorization: Bearer $IAM_TOKEN" \
  -d '{
        "cluster_id": "<идентификатор_кластера>",
        "user_name": "<имя_пользователя>"
      }' \
  mdb.api.cloud.yandex.net:443 \
  yandex.cloud.mdb.postgresql.v1.UserService.Get

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

  4. Чтобы изменить список ролей пользователя, воспользуйтесь вызовом UserService/Update и выполните запрос:

    Важно

    Метод API переопределит все параметры изменяемого объекта, которые не были явно переданы в запросе, на значения по умолчанию. Чтобы избежать этого, перечислите настройки, которые вы хотите изменить, в параметре update_mask (в виде массива строк paths[]).

    Формат перечисления настроек
    "update_mask": {
    "paths": [
        "<настройка_1>",
        "<настройка_2>",
        ...
        "<настройка_N>"
    ]
}

    grpcurl \
  -format json \
  -import-path ~/cloudapi/ \
  -import-path ~/cloudapi/third_party/googleapis/ \
  -proto ~/cloudapi/yandex/cloud/mdb/postgresql/v1/user_service.proto \
  -rpc-header "Authorization: Bearer $IAM_TOKEN" \
  -d '{
        "cluster_id": "<идентификатор_кластера>",
        "user_name": "<имя_пользователя>",
        "update_mask": {
          "paths": [
            "grants"
          ]
        },
        "grants": [
          "роль_1", "роль_2", ..., "роль_N"
        ]
      }' \
  mdb.api.cloud.yandex.net:443 \
  yandex.cloud.mdb.postgresql.v1.UserService.Update

    Где:

    • update_mask — перечень изменяемых параметров в виде массива строк paths[].

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

    • grants — массив строк с новыми ролями. Каждая строка соответствует отдельной роли. Возможные значения:

      • mdb_admin
      • mdb_monitor
      • mdb_replication
      • mdb_superuser

    Идентификатор кластера можно запросить со списком кластеров в каталоге, а имя пользователя — со списком пользователей в кластере.

  5. Убедитесь, что запрос был выполнен успешно, изучив ответ сервера.

Выдать привилегию пользователю

  1. Подключитесь к базе данных с помощью учетной записи владельца базы данных.
  2. Выполните команду GRANT. Подробное описание синтаксиса команды смотрите в документации PostgreSQL.

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

Вы можете выдавать привилегии пользователям через Terraform, используя сторонний провайдер — Terraform Provider for PostgreSQL.

Примечание

Terraform Provider for PostgreSQL не является частью сервиса Managed Service for PostgreSQL и не сопровождается командой разработки и службой поддержки Yandex Cloud, а его использование не входит в условия использования Yandex Managed Service for PostgreSQL.

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

  1. Добавьте провайдер postgresql в блок required_providers в файле с настройками провайдера:

    terraform {
  required_providers {
    ...
    postgresql = {
      source = "cyrilgdn/postgresql"
    }
    ...
  }
}

  2. Откройте конфигурационный файл Terraform с планом инфраструктуры.

    О том, как создать такой файл, см. в разделе Создание кластера.

  3. Добавьте провайдер postgresql и настройте для него доступ к интересующей базе данных от имени ее владельца:

    provider "postgresql" {
  host            = <FQDN_хоста>
  port            = 6432
  database        = <имя_БД>
  username        = <имя_пользователя_владельца_БД> 
  password        = <пароль_пользователя>
}

    О том, как получить FQDN хоста, см. инструкцию.

    Полный список настроек см. в документации провайдера.

  4. Добавьте ресурс postgresql_grant:

    resource "postgresql_grant" "<название_ресурса>" {
  database    = "<имя_БД>"
  role        = "<имя_пользователя>"
  object_type = "<тип_объекта>"
  privileges  = ["<список_привилегий>"]
  schema      = "<схема>"
  objects     = ["<список_объектов>"]
  columns     = ["<список_столбцов>"]
  with_grant_option = <разрешение_на_выдачу_привилегий>
}

    Где:

    • <название_ресурса> — название Terraform-ресурса с привилегиями. Должно быть уникальным в рамках манифеста Terraform.
    • database — имя базы данных, на которую выдаются привилегии.
    • role — имя пользователя, которому выдаются привилегии.
    • object_type — тип PostgreSQL-объекта, на который выдаются привилегии. Возможные значения: database, schema, table, sequence, function, procedure, routine, foreign_data_wrapper, foreign_server, column.
    • privileges — массив выдаваемых привилегий. Возможные значения: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE и USAGE. Описание привилегий см. в документации PostgreSQL.
    • schema — схема, на которую выдаются привилегии. Нельзя задать, если выбран тип объекта database.
    • (Опционально) objects — массив объектов, на которые выдаются привилегии. Если параметр не задан, привилегии будут выданы на все объекты указанного типа. Нельзя задать, если выбран тип объекта database или schema. Если выбран тип объекта column, массив может содержать только одно значение.
    • columns — массив столбцов, на которые выдаются привилегии. Параметр обязателен, если выбран тип объекта column. Нельзя задать, если выбран любой другой тип объекта, кроме column.
    • (Опционально) with_grant_option — если true, то пользователь с выданными привилегиями сможет выдавать эти привилегии другим пользователям. По умолчанию false.

  5. Повторно инициализируйте Terraform:

    terraform init

  6. Проверьте корректность настроек.

    1. В командной строке перейдите в каталог, в котором расположены актуальные конфигурационные файлы Terraform с планом инфраструктуры.

    2. Выполните команду:

      terraform validate

      Если в файлах конфигурации есть ошибки, Terraform на них укажет.

  7. Подтвердите изменение ресурсов.

    1. Выполните команду для просмотра планируемых изменений:

      terraform plan

      Если конфигурации ресурсов описаны верно, в терминале отобразится список изменяемых ресурсов и их параметров. Это проверочный этап: ресурсы не будут изменены.

    2. Если вас устраивают планируемые изменения, внесите их:

      1. Выполните команду:

        terraform apply

      2. Подтвердите изменение ресурсов.

      3. Дождитесь завершения операции.

Отозвать привилегию у пользователя

  1. Подключитесь к базе данных с помощью учетной записи владельца базы данных.
  2. Выполните команду REVOKE. Подробное описание синтаксиса команды смотрите в документации PostgreSQL.

Если вы ранее выдали привилегию с использованием Terraform:

  1. Откройте конфигурационный файл Terraform с планом инфраструктуры.

  2. В блоке postgresql_grant удалите привилегию, которую хотите отозвать, из параметра privileges.

    Чтобы отозвать все привилегии, оставьте массив privileges пустым или удалите ресурс postgresql_grant целиком.

  3. Проверьте корректность настроек.

    1. В командной строке перейдите в каталог, в котором расположены актуальные конфигурационные файлы Terraform с планом инфраструктуры.

    2. Выполните команду:

      terraform validate

      Если в файлах конфигурации есть ошибки, Terraform на них укажет.

  4. Подтвердите изменение ресурсов.

    1. Выполните команду для просмотра планируемых изменений:

      terraform plan

      Если конфигурации ресурсов описаны верно, в терминале отобразится список изменяемых ресурсов и их параметров. Это проверочный этап: ресурсы не будут изменены.

    2. Если вас устраивают планируемые изменения, внесите их:

      1. Выполните команду:

        terraform apply

      2. Подтвердите изменение ресурсов.

      3. Дождитесь завершения операции.

Примеры

Добавить пользователя с правами только на чтение

Внимание

Не используйте этот пример, если пользователь создан с помощью Terraform: последующие изменения, сделанные через Terraform, могут отменить привилегии пользователя, сделанные через SQL.

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

  1. Создайте пользователя с именем user2. При этом выберите базы данных, к которым должен иметь доступ пользователь.

  2. Подключитесь к базе данных db1 с помощью учетной записи владельца БД.

  3. Чтобы выдать права доступа только к таблице Products в схеме по умолчанию public, выполните команду:

    GRANT SELECT ON public.Products TO user2;

  4. Чтобы выдать доступ ко всем таблицам схемы myschema, выполните команду:

    GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO user2;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA myschema to user2;

  5. (Опционально) Чтобы изменить привилегии по умолчанию, выполните команду:

    ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO user2;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE, SELECT ON SEQUENCES TO user2;

Для отзыва выданных привилегий выполните команды:

REVOKE SELECT ON public.Products FROM user2;

REVOKE SELECT ON ALL TABLES IN SCHEMA myschema FROM user2;
REVOKE USAGE ON SCHEMA myschema FROM user2;

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

Для выдачи привилегий пользователям через Terraform используется сторонний провайдер — Terraform Provider for PostgreSQL.

Примечание

Terraform Provider for PostgreSQL не является частью сервиса Managed Service for PostgreSQL и не сопровождается командой разработки и службой поддержки Yandex Cloud, а его использование не входит в условия использования Yandex Managed Service for PostgreSQL.

Подробнее о выдаче привилегий см. в разделе Выдать привилегию пользователю.

Допустим, существует кластер mypg с пользователем user1 в роли владельца. Чтобы добавить в него нового пользователя user2 с доступом только на чтение из таблиц в схеме public базы данных db1:

  1. Добавьте провайдер postgresql в блок required_providers в файле с настройками провайдера:

    terraform {
  required_providers {
    ...
    postgresql = {
      source   = "cyrilgdn/postgresql"
    }
    ...
  }
}

  2. Откройте конфигурационный файл Terraform с планом инфраструктуры.

  3. Добавьте ресурс yandex_mdb_postgresql_user:

    resource "yandex_mdb_postgresql_user" "user2" {
  cluster_id      = yandex_mdb_postgresql_cluster.mypg.id
  name            = "user2"
  password        = "user2user2"
  permission {
    database_name = yandex_mdb_postgresql_database.db1.name
  }
}

  4. Добавьте провайдер postgresql и настройте для него доступ к базе данных db1:

    provider "postgresql" {
  host            = yandex_mdb_postgresql_cluster.mypg.host[0].fqdn
  port            = 6432
  database        = yandex_mdb_postgresql_database.db1.name
  username        = yandex_mdb_postgresql_user.user1.name
  password        = yandex_mdb_postgresql_user.user1.password
}

  5. Добавьте ресурс postgresql_grant со следующими атрибутами:

    resource "postgresql_grant" "readonly_tables" {
  database          = yandex_mdb_postgresql_database.db1.name
  role              = yandex_mdb_postgresql_user.user2.name
  object_type       = "table"
  privileges        = ["SELECT"]
  schema            = "public"
}

  6. Повторно инициализируйте Terraform:

    terraform init

  7. Проверьте корректность настроек.

    1. В командной строке перейдите в каталог, в котором расположены актуальные конфигурационные файлы Terraform с планом инфраструктуры.

    2. Выполните команду:

      terraform validate

      Если в файлах конфигурации есть ошибки, Terraform на них укажет.

  8. Подтвердите изменение ресурсов.

    1. Выполните команду для просмотра планируемых изменений:

      terraform plan

      Если конфигурации ресурсов описаны верно, в терминале отобразится список изменяемых ресурсов и их параметров. Это проверочный этап: ресурсы не будут изменены.

    2. Если вас устраивают планируемые изменения, внесите их:

      1. Выполните команду:

        terraform apply

      2. Подтвердите изменение ресурсов.

      3. Дождитесь завершения операции.

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

Предыдущая
Управление пользователями БД
Следующая
Управление расширениями