SEO

The following bug has been logged online:

Bug reference: 1830
Logged by: Bernard
Email address: bht@actrix.gen.nz
PostgreSQL version: 8.0.3
Operating system: Linux RedHat 9
Description: Non-super-user must be able to copy from a file
Details:

On the attempt to bulk load a table from a file that is owned by the
non-superuser current database user, the following error message is
printed:

"must be superuser to COPY to or from a file"

What is the reason for this limitation?

It can't justifiably be for security reasons because if a web application
such as tomcat requires to bulk load tables automatically on a regular basis
then one would be forced to let the web application connect as superuser,
which is very bad for security.

In MySQL bulk loading works for all users.

We need a Postgresql solution.

We have a web application where both MySQL and Postresql are supported. With
Postgresql, the application would have to connect as user postgres. We have
to explain this security risk to our clients very clearly.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Wed, Aug 17, 2005 at 09:22:16 +0100,
Bernard <bht@actrix.gen.nz> wrote:
>
> The following bug has been logged online:

This isn't a bug and you really should have asked this question on
another list. I am moving the discussion over to the general list.

>
> Bug reference: 1830
> Logged by: Bernard
> Email address: bht@actrix.gen.nz
> PostgreSQL version: 8.0.3
> Operating system: Linux RedHat 9
> Description: Non-super-user must be able to copy from a file
> Details:
>
> On the attempt to bulk load a table from a file that is owned by the
> non-superuser current database user, the following error message is
> printed:
>
> "must be superuser to COPY to or from a file"
>
> What is the reason for this limitation?

This is described in the documentation for the copy command.

>
> It can't justifiably be for security reasons because if a web application
> such as tomcat requires to bulk load tables automatically on a regular basis
> then one would be forced to let the web application connect as superuser,
> which is very bad for security.

No, because you can have the app read the file and then pass the data to
the copy command. To do this you use STDIN as the file name.

>
> In MySQL bulk loading works for all users.

You can use the \copy command in psql to load data from files.

>
> We need a Postgresql solution.
>
> We have a web application where both MySQL and Postresql are supported. With
> Postgresql, the application would have to connect as user postgres. We have
> to explain this security risk to our clients very clearly.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Bruno and interested list members

I want to follow what is suggested here. How are STDIN and STDOUT
addressed when using the JDBC driver?

Or in other words where can I write or receive megabytes of data?

I would not want to append this to the String of a SQL Statement in
Java because that is a String in memory.

Thanks

Bernard


On Wed, 17 Aug 2005 06:51:12 -0500, you wrote:

>On Wed, Aug 17, 2005 at 09:22:16 +0100,
> Bernard <bht@actrix.gen.nz> wrote:
>>
>> The following bug has been logged online:
>
>This isn't a bug and you really should have asked this question on
>another list. I am moving the discussion over to the general list.
>
>>
>> Bug reference: 1830
>> Logged by: Bernard
>> Email address: bht@actrix.gen.nz
>> PostgreSQL version: 8.0.3
>> Operating system: Linux RedHat 9
>> Description: Non-super-user must be able to copy from a file
>> Details:
>>
>> On the attempt to bulk load a table from a file that is owned by the
>> non-superuser current database user, the following error message is
>> printed:
>>
>> "must be superuser to COPY to or from a file"
>>
>> What is the reason for this limitation?
>
>This is described in the documentation for the copy command.
>
>>
>> It can't justifiably be for security reasons because if a web application
>> such as tomcat requires to bulk load tables automatically on a regular basis
>> then one would be forced to let the web application connect as superuser,
>> which is very bad for security.
>
>No, because you can have the app read the file and then pass the data to
>the copy command. To do this you use STDIN as the file name.
>
>>
>> In MySQL bulk loading works for all users.
>
>You can use the \copy command in psql to load data from files.
>
>>
>> We need a Postgresql solution.
>>
>> We have a web application where both MySQL and Postresql are supported. With
>> Postgresql, the application would have to connect as user postgres. We have
>> to explain this security risk to our clients very clearly.
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: Don't 'kill -9' the postmaster
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Bernard wrote:

> I want to follow what is suggested here. How are STDIN and STDOUT
> addressed when using the JDBC driver?

The current JDBC driver doesn't support this mode of COPY.

There was some work done in the past to support this but it never got to
the point of making it into the official driver; see the pgsql-jdbc
archives for details.

-O

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

Oliver and interested list members:

Thanks for the related information.

The majority of JDBC users trying to bulk load tables would not want
to send the data through their connection. This connection is designed
to send commands and to transfer only as much data as necessary and as
little as possible.

In other words, COPY, and its corresponding commands in other datbase
engines are designed to transfer data using the fastest possible
method, which is typically to and from files.

For the majority JDBC users, there is no real need for a STDIN/STDOUT
option because they can always write to a file.

The need is only created by the limitations of the Postgres COPY
command.

I can't see why a workaround should be developed instead of or before
fixing the COPY command.

It works in other DB engines.


Regards

Bernard




On Fri, 19 Aug 2005 11:10:42 +1200, you wrote:

