PostgreSQL roles, policies and privileged code

Roles and the identity running a statement

A role is PostgreSQL’s named identity for owning objects, receiving privileges and belonging to other roles. A role with LOGIN can be used to establish a connection, subject to the server’s authentication rules. A role without it can still own tables or collect grants for its members. Roles belong to the server’s cluster, rather than one database.

A membership has separate options for using a role’s privileges automatically, becoming that role and administering its membership. The runtime can inherit read grants without permission to become the granting role. Deployment can instead be allowed to become the migration owner deliberately.

CREATE ROLE shop_reader NOLOGIN;
GRANT shop_reader TO shop_runtime
  WITH INHERIT TRUE, SET FALSE, ADMIN FALSE;

-- Deployment may deliberately become the object owner:
GRANT shop_migrate TO shop_deploy
  WITH INHERIT FALSE, SET TRUE, ADMIN FALSE;

INHERIT TRUE makes the group’s ordinary privileges available without changing the runtime’s identity. SET TRUE allows SET ROLE. ADMIN TRUE allows administering membership of that role, so it is additional authority rather than a requirement for using a table. Keep it away from runtime and support. Inspect indirect paths too: inheritance follows chains of inheritable memberships; assuming a role requires a chain with SET TRUE on every link.

NOINHERIT alone does not make a membership safe. In PostgreSQL 18 the role attribute supplies the default inheritance option for new membership grants. A membership that does not inherit can still allow SET ROLE. Nor is inheriting an owning role a harmless read grant: ownership can let a member alter the object or replace privileged code. Do not give runtime either route to the migration owner.

session_user normally identifies the role that connected. current_user identifies the effective role whose privileges a statement uses. They initially agree. SET ROLE changes current_user; RESET ROLE restores the login’s ordinary role state. A security-definer function will make another temporary change later. Read both identities when testing:

SELECT session_user, current_user;
SELECT pg_has_role(current_user, 'shop_reader', 'USAGE');

The second query asks whether the group’s privileges are immediately usable. It is a useful diagnostic, but still run the actual task. Special role attributes such as SUPERUSER and CREATEROLE are not ordinary inherited object grants; keep application roles without those attributes, without BYPASSRLS, and unable to assume roles that have them.

Grants, ownership and new objects

A connection must pass several distinct boundaries. Database CONNECT allows connecting; it does not satisfy authentication. Schema USAGE allows looking up objects there; it does not grant a table read. Schema CREATE allows creating objects there, which runtime and support do not need. A table or column grant then permits the particular operation.

Suppose shop_migrate owns schema shop and its tables. Support needs customer IDs and names, plus order IDs, customer IDs and status. Give it those columns explicitly:

GRANT USAGE ON SCHEMA shop TO shop_support;
GRANT SELECT (tenant_id, customer_id, name)
  ON shop.customers TO shop_support;
GRANT SELECT (tenant_id, order_id, customer_id, status)
  ON shop.orders TO shop_support;

SELECT * on orders will now fail because it includes the ungranted placed_at. The intended selected-column query succeeds. An UPDATE also needs SELECT on columns it reads in expressions or conditions, so derive grants from the complete SQL, not just its first word. Where a default uses nextval on a separately managed sequence, its sequence privilege needs checking too; a table grant does not grant sequence access.

Privileges add together. Revoking a direct grant cannot cancel the same privilege inherited from a group or obtained through PUBLIC, the implicit group containing every role. Likewise, a column restriction does not subtract from an existing whole-table grant. Inspect those other paths before calling the support grants narrow.

Keep the owner separate because it controls the object’s definition and grants. A runtime that owns its table can change its policy. Audit schema creation as well: an upgraded database may retain older PUBLIC privileges on schema public. A schema used to resolve trusted code must not let untrusted callers create replacement objects. The PostgreSQL schema documentation describes the defaults and upgrade differences.

Default privileges belong to the creator

