Skip site navigation (1) Skip section navigation (2)

Re: [GENERAL] BUG #1830: Non-super-user must be able to copy

From: Tino Wildenhain <tino(at)wildenhain(dot)de>
To: Bernard <bht(at)actrix(dot)gen(dot)nz>
Cc: andrew(at)supernews(dot)com, pgsql-bugs(at)postgresql(dot)org,pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] BUG #1830: Non-super-user must be able to copy
Date: 2005-08-19 11:31:56
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugspgsql-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 :-)

In response to

pgsql-bugs by date

Next:From: Stephen FrostDate: 2005-08-19 13:15:52
Subject: Re: BUG #1830: Non-super-user must be able to copy from a
Previous:From: Oliver JowettDate: 2005-08-19 11:12:31
Subject: Re: BUG #1830: Non-super-user must be able to copy from a

pgsql-general by date

Next:From: Tino WildenhainDate: 2005-08-19 11:34:12
Subject: Re: How to DES encrypt/decrypt strings from PL/pgSQL
Previous:From: Oliver JowettDate: 2005-08-19 11:12:31
Subject: Re: BUG #1830: Non-super-user must be able to copy from a

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group