>Bernard wrote:
>
>> I want to follow what is suggested here. How are STDIN and STDOUT
>> addressed when using the JDBC driver?
>
>The current JDBC driver doesn't support this mode of COPY.
>
>There was some work done in the past to support this but it never got to
>the point of making it into the official driver; see the pgsql-jdbc
>archives for details.
>
>-O
>
>---------------------------(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


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Bernard wrote:

> The majority of JDBC users trying to bulk load tables would not want
> to send the data through their connection. This connection is designed
> to send commands and to transfer only as much data as necessary and as
> little as possible.

I don't understand why this is true at all -- for example, our
application currently does bulk INSERTs over a JDBC connection, and
moving to COPY has been an option I looked at in the past. Importing
lots of data from a remote machine is hardly an uncommon case.

> The need is only created by the limitations of the Postgres COPY
> command.
>
> I can't see why a workaround should be developed instead of or before
> fixing the COPY command.
>
> It works in other DB engines.

I guess that other DB engines don't care about unprivileged DB users
reading any file that the backend can access.

-O

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Oliver and interested list members:

I was referring to the majority of users wanting to "bulk" load tables
not to the majority of all or whatever users who may or may not know
or care about the difference in performance between INSERT and COPY.

This difference of performance is the main reason for the COPY
command, and this is also the reason why bulk loading through the JDBC
interface will never match the performance of the COPY fith files
command.

The COPY command with STDIN or STDOUT is a speciality that the
majority of users would not normally ask for because they usually
think in terms of files and rightly so.

Comparable with a STDIN/STDOUT workaround would be to pipe input and
output to and from SQL stored procedures.

What I mean to say is that we want this to be strictly server side for
best performance and we don't want to get the client involved in the
raw processing which is in violation of any 3 tier client-server
architecture.

In addition to this, not only will the client and network be loaded
with additional processing demand, but the server load will also
increase because it has to service the JDBC interface for I/O.

The whole architectural setup for such "bulk" loading is a mess.

Regards,

Bernard


On Fri, 19 Aug 2005 12:27:01 +1200, you wrote:

>Bernard wrote:
>
>> The majority of JDBC users trying to bulk load tables would not want
>> to send the data through their connection. This connection is designed
>> to send commands and to transfer only as much data as necessary and as
>> little as possible.
>
>I don't understand why this is true at all -- for example, our
>application currently does bulk INSERTs over a JDBC connection, and
>moving to COPY has been an option I looked at in the past. Importing
>lots of data from a remote machine is hardly an uncommon case.
>
>> The need is only created by the limitations of the Postgres COPY
>> command.
>>
>> I can't see why a workaround should be developed instead of or before
>> fixing the COPY command.
>>
>> It works in other DB engines.
>
>I guess that other DB engines don't care about unprivileged DB users
>reading any file that the backend can access.
>
>-O
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: Don't 'kill -9' the postmaster


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

"Oliver Jowett" <oliver@opencloud.com>
wrote:43052755.7000003@opencloud.com...
> Bernard wrote:
>
>> The majority of JDBC users trying to bulk load tables would not want
>> to send the data through their connection. This connection is designed
>> to send commands and to transfer only as much data as necessary and as
>> little as possible.
>
> I don't understand why this is true at all -- for example, our
> application currently does bulk INSERTs over a JDBC connection, and
> moving to COPY has been an option I looked at in the past. Importing
> lots of data from a remote machine is hardly an uncommon case.

When exporting and importing data from other data sources, there maybe
many rows to be moved. In this special case, should COPY be faster than
INSERTs? Have pgsql-jdbc supported COPY?

If I read correctly, what Bernard want is COPY from/to server-side files.
That is actually a security risk for non-superuser. They may read or
overwrite
any files can read/write by postgres server process.

>
>> The need is only created by the limitations of the Postgres COPY
>> command.
>>
>> I can't see why a workaround should be developed instead of or before
>> fixing the COPY command.
>>
>> It works in other DB engines.
>
> I guess that other DB engines don't care about unprivileged DB users
> reading any file that the backend can access.
>
> -O
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

Bernard wrote:

> This difference of performance is the main reason for the COPY
> command, and this is also the reason why bulk loading through the JDBC
> interface will never match the performance of the COPY fith files
> command.

In some admittedly unscientific tests I see less than 10% difference
between server-side COPY and client-side COPY FROM STDIN (psql's \copy
command) on a 28mb input file. That's down in the per-run noise.

Doing it via JDBC will undoubtably add some extra overhead, but I'd
estimate that it's about the same sort of overhead as writing your data
out to a file from Java in the first place takes.

If you've already got the data in a file, why not just use psql's \copy
command? This uses COPY FROM STDIN, reads the file as the user running
psql, and does not require superuser permissions.

> The whole architectural setup for such "bulk" loading is a mess.

Do you have a concrete suggestion for improving bulk loading that
doesn't open security holes?

-O

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Bernard wrote:
> Oliver and interested list members:

[...]

And please fix your anti-spam system so it doesn't send me a "you must
jump through these hoops to send me email" message every time please!

(usual cc: to poster removed for that reason)

-O

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster