Re: Disable executing external commands from psql?

>
> Eh, it's just that the users usually complain about seeing other
> people's roles and databases and whatnot..
That makes sense.  I don't think that would be a problem in this case.
> Also, it means you can't grant 'create role' to anyone, so users can't create or drop their own
> roles (an admin will have to handle that) when you have a single shared
> cluster environment.
That's fine too.  Each user just needs their own role and database, and
just for running this blasted app...

> What's the issue with port-forwarding (or using SSL/Certificates/etc)
> connections to the database and having them run psql locally, if they're
> so inclined?  Or they could use ODBC to connect from MS Access or
> something along those lines..  Depends on your userbase, of course.
>
Yeah, that userbase is the issue.  My app is targeted towards nonprofit
organizations, especially those that don't have their own data systems.
My experience has been that for some reason these folks just don't want
to download and configure a Linux environment just to be able to "kick
the tires" on this thing.  So I was thinking self-serve-created demo
accounts would be a good way to go.  They'd still need putty and WinSCP
to actually configure anything, but that seems like a lot lower bar to
hurdle.

(I see two scenarios for this--one is purely demo, I put up a machine on
which I care about nothing, and warn people nothing is secure.  For
this, I'd still want to tighten security where possible (and not too
onerous), which is where things like the \! disabling could come in.  I
also had some thoughts about offering some production environment
hosting, and in that scenario I would absolutely want and need to make
things truly secure.)

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

I meant to ask this on the last response, but I _still_ haven't seen any
answer to this question.  I hear you about the pager, but it's another
thing I could live without.  It's really just that \i... :)

At this point, I'm going to have to figure out whether to use ssh/psql
(hacked or unhacked), or just add a web interface to execute psql files
within my app.  The downside I see to that is it would prevent people
from dropping and recreating their database.

I really do appreciate all the responses and advice.  If I sound
frustrated, it's at myself for not having figured this all out beforehand!

Ken


On 06/01/2010 07:32 PM, Stephen Frost wrote:
> * Ken Tanzer (ken.tanzer@gmail.com) wrote:
>
>>> 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...
>>
> Eh, it's just that the users usually complain about seeing other
> people's roles and databases and whatnot..  Also, it means you can't
> grant 'create role' to anyone, so users can't create or drop their own
> roles (an admin will have to handle that) when you have a single shared
> cluster environment.
>
>
>>> 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.
>>
> What's the issue with port-forwarding (or using SSL/Certificates/etc)
> connections to the database and having them run psql locally, if they're
> so inclined?  Or they could use ODBC to connect from MS Access or
> something along those lines..  Depends on your userbase, of course.
>
>
>>> 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.
>>
> You can mount /home (and any other filesystems they have write access to)
> with noexec and then lock down what binaries they have rights to execute
> on the other filesystems..  If you use a virtual system solution like I
> was mentioning earlier, you could still force their shell to be psql
> but be able to worry less over them using \!...  Also realize that
> there are things like the PAGER being executed by psql when queries end
> up returning more than a pager worth of data.  Depending on the pager,
> it may provide a ! or !-like command..  Guess you could tell your users
> "sorry, you can't have a PAGER" and hack that out of psql too.. :)
>
>     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
-------------------------------------------------------