BUG #1830: Non-super-user must be able to copy from a file

On Wed, Aug 17, 2005 at 09:22:16 +0100,
Bernard <bht@actrix.gen.nz> wrote:

The following bug has been logged online:

This isn't a bug and you really should have asked this question on
another list. I am moving the discussion over to the general list.

Bug reference: 1830
Logged by: Bernard
Email address: bht@actrix.gen.nz
PostgreSQL version: 8.0.3
Operating system: Linux RedHat 9
Description: Non-super-user must be able to copy from a file
Details:

On the attempt to bulk load a table from a file that is owned by the
non-superuser current database user, the following error message is
printed:

"must be superuser to COPY to or from a file"

What is the reason for this limitation?

This is described in the documentation for the copy command.

It can't justifiably be for security reasons because if a web application
such as tomcat requires to bulk load tables automatically on a regular basis
then one would be forced to let the web application connect as superuser,
which is very bad for security.

No, because you can have the app read the file and then pass the data to
the copy command. To do this you use STDIN as the file name.

In MySQL bulk loading works for all users.

You can use the \copy command in psql to load data from files.

Bruno and interested list members

I want to follow what is suggested here. How are STDIN and STDOUT
addressed when using the JDBC driver?

Or in other words where can I write or receive megabytes of data?

I would not want to append this to the String of a SQL Statement in
Java because that is a String in memory.

Thanks

Bernard

On Wed, 17 Aug 2005 06:51:12 -0500, you wrote:

On Fri, Aug 19, 2005 at 10:16:29AM +1200, Bernard wrote:

Bruno and interested list members

I want to follow what is suggested here. How are STDIN and STDOUT
addressed when using the JDBC driver?

Or in other words where can I write or receive megabytes of data?

I don't know how JDBC does it, but as an example the Perl Pg module has
these functions:

$ret = $conn->putline($string)

Sends a string to the backend. The application must explicitly
send the two characters "\." to indicate to the backend that it
has finished sending its data.

$ret = $conn->putnbytes($buffer, $nbytes)

Sends n bytes to the backend. Returns 0 if OK, EOF if not.

$ret = $conn->endcopy

This function waits until the backend has finished the copy.
It should either be issued when the last string has been sent to
the backend using putline or when the last string has been
received from the backend using getline. endcopy returns 0 on
success, 1 on failure.

Looking at google I see the following:

http://archives.postgresql.org/pgsql-jdbc/2004-10/msg00166.php

saying it's not supported but there is a patch. That was a year ago
though. Some later emails suggest it is possible.

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Bernard wrote:

I want to follow what is suggested here. How are STDIN and STDOUT
addressed when using the JDBC driver?

The current JDBC driver doesn't support this mode of COPY.

There was some work done in the past to support this but it never got to
the point of making it into the official driver; see the pgsql-jdbc
archives for details.

-O

Oliver and interested list members:

Thanks for the related information.

The majority of JDBC users trying to bulk load tables would not want
to send the data through their connection. This connection is designed
to send commands and to transfer only as much data as necessary and as
little as possible.

In other words, COPY, and its corresponding commands in other datbase
engines are designed to transfer data using the fastest possible
method, which is typically to and from files.

For the majority JDBC users, there is no real need for a STDIN/STDOUT
option because they can always write to a file.

The need is only created by the limitations of the Postgres COPY
command.

I can't see why a workaround should be developed instead of or before
fixing the COPY command.

It works in other DB engines.

Regards

Bernard

On Fri, 19 Aug 2005 11:10:42 +1200, you wrote:

Bernard wrote:

The majority of JDBC users trying to bulk load tables would not want
to send the data through their connection. This connection is designed
to send commands and to transfer only as much data as necessary and as
little as possible.

I don't understand why this is true at all -- for example, our
application currently does bulk INSERTs over a JDBC connection, and
moving to COPY has been an option I looked at in the past. Importing
lots of data from a remote machine is hardly an uncommon case.

The need is only created by the limitations of the Postgres COPY
command.

I can't see why a workaround should be developed instead of or before
fixing the COPY command.

It works in other DB engines.

I guess that other DB engines don't care about unprivileged DB users
reading any file that the backend can access.

-O

Oliver and interested list members:

I was referring to the majority of users wanting to "bulk" load tables
not to the majority of all or whatever users who may or may not know
or care about the difference in performance between INSERT and COPY.

This difference of performance is the main reason for the COPY
command, and this is also the reason why bulk loading through the JDBC
interface will never match the performance of the COPY fith files
command.

The COPY command with STDIN or STDOUT is a speciality that the
majority of users would not normally ask for because they usually
think in terms of files and rightly so.

Comparable with a STDIN/STDOUT workaround would be to pipe input and
output to and from SQL stored procedures.

What I mean to say is that we want this to be strictly server side for
best performance and we don't want to get the client involved in the
raw processing which is in violation of any 3 tier client-server
architecture.

In addition to this, not only will the client and network be loaded
with additional processing demand, but the server load will also
increase because it has to service the JDBC interface for I/O.

The whole architectural setup for such "bulk" loading is a mess.

