Access Control¶

AkasicDB vector search follows the PostgreSQL access control model. A vector is a column type, and a table with a vector column is a regular PostgreSQL table. Table privileges and Row-Level Security (RLS) therefore apply to vector search as they do to any other query, without a separate mechanism.

This page is based on Row Security Policies in the PostgreSQL 15 documentation, with the examples adapted to vector search.

Example Setup¶

The examples on this page use the following table, which stores documents with an embedding and a department.

CREATE TABLE documents (
  id         int PRIMARY KEY,
  title      text NOT NULL,
  department text NOT NULL,
  embedding  vector(3)
);

CREATE INDEX documents_embedding_idx
  ON documents
  USING vectoron (embedding vector_l2_ops);

INSERT INTO documents VALUES
  (1, 'Engineering roadmap', 'engineering', '[0.9, 0.1, 0.0]'),
  (2, 'API design guide',    'engineering', '[0.8, 0.2, 0.1]'),
  (3, 'Incident review',     'engineering', '[0.2, 0.9, 0.1]'),
  (4, 'Quarterly budget',    'finance',     '[0.1, 0.8, 0.2]'),
  (5, 'Expense policy',      'finance',     '[0.7, 0.3, 0.2]'),
  (6, 'Audit checklist',     'finance',     '[0.3, 0.2, 0.9]');

Table and Column Privileges¶

Vector search reads rows through SELECT, so a role needs the SELECT privilege on the target table.

CREATE ROLE analyst;
GRANT SELECT ON documents TO analyst;

Column-level privileges also apply. A role that lacks the privilege on the vector column cannot reference it in ORDER BY, so it cannot run vector search even if it can read the other columns.

CREATE ROLE viewer;
GRANT SELECT (id, title) ON documents TO viewer;

Here viewer can read id and title but a query that orders by embedding fails with a permission error.

Row-Level Security¶

Privileges decide whether a role can query a table. RLS decides which rows the role can see. RLS is disabled by default; enable it per table.

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

Once enabled, the table is default-deny: a role sees no rows until a policy grants access. Policies are created with CREATE POLICY.

The USING expression selects the rows a role can see in SELECT, UPDATE, and DELETE.
The WITH CHECK expression selects the rows a role can write in INSERT and UPDATE. If omitted, it defaults to the USING expression.

The RLS USING expression applies to vector search in the same way as to other queries. Rows not allowed by the policy are not included in the search results, and LIMIT applies to results that pass the policy.

Example: Department-Scoped Vector Search¶

The following setup restricts each role to documents in its own department. doc_admin can see and modify everything.

CREATE ROLE doc_admin;
CREATE ROLE engineering;
CREATE ROLE finance;
CREATE ROLE alice;
CREATE ROLE bob;
GRANT engineering TO alice;
GRANT finance TO bob;

GRANT SELECT ON documents TO engineering, finance;
GRANT SELECT, INSERT, UPDATE, DELETE ON documents TO doc_admin;

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY admin_all ON documents TO doc_admin
  USING (true)
  WITH CHECK (true);

CREATE POLICY engineering_read ON documents FOR SELECT TO engineering
  USING (department = 'engineering');

CREATE POLICY finance_read ON documents FOR SELECT TO finance
  USING (department = 'finance');

PostgreSQL does not separate users from groups; both are roles. In this example, engineering and finance are group-style roles that represent departments, while alice and bob are individual user roles registered as their members with statements like GRANT engineering TO alice;. Privileges and policies are attached to the department roles, so onboarding a new user only takes adding the user as a member of a department role.

A policy created with TO role also applies to members of that role, so alice gets engineering_read and bob gets finance_read. In a real environment, a role that a person connects with needs the LOGIN attribute, as in CREATE ROLE alice LOGIN PASSWORD '...';. This example works without LOGIN because it switches roles with SET ROLE from an administrator session.

Top-k Results per Role¶

Every role runs the same top-3 query. Use SET ROLE to switch roles in a session, and RESET ROLE to switch back.

Note

Running SET ROLE from a non-superuser session requires the login role to be a member of the target role; otherwise the command fails with permission denied to set role. Grant the membership first from an administrator session, as in GRANT alice TO <login role>;.

SELECT id, title, embedding <-> '[1,0,0]'::vector AS distance
  FROM documents
 ORDER BY embedding <-> '[1,0,0]'::vector
 LIMIT 3;

When doc_admin runs the query above, it returns the following results.

SET ROLE doc_admin;

 id |        title        | distance
----+---------------------+----------
  1 | Engineering roadmap |     0.02
  2 | API design guide    |     0.09
  5 | Expense policy      |     0.22

When alice runs the same query, only engineering documents are returned.

