Hey! We’d appreciate some guidance on whether we’re using SpiceDB correctly for resource ownership and list queries. Our resources are stored in PostgreSQL, but their workspace relationship currently exists only in SpiceDB:

definition resource {
    relation workspace: workspace

    permission view = workspace->can_view_resources
    permission edit = workspace->can_edit_resources
    permission manage = workspace->can_manage_resources
}

To list resources, we currently: - Use fully consistent ReadRelationships to retrieve every resource ID associated with the workspace. - Pass those IDs to PostgreSQL using WHERE id IN (...). - Apply filtering, sorting, counting, and pagination in PostgreSQL. A real request for 50 rows generated a query containing hundreds of IDs and took approximately six seconds:

SELECT *
FROM resources
WHERE id IN (<hundreds of IDs>)
  AND deleted_at IS NULL
ORDER BY created_at DESC
LIMIT 50;

We haven’t confirmed that the large IN clause is the only cause, but the query size grows with the workspace’s total number of resources rather than the requested page size. We’re considering making PostgreSQL authoritative for ownership and discoverability:

resources.owner_workspace_id
resource_workspace_access(resource_id, workspace_id)

SpiceDB would remain responsible for deciding whether a workspace or user can view, edit, or manage a resource. My questions are: - Is our current ReadRelationships → PostgreSQL IN (...) approach an intended SpiceDB pattern, or are we using SpiceDB incorrectly? - Is PostgreSQL ownership/discovery combined with SpiceDB action authorization a better architectural boundary? - For resources shared across workspaces, where should that relationship live, and how would you keep PostgreSQL and SpiceDB consistent? Any feedback or examples from similar systems would be very helpful.

there's documentation here: https://authzed.com/docs/spicedb/modeling/protecting-a-list-endpoint if you aren't using the [postgres foreign data wrapper](https://github.com/authzed/spicedb/tree/main/internal/fdw) i'd recommend at least giving it a look. i'd also recommend that you don't use

ReadRelationships

for checking access - it bypasses SpiceDB's caching layers. it's better to use

LookupResources

or

LookupSubjects

, whichever one fits your use case.

Thanks, this is very helpful, especially the clarification about

ReadRelationships

. We will also take a closer look at the PostgreSQL FDW. We have two categories of resources. Non-shareable resources These belong to exactly one workspace, and access is uniform. A user can either access all resources of that type in the workspace or none. For these, PostgreSQL stores

workspace_id

. SpiceDB only contains workspace-level action permissions such as

can_view_resources

,

can_edit_resources

, and

can_manage_resources

. The list flow would be: 1. Check the workspace-level permission once in SpiceDB. 2. Query PostgreSQL using

workspace_id

. 3. Do not represent every individual resource in SpiceDB. Shareable resources These can be associated with multiple workspaces. PostgreSQL stores only the resource-to-workspace associations, allowing it to handle filtering, sorting, and pagination:

text
resource-1 -> workspace-a
resource-2 -> workspace-a
resource-3 -> workspace-a

SpiceDB remains the source of truth for the precise relationship:

text
resource:1#owner@workspace:a
resource:2#owner@workspace:a
resource:3#read_only@workspace:a

The resource definition could be simplified to:

zed
definition resource {
    relation owner: workspace
    relation read_only: workspace

    permission view =
        owner->can_view_resources +
        read_only->can_view_resources

    permission edit =
        owner->can_edit_resources

    permission manage =
        owner->can_manage_resources
}

This assumes shared workspaces only require read-only access, while the owning workspace can potentially view, edit, and manage the resource. For a list endpoint, PostgreSQL would first return a page of associated resources. We could then use

CheckBulkPermissions

to evaluate

view

for the current user across those candidates. If the UI also needs to display whether the workspace is the owner or has read-only access, we could expose relation-backed permissions such as

is_owner

and

is_read_only

. Would this be a better architecture for our use case? In particular, does using PostgreSQL for resource discovery and SpiceDB for the relationship type and effective permissions seem like the correct pattern?

yeah, that sounds sane to me. prefilter vs postfilter (LookupResources and BulkCheck respectively) comes down to how big a page is compared to how many resources a user typically has access to. one nice thing about the postgres FDW is that it can make that decision dynamically and abstract over the concern - you invoke SpiceDB by joining against a synthetic table, which then runs through the postgres query planner.