Postgres: Setting values for fields using role-based column presets¶

Introduction¶

Let’s say you want certain fields to have their values set automatically using session variables or fixed values when a row is created/updated with a particular user role.

Hasura GraphQL engine’s column presets let you define role-based values for any field/column. These values can either be a session variable value or a static value.

Example: Say we have a field user_id in a table article which is to be set to the id of the user, from the value of the user’s session variable whenever a new row is added to the article table.

Step 1: Configure a column preset¶

Console

CLI

API

The column preset option is available under the Permissions tab of a table. Open the console and head to Data -> [article] -> Permissions:

Enable the column preset option to define presets for one or more columns. For each column, you can pick between setting the preset using a static value or from a session variable.

For our chosen example, we’ll use the from session variable option and configure the user_id column to be automatically populated based on the value of the X-Hasura-User-Id session variable.

You can set column presets in the tables.yaml file inside the metadata directory:

 - table:
     schema: public
     name: article
   insert_permissions:
   - role: user
     permission:
       check: {}
       set:
         user_id: x-hasura-User-Id
       columns:
       - content
       - rating
       - title
       backend_only: false

Apply the metadata by running:

hasura metadata apply

You can add column presets by using the create_insert_permission metadata API:

POST /v1/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type" : "create_insert_permission",
  "args" : {
    "table" : "article",
    "role" : "user",
    "permission" : {
      "check" : {},
      "set":{
        "user_id":"X-Hasura-User-Id"
      },
      "columns":["title","content", "rating"]
    }
  }
}

Step 2: Run an insert mutation¶

Head to the GraphiQL interface in the console and try making an insert mutation on the article table with the following headers (to run through this example, don’t forget to also grant the user role sufficient permissions to select from the article table):

• X-Hasura-Role –> user (to test the behaviour for the configured role)
• X-Hasura-User-Id –> 1 (this is the value we should expect in the user_id field)

As mentioned earlier, you’ll notice when you add the X-Hasura-Role header that the field, user_id, is no longer available as the mutation type’s field:

Now, if we run the following insert mutation, we’ll see that the user_id field is indeed being set with the value passed in the X-Hasura-User-Id variable:

{
    "errors": [
      {
        "path": "$",
        "error": "\"x-hasura-user-id\" header is expected but not found",
        "code": "not-found"
      }
    ]
}

Also see¶

• Postgres: Setting default values for fields using Postgres defaults
• Postgres: Setting values of fields using SQL functions