Re: pg_ls_dir & friends still have a hard-coded superuser check

Robert,

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Wed, Jan 25, 2017 at 2:08 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > That isn't what you're doing with those functions though, you're giving
> > the monitoring tool superuser-level rights but trying to pretend like
> > you're not.
>
> Uh, how so? None of those functions can be used to escalate to
> superuser privileges. I am trying to give SOME superuser privileges
> and not others. That IS how good security works.

Your email is 'pg_ls_dir & friends', which I took to imply at *least*
pg_read_file() and pg_read_binary_file(), and it's not unreasonable to
think you may have meant everything in adminpack, which also includes
pg_file_write(), pg_file_rename() and pg_file_unlink().

> I don't really think it's necessary to outline the use case more than
> I have already. It's perfectly reasonable to want a monitoring tool
> to have access to pg_ls_dir() - for example, you could use that to
> monitor for relation files orphaned by a previous crash.

Sure. What I believe would be better would be a function in PG that
allows you to know about all of the relation files which were orphaned
from a previous crash, and perhaps even one to go clean all of those up.
I certainly would have no issue with both of those being available to a
non-superuser.

Sure, you could do the same with pg_ls_dir(), various complex bits of
SQL to figure out what's been orphaned, and pg_file_unlink() to handle
the nasty part of unlinking the files, but you could also use
pg_ls_dir() to look at the files in PG's home directory, or
pg_file_unlink() to remove the PG user's .bashrc.

Does the monitoring tool need to be able to see the files in PG's root
directory, or to be able to unlink the PG user's .bashrc? No.

> Also, as
> mentioned above, I don't think this should have to be litigated for
> every single function individually. If it's a good idea for a
> non-superuser to be able to pg_start_backup() and pg_stop_backup(),
> the person doing that has access to read every byte of data in the
> filesystem; if they don't, there's no point in giving them access to
> run those functions. Access to just pg_ls_dir(), for example, can't
> be any more dangerous than that. Indeed, I'd argue that it's a heck
> of a lot LESS dangerous.

It wasn't litigated for every single function. A reivew of all cases
was performed and a very lengthy discussion held about how to give
non-superusers access to the functions which made sense was done, with
the resulting work finally being accepted and included into 9.6.

Further, as discussed and explained on the thread I just linked you to,
the assumption that a user who can call pg_start/stop_backup() has
access to read every byte on the filesystem isn't correct.

Am I sure that someone who has pg_ls_dir() could get superuser access on
the box? No, but I am sure that, in my opinion, it's frankly a pretty
dirty hack to use pg_ls_dir() in a monitoring tool and we should provide
better ways to monitor PG to our users.

Also, am I sure that we shouldn't ever give a user the ability to
arbitrairly list directories on the filesystem? No, but this isn't a
good justification for that change. If we *are* going to go down the
road of giving users filesystem-like access of any kind then I would
*much* rather look at the approach that I outlined before and that other
database systems have, which is to have a way to say "user X can read,
write, list, do whatever, with files in directory /blah/blah". Perhaps
with sub-directory abilities too. Nannyism, if we had that ability,
would be to say "you can set DIRECTORY to anything but PGDATA." or "you
can't set DIRECTORY to be /".

I don't think it's nannyism to keep things like pg_read_file and
pg_file_write as superuser-only.

Thanks!

Stephen