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

Emi Lu <emilu@cs.concordia.ca> writes:

Greetings,

I met a question about Copy. I tried to run :
copy test(studid) from '/myownDIR/a.txt';

But I got an error as the following:
ERROR: must be *superuser* to COPY to or from a file

May I know is it possible that instead of supervuser, regular users
are able to use COPY as well?

You can use \copy in 'psql' on the client side, but you have to be a
superuser to do COPY on the server side, for security reasons.

If you are not using 'psql' and your client library supports it, you
can use 'COPY FROM stdin' and pass the data across the client
connection.

-Doug

Douglas McNaught <doug@mcnaught.org> writes:

You can use \copy in 'psql' on the client side, but you have to be a
superuser to do COPY on the server side, for security reasons.

I wonder if there's any way to relax this constraint.

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.

--
greg

Greg Stark <gsstark@mit.edu> writes:

Douglas McNaught <doug@mcnaught.org> writes:

You can use \copy in 'psql' on the client side, but you have to be a
superuser to do COPY on the server side, for security reasons.

I wonder if there's any way to relax this constraint.

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.

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.

-Doug

On Fri, Aug 26, 2005 at 06:04:52PM -0400, Greg Stark wrote:

Douglas McNaught <doug@mcnaught.org> writes:

You can use \copy in 'psql' on the client side, but you have to be a
superuser to do COPY on the server side, for security reasons.

I wonder if there's any way to relax this constraint.

This doesn't address the general problem, but a COPY inside a
SECURITY DEFINER function might work in certain cases. You'd still
have security issues to deal with, like the location of the input
or output file and making sure that SESSION_USER has appropriate
permissons on the table being copied to or from.

--
Michael Fuhr

Douglas McNaught <doug@mcnaught.org> writes:

Greg Stark <gsstark@mit.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

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@svana.org> http://svana.org/kleptog/

Martijn van Oosterhout <kleptog@svana.org> writes:

On Sat, Aug 27, 2005 at 01:20:29AM -0400, Greg Stark wrote:

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.

Well they're world-readable. So, uh, huh?

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.

You keep saying that. Why do you think so? Every bit of data has to be read in
by one process, copied into the kernel again, a context switch has to happen,
and then it has to be copied out of the kernel into another process, and then
written back again by the second process. If the machine is cpu-bound it will
cut its throughput dramatically. Context switches are expensive.

You would have to have a fast disk subsystem for this to really be an issue,
and be copying a lot of data for the slowdown to be really annoying. But there
are plenty of applications that fit that exact description. Data ware houses
spend most of their waking hours loading huge data sets using very large and
very fast raid arrays.

If you think checking uid is a hack (I don't see why it's at all hackish) then
I would suggest the really clean way of handling this is to simply pass the fd
along the unix domain socket. But it's not supported on nearly as many
variants of unix as simply checking the uid.

--
greg

Martijn van Oosterhout <kleptog@svana.org> writes:

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.

In any case here's some quick results from my system. There seems to a greater
than 21% slowdown associated with piping the data through two processes
instead of reading directly.

This file is small enough to have probably fit entirely within cache, but then
on the other hand I don't have the kind of fast RAID arrays data warehouses
live with. A fast raid array would mean both that reading in the raw data
would be more like my situation here where reading in the data is a small part
of the time and *also* that writing out the data which my machine had to
stream to a typical consumer level drive would take less time and that's
probably the dominant time in this test.

bash-3.00$ for i in `seq 1 10` ; do psql -d stark -c 'truncate table t' ; time psql -d stark -c '\copy t from '"'"'postalcodes.dat'"'"' with delimiter '"'"'\t'"'"'' ; done 2>&1 | grep real
real 0m5.223s
real 0m5.262s
real 0m5.322s
real 0m5.613s
real 0m5.394s
real 0m5.221s
real 0m5.365s
real 0m5.445s
real 0m5.247s
real 0m5.238s

