I’m building an internal tool and need permissions-aware search over Postgres data — how should I do it?

For permissions-aware search over Postgres, the cleanest approach is usually:

1. Model permissions in Postgres
2. Filter at query time with SQL
3. Use Postgres full-text / trigram indexes for search
4. Optionally precompute searchable views/materialized tables for scale

Here’s a practical way to think about it.

---

1) Put authorization in the data model

You want search results to be filtered by the same rules that govern normal access.

Typical patterns:

• User → role → permission
• User → group/team → resource
• Row-level ownership
• Tenant scoping
• Document ACLs: a document is visible to users/groups/roles listed in an access table

A common schema:

documents (
  id bigint primary key,
  tenant_id bigint not null,
  title text,
  body text,
  created_at timestamptz
)

document_acl (
  document_id bigint references documents(id),
  principal_type text,   -- 'user', 'group', 'role'
  principal_id bigint,
  can_read boolean
)

Then you determine the current user’s principals and filter results accordingly.

---

2) Prefer database-enforced security if possible

If your app can express access rules in SQL, PostgreSQL can enforce them directly using:

• Row Level Security (RLS) — best when each row has clear access rules
• Security-definer functions — when you need encapsulated checks
• Views — for simpler “safe” read surfaces

When to use RLS

Use RLS if access is mostly based on tenant, ownership, or a row ACL. Example:

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY doc_read_policy
ON documents
FOR SELECT
USING (
  tenant_id = current_setting('app.tenant_id')::bigint
);

Then your app sets:

SET app.tenant_id = '123';

This is great because search queries automatically respect permissions.

If your authorization is more complex than a single predicate, you can still use RLS, but it may become harder to maintain.

---

3) Search with Postgres-native search

For keyword/full-text search

Use tsvector + GIN:

ALTER TABLE documents
ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
  setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
  setweight(to_tsvector('english', coalesce(body, '')), 'B')
) STORED;

CREATE INDEX documents_search_vector_idx
ON documents USING gin (search_vector);

Query:

SELECT id, title
FROM documents
WHERE search_vector @@ plainto_tsquery('english', 'budget plan')
ORDER BY ts_rank(search_vector, plainto_tsquery('english', 'budget plan')) DESC;

For fuzzy matching / partial strings

Use pg_trgm:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX documents_title_trgm_idx
ON documents USING gin (title gin_trgm_ops);

Useful for typos, substring search, prefix-ish matching.

---

4) Combine search and permissions in one query

This is the key part: search first, but only among rows the user is allowed to see.

Example with ACL table:

SELECT d.id, d.title
FROM documents d
JOIN document_acl a
  ON a.document_id = d.id
WHERE a.can_read = true
  AND (
    (a.principal_type = 'user'  AND a.principal_id = $1)
    OR
    (a.principal_type = 'group' AND a.principal_id = ANY($2))
  )
  AND d.search_vector @@ plainto_tsquery('english', $3)
GROUP BY d.id, d.title
ORDER BY ts_rank(d.search_vector, plainto_tsquery('english', $3)) DESC
LIMIT 20;

Where:

• $1 = user id
• $2 = array of group ids
• $3 = search text

Important

If your ACL table can have multiple matching rows per document, either:

• use EXISTS, or
• deduplicate with GROUP BY / DISTINCT

EXISTS is often cleaner and performs well:

SELECT d.id, d.title
FROM documents d
WHERE d.search_vector @@ plainto_tsquery('english', $3)
  AND EXISTS (
    SELECT 1
    FROM document_acl a
    WHERE a.document_id = d.id
      AND a.can_read = true
      AND (
        (a.principal_type = 'user' AND a.principal_id = $1)
        OR (a.principal_type = 'group' AND a.principal_id = ANY($2))
      )
  )
ORDER BY ts_rank(d.search_vector, plainto_tsquery('english', $3)) DESC
LIMIT 20;

---

5) For performance, avoid “search everything then filter in app”

Don’t fetch broad results and filter in application code unless data is tiny. That leads to:

• security bugs
• wasted DB work
• poor pagination
• inconsistent ranking

Instead, let PostgreSQL do both filtering and ranking.

---

6) For large-scale ACLs, precompute access sets

If ACL checks get expensive, consider a denormalized access table:

document_visible_to (
  document_id bigint,
  user_id bigint
)

or

document_visible_to_group (
  document_id bigint,
  group_id bigint
)

Then a query becomes simpler and faster:

SELECT d.id, d.title
FROM documents d
JOIN document_visible_to v ON v.document_id = d.id
WHERE v.user_id = $1
  AND d.search_vector @@ plainto_tsquery('english', $2)
ORDER BY ts_rank(d.search_vector, plainto_tsquery('english', $2)) DESC
LIMIT 20;

This is a classic tradeoff:

• more write complexity
• much faster read/search performance

Good when permissions change less frequently than searches happen.

---

7) Consider materialized search tables for complex search

If your documents are made of multiple tables or computed fields, create a separate searchable projection:

document_search (
  document_id bigint primary key,
  tenant_id bigint,
  title text,
  body text,
  search_vector tsvector
)

Populate it from source tables, keep it in sync via:

• triggers
• background jobs
• periodic refresh
• application writes

Then search only that table, joined back to ACLs or filtered by RLS.

---

8) Use RLS + search together when you can

A very solid architecture is:

• enable RLS on the core table
• set session variables for user/tenant
• build GIN indexes on search vectors
• query normally

This gives you:

• centralized authorization
• fewer app-side mistakes
• good performance if indexes are correct

---

9) Be careful with pagination and ranking

If results can disappear due to permissions, use stable pagination:

• keyset pagination preferred
• order by rank + id as tiebreaker

Example:

ORDER BY rank DESC, id DESC

Then paginate using the last seen rank, id.

---

10) Practical recommendation by complexity

Simple internal tool

Use:

• Postgres full-text search
• ACL table
• EXISTS filter in SQL

Multi-tenant app with strong isolation

Use:

• RLS on all sensitive tables
• session variables for tenant/user
• search indexes on the same tables or search projections

High-scale / complex permissions

Use:

• precomputed visibility tables
• search projection table
• possibly a separate search engine only if Postgres can’t keep up

---

11) When to consider an external search engine

Stick with Postgres unless you need:

• advanced relevance tuning
• faceting
• highlighting
• very large corpora
• near-real-time indexing at huge scale

Even then, you’ll still need to enforce permissions somewhere. Common pattern:

• search engine returns candidate IDs
• Postgres verifies permissions before serving results

Never rely on the search engine alone for authorization unless it’s explicitly designed and audited for that.

---

12) A good default architecture

If I were building this for an internal tool, I’d start with:

• documents table with search_vector
• document_acl table
• EXISTS-based query
• GIN index on search_vector
• optional RLS for defense in depth
• if performance degrades, add a precomputed visibility table

That gets you a secure, maintainable system without over-engineering.

---

If you want, I can sketch:

1. a concrete schema for your use case, or
2. the exact SQL for tenant + role + group permissions, or
3. a Postgres RLS-based implementation.