Shared Accounts, Separate Data Access

A shared login is useful across several applications. It does not establish that every application, employee or customer should be able to read the same records.

I implemented separate access boundaries for a commerce platform’s staff and customer workflows. This note describes the pattern with generic names; it omits deployment configuration and client data.

Three checks answer three questions

Hiding a button does not protect its server endpoint. A server action checks the session and operation permission before invoking shared logic. Database grants then restrict the application’s connection, independently of what the page happens to render.

For customer-owned records, PostgreSQL row-level security adds a filter based on the authenticated customer. Queries still use appropriate predicates; the policy provides an additional boundary if a caller omits one.

Identity belongs to the transaction

Connection pools reuse connections. A session-level identity setting can therefore outlive the request that supplied it.

The customer data wrapper starts a transaction, installs the verified account identity as a transaction-local setting, and runs the shared data functions on that transaction. In simplified form:

Verified session
  → begin transaction
  → set local account identity
  → execute scoped data functions
  → commit or roll back

The row policy compares the record owner with that identity. With no identity in scope, it matches no customer rows. The wrapper also rejects accidental use outside the configured scope.

The identity must come from the verified session. Accepting an account ID from the request would merely move an ownership bug into a database setting.

Test with the restricted role

A superuser can bypass row-level security, so a passing test under that role would not establish the boundary used by the application.

The integration tests create separate customers and connect with restricted roles. They check that a customer can read and update their own records, cannot access another customer’s records, and sees no rows without an identity. They also verify that unrelated applications lack access to the protected data.

Staff access has a different scope. Relevant views and exports write an access-log entry before returning protected records, and the staff database role cannot update or delete that log. This records who used an authorized capability as well as preventing unauthorized use.