Database Permission Tests for Readers, Editors and API Clients

Describe Actions Before Choosing Role Names

Write the actual policy in ordinary language: a sales representative may edit their own assigned contacts, a reviewer may read an approved subset and an integration may create incoming requests but must not export the whole customer list. Role names alone are too vague to test.

Separate data validation from authorization. A database constraint can reject an invalid value without deciding whether a particular user should have seen the record in the first place. Both are important; neither substitutes for the other.

Documentation: PostgreSQL: constraints and relational integrity

List Every Way a User Can Reach Data

Include the application interface, shared links, forms, exports, file downloads and API clients that are actually enabled. A page that appears restricted in the browser can still be misconfigured through another available path. Do not assume all products expose the same paths or have the same edition controls.

Treat a saved filter as a presentation choice until product documentation and an access test establish a security boundary. Keep public-link behavior separate from authenticated-user behavior. The product-specific permission and token guides explain the documented controls for each system.

Use Clean Representative Identities

Create synthetic records and authorized test identities for the roles you intend to support. Use a separate browser session or another documented method to avoid accidentally testing with an administrator’s existing credentials. Keep a record of which identity performed each action.

An API token can have a different scope from a human account. Record its resource scope, available actions and expiry if supported in the edition. A broad token that happens to be used by a narrow script is still broad access. Do not write secret token values into the worksheet or into screenshots.

Test the Negative Cases

For every permitted action, add a related denial case: another team’s record, an unshared table, a forbidden update or an export beyond the allowed scope. Record the expected result before trying it so that a convenient outcome is not accepted after the fact.

If a denial check unexpectedly succeeds, stop using the sample setup as a basis for real sharing. Investigate the effective role, public links, token privileges and edition behavior. Do not infer safety merely because a control is hidden in the interface.

1. Define the workflow and sensitive record categories.
2. Write allowed and denied actions for each representative identity.
3. List the enabled interface, sharing, download and API paths.
4. Run a positive and negative case through each relevant path.
5. Record the observed result and the source or configuration that explains it.
6. Repeat after changes to roles, public links, tokens or editions.

Keep the Decision Narrow

A passed check supports the tested identity, action, resource and configuration. It does not prove every possible security property of the product. Keep those boundaries in the acceptance note and revisit the matrix when the workflow changes.

For a small team, a compact matrix is often enough to make a sharing decision clearer. If the required policy cannot be represented or verified in the chosen edition, change the workflow or evaluate another supported option before loading sensitive data. No permission test is claimed as completed here.

An Illustrative Role and Action Matrix

This is an illustrative worksheet to adapt to your own evaluation. It is not a measured product test or a claim about results in your installation.

Make the sharing decision from observed allow and deny checks tied to the exact configuration. A hidden control or a reassuring role name is not equivalent evidence.