Bernard schrieb:
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.
Well, since you are a Java programmer, why not fixing it?
The soruce is all yours :-)
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.
MySQL isnt by any means a promoter of database or security standards :-)
Btw, by the time you filled that thread, you could just have
put your COPY call into a plsql function with securitydefiner.
This way making it possible to copy (preferably hard coded)
files into the tables.
Just my 0.002Ct :-)
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
