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

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 6: explain analyze is your friend

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





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

http://archives.postgresql.org

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


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

http://archives.postgresql.org

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


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

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

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

http://archives.postgresql.org

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

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

* 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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFDBduIrzgMPqB3kigRAl6QAJ9PX08LTSdGeG/+FIi8Tpxk2y/LrgCgjYdR
+Uql1yEkgTbFAMu/rNbb83Q=
=s0GK
-----END PGP SIGNATURE-----

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

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

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.


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings