The following bug has been logged online:

Bug reference: 1830
Logged by: Bernard
Email address: bht (AT) actrix (DOT) 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?

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.

In MySQL bulk loading works for all users.

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

On Wed, Aug 17, 2005 at 09:22:16 +0100,
Bernard <bht (AT) actrix (DOT) gen.nz> wrote:
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.

This is described in the documentation for the copy command.

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.

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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:


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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:


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

On 2005-08-19, Bernard <bht (AT) actrix (DOT) gen.nz> wrote:
But not necessarily on the server.

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

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.

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

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

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

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

Andrew

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

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

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

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.

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.

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.

I agree one has to compare costs carefully.

Regards,

Bernard

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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



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

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 (AT) actrix (DOT) gen.nz>
To: "Sean Utt" <sean (AT) strateja (DOT) 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:

....





---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org