Re: Hiding a GUC from SQL

On Thu, Jun 18, 2020 at 7:47 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> On Wed, 2020-06-17 at 13:23 -0700, Michel Pelletier wrote:
> >
> > Any thoughts on weaknesses to this approach would be welcome. Thanks!
>

> A superuser can access files and start programs on the server machine.
>

> A dedicated superuser may for example attach to PostgreSQL with a debugger
> and read the value of the variable.
>

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.

> It is mostly useless to try to keep a superuser from doing anything that
> the "postgres" operating system user can do.
>

Agreed, thanks for your suggestions!

-Michel

>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>