A grant on existing objects does not decide the permissions of the next migration’s objects. ALTER DEFAULT PRIVILEGES sets rules for future objects created by a named role in the current database. It does not repair existing grants, and a creator does not inherit another role’s defaults.

ALTER DEFAULT PRIVILEGES FOR ROLE shop_migrate
  REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

This command deliberately has no IN SCHEMA: a per-schema default revoke cannot subtract the built-in global PUBLIC execution grant. Existing routines need their own revoke. A migration that first does SET ROLE shop_migrate creates objects owned by that role and uses its defaults; one that remains shop_deploy uses the deployment role’s defaults instead. Test a newly created object under the actual migration path.

Default table grants can be useful for a deliberately uniform schema. They are a poor substitute for deciding whether a new table is tenant-protected and whether support should read every future column. Keep those decisions in the migration review.

Tenant identity in a shared connection

All requests through shop_runtime have the same database login. A policy needs another value to distinguish their tenant scope. In this first design, the trusted application checks the person’s right to tenant 10, starts a transaction on one borrowed connection and sets a transaction-local value before any data query:

BEGIN;
SET LOCAL shop.tenant_id = '10';
SELECT customer_id, name FROM shop.customers;
COMMIT;

The production tenant value should come from the application’s authorised choice, bound as a value through its database client. For example, set_config('shop.tenant_id', $1, true) can set it locally within the transaction. Both statements above and the data work must use the same connection. An isolated SET LOCAL outside a transaction has no useful lifetime for the later query.

SET LOCAL ends at commit or rollback and restores the previous session value. That makes an empty baseline essential: setting a local 10 over an old session 20 restores 20 afterwards. Keep pooled connections free of retained tenant settings, and initialise every request before querying. Our policy treats a missing or empty value as no tenant; a malformed integer raises an error.

The setting is caller-controlled. A holder of the runtime connection can set it to 20 and see tenant-20 rows under this design. Row security catches a forgotten query condition while context is correct; it does not authenticate the person or prevent the service from choosing a false context. A stronger boundary needs authority the caller cannot forge. We will use a protected mapping for the separate support interface below.

Row-level security

Row-level security, or RLS, adds per-row policies to ordinary object privileges. A policy does not supply a missing table grant. Enable it on each protected table; until an applicable policy exists, ordinary callers see no rows and cannot create new ones.

ALTER TABLE shop.customers ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_customers ON shop.customers
  TO shop_runtime, shop_support
  USING (
    tenant_id = nullif(current_setting('shop.tenant_id', true), '')::integer
  )
  WITH CHECK (
    tenant_id = nullif(current_setting('shop.tenant_id', true), '')::integer
  );

The true argument makes current_setting return NULL when the setting is absent; nullif also turns an empty value into NULL. A row passes only when the policy expression is true. Comparing a tenant with NULL is unknown, so missing context admits no row.

USING decides which existing rows can be reached. WITH CHECK decides which new row versions may be stored by an insert or update. Put an equivalent tenant policy on shop.orders. With tenant context 10, customer 4 is outside the caller’s scope, while order 103 is an existing permitted row.

SELECT customer_id, name FROM shop.customers;

A name-only update targeting customer 4 can test the existing-row boundary. Give the diagnostic caller an explicit UPDATE (name) grant: without RLS it can reach Rowan’s row, while the tenant policy hides that target. This probe is separate from support’s read-only task.

With RLS, the distinction affects error handling. A read of customer 4 returns no rows. An update targeting only hidden or nonexistent rows can report UPDATE 0. But order 103 is reachable with tenant context 10, so an update proposing tenant 20 fails WITH CHECK and PostgreSQL rejects it with SQLSTATE 42501. An insert proposing tenant 20 fails that check too. PostgreSQL checks these proposed-row expressions before ordinary constraints, so a policy error does not establish that the proposal would otherwise be valid.

