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