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

#1

Greg Stark wrote:

Quote:

Oliver Jowett <oliver (AT) opencloud (DOT) com> writes:

Bernard was also objecting to the overhead of pushing the data down a
TCP pipe when it's already available locally, I think.. I didn't find
any real difference there when I compared the two methods, though.

What makes you think it's necessarily available locally?

Nothing in general -- that was just the case he had.

-O

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

#2

Bernard schrieb:

Quote:

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

Quote:

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 2: Don't 'kill -9' the postmaster

#3

On Fri, 19 Aug 2005, Bernard wrote:

Quote:

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.

No, it's not solved. It prevents that problem for the configuration
files, but still gives access to other world readable files on the system
for example /etc/passwd on many systems (yes it's not terribly interesting
in general, but still is often not acceptable to retrieve).

You'd probably want to add the ability to setup which directories that are
allowed to be read or written to as configuration separately from unix
file permissions.

No, it doesn't take a genius, but it's not as trivial as you seem to think
it is, either. And honestly, until there's a workable plan that addresses
these issues, opening it up seems foolish.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

#4

On Fri, Aug 19, 2005 at 08:03:39AM -0700, Stephan Szabo wrote:

Quote:

On Fri, 19 Aug 2005, Bernard wrote:

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.

No, it's not solved. It prevents that problem for the configuration
files, but still gives access to other world readable files on the system
for example /etc/passwd on many systems (yes it's not terribly interesting
in general, but still is often not acceptable to retrieve).

You'd probably want to add the ability to setup which directories that are
allowed to be read or written to as configuration separately from unix
file permissions.

FWIW, this is exactly what Oracle does. A DBA has to configure what
directories you can bulk copy to/from.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby (AT) pervasive (DOT) com
Pervasive Software http://pervasive.com 512-569-9461

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [GENERAL] [BUGS] BUG #1830: Non-super-user must be able to copy - 08-19-2005 , 05:01 AM

Re: [GENERAL] [BUGS] BUG #1830: Non-super-user must be able to copy - 08-19-2005 , 08:31 AM

Re: [GENERAL] [BUGS] BUG #1830: Non-super-user must be able to copy - 08-19-2005 , 10:06 AM

Re: [GENERAL] [BUGS] BUG #1830: Non-super-user must be able to copy - 08-19-2005 , 04:10 PM