bash-3.00$ for i in `seq 1 10` ; do psql -d stark -c 'truncate table t' ; time psql -U postgres -d stark -c 'copy t from '"'"'/home/stark/src/saleslookout/postalcodes.dat'"'"' with delimiter '"'"'\t'"'"'' ; done 2>&1 | grep real
real 0m4.011s
real 0m4.058s
real 0m4.308s
real 0m4.498s
real 0m4.220s
real 0m4.049s
real 0m4.131s
real 0m4.488s
real 0m4.166s
real 0m4.152s

--
greg

Greg Stark <gsstark@mit.edu> writes:

In any case here's some quick results from my system. There seems to a greater
than 21% slowdown associated with piping the data through two processes
instead of reading directly.

Well, if the penalty is order of 20% (as opposed to integer multiples)
I think the discussion is over. We're not going to introduce arguable
security holes for that sort of gain --- there are other places we could
find that much speedup for much less risk.

(BTW, were you testing CVS tip or 8.0? The recent COPY FROM speedup
patch would have affected this test.)

regards, tom lane

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.

Well they're world-readable. So, uh, huh?

I haven't completely followed the details of this, but I took the point
to be that the files might be readable for anyone with a real account
on the server machine, but that doesn't mean they should be accessible
to every remote DB user.

- John Burger
MITRE

Tom Lane <tgl@sss.pgh.pa.us> writes:

Greg Stark <gsstark@mit.edu> writes:

In any case here's some quick results from my system. There seems to a greater
than 21% slowdown associated with piping the data through two processes
instead of reading directly.

Well, if the penalty is order of 20% (as opposed to integer multiples)
I think the discussion is over. We're not going to introduce arguable
security holes for that sort of gain --- there are other places we could
find that much speedup for much less risk.

Well it's not like it's an either or thing. a 40% speed increase would be even
better.

I can't see how letting users read files they own can possibly be a security
hole. The only case would be if there are files they own in directories they
don't have access to read. Which would be a pretty strange circumstance.

I could see saying it's not worth the effort to implement it. (Though what I
suggested would be a pretty simple patch.) So if I went and implemented it
and/or the solution based on passing an fd to the server would it be accepted
(assuming the code quality was up to snuff)?

(BTW, were you testing CVS tip or 8.0? The recent COPY FROM speedup
patch would have affected this test.)

No. Actually sadly this is 7.4.

I would expect the parsing changes to help in either case though, no?
In any case my test was pretty unscientific. I just wanted to say it's not
going to be zero effect.

--
greg

"John D. Burger" <john@mitre.org> writes:

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.

Well they're world-readable. So, uh, huh?

I haven't completely followed the details of this, but I took the point to be
that the files might be readable for anyone with a real account on the server
machine, but that doesn't mean they should be accessible to every remote DB
user.

I was only suggesting using this from a local unix user where you can actually
authoritatively say something about the uid of the connecting user. I
suggested that if the owner of the file matches the uid of the connecting user
(which you can get on a unix domain socket) then there's no reason not to
grant access to the file.

There isn't really any gain to be had from remote users since they have to get
the data to the server one way or another anyways. There's no good reason for
piping it over a libpq connection to be any less efficient than an ftp
connection (though it might be in practice, that's just an engineering problem
to solve).

If you already have files sitting on the server and want to grant access to
them to remote users then I would say using a SECURITY DEFINER function is
exactly what you want. The server doesn't know anything about the remote user
so it definitely needs the dba to tell it to allow access to the files.

--
greg

Greg Stark <gsstark@mit.edu> writes:

I was only suggesting using this from a local unix user where you can
actually authoritatively say something about the uid of the connecting
user. I suggested that if the owner of the file matches the uid of the
connecting user (which you can get on a unix domain socket)

... on some platforms ... and half the world connects over TCP even on
local connections ...

then there's no reason not to grant access to the file.

Assuming that the server itself can get at the file, which is
questionable if the file is owned by the connecting user rather than the
server (and, for instance, may be located under a not-world-readable
home directory). And then there are interesting questions like whether
the server and the user see eye-to-eye on the name of the file (consider
server inside chroot jail, AFS file systems, etc).

There are enough holes in this to make it less than attractive. We'd
spend more time answering questions about "why doesn't this work" than
we do now, and I remain unconvinced that there would be no exploitable
security holes.

regards, tom lane

