Re: Disable executing external commands from psql?

Thanks for asking a bunch of good questions,  that I don't have good
answers to all of... :)  But I'll try:

> If you're exposing the ability to run psql, what makes you think you're
> not effectively exposing the database?
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.

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.

> 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? ;) )

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.

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

------

In closing, I'd just say that I can see how there are some potential
problems, but it's also been useful just to find out if the \! thing is
possible or not.  This is all intended for a demo site, most of which I
don't care too much about.  At the same time, it seemed prudent to lock
down and tighten some things where possible, even if the security
achieved is not complete.  If anyone has some better suggestions about
how to set up such a scenario, I really would appreciate hearing them.
(I know, it's kind of off-list-topic.)  And thanks for the suggestions
and questions, even if it's given me a big headache and more unanswered
questions than I started with! :)

Cheers,
Ken






On 06/01/2010 05:30 PM, Tom Lane wrote:
> Ken Tanzer<ken.tanzer@gmail.com>  writes:
>
>>> The better way to go about that is to not let them have an account on
>>> the server machine in the first place.
>>>
>
>> Somehow, exposing my database ports to the internet scares me more than
>> any (possibly crazy) stuff I'm trying to do.  :)
>>
> If you're exposing the ability to run psql, what makes you think you're
> not effectively exposing the database?
>
>
>> But seriously I think I need to give them accounts--I'm setting up
>> online instances of a web app, so they have a set of (editable) PHP
>> files, possibly some storage, a log file, etc.  It seemed that setting
>> each up as its own user was better than going through some uber-process
>> that had access to all the files.
>>
> How are you going to let them edit the PHP files, or read the log file,
> if all they can get to is psql?
>
>
>> Just to be clear, cause I'm a little thick sometimes, it is not possible
>> to do this?
>>
> 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.
>
>             regards, tom lane
>


--
-------------------------------------------------------
AGENCY Software
For nonprofits that want to take control of their data

Use it.  Like it.  Share it.  Build it.  Buy it.
http://agency-software.org
-------------------------------------------------------