An ALL or UPDATE policy with USING and no explicit WITH CHECK reuses its USING expression for proposed rows. Writing both here makes the two questions visible. More elaborate command-specific policies need care: an update that reads columns may also need the applicable SELECT policy. Test the real statement, including its conditions and RETURNING.

Combining policies

Adding a second policy usually widens access. Policies are permissive by default, and applicable permissive policies combine with OR. A restrictive policy supplies an additional AND. It can narrow an existing allowance but cannot grant one on its own.

Do not count policy names as layers of protection. Read the combined expression for each command and every effective role, including inherited memberships. A small policy set that you can predict is easier to preserve than several overlapping exceptions. CREATE POLICY gives the per-command rules.

Where row policies stop

Table owners normally bypass row security. Superusers and roles with BYPASSRLS bypass it too. ALTER TABLE shop.customers FORCE ROW LEVEL SECURITY subjects the owner to policies during ordinary access, but does not constrain a superuser or BYPASSRLS role. It also does not take the owner’s ability to change the table’s policy away. FORCE is useful defence against accidental owner reads, not a reason to make runtime the owner.

RLS covers row access, rather than every operation or information channel. TRUNCATE is outside its row filtering, so do not grant it merely because deletes are filtered. Uniqueness and foreign-key checks bypass row security to preserve integrity; their success or error can reveal that some otherwise hidden value exists. A tenant policy is not a promise of complete information isolation.

Keep tenant integrity as well. A permitted tenant-10 order can still propose customer 4, a mismatched association that a tenant-only policy cannot detect. The composite reference in Constraints and data integrity enforces the pair. Policies answer who may reach or create a row; references answer whether its association is valid.

Views and the authority behind them

A view adds another privilege path. By default, PostgreSQL checks its base relations using the view owner’s privileges and RLS policies. If that owner also owns the customer table and normally bypasses its policies, granting support access to an unfiltered view can disclose all four customers.

CREATE VIEW shop.customer_names
WITH (security_invoker = true) AS
SELECT tenant_id, customer_id, name
FROM shop.customers;

A security-invoker view requires the caller’s relevant base-relation permissions as well as permission on the view. It therefore suits the selected-column grants already given to support. It is not an interface for hiding those base privileges from the caller. If a view supplies its own security filter, analyse that filter and PostgreSQL’s security_barrier rules as well. security_barrier controls evaluation around a view’s filter; it does not select invoker privileges. Functions called from a view also retain their own invoker or definer rules.

SECURITY DEFINER functions

A function normally runs with its caller’s privileges: SECURITY INVOKER. Use that default when no extra authority is needed. SECURITY DEFINER temporarily runs the function with its owner’s privileges, changing current_user while retaining session_user. That can expose work callers cannot do directly, including rows allowed to the function owner rather than to the caller.

For a separate support design, remove support’s raw customer grants and any other customer-reading interfaces, including the example views, then offer one lookup. A protected table shop_private.support_scope(login_name, tenant_id) maps the actual shop_support login to tenant 10. Only the trusted migration authority may change the mapping. This local example fixes one login to one tenant; it is not the runtime’s freely settable context, and does not establish a person’s rights within that tenant.

The function owner, shop_lookup, is a non-login role with no superuser, role-creation or RLS-bypass authority. It owns no base tables. Give it only schema usage and reads of the mapping and needed customer columns. A customer SELECT policy for this role allows the lookup rows; the function’s join then enforces its tenant scope. Support cannot become this role, edit its mapping or replace its function.

search_path and trusted objects

search_path is the schema order PostgreSQL searches for an unqualified object name. If privileged code refers to support_scope without its schema, a caller-controlled table can change what the function trusts. Temporary relations are particularly easy to miss: unless explicitly placed later, their schema is searched before the ordinary path for relation names.

The secured function names both tables and fixes its search path to trusted schemas, with pg_temp explicitly last. This also avoids trusting caller-selected functions, operators or types through an unsafe path. Protect who may create in those trusted schemas and who may replace the referenced objects. Qualification cannot secure a dependency that an attacker may alter.

