Thread: Re: 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.

>
> We need a Postgresql solution.
>
> We have a web application where both MySQL and Postresql are supported. With
> Postgresql, the application would have to connect as user postgres. We have
> to explain this security risk to our clients very clearly.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

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 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.
>
>>
>> We need a Postgresql solution.
>>
>> We have a web application where both MySQL and Postresql are supported. With
>> Postgresql, the application would have to connect as user postgres. We have
>> to explain this security risk to our clients very clearly.
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: Don't 'kill -9' the postmaster
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend

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

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:
>
>> 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
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match

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:

>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
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: Don't 'kill -9' the postmaster

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:
>
>> 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
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly

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

Andrew

On Fri, 19 Aug 2005 04:17:16 -0000, you wrote:

>> In the majority of bulk load cases, the input exists as a file already
>
>But not necessarily on the server.

True. But I am concerned with the server, and there I want that things
are handled on the server, not on the client.

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

Why do you say that? That option does not exist because the Postgresql
JDBC driver does not support it.

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

May be. Not acceptable by whom?

If the owner of an application owning the connections trusts the
application and gets the postgres superuser to grant it the right to
read from files, then it is obviously acceptable to the owner of the
application and to the postgres superuser. There is no doubt about
that and the owner of the application is not concerned with 3rd party
acceptability. This would be a solution even if Postgres system files
were totally exposed. Better than nothing.

But we can take this one step further so that we don't even need to
trust ourselves:

The logical next step is that for a non-postgresql-superuser user,
COPY FROM files have to be world-readable and COPY TO files and
directories have to be world-writable. The server checks the file
attributes and grants copy permission depending on them. Obviously any
Postrgres system files must not be world-readable and world-writable.

Problem solved. One doesn't need to be a genius to figure this out.

Not having at least this primitive solution is quite powerless.

Simply rejecting this command when the user is not superuser can only
be considered a temporary workaround solution.

It is long overdue for replacement.

And trust me, it is quite frustrating having to hit such a barrier
after having seen this feature implemented in MySQL for the last ten
years. I am not talking about myself only. Just do a google groups
search "jdbc postgres COPY STDIN" and you will see what I mean.

Lots of frustration, improvised stuff but no generic solution to this
simple problem.

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

There is obviously a difference between the permission to read system
files and to destroy them. But this was only a suggestion. The
distinction might not be required at all.

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

I did not know the internals of whether this could be done or not. It
is just a conceptual idea where somehow the server may be able to
utilise file permission information. I have my own reservations, too.

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

I agree one has to compare costs carefully.

Regards,

Bernard

Oliver Jowett <oliver@opencloud.com> writes:

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

What makes you think it's necessarily available locally?

--
greg

Greg,

The desired COPY FILE functionality for a local non-superuser user
would require a local file. That file is available locally.

A suggested workaround COPY with STDIN would involve the TCP pipe.
This does of course have the support for remote uploads.

But I am not currently interested in remote data transfers.

Regards

Bernard

On 19 Aug 2005 02:03:54 -0400, you wrote:

>
>Oliver Jowett <oliver@opencloud.com> writes:
>
>> 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.
>
>What makes you think it's necessarily available locally?

This is silly.

The bug being reported is that a non-super-user can't copy from a server
side file with JDBC.

There are a jillion (no, really, a jillion) other ways to accomplish this,
because as is the Perl motto, there is more than one way to do it.

If this is really so important, Bernard should be able to get together a
group of people who would pay the JDBC developers (or another group of
qualified programmers) to solve their problem.

If it isn't important enough to pay somebody to solve the problem, it isn't
that damned important.

If I had a dollar for every time some client said, "It's an emergency, and
we really need help!" and I said, "Okay, if it is an emergency, we can drop
everything we are doing now, and solve your problem at time and a half." and
they said, "Never mind, it wasn't an emergency after all." I'd have at least
10 dollars. :-)