RESET ROLE;
SET ROLE alice;

 id |        title        | distance
----+---------------------+----------
  1 | Engineering roadmap |     0.02
  2 | API design guide    |     0.09
  3 | Incident review     |     1.46

When bob runs the same query, only finance documents are returned.

RESET ROLE;
SET ROLE bob;

 id |       title       | distance
----+-------------------+----------
  5 | Expense policy    |     0.22
  6 | Audit checklist   |     1.34
  4 | Quarterly budget  |     1.49

Search results for each role contain only rows allowed by the applicable policies. Rows not allowed by a policy are not included. LIMIT k is an upper bound on the number of returned rows. Fewer than k rows may be returned when fewer rows are accessible or when search does not find enough results. RLS guarantees access control, not result count or recall.

RLS and Vector Indexes¶

Vector search with RLS returns only rows that pass the applicable policies. When few rows pass the policies, the query may return fewer rows than requested by LIMIT or take longer to complete. Test result count, recall, and latency using representative policies and data distributions.

Keep policy expressions simple, preferably as comparisons against columns in the current row. Subqueries that access other tables and expensive functions can slow down search.

Application Integration Patterns¶

The examples above switch roles with SET ROLE to demonstrate the behavior. In production, what maps to a database role depends on the layer that owns the access boundary.

Per-service roles. When multiple services share one database, giving each service its own role with minimal privileges is common practice. It applies the least-privilege principle, makes per-service activity identifiable in monitoring and audit logs, and limits the impact of a leaked credential. If the access boundary is the service itself, table and column privileges are usually sufficient and RLS is not required.

Per end user or tenant. Creating a database role for every end user is uncommon; it scales poorly and interacts badly with connection pooling. The standard pattern is a single shared service role, a session variable set per transaction, and a policy that reads the variable with current_setting. The app.department below is not a reserved setting but an arbitrary session variable named by the application; PostgreSQL allows creating and reading any dotted-name variable without a declaration.

app.department does not identify the logged-in user. Anyone who can execute SQL as app_service can change this value. The service must set it from the department stored in the logged-in user's account. If the service uses a department value supplied in an API request or allows the user to connect as app_service, the user can access rows from another department.

CREATE ROLE app_service;
GRANT SELECT ON documents TO app_service;

CREATE POLICY app_department_scope ON documents FOR SELECT TO app_service
  USING (department = current_setting('app.department', true));

The application runs every request inside a transaction and sets the variable with SET LOCAL.

BEGIN;
SET LOCAL app.department = 'engineering';

SELECT id, title
  FROM documents
 ORDER BY embedding <-> '[1,0,0]'::vector
 LIMIT 3;
COMMIT;

Each transaction returns only rows allowed by the policy for the scope specified by the session variable. This allows per-tenant access control with a shared connection pool.

A few points keep this pattern safe.

Use SET LOCAL inside a transaction rather than SET, so the value never leaks to the next request that reuses the pooled connection.
The second argument true of current_setting makes it return NULL instead of an error when the variable is not set. A NULL comparison makes the policy match no rows, so a request that forgets to set the variable sees nothing rather than everything.
SET ROLE also works with a shared connection, but a missing RESET ROLE leaks the role to the next request. The session variable pattern with SET LOCAL avoids this class of mistakes.

Combining Policies¶

Policies are permissive by default: when several policies apply to a query, a row is visible if any one of them accepts it (OR). Restrictive policies, created with AS RESTRICTIVE, must all accept the row (AND) and are used to tighten access on top of permissive policies. For example, the following policy limits doc_admin access to local connections only.

CREATE POLICY admin_local_only ON documents AS RESTRICTIVE TO doc_admin
  USING (pg_catalog.inet_client_addr() IS NULL);

At least one permissive policy must accept a row; restrictive policies alone grant nothing.

Bypassing RLS¶

RLS does not apply in the following cases, which matters when validating a policy setup.

The table owner bypasses RLS by default. To apply policies to the owner as well, use ALTER TABLE documents FORCE ROW LEVEL SECURITY;.
Superusers and roles with the BYPASSRLS attribute always bypass RLS.
SET row_security = off; does not disable filtering for ordinary users; instead, queries that would be filtered fail with an error. This is useful for backup tools that must not silently omit rows.

When you test policies from a superuser session, switch with SET ROLE first. Otherwise every row stays visible and the policy appears to have no effect.

Notes¶

Referential integrity checks, such as foreign key constraints, bypass RLS. A carefully crafted insert can reveal whether a hidden key value exists, so consider this channel when the existence of a row is itself sensitive.
TRUNCATE and REFERENCES privileges are not subject to RLS.
A vector index does not change the set of rows allowed by RLS. However, index type and search settings can affect result count, recall, and performance.