Обсуждение: Disable executing external commands from psql?

Hi.  I'm wondering if it is possible to disable use of  \! to execute
commands in psql?  I see this has come up on the list before
(http://archives.postgresql.org/pgsql-admin/2007-07/msg00242.php), but I
don't see anyone saying whether it is possible or not, just that it's a
bad or useless idea.

It may or may not be a bad idea (e.g., carry some risk).  My scenario is
that I'd like to give people that I don't necessarily know (or therefore
trust) the ability to run psql for a database I've already set up for
them.  I set their login shell to psql, so they can simply ssh in, and
they are in psql.  From there, though, they can do a simple \!
/bin/bash, and they've got way more access than I want them to.

So is there any way to disable the "\!" stuff?  If there's a better way
to go about this, I suppose I'm all ears too!

Thanks.

Ken Tanzer

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

Ken Tanzer <ken.tanzer@gmail.com> writes:
> Hi.  I'm wondering if it is possible to disable use of  \! to execute
> commands in psql?  I see this has come up on the list before
> (http://archives.postgresql.org/pgsql-admin/2007-07/msg00242.php), but I
> don't see anyone saying whether it is possible or not, just that it's a
> bad or useless idea.

Yes, it seems pretty useless.

> It may or may not be a bad idea (e.g., carry some risk).  My scenario is
> that I'd like to give people that I don't necessarily know (or therefore
> trust) the ability to run psql for a database I've already set up for
> them.  I set their login shell to psql, so they can simply ssh in, and
> they are in psql.  From there, though, they can do a simple \!
> /bin/bash, and they've got way more access than I want them to.

> So is there any way to disable the "\!" stuff?  If there's a better way
> to go about this, I suppose I'm all ears too!

The better way to go about that is to not let them have an account on
the server machine in the first place.  Just expose the postmaster port
(perhaps via ssh tunneling) and let them run psql on their own machines.

            regards, tom lane

Ken Tanzer wrote:
> Hi.  I'm wondering if it is possible to disable use of  \! to execute
> commands in psql?  I see this has come up on the list before
> (http://archives.postgresql.org/pgsql-admin/2007-07/msg00242.php), but I
> don't see anyone saying whether it is possible or not, just that it's a
> bad or useless idea.
>
> It may or may not be a bad idea (e.g., carry some risk).  My scenario is
> that I'd like to give people that I don't necessarily know (or therefore
> trust) the ability to run psql for a database I've already set up for
> them.  I set their login shell to psql, so they can simply ssh in, and
> they are in psql.  From there, though, they can do a simple \!
> /bin/bash, and they've got way more access than I want them to.
>
> So is there any way to disable the "\!" stuff?  If there's a better way
> to go about this, I suppose I'm all ears too!

Sure use SHELL=/usr/bin/false:

    $ SHELL=/usr/bin/false psql
    psql (9.0beta1)
    Type "help" for help.

    postgres=> \!
    postgres=>

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + None of us is going to be here forever. +

>
> Sure use SHELL=/usr/bin/false:
>
>     $ SHELL=/usr/bin/false psql
>     psql (9.0beta1)
>     Type "help" for help.
>
>     postgres=>  \!
>     postgres=>
>
>

Trouble is, that doesn't stop

\! bash


On 06/01/2010 04:57 PM, Bruce Momjian wrote:
> Ken Tanzer wrote:
>
>> Hi.  I'm wondering if it is possible to disable use of  \! to execute
>> commands in psql?  I see this has come up on the list before
>> (http://archives.postgresql.org/pgsql-admin/2007-07/msg00242.php), but I
>> don't see anyone saying whether it is possible or not, just that it's a
>> bad or useless idea.
>>
>> It may or may not be a bad idea (e.g., carry some risk).  My scenario is
>> that I'd like to give people that I don't necessarily know (or therefore
>> trust) the ability to run psql for a database I've already set up for
>> them.  I set their login shell to psql, so they can simply ssh in, and
>> they are in psql.  From there, though, they can do a simple \!
>> /bin/bash, and they've got way more access than I want them to.
>>
>> So is there any way to disable the "\!" stuff?  If there's a better way
>> to go about this, I suppose I'm all ears too!
>>
> Sure use SHELL=/usr/bin/false:
>
>     $ SHELL=/usr/bin/false psql
>     psql (9.0beta1)
>     Type "help" for help.
>
>     postgres=>  \!
>     postgres=>
>
>


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

>
> The better way to go about that is to not let them have an account on
> the server machine in the first place.  Just expose the postmaster port
> (perhaps via ssh tunneling) and let them run psql on their own machines.
Somehow, exposing my database ports to the internet scares me more than
any (possibly crazy) stuff I'm trying to do.  :)

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.

Just to be clear, cause I'm a little thick sometimes, it is not possible
to do this?

Thanks,

Ken


On 06/01/2010 04:55 PM, Tom Lane wrote:
> Ken Tanzer<ken.tanzer@gmail.com>  writes:
>
>> Hi.  I'm wondering if it is possible to disable use of  \! to execute
>> commands in psql?  I see this has come up on the list before
>> (http://archives.postgresql.org/pgsql-admin/2007-07/msg00242.php), but I
>> don't see anyone saying whether it is possible or not, just that it's a
>> bad or useless idea.
>>
> Yes, it seems pretty useless.
>
>
>> It may or may not be a bad idea (e.g., carry some risk).  My scenario is
>> that I'd like to give people that I don't necessarily know (or therefore
>> trust) the ability to run psql for a database I've already set up for
>> them.  I set their login shell to psql, so they can simply ssh in, and
>> they are in psql.  From there, though, they can do a simple \!
>> /bin/bash, and they've got way more access than I want them to.
>>
>
>> So is there any way to disable the "\!" stuff?  If there's a better way
>> to go about this, I suppose I'm all ears too!
>>
> The better way to go about that is to not let them have an account on
> the server machine in the first place.  Just expose the postmaster port
> (perhaps via ssh tunneling) and let them run psql on their own machines.
>
>             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
-------------------------------------------------------

Ken Tanzer wrote:
> >
> > Sure use SHELL=/usr/bin/false:
> >
> >     $ SHELL=/usr/bin/false psql
> >     psql (9.0beta1)
> >     Type "help" for help.
> >
> >     postgres=>  \!
> >     postgres=>
> >
> >
>
> Trouble is, that doesn't stop
>
> \! bash

Ah, I see.  libc hardcodes the SHELL executable into the system()
library call.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + None of us is going to be here forever. +

Ken Tanzer wrote:
> >
> > The better way to go about that is to not let them have an account on
> > the server machine in the first place.  Just expose the postmaster port
> > (perhaps via ssh tunneling) and let them run psql on their own machines.
> Somehow, exposing my database ports to the internet scares me more than
> any (possibly crazy) stuff I'm trying to do.  :)
>
> 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.
>
> Just to be clear, cause I'm a little thick sometimes, it is not possible
> to do this?

Maybe put them into a chroot() environment, or hack psql to remove \!.
No one has asked for this before.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + None of us is going to be here forever. +

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

Ken,

* Ken Tanzer (ken.tanzer@gmail.com) wrote:
> Hi.  I'm wondering if it is possible to disable use of  \! to execute
> commands in psql?  I see this has come up on the list before
> (http://archives.postgresql.org/pgsql-admin/2007-07/msg00242.php), but I
> don't see anyone saying whether it is possible or not, just that it's a
> bad or useless idea.

It'd be difficult to close all the holes and would result in seriously
reduced functionality to your users.

> It may or may not be a bad idea (e.g., carry some risk).  My scenario is
> that I'd like to give people that I don't necessarily know (or therefore
> trust) the ability to run psql for a database I've already set up for
> them.  I set their login shell to psql, so they can simply ssh in, and
> they are in psql.  From there, though, they can do a simple \!
> /bin/bash, and they've got way more access than I want them to.

You can port-forward the database port through SSH (so you'd only expose
port 22 to the internet) and then have the clients run psql on their
system.  That would let them use things like \copy, which is extremely
useful.

> So is there any way to disable the "\!" stuff?  If there's a better way
> to go about this, I suppose I'm all ears too!

Yes, listen to the suggestions made by myself and others..  Disabling \!
wouldn't be enough and the more you hack on psql to disable things to
become a 'secure' database shell, the more annoyed and frustrated your
users will end up being.

Have you considered something like linux containers or vservers instead?

    Thanks,

        Stephen

On 02/06/10 08:06, Ken Tanzer wrote:

> Somehow, exposing my database ports to the internet scares me more than
> any (possibly crazy) stuff I'm trying to do.  :)

Why? Surely it's less scary than exposing ssh+shell access (!!), even if
you think the shell is locked down to running only a crippled version of
psql.

You can use SSL with client certificates to lock down access to the
database if you don't trust simple SSL-protected username/password
authentication alone.

Given the choice, I'd expose Pg to the Internet _any_ day before even
considering exposing semi-public ssh access when I didn't absolutely
have to.

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

Sounds like you need to provide them with a web interface to do the
work, and have the web app talk to Pg.

--
Craig Ringer

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

OK, hadn't seen your response (and Stephen Frost's) before sending
mine.  I think I hear everybody loud and clear--bad idea!

Ken

On 06/01/2010 06:47 PM, Craig Ringer wrote:
> On 02/06/10 08:06, Ken Tanzer wrote:
>
>
>> Somehow, exposing my database ports to the internet scares me more than
>> any (possibly crazy) stuff I'm trying to do.  :)
>>
> Why? Surely it's less scary than exposing ssh+shell access (!!), even if
> you think the shell is locked down to running only a crippled version of
> psql.
>
> You can use SSL with client certificates to lock down access to the
> database if you don't trust simple SSL-protected username/password
> authentication alone.
>
> Given the choice, I'd expose Pg to the Internet _any_ day before even
> considering exposing semi-public ssh access when I didn't absolutely
> have to.
>
>
>> 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.
>>
> Sounds like you need to provide them with a web interface to do the
> work, and have the web app talk to Pg.
>
> --
> Craig Ringer
>


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

OK one more question on this thread.  It occurs to me that for the web
app, DB username and password is read from a configuration file.  (I
understand this to be a common method for web applications.)  But since
apache needs to read the file, then all users can read each others'
passwords.  Arrghh.  I'm just wondering how web hosters typically deal
with this issue (or is your info for, say, Wordpress exposed to other
users if they know where to look for it?)  Sorry if this is too off-topic...

Ken

I'll look at

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

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

* Ken Tanzer (ken.tanzer@gmail.com) wrote:
> OK one more question on this thread.  It occurs to me that for the web
> app, DB username and password is read from a configuration file.  (I
> understand this to be a common method for web applications.)  But since
> apache needs to read the file, then all users can read each others'
> passwords.  Arrghh.  I'm just wondering how web hosters typically deal
> with this issue (or is your info for, say, Wordpress exposed to other
> users if they know where to look for it?)  Sorry if this is too
> off-topic...

Have the username/password for each user site passed through
environment variables which are in the apache config file for the
virtual site they have access to the web root of.  Then deny access to
the apache config files (the users don't really need access to it
anyway, and neither does www-data; apache will read them as root during
startup).

    Thanks,

        Stephen

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


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

* 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

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

Ken Tanzer <ken.tanzer@gmail.com> writes:
>> 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.

No, you missed the point: those functions, as well as others, are
useless unless psql is running inside a filesystem that the user has
(easy) read/write access to.  psql is really, really meant to be run
on the client side.

            regards, tom lane

>    psql is really, really meant to be run
> on the client side.
I appreciate that, but the people I'm targeting are just not going to
have psql on their systems.


> No, you missed the point: those functions, as well as others, are
> useless unless psql is running inside a filesystem that the user has
> (easy) read/write access to.
Maybe I'm missing something (or haven't explained).  The users would
definitely have access to the filesystems.  Setting my login shell to
psql or lobotomizing the \! function wouldn't change that.

But even going beyond that, I love the psql program.  I really, really
do.  I use it all the time, and would choose it over a GUI or other
means of access any day.  I wouldn't want to live without \!, or PAGER,
or lots of other nifty stuff.  But for the use cases I'm envisioning,
those are all useless frills (or if not useless, of secondary or
tertiary significance).  There's still a lot you can do with psql even
without those commands, and I don't see why that should be considered an
invalid use case.

Thanks for listening, and cheers,

Ken



On 06/01/2010 08:22 PM, Tom Lane wrote:
> Ken Tanzer<ken.tanzer@gmail.com>  writes:
>
>>> 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.
>>
> No, you missed the point: those functions, as well as others, are
> useless unless psql is running inside a filesystem that the user has
> (easy) read/write access to.  psql is really, really meant to be run
> on the client side.
>
>             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
-------------------------------------------------------

* Ken Tanzer (ken.tanzer@gmail.com) wrote:
> 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.

You know that you can run psql from under Windows directly..  right?
They wouldn't need a Linux environment to run it.  Just package it up
with PuTTY and WinSCP.

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

If *all* they need is \i, you could write your own libpq-using
client-side app which runs under Windows and reads the files and
executes the commands in them..

    Thanks,

        Stephen