Remember, open source means free as in speech, not as in beer.
With open source, there is a free lunch, but you have to grow the grain,
feed part to the livestock, and grind the rest into flour, bake the bread,
make the cold cuts, and the mayo.... oh, you wanted tomato? Hope you planted
some....

Everyone involved in developing open source software is doing so either with
support from their employer (who hope that they will reap some benefit) or
they earn a living by consulting.

Bottom line:
If this is really important to you, either fix the problem, or provide
someone else with incentive to fix the problem.
In this case, attempting to appeal to/tear down the ego of the developers is
not working, so you will have to resort to more concrete methods, i.e.
money. Nice effort though. No matter how much our pride is involved in this,
nothing greases the wheels like cash.

Sean

Yeah, I'm -vvv tonight.

psql provides \COPY table from file

how about
String cmd = "psql -c '\COPY table from file' -U user -d database"
Process p = Runtime.getRuntime( ).exec( cmd );
yatta yatta yatta, blah blah blah

naturally, if the database/server doesn't trust you, you'll have to jump
some hoops to get the password to the server, but then, trust is really the
issue, isn't it? And if you've been keeping an eye on the securityfocus
lists, you'd know that it is all about the trust.

What I keep hearing in this ongoing thread is the tradeoff between
convenience and security.

I want security, and you want convenience. If you own the server, you win.
Buy a server, pay for the bandwidth, learn to administer a server, and all
your problems disappear.

Don't want to do that? Then you have to live by my rules, because I own the
server, where your database lives.

Once again, if you are the expert, solve the problem. If you are not, pay
the expert to solve the problem. If the cost to fix the problem is higher
than you are willing to pay, the problem is not that important. If the cost
to fix the problem is more than you can afford --- bummer
dude!!!!

That is the way the world works. If you don't like it, look for a different
world. If you are rich, and/or completely without morals, and prone to
self-centered fantasy try the world of people who pretend to be Conservative
Republicans in the USA. (This is not intended as a slam against people who
are actually Republicans, or Conservatives --- unless they have failed to
speak out against those who usurp their identity.)

Sean



----- Original Message -----
From: "Bernard" <bht@actrix.gen.nz>
To: "Greg Stark" <gsstark@mit.edu>
Cc: <pgsql-bugs@postgresql.org>; <pgsql-general@postgresql.org>
Sent: Friday, August 19, 2005 12:21 AM
Subject: Re: [GENERAL] [BUGS] BUG #1830: Non-super-user must be able to copy
from a


Greg,

The desired COPY FILE functionality for a local non-superuser user
would require a local file. That file is available locally.

A suggested workaround COPY with STDIN would involve the TCP pipe.
This does of course have the support for remote uploads.

But I am not currently interested in remote data transfers.

Regards

Bernard

On 19 Aug 2005 02:03:54 -0400, you wrote:

>
>Oliver Jowett <oliver@opencloud.com> writes:
>
>> 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.
>
>What makes you think it's necessarily available locally?


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

On Fri, Aug 19, 2005 at 05:49:06PM +1200, Bernard wrote:
> If the owner of an application owning the connections trusts the
> application and gets the postgres superuser to grant it the right to
> read from files, then it is obviously acceptable to the owner of the
> application and to the postgres superuser. There is no doubt about
> that and the owner of the application is not concerned with 3rd party
> acceptability. This would be a solution even if Postgres system files
> were totally exposed. Better than nothing.

I think what people are trying to tell you is that "permission to read
server files" == "superuser". If the postgres superuser grants you
permission to read server files, they then have access to all files in
all databases in the server, i.e. they are superuser. You know, read
passwords, see ident mappings, etc...

So in your case, what's the problem with making your user a superuser,
it's not like you're limited to just one.

Finally, as someone pointed out, you can create a function to execute
the copy as a superuser and let your normal user call it.

No need to open up the whole system just to solve something that a five
line function will do just as well.
--
Martijn van Oosterhout   <kleptog@svana.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.

cc'ing the list, haven't seen it show up there....

