Query Policies

A query policy limits how a querying company can use the columns an access rule exposes. This page describes the policy object, how Narrative applies it to a query, and how it validates a policy. For the reasons to use one, see Query policies. For step-by-step instructions, see Applying query policies.

The policy object

{
  "default": { "projection": {}, "group_by": {}, "join": {} },
  "usage_rules": [ { "attribute": "…", "projection": {}, "group_by": {}, "join": {} } ],
  "join_required_rule": { "match": [ { "keys": [ { "attribute": "…", "form": "raw" } ] } ] }
}

Field	Type	Description
`default`	object or `null`	Rules for every column that no `usage_rules` entry covers. Holds optional `projection`, `group_by`, and `join` rules. A usage it leaves out is denied
`usage_rules`	array or `null`	Rules for individual columns. See Usage rules
`join_required_rule`	object or `null`	Requires every query to join the access rule to the querying company’s own data. See Join-required rule

All three fields are optional, and the object accepts no other fields. An empty policy, {}, denies every use of every column.

Columns: attributes and fields

A policy names a column in one of two ways:

Key	Names	Example
`attribute`	A Rosetta Stone attribute that the access rule’s NQL selects from `_rosetta_stone`. Use a dot for a property of the attribute	`email`, `id.value`
`field`	Any other column of the access rule, such as a dataset column it selects. Use a dot for a nested property of the column	`segment_id`, `address.zip`

Every column a policy names must be one the access rule’s NQL selects:

An attribute must be selected from _rosetta_stone. An access rule that selects a dataset column mapped to email, but not _rosetta_stone.email itself, does not expose the email attribute to a policy.
A field that renames an attribute is that attribute. When the access rule selects _rosetta_stone.email AS email, {"field": "email"} and {"attribute": "email"} name the same column, so a policy can hold a rule for only one of them.

Usage rules

Each usage_rules entry sets rules for one column.

Field	Type	Description
`attribute`	string	The attribute this entry covers. Set exactly one of `attribute` and `field`
`field`	string	The field this entry covers. Set exactly one of `attribute` and `field`
`projection`	projection rule	Optional
`group_by`	group-by rule	Optional
`join`	join rule	Optional

How a column’s rule is resolved

Narrative resolves each usage of a column (projection, group by, and join) on its own, taking the first rule it finds:

The usage_rules entry for the column, if that entry sets the usage
The entry for each enclosing column, from the nearest up. A rule for address covers address.zip unless an entry for address.zip sets the same usage
The default rule for the usage
Deny

An entry that sets only projection therefore takes group_by and join from the enclosing column’s entry, or from default.

Rules and modes

Every rule has a required mode:

Mode	Effect
`allow`	Permits every use, including the raw value
`deny`	Permits no use
`restrict`	Permits the column only inside the functions the rule lists, and never the raw value

Under restrict, each kind of rule accepts these function lists:

Rule	`scalar_operators`	`aggregate_operators`	`external_operators`
`projection`	Yes	Yes	Yes
`group_by`	Yes	No	No
`join`	Yes	No	No

scalar_operators — Scalar functions, such as SHA2, UPPER, or SUBSTRING
aggregate_operators — Aggregate functions, such as COUNT or SUM. Add _DISTINCT to a name to permit its distinct form: COUNT_DISTINCT permits COUNT(DISTINCT …). Listing COUNT does not permit COUNT(DISTINCT …)
external_operators — Functions that send values outside the query: AI_COMPLETE, AI_CONVERSATION, CALL_MODEL_FUNCTION, EMBED_TEXT_768, and EMBED_TEXT_1024. See External functions

Function names are case-insensitive. A list a rule leaves out is empty. Under allow and deny, the lists are not used. When a query applies several functions to a column, each one must be listed. UPPER(SHA2(email, 256)) needs both SHA2 and UPPER. Window functions are never permitted under restrict.

What each usage covers

A policy rules on three usages. Each query clause counts as one of them:

Usage	Query clauses
`projection`	The column’s value reaches the query result, directly or through functions. Includes `UNNEST` of the column
`group_by`	`GROUP BY` on the column. Also `UNION`, `INTERSECT`, and `EXCEPT`, which compare whole rows
`join`	The column appears in a join’s `ON` condition. Comparisons (`=`, `<`, and so on), `AND`, and `OR` in the condition are not functions on the column, so `restrict` ignores them

These uses are not checked:

WHERE filters
ORDER BY
A column a subquery selects but whose value never reaches the result. SELECT COUNT(1) FROM (SELECT email FROM …) reads no value of email
UNION ALL
The nio_last_modified_at column that DELTA filters on

