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

On Sat, Aug 27, 2005 at 01:20:29AM -0400, Greg Stark wrote:
> > 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.

But these users couldn't choose where the database would end up. The
target directory had to be blessed (initdb) by the admin first.

> 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.

Well, they would have access to every world readable file on the
system, ie /etc, /usr, /lib, ... most files are world readable. There's
a lot of discussion about this, yet no-one has demonstrated that COPY
FROM STDIN isn't just as good and avoids all the issues entirely.

Checking UIDs is just a hack which would only make a difference if
you're on the same machine as the server which by definition is the
situation with the least to gain. Copying over a UNIX domain socket is
hardly likely to be a bottleneck.

> 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.

Err, anyone who can read pg_shadow can get enough info to login as any
user in any database in that cluster, so yes, it does allow people to
escalate their privelidges. Not to mention being able to read
certificates and pg_ident files and such...

There's still no reason why an admin who considered it safe couldn't
just create a SECURITY DEFINER function to do it on behalf of the user.
The server doesn't need any changes.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.