And yeah, I'm using Outlook Express and the quoting is crappy. So sue me....

I never saw your request rejected, though it did rank low on priority -- in
my book at least. The problem has been discussed at length, and there are
multiple ways to solve your problem without making any changes to postgres.
Because there are so many ways to solve your problem, your request amounts
to a feature, not a bug, and a very low ranking feature at that. Just
because other similar systems do something, does not mean that anyone else
should. If you like the way they do it better, go with them. Microsoft
allowed Outlook to set up volunteer administrators if they sent a properly
crafted email -- some people like that sort of thing.

While I really appreciate your attempts to motivate the postgres team to
action through peer pressure (mysql and all the other databases kiss on the
first date) -- as I said, if you can't personally fix the problem, and you
won't/can't pay someone else to fix the problem, then you have to hope that
the problem bugs someone who can pay to fix the problem, or that someone who
can fix the problem feels the itch, and can scratch it. That may or may not
make you happy, but it is the reality. Again, there are many other ways to
solve this problem (uploading bulk table date) -- I am going to make a
wild-ass guess that the phpPgAdmin team have had to address this issue
(http://phppgadmin.org/) and have come to some compromise.

Dump the vinegar, try the honey.

Sean


----- Original Message -----
From: "Bernard" <bht@actrix.gen.nz>
To: "Sean Utt" <sean@strateja.com>
Sent: Friday, August 19, 2005 1:52 AM
Subject: Re: [BUGS] BUG #1830: Non-super-user must be able to copy from a
file


Sean,

I am glad that our discussion has come this far, because at the start
of it, my request was rejected as not being relevant and I was looking
a bit depressed.

The options for fast bulk loads from within a Java server programs as
non-superuser user are clearly limited and inefficient.

I still had trouble explaining the issue and after some time, it has
become obvious that the STDIN option suggested for COPY is not
available in the JDBC driver.

Oliver asked to suggest a solution that does not open any security
holes.

A simple solution has been suggested that works without changes to the
JDBC driver. I repeat it here:

For a non-postgresql-superuser user, COPY FROM files have to be
world-readable and COPY TO files and directories have to be
world-writable. The server checks the file attributes and grants copy
permission depending on them. Obviously any Postrgres system files
must not be world-readable and world-writable.

I am not suggesting to enhance the JDBC driver to support COPY with
STDIN, because my architecture doesn't require it and it is clearly
going to be slower due to driver/comms overhead.

I appreciate your comments regarding funding of developers. I hope I
will be able to provide a share in the future but currently I am not
in the position to do so.

Regards

Bernard

On Fri, 19 Aug 2005 00:46:29 -0700, you wrote:

...

>Bottom line:
>If this is really important to you, either fix the problem, or provide
>someone else with incentive to fix the problem.
>In this case, attempting to appeal to/tear down the ego of the developers
>is
>not working, so you will have to resort to more concrete methods, i.e.
>money. Nice effort though. No matter how much our pride is involved in
>this,
>nothing greases the wheels like cash.
>
>Sean

Sean,

Thanks for the psql suggestion. Certainly this is a possible robust
solution. The disadvantage could be that you need a new process for
each table. Or can you launch psql with multiple commands?

I am not looking for convenience mainly. I am interested in execution
speed, maintainability and portability.

Luckily I control the procedures for the server and I am now trusting
the application enough to give multiple instances of it postges
superuser connections.

With superuser connections, I can use COPY FROM FILE without
restrictions.

It should work unless something sinister in superuser jdbc connections
pops up. Any warnings?

I do this because I prefer to keep things clean, efficient and
streamlined. The application can run with different database engines.
The user decides which one to use. They may get documentation about
the security risks of running with superuser and the decision which
database to use is theirs.

Regards

Bernard

On Fri, 19 Aug 2005 01:10:46 -0700, you wrote:

>Yeah, I'm -vvv tonight.
>
>psql provides \COPY table from file
>
>how about
>String cmd = "psql -c '\COPY table from file' -U user -d database"
>Process p = Runtime.getRuntime( ).exec( cmd );
>yatta yatta yatta, blah blah blah
>
>naturally, if the database/server doesn't trust you, you'll have to jump
>some hoops to get the password to the server, but then, trust is really the
>issue, isn't it? And if you've been keeping an eye on the securityfocus
>lists, you'd know that it is all about the trust.
>
>What I keep hearing in this ongoing thread is the tradeoff between
>convenience and security.
>
>I want security, and you want convenience. If you own the server, you win.
>Buy a server, pay for the bandwidth, learn to administer a server, and all
>your problems disappear.
>
>Don't want to do that? Then you have to live by my rules, because I own the
>server, where your database lives.
>
>Once again, if you are the expert, solve the problem. If you are not, pay
>the expert to solve the problem. If the cost to fix the problem is higher
>than you are willing to pay, the problem is not that important. If the cost
>to fix the problem is more than you can afford --- bummer
>dude!!!!
>
>That is the way the world works. If you don't like it, look for a different
>world. If you are rich, and/or completely without morals, and prone to
>self-centered fantasy try the world of people who pretend to be Conservative
>Republicans in the USA. (This is not intended as a slam against people who
>are actually Republicans, or Conservatives --- unless they have failed to
>speak out against those who usurp their identity.)
>
>Sean
>
>
>
>----- Original Message -----
>From: "Bernard" <bht@actrix.gen.nz>
>To: "Greg Stark" <gsstark@mit.edu>
>Cc: <pgsql-bugs@postgresql.org>; <pgsql-general@postgresql.org>
>Sent: Friday, August 19, 2005 12:21 AM
>Subject: Re: [GENERAL] [BUGS] BUG #1830: Non-super-user must be able to copy
>from a
>
>
>Greg,
>
>The desired COPY FILE functionality for a local non-superuser user
>would require a local file. That file is available locally.
>
>A suggested workaround COPY with STDIN would involve the TCP pipe.
>This does of course have the support for remote uploads.
>
>But I am not currently interested in remote data transfers.
>
>Regards
>
>Bernard
>
>On 19 Aug 2005 02:03:54 -0400, you wrote:
>
>>
>>Oliver Jowett <oliver@opencloud.com> writes:
>>
>>> 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.
>>
>>What makes you think it's necessarily available locally?
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match

Sean,

Very diplomatic way to get the message across without offending anyone
except the bastards.

Capatalism is good for development. But it has to be kept in check as
to not destroy the basis on which it once grew and provided fair
chances for anyone to participate. Who is keeping it in check today?
We need a reformed system. Maybe the world needs a social market
economy.

On Fri, 19 Aug 2005 01:10:46 -0700, you wrote:

...
>That is the way the world works. If you don't like it, look for a different
>world. If you are rich, and/or completely without morals, and prone to
>self-centered fantasy try the world of people who pretend to be Conservative
>Republicans in the USA. (This is not intended as a slam against people who
>are actually Republicans, or Conservatives --- unless they have failed to
>speak out against those who usurp their identity.)
>
>Sean

Greg Stark wrote:
> Oliver Jowett <oliver@opencloud.com> writes:
>
>
>>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.
>
>
> What makes you think it's necessarily available locally?

Nothing in general -- that was just the case he had.

-O

Bernard wrote:

>>Certainly supporting COPY via STDIN within the java code seems preferable.
>
> Why do you say that? That option does not exist because the Postgresql
> JDBC driver does not support it.

If you raise this on pgsql-jdbc (so far, I haven't seen anything on that
list from you at all..) we can look at putting support in.

In fact Kris just started a thread to that end -- perhaps you would like
to comment on it?

-O

Bernard schrieb:
> Andrew
>
> On Fri, 19 Aug 2005 04:17:16 -0000, you wrote:
>
>
>>>In the majority of bulk load cases, the input exists as a file already
>>
>>But not necessarily on the server.
>
>
> True. But I am concerned with the server, and there I want that things
> are handled on the server, not on the client.
>
>
>>>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.
>
>
> Why do you say that? That option does not exist because the Postgresql
> JDBC driver does not support it.

Well, since you are a Java programmer, why not fixing it?
The soruce is all yours :-)

