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

* Stephan Szabo (sszabo(at)megazone(dot)bigpanda(dot)com) wrote:
>
> On Fri, 19 Aug 2005, Bernard wrote:
>
> > My suggestions for improving the COPY command so it can be used by
> > non-superuser users would be as follows:
>
> If you want to do this without switching to a different UNIX user, can't
> you already write a small SECURITY DEFINER function as a superuser that
> does the copy from file based on arguments and then give permissions to
> that function to the appropriate non-superusers?

Generally, I think this is the approach that makes the most sense. Of
course, the SECURITY DEFINER function should also check that the
arguments match a pre-defined list of valid file names/table names, etc.
Personally, I do like the idea of a user-level 'copy server-side files'
permission that could be granted to reduce the need for things to run as
superuser. I'd probably still set up a SECURITY DEFINER function to a
user with those permissions as an additional layer of security but it'd
be nice to not have to run the function as superuser.

I understand the concern that a user might be able to escalate to
superuser status using that permission but I feel that's more an issue
that an administrator needs to understand and deal with than a problem
with allowing that permission. Ways to avoid it would include: Using
PAM (it's at least somewhat difficult to crack a decent hash'd password
in /etc/shadow), Using local-socket-only ident only for superuser,
hacking Postgres to support Unix-like password hashing/checking (same
issue as w/ PAM though), hacking Postgres to support SASL (and then
using saslauthd so Postgres doesn't need access to the file which has
the password hashes directly), using Kerberos for authentication (my
personal favorite, Kerberos for users, local-ident only for superuser).

It is, of course, good to note that current Postgres 'md5' auth method
usage means that a compromise of pg_shadow (pg_authid) gives the
attacker superuser access immediately (the hash itself is the actual
authentication token, the password isn't actually interesting in that
case).

Thanks,

Stephen