Re: Disable executing external commands from psql?

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

Well thanks for letting me know that little tidbit. I'm sure it will
horrify you and others, but it actually revives my enthusiasm for the
lobotomized psql...

> 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..
>
I think it's fair to say I realize it, but am perhaps not drawing the
appropriate conclusions as to what risk this might involve? Please tell
me why I should care...

> Yeah, that's a bit far afield from the purpose of this list...
>
In fairness to me, I only brought it up in response to a question,
although I plead guilty to throwing in a parenthetical off-topic question

> 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.
Yes I'm quite familiar with those functions. If I didn't have a
boatload of scripts depending on "\i", I probably wouldn't care much
about giving users access to psql in the first place.

> If you're ok with them having access to the filesystem, what is the issue with giving them
> a shell?
It seems to me that executing programs is a whole level of danger above
and beyond access to the filesystem.

Thanks!

Ken

On 06/01/2010 07:08 PM, Stephen Frost wrote:
> Ken,
>
> * Ken Tanzer (ken(dot)tanzer(at)gmail(dot)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
>

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