>
>>>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.
>
>
> May be. Not acceptable by whom?
>
> If the owner of an application owning the connections trusts the
> application and gets the postgres superuser to grant it the right to
> read from files, then it is obviously acceptable to the owner of the
> application and to the postgres superuser. There is no doubt about
> that and the owner of the application is not concerned with 3rd party
> acceptability. This would be a solution even if Postgres system files
> were totally exposed. Better than nothing.
>
> But we can take this one step further so that we don't even need to
> trust ourselves:
>
> The logical next step is that for a non-postgresql-superuser user,
> COPY FROM files have to be world-readable and COPY TO files and
> directories have to be world-writable. The server checks the file
> attributes and grants copy permission depending on them. Obviously any
> Postrgres system files must not be world-readable and world-writable.
>
> Problem solved. One doesn't need to be a genius to figure this out.

> Not having at least this primitive solution is quite powerless.
>
> Simply rejecting this command when the user is not superuser can only
> be considered a temporary workaround solution.
>
> It is long overdue for replacement.
>
> And trust me, it is quite frustrating having to hit such a barrier
> after having seen this feature implemented in MySQL for the last ten
> years. I am not talking about myself only. Just do a google groups
> search "jdbc postgres COPY STDIN" and you will see what I mean.

MySQL isnt by any means a promoter of database or security standards :-)

