Dev.to Security 🔐 Cybersecurity 👁 0 📖 2 min read

PostgreSQL parameter SET grants are administrative authority

PostgreSQL parameter SET grants are administrative authority PostgreSQL configuration parameters are often treated as tuning knobs. Some of them also influence where a process writes, which indexes it trusts, how much

PostgreSQL parameter SET grants are administrative authority

PostgreSQL configuration parameters are often treated as tuning knobs. Some of them also influence where a process writes, which indexes it trusts, how much memory an operation may use, or how a query is planned. The SET privilege on a parameter lets a role change that value for its session. PostgreSQL's privilege documentation says this grant is meaningful for parameters that normally require superuser authority to set.

Find the parameters that cross a security boundary

Start with the catalog rather than a hand-written list. The pg_settings view exposes the context of each parameter, which describes where the value may be set. A parameter with a privileged context is a candidate for a controlled role-level grant and monitoring.

SELECT name, context, setting, unit
FROM pg_settings
WHERE context IN ('superuser', 'sighup')
ORDER BY name;

The query is a starting point, not a complete policy. Review application behavior and extensions as well as core server settings. A parameter that changes a path, loads a library, or alters a security hook deserves a stricter review than a query planner cost.

Grant only the named parameter and only to a role that needs it.

GRANT SET ON PARAMETER work_mem TO app_runtime;
REVOKE SET ON PARAMETER work_mem FROM PUBLIC;

The owner or superuser can still change more. The goal is to prevent an application role from gaining authority over a sensitive parameter merely because connection-time options or SET statements are accepted by libpq.

Session values are not a tenant boundary

A role that can set a parameter for its session can do so for every connection it controls. Pooling complicates the picture: a value may persist on a reused connection, and a RESET may return to a role or database default rather than the administrator's intended baseline. Make the application send an explicit startup policy and verify the effective value from within the session.

Do not treat SET as a substitute for resource limits enforced by the operating system or a dedicated service account. If a parameter controls a filesystem path, check file ownership and mount isolation. If it controls a library or extension, review installation and execution permissions separately.

Audit the grants and the effects

Review parameter grants during joiner, mover, and leaver processing. Record the role, parameter, expected value, allowed range, and reason. Test a fresh connection, a pooled connection, and a connection that supplies options before login. Confirm that a failure to set the value is visible rather than silently falling back to a weaker default.

The ALTER SYSTEM privilege is separate and should be reviewed independently. A team that understands which parameters are merely tunable and which parameters cross an administrative boundary can keep the database configuration stable while still granting the smallest useful exception.

References

📰 Read the original article on Dev.to Security

Originally published by Dev.to Security. Aggregated on AIWithGhost for educational purposes — full credit and traffic to the original publisher.