Re: Disable executing external commands from psql?

Ken,

* Ken Tanzer (ken.tanzer@gmail.com) wrote:
> I could be way off base, but it seems like the exposure is limited.
> Sure, each user can access their database, providing they can
> authenticate successfully.  (Of course, I don't care what they do with
> their database.)  This essentially results in a bunch of limited
> exposures of individual DBs, which seems somehow different than one
> point of access that can access multiple databases, including
> potentially ones I do care about.  I have a lot of faith that a Postgres
> user won't be able to do anything other than access their own database.

You realize that some information (like roles/users) is shared
cluster-wide and isn't limited to a specific database, right?  That's
usually where web-hosting folks trip up first..

> If I understand your comment correctly, that would be suggesting that I
> have already exposed all the databases, because you could ssh in with my
> credentials and do all kinds of root stuff.

Giving users a shell doesn't mean you've given them access to all
databases or 'root stuff' at all.  I can understand not wanting to give
users a shell on your database server, but it certainly wouldn't be the
same as giving them root on it.

>> How are you going to let them edit the PHP files, or read the log file,
>> if all they can get to is psql?
> Well now that's a great question.  I had thought I was going to have
> people use sftp/scp, but I can see that apparently doesn't work without
> a more "normal" shell than psql.  (Although maybe you could build that
> support in? ;) )

Erm, I don't believe you need a real shell to allow them sftp..  You
just have to set things up correctly.

> So I guess my other option is to use one of these web-based server side
> file managers, and lock the top level at the user's home directory.
> (There seem to be lots of them out there--would anyone suggest a good
> one that allows upload/editing?? )  I can see this would need to be
> user/password authenticated, and to respect the permissions/run as each
> individual user.

Yeah, that's a bit far afield from the purpose of this list...

>> You could always build your own lobotomized version of psql.  I think
>> though that (a) this is not likely to close all the holes and (b) the
>> whole concept needs rethinking anyway.  psql is *meant* to be executed
>> on the client side.  You're trying to put the firewall in the wrong
>> place, and what you're mainly going to accomplish is annoy your users.
>> You will for example be making it awfully difficult for them to use
>> \copy, \i, \e, \g, the list goes on.
> I'm not really eager to go down this path, but nonetheless it's not
> obvious to me why giving psql a lobotomy (or hopefully a careful
> surgical tweak) to disable the "\!" functionality would impact all those
> other functions.

Have you looked at what those functions are..?  \copy is used to copy a
file on the filesystem into the database; \i allows a user to run SQL
commands from a file on the filesystem, etc, etc.  If you're ok with
them having access to the filesystem, what is the issue with giving them
a shell?  I'm sure you'd want to lock it down some, but that shouldn't
be all that difficult to do..

    Thanks,

        Stephen