Because filters are not checked, a column whose projection is denied can still appear in a WHERE clause. A query such as WHERE email = '[email protected]' can test whether a value is present. Leave a column out of the access rule’s NQL entirely when partners must not be able to filter on it.

External functions

External functions are checked wherever a query applies them to a column, filters included, against the column’s projection rule:

Projection mode	External functions permitted
`allow`	All
`deny`	None
`restrict`	Only those in `external_operators`

Listing an external function in external_operators does not permit it in a join or group_by. Those rules accept only scalar_operators, and an external function is not a scalar function.

Rosetta Stone attributes the access rule does not select

When an access rule has a query policy, a query can read a Rosetta Stone attribute through it only if the access rule’s NQL selects that attribute from _rosetta_stone. A query that reads any other attribute through the access rule, such as one mapped from a column the rule selects or a derived attribute, gets no rows from it.

Join-required rule

A join-required rule makes the access rule usable only in queries that join it to the querying company’s own data.

Field	Type	Description
`match`	array, at least one entry	Ways to satisfy the rule. A query must satisfy one entry
`match[].keys`	array, at least one entry	Columns that must all be joined for the entry to be satisfied
`match[].keys[].attribute`	string	An attribute key. Set exactly one of `attribute` and `field`
`match[].keys[].field`	string	A field key. Set exactly one of `attribute` and `field`
`match[].keys[].form`	`"raw"` or `{"operator": "<name>"}`	`"raw"` requires the key itself on the access rule’s side of the join. `{"operator": "SHA2"}` requires exactly that one function applied to the key, and nothing else

A query satisfies a match entry when all of these hold:

The join is direct. Each key appears in an ON equality whose other side reads columns of one table: a dataset the querying company owns, or the querying company’s own Rosetta Stone data (company_data.rosetta_stone, or rosetta_stone under its own company slug). The other side can apply any functions.
One table matches every key. All keys of the entry are equated with columns of the same consumer table.
Each key is in its form. A raw key has no function applied on the access rule’s side. An operator key has exactly the named function applied, so SUBSTRING(SHA2(email, 256), 1, 4) does not satisfy {"operator": "SHA2"}.
Every join is an inner join. A LEFT, RIGHT, or FULL join anywhere in the tree of joins that contains the access rule breaks the rule, on either side of the access rule.

These do not satisfy a match entry:

Reading the access rule without a join, or through a CROSS JOIN
Joining it only to another company’s access rule or dataset, or to Rosetta Stone data the querying company does not own
Equating the key only with a literal, or with a subquery
Joining it to the consumer’s table through an intermediate table, or in a separate join tree
An OR in the ON condition, or a comparison other than equality between the two tables, such as ar.name > c.full_name. A condition on one table, such as c.status = 'active', can sit beside the key equality

A query can join the consumer’s table to further tables of its own, or to other companies’ tables, once the key equality is in place. Each key must also be joinable under the policy’s own rules. The column’s resolved join rule must be allow, or restrict with the key’s operator listed in its scalar_operators. A raw key needs allow.

How Narrative enforces a policy

Narrative checks the policy when it compiles a query, once for each access rule the query reads. That includes access rules a query reaches through Rosetta Stone, such as narrative.rosetta_stone. When the query breaks the policy, Narrative leaves that access rule out of the query. The access rule contributes no rows, and the query runs on the access rules that remain. The query does not fail, and the response does not report which access rule was left out or why. Only reads through an access rule are checked. A query that reads a dataset directly, as you do with your own data under company_data, is not.

Managing a policy through the API

Method	Path	Action	Who can call it
`POST`	`/v2/access-rules`	Creates an access rule. Include `query_policy` in the body to create it with a policy	The company creating the access rule
`GET`	`/v2/access-rules/{access_rule_id}/query-policy`	Returns the policy. Returns `404` when the access rule has none	Any company that can view the access rule
`PUT`	`/v2/access-rules/{access_rule_id}/query-policy`	Attaches a policy, replacing the current one. Returns the stored policy	The owning company
`DELETE`	`/v2/access-rules/{access_rule_id}/query-policy`	Removes the policy. Returns `204`, also when the access rule had none	The owning company

Every v2 access rule response includes query_policy, which is null when the access rule has none. That includes responses to companies the rule is shared with, so a partner can read the terms before querying. For request and response schemas, see the API reference.

Validation

Narrative validates a policy against the access rule’s NQL when you create an access rule with one and when you PUT one. An invalid policy returns 400. A POST with an invalid policy creates nothing, and a PUT with one leaves the current policy in place. error_description lists every problem, separated by semicolons. Each problem starts with where it is in the policy:

{
  "error": "invalid_request",
  "error_description": "invalid query policy: usage_rules[attribute=email]: attribute 'email' is not exposed by this access rule; default.projection.aggregate_operators: UPPER is not an aggregate function"
}

Message	Cause
`attribute '<name>' is not exposed by this access rule`	The access rule’s NQL does not select the attribute from `_rosetta_stone`
`field '<name>' is not exposed by this access rule`	The access rule’s NQL does not select the field
`attribute '<name>' has more than one rule`	Two `usage_rules` entries name the same column, including an attribute entry and a field entry for a column that renames it
`<NAME> is not a known function`	No NQL function has that name
`<NAME> is an aggregate or external function, not a scalar one`	A `scalar_operators` list, or a join key’s `form.operator`, names an aggregate or external function
`<NAME> is not an aggregate function`	An `aggregate_operators` list names another kind of function, or adds `_DISTINCT` to an aggregate with no distinct form
`<NAME> is not an external function`	An `external_operators` list names a function that is not external
`the join rule of attribute '<name>' does not permit a join`	A join-required key’s column resolves to `join` mode `deny`
`attribute '<name>' is a raw key, but its join rule is restrict`	A `raw` join-required key’s column resolves to `join` mode `restrict`
`attribute '<name>' uses <NAME>, which its join rule does not list`	A join-required key’s operator is missing from its column’s `join.scalar_operators`
`the nql of this access rule is missing or does not compile, so no field can be checked against it`	The access rule’s NQL cannot be compiled

Messages about a field read field '<name>' where the table shows attribute '<name>'. A request body that does not match the schema, such as an unknown field or mode, also returns 400.

Example

A company with the slug acme shares an access rule named customer_segments_ar. Its NQL selects the email attribute and a segment_id column:

SELECT
  company_data.customer_segments._rosetta_stone.email AS email,
  company_data.customer_segments.segment_id
FROM company_data.customer_segments

This policy lets a partner see each segment and how many distinct email addresses it holds, but never an email address itself. The partner can only use the access rule by joining it to their own data on the SHA-256 hash of the email:

{
  "default": {
    "projection": { "mode": "deny" },
    "group_by": { "mode": "deny" },
    "join": { "mode": "deny" }
  },
  "usage_rules": [
    {
      "attribute": "email",
      "projection": { "mode": "restrict", "aggregate_operators": ["COUNT_DISTINCT"] },
      "join": { "mode": "restrict", "scalar_operators": ["SHA2"] }
    },
    {
      "field": "segment_id",
      "projection": { "mode": "allow" },
      "group_by": { "mode": "allow" }
    }
  ],
  "join_required_rule": {
    "match": [
      { "keys": [ { "attribute": "email", "form": { "operator": "SHA2" } } ] }
    ]
  }
}

This query satisfies the policy. It joins on SHA2 of the email, which the join rule lists and the join-required key names. It returns the email only inside COUNT(DISTINCT …), and it selects and groups by segment_id, which the policy allows:

SELECT
  ar.segment_id,
  COUNT(DISTINCT ar.email) AS matched_emails
FROM acme."customer_segments_ar" ar
INNER JOIN company_data.crm_hashed c
  ON SHA2(ar.email, 256) = c.email_sha256
GROUP BY ar.segment_id

The same query with SELECT ar.email would get no rows from the access rule, because a raw email reaching the result breaks the projection rule. Dropping the join would get none either, because nothing would satisfy the join-required rule.

Access Rules

What query policies are for and how they fit with access rules

Applying Query Policies

Attach, check, and remove a query policy

Overview

Connectors

Integrations

Workflow Reference

Webhook Reference

Access Rule Reference

Security Reference

Architecture Reference

Error Reference

API Policies

Deprecations

Model Inference Reference

Rosetta Stone Reference

UI Reference

SDKs

Glossary

Billing

The policy object

Columns: attributes and fields

Usage rules

How a column’s rule is resolved

Rules and modes

What each usage covers

External functions

Rosetta Stone attributes the access rule does not select

Join-required rule

How Narrative enforces a policy

Managing a policy through the API

Validation

Example

Access Rules

Applying Query Policies

​The policy object

​Columns: attributes and fields

​Usage rules

​How a column’s rule is resolved

​Rules and modes

​What each usage covers

​External functions

​Rosetta Stone attributes the access rule does not select

​Join-required rule

​How Narrative enforces a policy

​Managing a policy through the API

​Validation

​Example

​Related content

Access Rules

Applying Query Policies

The policy object

Columns: attributes and fields

Usage rules

How a column’s rule is resolved

Rules and modes

What each usage covers

External functions

Rosetta Stone attributes the access rule does not select

Join-required rule

How Narrative enforces a policy

Managing a policy through the API

Validation

Example

Related content