Database access and least privilege

Who the database sees

Authentication establishes an identity. Authorisation decides what that identity may do. Signing in to the shop establishes the person; signing in to the database establishes the service’s database identity. Those are separate checks.

For Mina’s customer lookup, the service first verifies the person’s sign-in and right to read customer 1 in tenant 10. Only then does it send the lookup through its database login, shop_runtime, with tenant context 10. A request naming tenant 20 must fail that application check before it reaches the database.

The service can put tenant 10 in a query condition, or pass it as context that a database policy uses. Either way, the tenant value has to come from an authorised choice. Copying a tenant ID from a URL into the connection does not establish that right.

Tenant isolation is only one boundary. Two people within tenant 10 may have different rights to Mina’s details. A policy that allows every tenant-10 row does not decide that question. The application needs whatever customer, staff or relationship checks its task requires as well.

Privileges follow the work

Start with an operation, then identify its objects, actions, columns and row scope. “The application needs database access” is too broad to turn into a useful grant. “Checkout reads product price and stock, then creates an order and its lines” gives each privilege a reason.

The shop uses three logical identities. shop_runtime handles authorised application requests. shop_migrate changes the schema. shop_support reads selected details for a tenant it is authorised to support. They are task names, not customers.

This is a sufficient starting design for these tasks, not the union of everything a shop might eventually do. If another application operation records a payment, derive that operation’s authority separately. Giving checkout every payment privilege “for later” defeats the exercise.

SELECT, INSERT, UPDATE and DELETE permit different work. Read access does not imply update access, and an insert grant does not imply deletion. A column grant can narrow an update to stock rather than catalogue price. A real stock update also reads columns in its calculation and condition, so verify the complete statement rather than granting its leading SQL verb alone.

A support login that cannot change a row can still disclose it. Limit its selected columns and tenant scope to the lookup, and limit how long the authority lasts. An export of every customer name is a powerful operation even when it makes no database changes.

Ownership and indirect privileges

Effective authority can arrive along several paths. A direct grant is one; membership in a group with broader grants is another. Privileges available to everyone, object ownership, and callable views or routines can add more. Revoking the direct table grant removes only one path.

Ownership matters because an owner can normally change the object and its access rules. Giving the running service ownership of its tables makes a narrow list of ordinary grants misleading: the service may be able to change the boundary itself. Keep it unable to become the migration identity or grant itself broader access.

A restricted database interface can be useful. Support might be allowed to call one lookup routine or read one view rather than access the base tables. That interface must enforce its promised tenant and column scope, and the caller must have no alternative path around it. Privileged routines require particular care because they can run with another identity’s authority. PostgreSQL roles, policies and privileged code follows ownership, membership and those execution rules in detail.

Table access is not tenant access

Suppose shop_runtime can read the customers table. The application ordinarily supplies a tenant condition. One lookup accidentally leaves it out:

SELECT customer_id, name
FROM customers
WHERE customer_id = $1;

With parameter 4, this asks for Rowan. The parameter preserves the structure of the SQL: 4 is a value, not new SQL to execute. It says nothing about whether this request may read customer 4. Parameterised queries and access checks solve different problems.

Removing the customer condition as well would ask for every customer. A table grant alone allows all four rows, including Rowan’s. A correctly enforced tenant-10 row policy instead limits that same query to Mina, Tariq and Edda.

A table grant permits an action on an object. With no row restriction, it does not distinguish the tenants stored there. An application check can reject tenant 20 before SQL runs, but each query still needs the correct scope. A database-enforced row boundary can catch an omitted condition on the protected table, even when the application query is wrong.

Row-level security is one way to enforce that boundary. A policy supplies a rule for the rows a connection may read or change, in addition to its ordinary privileges. A restricted view or routine is another possible interface. Choose the interface with its bypass paths in view: keeping a filtered view while also granting unrestricted access to its base table does not enforce the filter.

What a row boundary trusts

The tenant rule compares each row’s tenant_id with the current request’s tenant context. It can make that comparison only because somebody supplied the context. In this design, that somebody is the service.

If shop_runtime can freely set the context to 20, the policy will then permit tenant-20 rows. It still protects against a forgotten query condition when context is correct. It does not protect tenant 20 from a service that chooses 20 incorrectly, or from someone who obtains the same unrestricted context-setting authority.

