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


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

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

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

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

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?

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

[ 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

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

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

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

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

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

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

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

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

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

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