Re: About "ERROR: must be *superuser* to COPY to or from a file"

Douglas McNaught <doug(at)mcnaught(dot)org> writes:

> Greg Stark <gsstark(at)mit(dot)edu> writes:
>
> > If you're connected via a unix domain socket we can know the UID of the client
> > end. I don't see reproducing the entire unix semantics but if file is owned by
> > the same uid as the user connecting it seems like it ought to be safe.
>
> That's an interesting point. You'd have to make sure you weren't
> following a user-owned symlink to a 'postgres'-owned file, but that's
> doable.

FWIW you have to go pretty far out of your way to get this wrong. The Unix API
specifically does everything it can to make sure symlinks behave like the
target unless you use special syscalls like lstat.

The way you would do this is by first opening the file, then calling fstat on
the resulting file descriptor. This avoids any race conditions, symlink, or
any other conceivable attack. You're getting the owner uid of the actual file
you have open.

> Of course that method only applies to a subset of PG users, and
> completely excludes the Windows side. It might also conflict with
> security policies that forbid PG from reading and writing outside its
> own data directory.

Well that's already out with the advent of tablespaces. Hell, even before
tablespaces Postgres had "CREATE DATABASE WITH LOCATION = ...". You can grant
permission to create databases to regular users.

For that matter it might be handy to be able to grant permission to regular
users to load or dump files to arbitrary locations. The security consequences
would have to be documented but I don't think they're so bad that you can say
nobody should ever be granting the privilege.

As far as I can see the consequence is limited to allowing non-privileged
users to *read* data owned by Postgres which basically means being able to
read logs and table contents. It doesn't allow regular users to escalate their
privileges beyond that (barring poor password choices or passwords in logs).
So you can grant this privilege to any user you don't mind having *read*
access to the entire database. In many installations I can imagine plenty of
users that have read access to the entire database but not write access.

--
greg