A stronger boundary needs an identity or assertion the caller cannot freely forge, validated by the enforcing layer. Separate tenant credentials are one possible design; a tightly controlled interface that validates the caller’s authority is another. Both introduce identity and credential management work. Moving a tenant value into a session variable alone does not create that stronger boundary.

A pool adds another question: how long does the context live? Suppose a tenant-10 request leaves its context on a session, then a tenant-20 request borrows that connection without setting its own scope. The policy still compares against 10 and can expose Mina, Tariq and Edda. The policy has applied its rule correctly to the wrong request context.

The safe setup path acquires a connection, begins the transaction, establishes the authorised context, performs the data work on that same connection, then commits or rolls back before returning it. A query that runs before setup is already outside that path. SQL at the application boundary explains how to keep one request’s connection and transaction together.

Transaction-scoped context reduces the chance of leaving one request’s value for the next. In PostgreSQL, a local setting ends with the transaction and restores any prior session setting. Therefore the model assumes there is no old tenant value underneath it. Keep the connection’s baseline empty; setting a local value over a stale session value merely postpones the leak. Choose missing-context behaviour deliberately: reject the operation or match no rows, rather than silently choosing a tenant.

Writes need two checks. Which existing rows may the operation target? Which tenant may its new or changed row claim? A request scoped to tenant 10 must not move an order into tenant 20 by updating tenant_id. It must not insert a tenant-20 order either. A read filter alone does not establish those write rules. PostgreSQL’s policy expressions for existing and proposed rows, and the identities that bypass them, belong in the native implementation article.

Access and integrity answer different questions

Rowan is a valid customer. Reading Rowan’s valid row from a tenant-10 request is an access failure. A foreign key cannot decide that the reader is unauthorised.

Both rules are needed. A tenant-10 writer might create an order naming customer 4. The order’s tenant passes its access policy, but Rowan belongs to tenant 20: no customer has the pair (10, 4). A composite reference from the order’s tenant and customer ID to the customer’s corresponding pair rejects that association. Constraints and data integrity builds and tests the reference; it does not replace the access decision here.

Test the identity that will do the work

A query run as an administrator can prove that rows exist, but it cannot establish what the runtime login may do. The same applies to testing a row policy as a table owner that bypasses it. Open connections using the deployed runtime and support identities, check the effective identity, and run the real statements through them.

For each task, pair an allowed operation with a forbidden one. Include rows that make the distinction visible: an empty tenant-20 table would conceal the missing boundary that Rowan exposes.

An empty result is evidence only for the statement you ran. An update may return no rows because it matched none, because the interface hid them, or because it made a change without returning row data. Record its affected-row count and check stored values. For a forbidden write, a successful test establishes that the forbidden change did not happen.

A disposable native PostgreSQL 18.6 run checked the narrower customer/order example used here through separate shop_runtime and shop_support login connections. With tenant context 10, the policy returned customers 1–3 and hid customer 4. Support could read its granted columns but could not update order status or read the ungranted order timestamp. The runtime could not alter the customer table or become shop_migrate; tenant-changing writes failed. All three original orders were unchanged afterwards.

The counterexamples mattered as much. Before enabling the row policy, the runtime read all four customers. Setting its freely changeable context to 20 exposed Rowan. A retained session value survived into another transaction. The table owner read all four rows despite the policy. These observations verify those PostgreSQL mechanisms in this fixture; they do not verify the shop’s sign-in checks or a production pool. The request schedules are explanatory models, not captured server traces.

Keep privileges correct as the schema changes

A migration changes the access design as well as the schema. A new table needs an explicit decision about its readers and writers. A new column may become readable through an existing table-wide grant. A new routine may expose work that its callers could not perform directly.

Review the authority that will exist after the migration, including group memberships and default privileges for newly created objects. Then rerun the affected allow-and-deny tests as the runtime and support identities. A migration that succeeds as its owner has answered a different question.

Temporary support access needs an end as well as a beginning. Remove it when the task finishes, close or invalidate connections as the database’s rules require, and verify that the former access path no longer works. Keeping the task, its grant and its test together makes the next review a concrete comparison.

Take the three task requirements and their forbidden cases to PostgreSQL roles, policies and privileged code for native roles and row policies, or Operating and securing a CockroachDB cluster for its secure identities and privilege checks. The syntax changes, but the question stays the same. What may this connection do, on whose behalf, and what evidence establishes the boundary?