Assigning privileges and roles to PostgreSQL users

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

By default, the CLI uses the folder specified when creating the profile. To change the default folder, use the yc config set folder-id <folder_ID> command. You can also set a different folder for any specific command 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 describing your infrastructure.

   For more information about creating this file, see this guide.

   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. Validate your configuration.

   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 resource changes.

   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 place it in an 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 get the cluster ID from the folder’s cluster list, and the username from the cluster’s user list.

   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: Comma-separated list of settings you want to update.

     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. Check the server response to make sure your request was successful.

1. Get an IAM token for API authentication and place it in an 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 settings you want to update as an array of paths[] strings.

     Here, we provide only one setting.

   • 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 get the cluster ID from the folder’s cluster list, and the username from the cluster’s user list.

5. Check the server response to make sure your request was successful.

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.

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 this guide.

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.
   • objects (optional): 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.
   • with_grant_option (optional): If true, a user with the privileges can grant the same privileges to other users. The default value is false.

5. Initialize Terraform once again:

   terraform init
   

6. Validate your configuration.

   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 resource changes.

   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.

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 resource changes.

   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.

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.

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.

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.