Btw, by the time you filled that thread, you could just have
put your COPY call into a plsql function with securitydefiner.
This way making it possible to copy (preferably hard coded)
files into the tables.

Just my 0.002Ct :-)

* Stephan Szabo (sszabo@megazone.bigpanda.com) wrote:
>
> 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?

Generally, I think this is the approach that makes the most sense.  Of
course, the SECURITY DEFINER function should also check that the
arguments match a pre-defined list of valid file names/table names, etc.
Personally, I do like the idea of a user-level 'copy server-side files'
permission that could be granted to reduce the need for things to run as
superuser.  I'd probably still set up a SECURITY DEFINER function to a
user with those permissions as an additional layer of security but it'd
be nice to not have to run the function as superuser.

I understand the concern that a user might be able to escalate to
superuser status using that permission but I feel that's more an issue
that an administrator needs to understand and deal with than a problem
with allowing that permission.  Ways to avoid it would include: Using
PAM (it's at least somewhat difficult to crack a decent hash'd password
in /etc/shadow), Using local-socket-only ident only for superuser,
hacking Postgres to support Unix-like password hashing/checking (same
issue as w/ PAM though), hacking Postgres to support SASL (and then
using saslauthd so Postgres doesn't need access to the file which has
the password hashes directly), using Kerberos for authentication (my
personal favorite, Kerberos for users, local-ident only for superuser).

It is, of course, good to note that current Postgres 'md5' auth method
usage means that a compromise of pg_shadow (pg_authid) gives the
attacker superuser access immediately (the hash itself is the actual
authentication token, the password isn't actually interesting in that
case).

    Thanks,

        Stephen

On Fri, Aug 19, 2005 at 09:15:52AM -0400, Stephen Frost wrote:
> Personally, I do like the idea of a user-level 'copy server-side files'
> permission that could be granted to reduce the need for things to run as
> superuser.

There is one important point though: The server copying things is
seriously restricted. No matter how much authentication you do, the
server cannot *become* you. Hence it cannot access your files unless
they are world readable.

For similar reasons, it cannot prevent the user from accessing the
postgresql system files since it *is* the postgresql user and that
cannot be changed. The UNIX way around this would be for the client to
open the file with its permissions and then pass the FD to the server.
But that's a rather interesting twist that only works on local sockets.