Regards,

Bernard

On Fri, 19 Aug 2005 12:27:01 +1200, you wrote:

"Oliver Jowett" <oliver@opencloud.com>
wrote:43052755.7000003@opencloud.com...

Bernard wrote:

The majority of JDBC users trying to bulk load tables would not want
to send the data through their connection. This connection is designed
to send commands and to transfer only as much data as necessary and as
little as possible.

I don't understand why this is true at all -- for example, our
application currently does bulk INSERTs over a JDBC connection, and
moving to COPY has been an option I looked at in the past. Importing
lots of data from a remote machine is hardly an uncommon case.

When exporting and importing data from other data sources, there maybe
many rows to be moved. In this special case, should COPY be faster than
INSERTs? Have pgsql-jdbc supported COPY?

If I read correctly, what Bernard want is COPY from/to server-side files.
That is actually a security risk for non-superuser. They may read or
overwrite
any files can read/write by postgres server process.

Bernard wrote:

This difference of performance is the main reason for the COPY
command, and this is also the reason why bulk loading through the JDBC
interface will never match the performance of the COPY fith files
command.

In some admittedly unscientific tests I see less than 10% difference
between server-side COPY and client-side COPY FROM STDIN (psql's \copy
command) on a 28mb input file. That's down in the per-run noise.

Doing it via JDBC will undoubtably add some extra overhead, but I'd
estimate that it's about the same sort of overhead as writing your data
out to a file from Java in the first place takes.

If you've already got the data in a file, why not just use psql's \copy
command? This uses COPY FROM STDIN, reads the file as the user running
psql, and does not require superuser permissions.

The whole architectural setup for such "bulk" loading is a mess.

Do you have a concrete suggestion for improving bulk loading that
doesn't open security holes?

-O

Bernard wrote:

Oliver and interested list members:

[...]

And please fix your anti-spam system so it doesn't send me a "you must
jump through these hoops to send me email" message every time please!

(usual cc: to poster removed for that reason)

-O

Oliver and interested list members:

In the majority of bulk load cases, the input exists as a file already
and cannot be had without reading from that file. So the writing of
the file does not count as an additional processing overhead.

The use of psql in our case requires the launching of an external
process from within the running Java application, which is an overhead
in processing and code maintenance that must not be under-estimated.

My suggestions for improving the COPY command so it can be used by
non-superuser users would be as follows:

1) Add optional Postgresql user permission to use the COPY command
with files.

or

2) Split up security risk calculations between the two directions "TO"
and "FROM" and relax security. Look at MySQL for clues. The
application developer can manage security on file system permission
level.

or

3) Close the ident loop in such a way that if a Postgresql user was
granted access via ident as an operating system user then the COPY
command is executed as a process with that user ID and not as postgres
superuser.

Option 2) would possibly be the easiest and my personal preference.

Option 1) would possibly the most politically correct one and would
fully satisfy my requirements.

Option 3) would possibly open a can of worms, especially because there
are already unresolved authentication issues with ident and Java.

There are systems where a database user cannot possibly be a lose
cannon type of user. For example, in a 3 tier client-server
application or in most web applications the end users never get their
hands on a database connection. The connections are owned by the
server and the users cannot write server code. In these cases there is
the question why not use superuser postgres for the connections? I
will do it but I have unspecified reservations - just a strange
feeling.

I hope that this discussion was not entirely useless and will lead to
an improvement of the current status, whatever it may be.

Oliver, my apologies regarding the spam filter - that part of my ISP's
service is currently unaccessible for configuration.

Regrads

Bernard

On Fri, 19 Aug 2005 14:11:38 +1200, you wrote:

Bernard wrote:

1) Add optional Postgresql user permission to use the COPY command
with files.

or

2) Split up security risk calculations between the two directions "TO"
and "FROM" and relax security. Look at MySQL for clues. The
application developer can manage security on file system permission
level.

Could you be more specific rather than sending me on a random trawl
through the documentation of software I'm not familiar with?

The issue is that COPY FROM will read any file that the backend user can
access, so allowing it is equivalent to allowing read access to all data
in the database, including configuration and access control information,
plus whatever other files you happen to be able to read on the system:

testbulk=# create temporary table haxxor(line text);
CREATE TABLE
testbulk=# copy haxxor from '/etc/passwd';
COPY
testbulk=# select * from haxxor;
line
-------------------------------------------------------------------
root:x:0:0:root:/root:/bin/bash
bin:x:1:1:bin:/bin:/bin/false
[...]

I don't see how splitting up the checks for COPY FROM vs COPY TO helps.

3) Close the ident loop in such a way that if a Postgresql user was
granted access via ident as an operating system user then the COPY
command is executed as a process with that user ID and not as postgres
superuser.

The backend doesn't run as root so it can't seteuid().

Option 3) would possibly open a can of worms, especially because there
are already unresolved authentication issues with ident and Java.

What are the unresolved issues here? I haven't seen any reports on the
JDBC list beyond some older IPV6-related problems which are easy enough
to work around.

-O

Bernard wrote:

2) Split up security risk calculations between the two directions "TO"
and "FROM" and relax security. Look at MySQL for clues. The
application developer can manage security on file system permission
level.

I looked at MySQL's docs briefly and its behaviour seems almost the same
as PostgreSQL's with some minor differences:

- the equivalent to COPY is "LOAD DATA INFILE"
- the equivalent to FROM STDIN is "LOCAL"
- for non-LOCAL loads, the DB user must have FILE privilege which is
"file access on server host".

Given FILE privilege in MySQL, you can read existing files and create
new files based on the access the server user has.

It sounds like what you really want is the ability to grant something
like FILE access without granting all superuser rights? Sounds like a
feature request, not a bug, to me :-)

Also, you better hope that there's no sensitive information readable by
the server user that could be used to gain superuser access.. such as
.pgpass files or info from pg_hba.conf, for example.

-O

On 2005-08-19, Bernard <bht@actrix.gen.nz> wrote:

Oliver and interested list members:

In the majority of bulk load cases, the input exists as a file already

But not necessarily on the server.

The use of psql in our case requires the launching of an external
process from within the running Java application, which is an overhead
in processing and code maintenance that must not be under-estimated.

Certainly supporting COPY via STDIN within the java code seems preferable.

My suggestions for improving the COPY command so it can be used by
non-superuser users would be as follows:

1) Add optional Postgresql user permission to use the COPY command
with files.

Not acceptable, since the ability to copy from a file permits you to
read from the internals of the database itself bypassing security
restrictions; in particular, if there is a password for the postgres
superuser, then it would be trivially exposed by this method. A user
with permission to use COPY thus becomes security-equivalent to a
superuser in any case.

or

2) Split up security risk calculations between the two directions "TO"
and "FROM" and relax security. Look at MySQL for clues. The
application developer can manage security on file system permission
level.

Same problem as above. COPY FROM is not in any sense less of a security
risk than COPY TO.

or

3) Close the ident loop in such a way that if a Postgresql user was
granted access via ident as an operating system user then the COPY
command is executed as a process with that user ID and not as postgres
superuser.

Postgres does not itself run as root, therefore it lacks the ability to
spawn a program that runs under a different userid to itself.

Over the local socket, which is the only context in which ident auth is
at all trustable, it would in theory be possible to implement COPY to a
file descriptor opened by the client and passed through the socket. I
personally think it is unlikely that this would be worth the (not
inconsiderable) amount of work needed to implement it, since the performance
overhead of copying the data via the socket instead is not a large factor
in the overall cost of a large copy.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

On Fri, 19 Aug 2005, Bernard wrote:

My suggestions for improving the COPY command so it can be used by
non-superuser users would be as follows:

If you want to do this without switching to a different UNIX user, can't
you already write a small SECURITY DEFINER function as a superuser that
does the copy from file based on arguments and then give permissions to
that function to the appropriate non-superusers?

[ A bit off topic, but... ]

Oliver Jowett <oliver@opencloud.com> writes:

And please fix your anti-spam system so it doesn't send me a "you must
jump through these hoops to send me email" message every time please!

It's standard policy on the PG lists that we boot subscribers who
auto-reply to list mail like that. If you find yourself getting
unsolicited 'bot replies from list postings, let Marc know.

(Note: if you replied To: somebody and cc: to the list, and you got
the antispam challenge due to the To: copy, that's not grounds for
list removal. But it's still a sign of a jerk. If I take the time
to answer someone's question, I'm not going to look very favorably
on a demand to confirm that I'm a human before they'll deign to read
my answer.)

regards, tom lane

Oliver Jowett <oliver@opencloud.com> writes:

It sounds like what you really want is the ability to grant something
like FILE access without granting all superuser rights? Sounds like a
feature request, not a bug, to me :-)

AFAICT, the complaint really boils down to there not being any support
for COPY-from-client in the JDBC driver. Which is definitely a feature
request, but not one directed to the server geeks ;-)

What is the story on JDBC COPY support, anyway? I'm aware that there's
an unofficial patch for that, but I'm not clear about why it's not made
it into the accepted version.

regards, tom lane

Tom Lane wrote:

What is the story on JDBC COPY support, anyway? I'm aware that there's
an unofficial patch for that, but I'm not clear about why it's not made
it into the accepted version.

I didn't like the whole "here is an undifferentiated stream of data"
approach -- there were some JDBC interfaces we could adapt to read/write
typed data. That never happened, though.

I suppose we could apply a patch similar to the original one, given that
there doesn't seem like much interest in a typed version, but it's
likely to need rework as there's been at least one overhaul of the
driver's protocol handling layer since then.

-O

Tom Lane wrote:

Oliver Jowett <oliver@opencloud.com> writes:

It sounds like what you really want is the ability to grant something
like FILE access without granting all superuser rights? Sounds like a
feature request, not a bug, to me :-)

AFAICT, the complaint really boils down to there not being any support
for COPY-from-client in the JDBC driver.

Bernard was also objecting to the overhead of pushing the data down a
TCP pipe when it's already available locally, I think.. I didn't find
any real difference there when I compared the two methods, though.

-O