-- As a privileged fixture administrator, after provisioning shop_lookup's
-- narrow reads and SELECT policy; it owns this function only.
BEGIN;
GRANT CREATE ON SCHEMA shop TO shop_lookup;
CREATE FUNCTION shop.customer_name(p_customer_id integer)
RETURNS TABLE (customer_id integer, name text)
LANGUAGE sql
SECURITY DEFINER
SET search_path = pg_catalog, shop_private, pg_temp
AS $$
  SELECT c.customer_id, c.name
  FROM shop.customers AS c
  JOIN shop_private.support_scope AS s
    ON s.tenant_id = c.tenant_id
  WHERE s.login_name = session_user
    AND c.customer_id = p_customer_id
$$;
ALTER FUNCTION shop.customer_name(integer) OWNER TO shop_lookup;
REVOKE CREATE ON SCHEMA shop FROM shop_lookup;
REVOKE ALL ON FUNCTION shop.customer_name(integer) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION shop.customer_name(integer) TO shop_support;
COMMIT;

session_user is deliberately used for the protected login mapping. Inside this function, current_user is always shop_lookup, so mapping that value would confuse the function owner with its callers. This pattern assumes support connects as its actual dedicated login. It does not recover a person’s identity from a shared service login or distinguish roles assumed by that login.

With the mapping to 10, customer_name(1) returns Mina; customer_name(4) returns no row. Setting shop.tenant_id to 20 has no effect on this join. Replacing the mapping reference with a caller-settable tenant argument would surrender that boundary: a valid integer is still no proof of authority.

A narrow callable interface

New functions normally grant execution to PUBLIC. Revoke it and grant the intended caller within the creation transaction so there is no committed public-access window. The earlier default-privilege revoke also helps for future migration-created routines; check the actual creator and existing functions. Callers need schema usage to reach the function as well.

This interface accepts a customer key and returns two columns. It cannot change payment data, run arbitrary SQL or choose an object name. Keep that small contract. If a routine genuinely needs dynamic SQL, bind data values and quote identifiers correctly, then separately decide which objects and operations that caller is authorised to select. Correct quoting does not make an arbitrary table choice safe.

A definer function is part of the access boundary, including everything it calls. A trigger or another routine reached from it can add authority or side effects. Its owner’s grants, policies, dependencies and replacement privileges need review together. Types and extensions in PostgreSQL owns the additional installation and lifecycle privileges of extension code.

Testing the application identities

Open connections as the actual runtime and support logins, using their intended authentication path. Confirm session_user, current_user and the target database before testing the task. An administrator’s SET ROLE can help diagnose grants but does not test login authentication, and it leaves a different session_user for a function that maps the original login.

In an owned disposable PostgreSQL 18.6 run, separate login connections tested the narrower customer/order fixture used here. They used a local Unix socket with trust authentication, so this run does not establish password or network authentication.

Before RLS, runtime could read all four customers; enabling it without a policy hid them all. With the tenant policy and context 10, it read customers 1–3, hid Rowan and rejected the attempted tenant-20 writes. Both commit and rollback ended the local context. A deliberately retained session setting reappeared after the local override, and freely setting context 20 exposed Rowan.

The same run checked owner bypass, owner access under FORCE, permissive and restrictive combinations, and the two view authority paths. The secured support routine returned Mina and hid Rowan even with a counterfeit temporary mapping; the unsafe unqualified version returned Rowan. A login without the execution grant could not call the secured routine. Future-object defaults affected a new object and left the earlier object unchanged. All starting customer and order rows remained unchanged.

Those tests establish mechanisms in this fixture. They do not test the shop’s sign-in service, deployed credentials or production pool. Keep those application paths in the acceptance test, and rerun the affected allowed and forbidden cases when a migration changes roles, policies, views, routines or default privileges. An access design remains useful only while the identity taking each path is the one you intended.