Re: Possibility to disable `ALTER SYSTEM` - Mailing list pgsql-hackers

From Joel Jacobson
Subject Re: Possibility to disable `ALTER SYSTEM`
Date
Msg-id d1a87492-d208-48b6-8b17-c1e01f70597c@app.fastmail.com
Whole thread Raw
In response to Re: Possibility to disable `ALTER SYSTEM`  (Gabriele Bartolini <gabriele.bartolini@enterprisedb.com>)
Responses Re: Possibility to disable `ALTER SYSTEM`
Re: Possibility to disable `ALTER SYSTEM`
List pgsql-hackers
On Fri, Sep 8, 2023, at 16:17, Gabriele Bartolini wrote:
> ```
> postgres=# ALTER SYSTEM SET wal_level TO minimal;
> ERROR:  could not open file "postgresql.auto.conf": Permission denied
> ```

+1 to simply mark postgresql.auto.conf file as not being writeable.

To improve the UX experience, how about first checking if the file is not writeable, or catch EACCESS, and add a
user-friendlyhint?
 

```
postgres=# ALTER SYSTEM SET wal_level TO minimal;
ERROR:  could not open file "postgresql.auto.conf": Permission denied
HINT: The ALTER SYSTEM command is effectively disabled as the configuration file is set to read-only.
```

On Fri, Sep 8, 2023, at 23:43, Magnus Hagander wrote:
> We need a "allowlist" of things a user can do, rather than a blocklist
> of "they can do everything they can possibly think of and a computer
> is capable of doing, except for this one specific thing". Blocklisting
> individual permissions of a superuser will never be secure.

+1 for preferring an "allowlist" approach over a blocklist.

In a way, I think this is similar to the project's philosophy on Query Hints, which I strongly support as I think it
leadsto a better PostgreSQL over the long term. It creates a crucial feedback loop between users facing query planner
issuesand our developer community, providing essential insights for enhancing the Query Planner.
 

If users were to simply apply Query Hints as a quick fix instead of reporting underlying problems, we would often lose
thesevaluable opportunities for improvement of the Query Planner.
 

Similarly, I think it's crucial to identify functionalities that currently require superuser privileges and cannot yet
beexplicitly granted to non-superusers.
 

/Joel



pgsql-hackers by date:

Previous
From: Amit Kapila
Date:
Subject: Re: Synchronizing slots from primary to standby
Next
From: Mats Kindahl
Date:
Subject: Re: glibc qsort() vulnerability