Privilege escalation occurs when an account or process gains or exercises permissions beyond those originally intended. In PostgreSQL, it often enters through convenience features that silently run with more authority than the caller: objects owned by a superuser, SECURITY DEFINER functions, or object lookup through an unsafe search_path. The result can be unauthorized data access, query hijacking, destructive actions, and weak accountability. section 8.4 section 8.6 section 8.5
A common mistake is involving a superuser in ordinary database ownership. Superusers bypass most permission checks, so an object carrying superuser authority can expose data or actions to unprivileged users. This can also defeat row-level security policies: a view owned by a superuser may return rows regardless of the policy. Avoid making routine users or objects depend on superuser authority; reserve it for administration that truly requires it. section 8.4
Reduce escalation paths by treating authority, name resolution, and function execution as separate security boundaries:
- Create databases and ordinary objects with dedicated nonprivileged owner roles, then GRANT only the required object privileges to users and application roles. section 8.4
- Control CREATE privileges on schemas in every user's search_path. Remove schemas writable by untrusted users, or schema-qualify references to objects owned by trusted roles. section 8.6
- Limit use of the public schema; separate schemas by application component, team, or tenant where appropriate. section 8.6
- Use SECURITY DEFINER only when necessary. Revoke its default PUBLIC execution privilege, grant EXECUTE selectively, validate inputs, and set a safe search_path. section 8.5
Unsafe name lookup can turn convenience into authority
A default search_path such as "$user", public lets PostgreSQL resolve unqualified object names from those schemas. If an untrusted role can create objects in a searched schema, an object-name override can hijack a query and create a privilege-escalation path. Inspect the active path with SHOW search_path; remove unsafe writable schemas or explicitly name trusted objects. Revoking CREATE on public from PUBLIC is one available control. section 8.6
Constrain a SECURITY DEFINER reporting function
A SECURITY DEFINER function can provide narrowly scoped access, such as calculating current-month sales, but it must not be callable by everyone or resolve names through an unsafe path. Create and lock down the function in one transaction, revoke PUBLIC access, grant EXECUTE only to intended roles, and set search_path to pg_catalog, erp, pg_temp. section 8.5
BEGIN;
CREATE OR REPLACE FUNCTION erp.current_month_sales()
...
REVOKE ALL ON FUNCTION erp.current_month_sales() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION erp.current_month_sales() TO sales_role;
GRANT EXECUTE ON FUNCTION erp.current_month_sales() TO finance_role;
COMMIT;
This transaction shows the critical ordering: create or replace the SECURITY DEFINER function, remove PUBLIC access, grant only approved roles, then commit. section 8.5
Privilege management is also an operational control. Shared superuser logins make auditing and accountability difficult because actions cannot be reliably attributed to an individual. PostgreSQL roles support role-based access control and should express both ownership and narrowly granted access rather than relying on a universal administrative account. section 8.4
