Contact UsGet started

Assigning privileges and roles to PostgreSQL users

Written by
Updated at May 5, 2025

Atomic permissions in PostgreSQL are called privileges and permission groups are called roles. For more information about access permissions, see the PostgreSQL documentation.

The user created with a Managed Service for PostgreSQL cluster is the owner of the first database in the cluster. You can create other users and configure their permissions as you wish:

Warning

Granting the public table create privilege to new users depends on the PostgreSQL version:

  • 14 and lower: The privilege is granted automatically and cannot be revoked.
  • 15 and higher: The privilege is manually granted to the user.

Updating the list of roles for a user

To assign a role to a user, use the Yandex Cloud interfaces: the roles assigned by the GRANT request are canceled during the next database operation.

With Managed Service for PostgreSQL, you cannot access predefined roles, including the superuser role. You can assign users only the following special roles:

  • mdb_superuser
  • mdb_admin
  • mdb_monitor
  • mdb_replication

Users with the mdb_superuser role have the highest privileges for working with clusters. For more information, see Assigning roles.

Note

You cannot create custom roles in Managed Service for PostgreSQL. User permissions depend on a set of privileges the user is granted.

  1. Go to the folder page and select Managed Service for PostgreSQL.
  2. Click the cluster name and open the Users tab.
  3. In the user name row, click and select Configure.
  4. Expand the DBMS settings list and select the roles you want to assign to the user in the Grants field.
  5. Click Save.

If you do not have the Yandex Cloud CLI yet, install and initialize it.

The folder specified when creating the CLI profile is used by default. To change the default folder, use the yc config set folder-id <folder_ID> command. You can specify a different folder using the --folder-name or --folder-id parameter.

To assign roles to a cluster user, provide the list of required roles in the --grants parameter. This will completely overwrite the existing roles. To add or remove roles, first, run the yc managed-postgresql user get command to get the list of current roles together with the user info.

To assign roles, run this command:

yc managed-postgresql user update <username> \
       --grants=<role_1,role_2> \
       --cluster-id <cluster_ID>

You can request the cluster name with the list of clusters in the folder and the username, with the list of users.

