* Stephan Szabo (sszabo@xxxxxxxxxxxxxxxxxxxxx) 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
Attachment:
signature.asc
Description: Digital signature