> I'd probably still set up a SECURITY DEFINER function to a
> user with those permissions as an additional layer of security but it'd
> be nice to not have to run the function as superuser.

Superuser is still limited by the system uid, that can't be changed.

> It is, of course, good to note that current Postgres 'md5' auth method
> usage means that a compromise of pg_shadow (pg_authid) gives the
> attacker superuser access immediately (the hash itself is the actual
> authentication token, the password isn't actually interesting in that
> case).

It's a compromise solution. Normal authentication (aka shadow file):
server has only hash but password is in clear over the wire. MD5 auth:
server knows the password (or enough to spoof) but it's not in the
clear over the wire. Pick your poison...

For true security use public key auth (certificates / keys / etc).

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.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.

Martijn van Oosterhout <kleptog@svana.org> writes:
> On Fri, Aug 19, 2005 at 09:15:52AM -0400, Stephen Frost wrote:
>> Personally, I do like the idea of a user-level 'copy server-side files'
>> permission that could be granted to reduce the need for things to run as
>> superuser.

> There is one important point though: The server copying things is
> seriously restricted. No matter how much authentication you do, the
> server cannot *become* you. Hence it cannot access your files unless
> they are world readable.

And maybe not even then.  For instance, on a SELinux system, the
postmaster will probably be forbidden by kernel-enforced security
policies from reading or writing any files outside the /var/lib/pgsql/
tree.  (This sort of restriction is used for most network-accessible
daemons in SELinux, so as to limit the system's exposure in case someone
manages to crack into the daemon.)  Server-side COPY is essentially
useless even for superusers in such a context.

The correct answer to this whole thread is "get some COPY support in
JDBC".  It's unlikely you'll persuade anyone that relaxing the
restrictions on server-side file access is a good idea.  The thrust
of recent discussions has been more about tightening 'em, in fact.

            regards, tom lane

On Fri, 19 Aug 2005, Bernard wrote:

> But we can take this one step further so that we don't even need to
> trust ourselves:
>
> The logical next step is that for a non-postgresql-superuser user,
> COPY FROM files have to be world-readable and COPY TO files and
> directories have to be world-writable. The server checks the file
> attributes and grants copy permission depending on them. Obviously any
> Postrgres system files must not be world-readable and world-writable.
>
> Problem solved. One doesn't need to be a genius to figure this out.

No, it's not solved.  It prevents that problem for the configuration
files, but still gives access to other world readable files on the system
for example /etc/passwd on many systems (yes it's not terribly interesting
in general, but still is often not acceptable to retrieve).

You'd probably want to add the ability to setup which directories that are
allowed to be read or written to as configuration separately from unix
file permissions.

No, it doesn't take a genius, but it's not as trivial as you seem to think
it is, either.  And honestly, until there's a workable plan that addresses
these issues, opening it up seems foolish.

On Fri, Aug 19, 2005 at 08:03:39AM -0700, Stephan Szabo wrote:
> On Fri, 19 Aug 2005, Bernard wrote:
>
> > But we can take this one step further so that we don't even need to
> > trust ourselves:
> >
> > The logical next step is that for a non-postgresql-superuser user,
> > COPY FROM files have to be world-readable and COPY TO files and
> > directories have to be world-writable. The server checks the file
> > attributes and grants copy permission depending on them. Obviously any
> > Postrgres system files must not be world-readable and world-writable.
> >
> > Problem solved. One doesn't need to be a genius to figure this out.
>
> No, it's not solved.  It prevents that problem for the configuration
> files, but still gives access to other world readable files on the system
> for example /etc/passwd on many systems (yes it's not terribly interesting
> in general, but still is often not acceptable to retrieve).
>
> You'd probably want to add the ability to setup which directories that are
> allowed to be read or written to as configuration separately from unix
> file permissions.

FWIW, this is exactly what Oracle does. A DBA has to configure what
directories you can bulk copy to/from.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software        http://pervasive.com        512-569-9461