On Mon, 2005-08-29 at 18:59, Tom Lane wrote:

Greg Stark <gsstark@mit.edu> writes:

I was only suggesting using this from a local unix user where you can
actually authoritatively say something about the uid of the connecting
user. I suggested that if the owner of the file matches the uid of the
connecting user (which you can get on a unix domain socket)

... on some platforms ... and half the world connects over TCP even on
local connections ...

then there's no reason not to grant access to the file.

Assuming that the server itself can get at the file, which is
questionable if the file is owned by the connecting user rather than the
server (and, for instance, may be located under a not-world-readable
home directory). And then there are interesting questions like whether
the server and the user see eye-to-eye on the name of the file (consider
server inside chroot jail, AFS file systems, etc).

There are enough holes in this to make it less than attractive. We'd
spend more time answering questions about "why doesn't this work" than
we do now, and I remain unconvinced that there would be no exploitable
security holes.

Plus, how is the server supposed to KNOW that you have access to the
file? psql may know who you are, but the server only knows who you are
in the "postgresql" sense, not the OS sense.

Scott Marlowe <smarlowe@g2switchworks.com> writes:

Plus, how is the server supposed to KNOW that you have access to the
file? psql may know who you are, but the server only knows who you are
in the "postgresql" sense, not the OS sense.

My original suggestion was that clients connected via unix domain sockets
should be allowed to read any file owned by the same uid as the connecting
client. (Which can be verified using getpeereid/SO_PEERCRED/SCM_CREDS.)

Alternatively and actually even better and more secure would be passing the fd
directly from the client to the server over the socket. That avoids any
question of the server bypassing any security restrictions. The client is
responsible for opening the file under its privileges and handing the
resulting fd to the server over the socket.

None of this helps for remote clients of course but remote clients can just
ftp the file to the server anyways and some manual intervention is necessarily
needed by the DBA to create a security policy for them.

--
greg

Greg Stark <gsstark@mit.edu> writes:

Alternatively and actually even better and more secure would be
passing the fd directly from the client to the server over the socket.

Sure ... on the platforms that support that, for the connection types
for which they support it. But I think that in the long run we'd regret
inventing any SQL operations whose semantics depend on the transport
mechanism.

regards, tom lane

On Tue, Aug 30, 2005 at 11:20:49PM -0400, Greg Stark wrote:

Scott Marlowe <smarlowe@g2switchworks.com> writes:

Plus, how is the server supposed to KNOW that you have access to the
file? psql may know who you are, but the server only knows who you are
in the "postgresql" sense, not the OS sense.

My original suggestion was that clients connected via unix domain sockets
should be allowed to read any file owned by the same uid as the connecting
client. (Which can be verified using getpeereid/SO_PEERCRED/SCM_CREDS.)

Alternatively and actually even better and more secure would be passing the fd
directly from the client to the server over the socket. That avoids any
question of the server bypassing any security restrictions. The client is
responsible for opening the file under its privileges and handing the
resulting fd to the server over the socket.

None of this helps for remote clients of course but remote clients can just
ftp the file to the server anyways and some manual intervention is necessarily
needed by the DBA to create a security policy for them.

What do people think about the Oracle method where bulk data operations
can only occur in a specified directory? Making that restriction might
address some of the security concerns. I don't think we should change
COPY in such a way that you *have* to use a specified directory, but if
it was an option that helped with the security concerns...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com 512-569-9461

Jim Nasby asked:

What do people think about the Oracle method where bulk data operations

can only occur in a specified directory? Making that restriction might
address some of the security concerns. I don't think we should change
COPY in such a way that you *have* to use a specified directory, but if
it was an option that helped with the security concerns...

Oracle's new (well, since 9i) DIRECTORY object (see
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_5007.htm#sthref4678)
has proven itself to be a flexible approach for me.

A privileged account creates the DIRECTORY object like so:

CREATE OR REPLACE DIRECTORY my_dir AS '/var/tmp/my_dir';

and then grants access to it:

GRANT READ ON my_dir to db_username;

I'd envision the COPY command using the DIRECTORY object something like:

COPY my_table FROM my_dir:'file.txt';