In PostgreSQL, what does ALTER SYSTEM actually change, and why would a team set allow_alter_system = off?
answer
- SQL that edits a file
- the data directory's auto file
- still needs reload or restart
- 17 added an off switch
- not a security boundary
basics
~20 sALTER SYSTEM writes the setting into postgresql.auto.conf, which overrides postgresql.conf once reloaded or restarted. allow_alter_system = off (PostgreSQL 17+) blocks the command so an external config tool stays the single source of truth; it is not security.
solid answer
~50 s`ALTER SYSTEM SET name = value` validates the value and rewrites `postgresql.auto.conf` in the data directory; nothing changes until a reload, or a restart for `postmaster`-context parameters. Because that file is read after `postgresql.conf`, its entries win. `ALTER SYSTEM RESET name` (or `SET name = DEFAULT`) removes the entry, and `RESET ALL` clears the file. It needs superuser or a `GRANT ALTER SYSTEM ON PARAMETER`, cannot run inside a transaction block, and writes no WAL, so standbys keep their own copy. Since PostgreSQL 17, `allow_alter_system = off`, set in `postgresql.conf` and applied by reload, makes the command fail with `ALTER SYSTEM is not allowed in this environment`. Teams use it when a configuration-management tool owns the files, so a well-meaning superuser cannot create a second source of truth. It is not a security control, and existing `postgresql.auto.conf` entries still apply.
code
sql · 7 linesALTER SYSTEM SET log_min_duration_statement = '250ms';
SELECT pg_reload_conf();
SHOW log_min_duration_statement;
-- remove the entry again; postgresql.conf or the default applies after reload
ALTER SYSTEM RESET log_min_duration_statement;
SELECT pg_reload_conf();go deeper
Recall that ALTER SYSTEM saves a setting to postgresql.auto.conf and that you still need a reload or a restart.
Explain why the auto file wins over postgresql.conf, how RESET removes an entry, and why the command is non-transactional and node-local.
Show judgement about who owns configuration: when ALTER SYSTEM is fine, when it fights a management tool, and why allow_alter_system is a guard rail rather than a security boundary.
Weigh SQL-driven configuration against files in version control across a fleet, including auditability, drift between primary and standbys, and incident-time convenience.
## What `ALTER SYSTEM` does `ALTER SYSTEM` is PostgreSQL's SQL interface for changing **cluster-wide** parameter defaults. It never touches `postgresql.conf`. Instead, it maintains a second file, **`postgresql.auto.conf`**, which always lives in the data directory and starts with the comment `# Do not edit this file manually!`. The sequence for `ALTER SYSTEM SET log_min_duration_statement = '250ms';` is: 1. Check privileges: a superuser, or a role granted `ALTER SYSTEM ON PARAMETER log_min_duration_statement`. 2. Validate the value against the parameter's type and range; a bad value fails with `invalid value for parameter`. 3. Rewrite `postgresql.auto.conf` through a temporary file, replacing any earlier entry for that name. 4. Do **nothing else**. The running value changes only after `SELECT pg_reload_conf();`, or after a restart for a `postmaster`-context parameter. Because the server reads `postgresql.auto.conf` after `postgresql.conf`, an `ALTER SYSTEM` value overrides whatever the main file says. ## What it will not do - **Apply immediately.** A successful `ALTER SYSTEM` is only a file write; forgetting the reload is the commonest reason a change "didn't work". - **Run in a transaction.** It acts on the file system and cannot be rolled back, so it is refused inside a transaction block or a function. - **Replicate.** It writes no WAL. Running it on a primary changes nothing on streaming standbys; run it on each node, or manage the files outside the database. A standby built with `pg_basebackup` does copy the primary's `postgresql.auto.conf` at that moment, and `-R` appends its connection settings to it. - **Set everything.** It refuses `data_directory`, `allow_alter_system` itself, and preset parameters that cannot appear in a configuration file. ## Who may run it - Superusers can change any parameter that is allowed in a configuration file. - Other roles need a per-parameter privilege: `GRANT ALTER SYSTEM ON PARAMETER log_min_duration_statement TO ops_oncall;`. - `ALTER SYSTEM RESET ALL` is superuser-only. - The separate `SET` privilege on a parameter (`GRANT SET ON PARAMETER ...`) covers session-level changes only; it does not allow `ALTER SYSTEM`. That gives an on-call role a narrow, auditable way to adjust, for example, a logging threshold without being handed superuser. ## Undoing a change | Command | Effect on `postgresql.auto.conf` | |---|---| | `ALTER SYSTEM RESET name;` | Removes that entry | | `ALTER SYSTEM SET name = DEFAULT;` | Same as `RESET` | | `ALTER SYSTEM RESET ALL;` | Removes every entry (superuser only) | After removal, the value falls back to `postgresql.conf` or the built-in default, again only after a reload or restart. If a bad restart-only value stops the server from starting, edit `postgresql.auto.conf` by hand while the server is down; with the server stopped, no concurrent `ALTER SYSTEM` can overwrite the edit. ## `allow_alter_system` (PostgreSQL 17 and later) PostgreSQL 17 added `allow_alter_system`, a boolean that defaults to `on`. Its key facts: - It can only be set in `postgresql.conf` or on the server command line, never through `ALTER SYSTEM`. - Its context is `sighup`, so a reload is enough to switch it. - When it is `off`, every `ALTER SYSTEM` fails with `ALTER SYSTEM is not allowed in this environment`. - Entries already in `postgresql.auto.conf` **keep taking effect**; only the command is blocked. The manual is explicit that this is **not a security feature**. A superuser can run operating-system commands and rewrite the file anyway. The setting exists for environments where a configuration-management tool or an HA manager owns the configuration: without it, a superuser fixing an incident with `ALTER SYSTEM` creates a value that silently overrides the tool's files, or that the tool later overwrites. PostgreSQL 17 also let `ALTER SYSTEM` set **unrecognized custom parameters** (dotted names like `myext.setting`) before the module that defines them is loaded. ## When to use which - Hand-managed single server: `ALTER SYSTEM` is convenient and leaves a clear trail in one file. - Fleet managed by a tool: keep `postgresql.conf` and `conf.d` in version control and set `allow_alter_system = off`. - Either way, check `pg_settings.sourcefile`: if it names `postgresql.auto.conf`, the value came from `ALTER SYSTEM`.
- Why does ALTER SYSTEM refuse to run inside BEGIN ... COMMIT?It rewrites `postgresql.auto.conf` directly on the file system, which a rollback could not undo. To avoid pretending it is transactional, PostgreSQL rejects it inside a transaction block or a function with `ALTER SYSTEM cannot run inside a transaction block`.
- You ran ALTER SYSTEM on the primary and reloaded. Why does a streaming standby still show the old value?`ALTER SYSTEM` writes a local file and generates no WAL, so nothing reaches the standby. Each node has its own `postgresql.auto.conf`; run the command on the standby too, or manage configuration outside the database.
- Says ALTER SYSTEM changes the running value at once, with no reload needed.
- Believes allow_alter_system = off stops a superuser from changing configuration.
- Wraps ALTER SYSTEM in a transaction, expecting to roll it back if it goes wrong.
- Assumes ALTER SYSTEM on a primary also changes every streaming standby.
- Thinks allow_alter_system = off disables the values already in postgresql.auto.conf.
Assumes PostgreSQL 18; allow_alter_system and ALTER SYSTEM on unrecognized custom parameters both arrived in 17, so 16 and older have no off switch.