Re: BUG #1830: Non-super-user must be able to copy from a file

On 2005-08-19, Bernard <bht@actrix.gen.nz> wrote:
> Oliver and interested list members:
>
> In the majority of bulk load cases, the input exists as a file already

But not necessarily on the server.

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

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

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

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

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.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services