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.
Column preset restricts mutation access for configured role
Column preset values are not overridable by the user. ie. If a column has a preset defined for a given role, access to the column for the mutation will be restricted for users with that role.
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 pg_create_insert_permission metadata API:
POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin
{
"type" : "pg_create_insert_permission",
"args" : {
"source": "<db_name>",
"table" : "article",
"role" : "user",
"permission" : {
"check" : {},
"set":{
"user_id":"X-Hasura-User-Id"
},
"columns":["title", "content", "rating"]
}
}
}
Note
To set a column preset for a nested object's column, simply set the corresponding column preset in the remote table.
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 theuser_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:

Note
Not passing the configured header will result in a run-time error:
{
"errors": [
{
"path": "$",
"error": "\"x-hasura-user-id\" header is expected but not found",
"code": "not-found"
}
]
}