Hiding a GUC from SQL
In my extension pgsodium I'm defining a custom variable at startup to store
a key:
https://github.com/michelp/pgsodium/blob/master/src/pgsodium.c#L1107
I'm using the flags GUC_NO_SHOW_ALL | GUC_NO_RESET_ALL | GUC_NOT_IN_SAMPLE
| GUC_DISALLOW_IN_FILE, and a custom "no show" show hook that obscures the
value. This idea was inspired from the pgcryptokey module from Bruce
Momjian.
The value cannot be shown either with SHOW or current_setting() and it does
not appear in pg_settings. From what I can tell, the value is inaccessible
from SQL, but I think it's worth asking the experts if there is some other
demonstrable way, from SQL, that this value could be leaked even to a
superuser. no sql level user should be able to see this value, only a C
function, like the pgsodium_derive() from which to derive other keys,
should be able to see it. I realize that someone with external process
access can get the key, my goal is to prevent accessing it from SQL.
Any thoughts on weaknesses to this approach would be welcome. Thanks!
-Michel
Michel Pelletier <pelletier.michel@gmail.com> writes:
In my extension pgsodium I'm defining a custom variable at startup to store
a key:
https://github.com/michelp/pgsodium/blob/master/src/pgsodium.c#L1107
I'm using the flags GUC_NO_SHOW_ALL | GUC_NO_RESET_ALL | GUC_NOT_IN_SAMPLE
| GUC_DISALLOW_IN_FILE, and a custom "no show" show hook that obscures the
value. This idea was inspired from the pgcryptokey module from Bruce
Momjian.
I guess I'm wondering why you're making it a GUC at all, if you don't
want any of the GUC facilities to apply.
As far as I can think at the moment, putting in a no-op show hook
is sufficient to prevent the value from being seen at the SQL level.
However, it's far from clear that doing that isn't going to have
negative side-effects; it'll possibly also break other things like
GUC save/restore (eg rolling back when a transaction fails).
It seems like if you want to be this paranoid, you'd be better off
not exposing the variable to the GUC machinery in the first place.
You could use a custom set-function (like setseed) to replace the one
bit of functionality you do want.
regards, tom lane
On Wed, 2020-06-17 at 13:23 -0700, Michel Pelletier wrote:
In my extension pgsodium I'm defining a custom variable at startup to store a key:
https://github.com/michelp/pgsodium/blob/master/src/pgsodium.c#L1107
I'm using the flags GUC_NO_SHOW_ALL | GUC_NO_RESET_ALL | GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE,
and a custom "no show" show hook that obscures the value. This idea was inspired from the
pgcryptokey module from Bruce Momjian.The value cannot be shown either with SHOW or current_setting() and it does not appear in pg_settings.
From what I can tell, the value is inaccessible from SQL, but I think it's worth asking
the experts if there is some other demonstrable way, from SQL, that this value could be
leaked even to a superuser. no sql level user should be able to see this value, only a C function,
like the pgsodium_derive() from which to derive other keys, should be able to see it.
I realize that someone with external process access can get the key, my goal is to prevent
accessing it from SQL.Any thoughts on weaknesses to this approach would be welcome. Thanks!
-Michel
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.
And if that doesn't work, there may be other things to try.
It is mostly useless to try to keep a superuser from doing anything that
the "postgres" operating system user can do.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
On Wed, Jun 17, 2020 at 3:55 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Michel Pelletier <pelletier.michel@gmail.com> writes:
In my extension pgsodium I'm defining a custom variable at startup to
store
a key:
https://github.com/michelp/pgsodium/blob/master/src/pgsodium.c#L1107
I'm using the flags GUC_NO_SHOW_ALL | GUC_NO_RESET_ALL |
GUC_NOT_IN_SAMPLE
| GUC_DISALLOW_IN_FILE, and a custom "no show" show hook that obscures
the
value. This idea was inspired from the pgcryptokey module from Bruce
Momjian.I guess I'm wondering why you're making it a GUC at all, if you don't
want any of the GUC facilities to apply.
An excellent point as it's loaded pre-fork I guess I don't need any of that
stuff.
It seems like if you want to be this paranoid, you'd be better off
not exposing the variable to the GUC machinery in the first place.
You could use a custom set-function (like setseed) to replace the one
bit of functionality you do want.
Thanks! I've implemented your suggestion similar to how setseed stores its
data.
Show quoted text
regards, tom lane
On Thu, Jun 18, 2020 at 7:47 AM Laurenz Albe <laurenz.albe@cybertec.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
Show quoted text
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Laurenz Albe wrote:
On Wed, 2020-06-17 at 13:23 -0700, Michel Pelletier wrote:
In my extension pgsodium I'm defining a custom variable at startup to store a key:
https://github.com/michelp/pgsodium/blob/master/src/pgsodium.c#L1107
I'm using the flags GUC_NO_SHOW_ALL | GUC_NO_RESET_ALL | GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE,
and a custom "no show" show hook that obscures the value. This idea was inspired from the
pgcryptokey module from Bruce Momjian.The value cannot be shown either with SHOW or current_setting() and it does not appear in pg_settings.
From what I can tell, the value is inaccessible from SQL, but I think it's worth asking
the experts if there is some other demonstrable way, from SQL, that this value could be
leaked even to a superuser. no sql level user should be able to see this value, only a C function,
like the pgsodium_derive() from which to derive other keys, should be able to see it.
I realize that someone with external process access can get the key, my goal is to prevent
accessing it from SQL.Any thoughts on weaknesses to this approach would be welcome. Thanks!
-Michel
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.And if that doesn't work, there may be other things to try.
It is mostly useless to try to keep a superuser from doing anything that
the "postgres" operating system user can do.Yours,
Laurenz Albe
But only mostly useless. :-) There are ways to limit the power of the
superuser. On Linux, for instance, "sysctl kernel.yama.ptrace_scope=3"
prevents tracing, debugging, and reading another process's memory, even
by the superuser, and the only way to turn it off is via a (hopefully
noticeable) reboot. And, if the keys aren't present on the server at
boot time, and aren't fetched from their remote source (or read from a
user) unless that yama setting is in place, then it will be very hard
for a superuser to obtain the keys. If a remote source KMS is used,
ideally, you'd also want it to cryptographically verify that its client
hadn't been tampered with (somehow), or to not hand out the keys except
for planned reboots. The point is that it's not useless to make things
harder for a superuser.
You might not stop a legitimate sitewide superuser whose family is being
held hostage, but you can stop, or at least make things much more
difficult, for a superuser process on a single host that is the result
of a software vulnerability that wasn't nobbled by apparmor or selinux
or grsecurity.
cheers,
raf
On Mon, 2020-06-22 at 09:44 +1000, raf wrote:
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.And if that doesn't work, there may be other things to try.
It is mostly useless to try to keep a superuser from doing anything that
the "postgres" operating system user can do.But only mostly useless. :-) There are ways to limit the power of the
superuser. On Linux, for instance, "sysctl kernel.yama.ptrace_scope=3"
prevents tracing, debugging, and reading another process's memory, even
by the superuser, and the only way to turn it off is via a (hopefully
noticeable) reboot.
Interesting. Will this block a user from debugging his own processes?
Perhaps you can plug that hole that way, but that was just the first thing
that popped in my head. Don't underestimate the creativity of attackers.
I for one would not trust my ability to anticipate all possible attacks,
and I think that would be a bad security practice.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Laurenz Albe wrote:
On Mon, 2020-06-22 at 09:44 +1000, raf wrote:
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.And if that doesn't work, there may be other things to try.
It is mostly useless to try to keep a superuser from doing anything that
the "postgres" operating system user can do.But only mostly useless. :-) There are ways to limit the power of the
superuser. On Linux, for instance, "sysctl kernel.yama.ptrace_scope=3"
prevents tracing, debugging, and reading another process's memory, even
by the superuser, and the only way to turn it off is via a (hopefully
noticeable) reboot.Interesting. Will this block a user from debugging his own processes?
Yes.
Perhaps you can plug that hole that way, but that was just the first thing
that popped in my head. Don't underestimate the creativity of attackers.
I for one would not trust my ability to anticipate all possible attacks,
and I think that would be a bad security practice.
Yes, but that's no reason not to perform as much risk
assessment and mitigation as you can afford/justify.
Not being able to prevent all attacks is no reason not
to prevent those that you can. :-) Nobody said anything
about underestimating anyone or trusting anyone.
Yours,
Laurenz Albe
cheers,
raf
On Sun, Jun 21, 2020 at 10:21 PM raf <raf@raf.org> wrote:
Laurenz Albe wrote:
But only mostly useless. :-) There are ways to limit the power of the
superuser. On Linux, for instance, "sysctl kernel.yama.ptrace_scope=3"
prevents tracing, debugging, and reading another process's memory, even
by the superuser, and the only way to turn it off is via a (hopefully
noticeable) reboot.Interesting. Will this block a user from debugging his own processes?
Yes.
Thanks for the tip raf!
Perhaps you can plug that hole that way, but that was just the first
thing
that popped in my head. Don't underestimate the creativity of attackers.
I for one would not trust my ability to anticipate all possible attacks,
and I think that would be a bad security practice.Yes, but that's no reason not to perform as much risk
assessment and mitigation as you can afford/justify.
Not being able to prevent all attacks is no reason not
to prevent those that you can. :-) Nobody said anything
about underestimating anyone or trusting anyone.
I'm trying to take as layered an approach as possible, aggressively hiding
the key in postgres memory is one approach I'm taking as the out of the box
experience, but I'm also working on AWS KMS integration and a Zymkey HSM
integration. In those cases, keys would be fetched on demand, and
unencrypted keys would only live in memory for a short transaction lifetime
while being used, and then discarded, and I think your ptrace_scope trick
will help add a layer in either case.
-Michel