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@xxxxxxxxxxxxxx so that your
> message can get through to the mailing list cleanly
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
