Re: [BUGS] BUG #1830: Non-super-user must be able to copy - Mailing list pgsql-general

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