Re: Hiding a GUC from SQL - Mailing list pgsql-general

Preventing access from regular users is pretty solid I believe, but you're right superusers is going to be a real challenge.  It is discouraging that just about every postgres deployment I've inherited over the years has some web user interacting process logging in as a superuser.  There are seemingly infinite web frameworks that do this out of the box like it's a feature.  If the database is coupled to the entire web via a superuser web client then I consider that a compromised system already and doing something like crypto, or banking, or PII is insane.

So, that being said I'm assuming some level of reasonable when I want to avoid access for superusers.  This is mainly to avoid mistakes that a superuser could make like accidentally leaking a key.    For that definition of "reasonable" I guess there are at least two risks here, one is accessing process state by invoking inspection tools like debuggers, and another is running the getkey script like `COPY foo FROM PROGRAM '/usr/share/postgresql/13/extension/pgsodium_getkey';`

For the first situation I can't think of any mitigation other than documenting and recommending that things like debuggers not be installed on systems that do crypto (or banking, or PII, etc).

For the second situation there are a couple mitigations at the expense of some annoyance lik getkey programs that prompt for the key on boot from an interactive console. For non-interactive getkeys  I'm considering an optional mode where the getkey program is deleted by the extension initialization after one use.  The key fetcher program must be placed in the right dir on every server start by some external process.