Re: Directory/File Access Permissions for COPY and Generic File Access Functions

Tom,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > Agreed- additional input from others would be great.
>
> I think this entire concept is a bad idea that will be a never-ending
> source of security holes.  There are too many things that a user with
> filesystem access can do to get superuser-equivalent status.

I'd be pretty disappointed if we are unable to implement a feature like
this which can't trivially bypass the in-database security and/or gain
superuser access.

> Here is one trivial example: you want to let user joe import COPY
> data quickly, so you give him read access in directory foo, which he
> has write access on from his own account.  Surely that's right in the
> middle of use cases you had in mind, or even if it wasn't, it sounds
> like a good idea no?  The problem is he can create symlinks, not just
> files, in that directory, and by pointing the symlink to the right
> place he can read any file the server can read.  pg_hba.conf, pg_authid,
> or even just tables he shouldn't have access to.  With a little luck he
> can crack the superuser's password, but even without that you've given
> him access to sensitive information.

While a use-case which would be great to support, we don't have the
ability to do it as well as SQL Server can since the backend can't
impersonate another user.  I'm definitely hoping to support Kerberos
credential proxying eventually (primairly for FDWs, but it's possible we
could use it for NFS also) and therefore we'd certainly have to caution
users about these risks.

The specific use-cases which I've been describing are cases where the
user doesn't have access to modify (or possibly even to read) the
filesystem- log directories, read-only NFS mounts, etc.

> If you were dumb enough to give joe *write* access in such a directory,
> so that he could COPY in both directions, it's game over altogether: he
> can become superuser in any number of ways, most easily by hacking
> pg_hba.conf.

You make a good point, certainly, but pg_hba.conf isn't necessairly
available to be modified by the PostgreSQL user.  Of course,
postgresql.auto.conf and the PG heap files could be overwritten if a
user is able to create symlinks and convince PG to access through them.

> You could ameliorate this problem by checking to see that the read/write
> target is a file not a symlink, but that's still subject to filesystem
> race conditions that could be exploited by anyone with the ability to
> retry it enough times.

As Andres already pointed out, there are ways to specifically address
these risks, I dare suggest because what we're talking about supporting
here is not new ground and others have found it to be a valuable
capability and worked to make it secure and safe to support.

> The larger point though is that this is just one of innumerable attack
> routes for anyone with the ability to make the server do filesystem reads
> or writes of his choosing.  If you think that's something you can safely
> give to people you don't trust enough to make them superusers, you are
> wrong, and I don't particularly want to spend the next ten years trying
> to wrap band-aids around your misjudgment.

I certainly don't have the experience you do in this area and am quite
interested in the other attack routes you're thinking of, and how other
databases which support this capability address them.  Perhaps they're
simply documented as known issues, or they aren't addressed at all and
bugs exist, but I'm not seeing these apparently obvious issues.

> If the objective is to give filesystem capabilities to someone you *do*
> trust, but they'd prefer to use it from an account without full superuser
> privileges, that can be solved much more simply by making access to the
> existing superuser-only I/O functions more granular.  That fits in just
> fine with the other project you've got of breaking down superuserness into
> smaller privileges.  But if we build a feature designed in the way being
> discussed in this thread, people will think it can be used to grant
> limited filesystem access to users they don't completely trust, and we're
> going to have to deal with the fallout from that.

The features which I've been proposing are, generally, intended to allow
a non-superuser to do things that are limited to the superuser today
while also preventing them from trivially being able to become a
superuser.  Any which can trivially be used to become a superuser would
at least need to be heavily caveat'd accordingly in the documentation,
which I'd be happy to do, but I'd like to have a better understanding of
the attack vectors under which there is such a risk- to provide
appropriate documentation, if nothing else.
Thanks!
    Stephen