To assign roles to a cluster user:

  1. Open the current Terraform configuration file with an infrastructure plan.

    For more information about creating this file, see Creating clusters.

    For a complete list of editable fields in the Managed Service for PostgreSQL cluster user configuration, see the Terraform provider documentation.

  2. Locate the user's yandex_mdb_postgresql_user resource.

  3. Add the grants attribute with the list of required roles:

    resource "yandex_mdb_postgresql_user" "<username>" {
  ...
  name   = "<username>"
  grants = [ "<role_1>","<role_2>" ]
  ...
}

  4. Make sure the settings are correct.

    1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.

    2. Run this command:

      terraform validate

      Terraform will show any errors found in your configuration files.

  5. Confirm updating the resources.

    1. Run this command to view the planned changes:

      terraform plan

      If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.

    2. If everything looks correct, apply the changes:

      1. Run this command:

        terraform apply

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. To check the list of current roles, use the User.Get method and send the following request, e.g., via cURL:

    curl \
  --request GET \
  --header "Authorization: Bearer $IAM_TOKEN" \
  --url 'https://mdb.api.cloud.yandex.net/managed-postgresql/v1/clusters/<cluster_ID>/users/<username>'

    You can request the cluster ID with the list of clusters in the folder and the username, with the list of users in the cluster.

    The list of current roles is in the grants parameter in the command output.

  3. To change the list of roles for a user, use the User.Update method and make a request:

    Warning

    The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the updateMask parameter as a single comma-separated string.

    curl \
  --request PATCH \
  --header "Authorization: Bearer $IAM_TOKEN" \
  --header "Content-Type: application/json" \
  --url 'https://mdb.api.cloud.yandex.net/managed-postgresql/v1/clusters/<cluster_ID>/users/<username>' \
  --data '{
            "updateMask": "grants",
            "grants": [
              "role_1", "role_2", ..., “role_N"
            ]
          }'

    Where:

    • updateMask: List of parameters to update as a single string, separated by commas.

      In this case, only one parameter is provided.

    • grants: Array of strings with new roles. Each row corresponds to a separate role. The possible values are:

      • mdb_admin
      • mdb_monitor
      • mdb_replication
      • mdb_superuser

  4. View the server response to make sure the request was successful.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Clone the cloudapi repository:

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

    Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

  3. To check the list of current roles, use the UserService.Get call and send the following request, e.g., via 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": "<cluster_ID>",
        "user_name": "<username>"
      }' \
  mdb.api.cloud.yandex.net:443 \
  yandex.cloud.mdb.postgresql.v1.UserService.Get

    The list of current roles is in the grants parameter in the command output.

  4. To change the list of roles for a user, use the UserService.Update call and make a request:

    Warning

    The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the update_mask parameter as an array of paths[] strings.

    Format for listing settings
    "update_mask": {
    "paths": [
        "<setting_1>",
        "<setting_2>",
        ...
        "<setting_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": "<cluster_ID>",
        "user_name": "<username>",
        "update_mask": {
          "paths": [
            "grants"
          ]
        },
        "grants": [
          "role_1", "role_2", ..., “role_N"
        ]
      }' \
  mdb.api.cloud.yandex.net:443 \
  yandex.cloud.mdb.postgresql.v1.UserService.Update

    Where:

    • update_mask: List of parameters to update as an array of paths[] strings.

      Only one parameter is provided in this case.

    • grants: Array of strings with new roles. Each row corresponds to a separate role. The possible values are:

      • mdb_admin
      • mdb_monitor
      • mdb_replication
      • mdb_superuser

    You can request the cluster ID with the list of clusters in the folder and the username, with the list of users in the cluster.

  5. View the server response to make sure the request was successful.

Granting a privilege to a user

  1. Connect to the database under the database owner's account.
  2. Run the GRANT command. For a detailed description of the command syntax, see the PostgreSQL documentation.

You can grant user privileges via Terraform only in a cluster with public hosts.

You can grant privileges to your users via Terraform using the third-party Terraform Provider for PostgreSQL.

Note

Terraform Provider for PostgreSQL is not part of Managed Service for PostgreSQL. It is not covered by Yandex Cloud support and its usage is not governed by the Yandex Managed Service for PostgreSQL Terms of Use.

To grant a privilege to a cluster user:

  1. Add the postgresql provider to the required_providers section in the provider configuration file:

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

  2. Open the Terraform configuration file with the infrastructure plan.

    For more information about creating this file, see Creating clusters.

  3. Add the postgresql provider and enable it to access the database you need as its owner:

    provider "postgresql" {
  host            = <host_FQDN>
  port            = 6432
  database        = <DB_name>
  username        = <DB_owner_username> 
  password        = <user_password>
}

    To learn how to get a host FQDN, see this guide.

    For a full list of settings, see the provider documentation.

  4. Add the postgresql_grant resource:

    resource "postgresql_grant" "<resource_name>" {
  database    = "<DB_name>"
  role        = "<username>"
  object_type = "<object_type>"
  privileges  = ["<list_of_privileges>"]
  schema      = "<schema>"
  objects     = ["<list_of_objects>"]
  columns     = ["<list_of_columns>"]
  with_grant_option = <permission_to_grant_privileges>
}

    Where:

    • <resource_name>: Name of the Terraform resource with privileges. It must be unique within the Terraform manifest.
    • database: Name of the database for which the privileges are granted.
    • role: Name of the user the privileges are granted to.
    • object_type: Type of PostgreSQL object for which the privileges are granted. Possible values: database, schema, table, sequence, function, procedure, routine, foreign_data_wrapper, foreign_server, column.
    • privileges: Array of privileges to grant. Possible values: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGE. You can find the descriptions of privileges in the PostgreSQL documentation.
    • schema: Schema for which you are granting privileges. You cannot specify it for database objects.
    • (Optional) objects: Array of objects for which privileges are granted. If you omit this parameter, privileges will be granted for all objects of the specified type. You cannot specify it for database or schema objects. If the object type is column, the array can contain only one value.
    • columns: Array of columns for which privileges are granted. This is a required parameter for column objects. You cannot specify it for any object type other than column.
    • (Optional) with_grant_option: If true, a user with the privileges can grant them to other users. The default value is false.

  5. Initialize Terraform once again:

    terraform init

  6. Make sure the settings are correct.

    1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.

    2. Run this command:

      terraform validate

      Terraform will show any errors found in your configuration files.

  7. Confirm updating the resources.

    1. Run this command to view the planned changes:

      terraform plan

      If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.

    2. If everything looks correct, apply the changes:

      1. Run this command:

        terraform apply

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

Revoking a privilege from a user

  1. Connect to the database under the database owner's account.
  2. Run the REVOKE command. For a detailed description of the command syntax, see the PostgreSQL documentation.

If you previously granted a privilege using Terraform:

  1. Open the Terraform configuration file with the infrastructure plan.

  2. In the postgresql_grant section, remove the privilege you want to revoke from the privileges parameter.

    To revoke all privileges, leave the privileges array empty or completely remove the postgresql_grant resource.

  3. Make sure the settings are correct.

    1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.

    2. Run this command:

      terraform validate

      Terraform will show any errors found in your configuration files.

  4. Confirm updating the resources.

    1. Run this command to view the planned changes:

      terraform plan

      If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.

    2. If everything looks correct, apply the changes:

      1. Run this command:

        terraform apply

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

Examples

Add a user with read-only permissions

Alert

Do not use this example if a user is created using Terraform: subsequent changes made via Terraform may cancel the user's privileges granted through SQL.

To add a new user (user2) to an existing cluster with read-only access to the db1 database:

  1. Create a user named user2. Select the databases that the user should have access to.

  2. Connect to the db1 database under the database owner account.

  3. Grant user2 the required permissions.

    Examples:

    • Grant access only to the Products table in the default public schema:

      GRANT SELECT ON public.Products TO user2;

    • Grant access to objects in myschema:

      GRANT USAGE ON SCHEMA myschema TO user2;

    • Grant access to all tables and sequences in myschema:

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

    • Allow calling my_function in myschema:

      GRANT EXECUTE ON FUNCTION myschema.my_function TO user2;

    • Change the default privileges for tables and sequences in myschema:

      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;

      The ALTER DEFAULT PRIVILEGES commands allow you to change access permissions for new objects you will create later (in this case, myschema tables and sequences) but do not affect permissions granted for existing objects.

      To update privileges for existing objects, use the GRANT and REVOKE commands.

You can grant user privileges via Terraform only in a cluster with public hosts.

User privileges are granted via Terraform using a third-party provider, Terraform Provider for PostgreSQL.

Note

Terraform Provider for PostgreSQL is not part of Managed Service for PostgreSQL. It is not covered by Yandex Cloud support and its usage is not governed by the Yandex Managed Service for PostgreSQL Terms of Use.

For more information about granting privileges, see Granting a privilege to a user.

Let's say you have a cluster named mypg with user1 as the owner. To add a new user (user2) to this cluster with read-only access to db1 tables with the public schema:

  1. Add the postgresql provider to the required_providers section in the provider configuration file:

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

  2. Open the Terraform configuration file with the infrastructure plan.

  3. Add the yandex_mdb_postgresql_user resource:

    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. Add the postgresql provider and configure its access permissions to 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. Add the postgresql_grant resource with the following attributes:

    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. Initialize Terraform once again:

    terraform init

  7. Make sure the settings are correct.

    1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.

    2. Run this command:

      terraform validate

      Terraform will show any errors found in your configuration files.

  8. Confirm updating the resources.

    1. Run this command to view the planned changes:

      terraform plan

      If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.

    2. If everything looks correct, apply the changes:

      1. Run this command:

        terraform apply

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

Revoking permissions

  1. Connect to the db1 database under the database owner account.

  2. Revoke the relevant access permissions from user2.

    Examples:

    1. Revoke all privileges for tables in the myschema schema:

      REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA myschema FROM user2;

    2. Revoke access for the Products table in the default public schema:

      REVOKE SELECT ON public.Products FROM user2;

    3. Revoke access to all tables in myschema:

      REVOKE SELECT ON ALL TABLES IN SCHEMA myschema FROM user2;

    4. Revoke access for objects in the myschema schema:

      REVOKE USAGE ON SCHEMA myschema FROM user2;

  1. Open the Terraform configuration file you used to grant privileges.

  2. In the postgresql_grant section, remove the privilege you want to revoke from the privileges parameter.

    To revoke all privileges, leave the privileges array empty or completely remove the postgresql_grant resource.

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

  3. Make sure the settings are correct.

    1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.

    2. Run this command:

      terraform validate

      Terraform will show any errors found in your configuration files.

  4. Confirm updating the resources.

    1. Run this command to view the planned changes:

      terraform plan

      If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.

    2. If everything looks correct, apply the changes:

      1. Run this command:

        terraform apply

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

Previous
DB user management
Next
Managing extensions