citusdata / citusdata/citus

Evaluate `STABLE` functions on the coordinator for non-insert queries

Open
#8,427 1 comment 1 reaction 0 assignees View on GitHub
backlog enhancement RLS
Dominant language
C
Stars
12.8k
Forks
794
Avg merge
2d 14h
Merged PRs (30d)
31

Description

There is an existing [`TODO`](https://github.com/citusdata/citus/blob/main/src/backend/distributed/executor/citus_custom_scan.c#L336) to evaluate stable functions on the coordinator for read paths. This would be helpful for our use-case of using Citus with the row-based sharding model in a multi-tenant application that enforces tenant boundaries with Row Level Security (RLS).

Specifically, we have a `STABLE` function, `get_tenant()`, to retrieve the tenant ID (distribution column value) for a given transaction that wraps Postgres' `current_setting()`.

```sql
CREATE OR REPLACE FUNCTION get_tenant()
RETURNS uuid AS $$
SELECT NULLIF(current_setting('app.tenant_id', true), '')::uuid;
$$ LANGUAGE SQL STABLE;
```

On write paths, `get_tenant()` is executed on the coordinator to derive the distribution column value set via `DEFAULT`. Citus defers shard selection and properly routes these queries to a specific shard using the value derived from the execution of `get_tenant()`. An example table setup would be:

```sql
CREATE TABLE foo_distributed (
"id" UUID NOT NULL,
"tenant_id" UUID NOT NULL DEFAULT get_tenant(),

CONSTRAINT "foo_distributed_pkey" PRIMARY KEY ("id", "tenant_id")
);

SELECT create_distributed_table('foo_distributed', 'tenant_id');

ALTER TABLE foo_distributed ENABLE ROW LEVEL SECURITY;

CREATE POLICY foo_distributed_tenant_rls ON foo_distributed
TO application_rls
USING (tenant_id = get_tenant())
WITH CHECK (tenant_id = get_tenant());
```

However, the behavior differs for `SELECT` queries. Consider the following:

```sql
SELECT * FROM foo_distributed
WHERE tenant_id=get_tenant();
```

The above query will always result in the coordinator performing a multi-shard query. If the coordinator is able to evaluate `get_tenant()`, shard selection should be consistent and Citus can efficiently route the query to the proper shard.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.