Thread: proposal: hide application_name from other users

On 01/21/2014 07:22 AM, Harold Giménez wrote:
> First of all, I apologize for submitting a patch and missing the commitfest
> deadline. Given the size of the patch, I thought I'd submit it for your
> consideration regardless.
>
> This patch prevents non-superusers from viewing other user's
> pg_stat_activity.application_name.  This topic was discussed some time ago
> [1] and consequently application_name was made world readable [2].
>
> I would like to propose that we hide it instead by reverting to the
> original behavior.  There is a very large number of databases on the same
> cluster shared across different users who can easily view each other's
> application_name values.  Along with that, there are some libraries that
> default application_name to the name of the running process [3], which can
> leak information about what web servers applications are running, queue
> systems, etc. Furthermore leaking application names in a multi-tenant
> environment is more information than an attacker should have access to on
> services like Heroku and other similar providers.

I don't find these arguments compelling to change it now. It's 
well-documented that application_name is visible to everyone. Just don't 
put sensitive information there.

For those users that don't mind advertising application_name, the patch 
would be highly inconvenient. For example, the database owner could no 
longer see the application_name of other users connected to her database.

- Heikki

On 01/21/2014 04:19 PM, Heikki Linnakangas wrote:
> On 01/21/2014 07:22 AM, Harold Giménez wrote:
>> First of all, I apologize for submitting a patch and missing the
>> commitfest
>> deadline. Given the size of the patch, I thought I'd submit it for your
>> consideration regardless.
>>
>> This patch prevents non-superusers from viewing other user's
>> pg_stat_activity.application_name.  This topic was discussed some time
>> ago
>> [1] and consequently application_name was made world readable [2].
>>
>> I would like to propose that we hide it instead by reverting to the
>> original behavior.  There is a very large number of databases on the same
>> cluster shared across different users who can easily view each other's
>> application_name values.  Along with that, there are some libraries that
>> default application_name to the name of the running process [3], which
>> can
>> leak information about what web servers applications are running, queue
>> systems, etc. Furthermore leaking application names in a multi-tenant
>> environment is more information than an attacker should have access to on
>> services like Heroku and other similar providers.
> 
> I don't find these arguments compelling to change it now. It's
> well-documented that application_name is visible to everyone. Just don't
> put sensitive information there.
> 
> For those users that don't mind advertising application_name, the patch
> would be highly inconvenient. For example, the database owner could no
> longer see the application_name of other users connected to her database.

It also means that monitoring tools must run as superuser to see
information they require, which to me is a total showstopper.

If you want control over visibility of application_name, it should be
done with a column privilige granted to a system role, or something like
that - so the ability to see it can be given to "public" on default
(thus not breaking BC) and if it's revoked from "public", given to roles
that need to see it.


-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services

* Craig Ringer (craig@2ndquadrant.com) wrote:
> It also means that monitoring tools must run as superuser to see
> information they require, which to me is a total showstopper.

We've already got *far* too much of that going on for my taste.  I'd
love to see a comprehensive solution to this problem which allows
monitoring systems to run w/o superuser privileges.

> If you want control over visibility of application_name, it should be
> done with a column privilige granted to a system role, or something like
> that - so the ability to see it can be given to "public" on default
> (thus not breaking BC) and if it's revoked from "public", given to roles
> that need to see it.

I agree with this- individuals should be able to control access to this
information for their databases/clusters.
Thanks,
    Stephen

Stephen Frost wrote:
> * Craig Ringer (craig@2ndquadrant.com) wrote:
> > It also means that monitoring tools must run as superuser to see
> > information they require, which to me is a total showstopper.
> 
> We've already got *far* too much of that going on for my taste.  I'd
> love to see a comprehensive solution to this problem which allows
> monitoring systems to run w/o superuser privileges.

Yeah, we need a CAP_MONITOR capability thingy.  (CAP_BACKUP would be
great too -- essentially "read only but read everything")

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Stephen Frost <sfrost@snowman.net> writes:
> * Craig Ringer (craig@2ndquadrant.com) wrote:
>> If you want control over visibility of application_name, it should be
>> done with a column privilige granted to a system role, or something like
>> that - so the ability to see it can be given to "public" on default
>> (thus not breaking BC) and if it's revoked from "public", given to roles
>> that need to see it.

> I agree with this- individuals should be able to control access to this
> information for their databases/clusters.

I think that'd be much more complexity than the case justifies.  The
argument that application_name might contain sensitive information seems
ludicrously weak to me: whatever a client is exposing as application_name
is its own darn choice.  If you don't like it, go fix the client.
If there is some client library that sets application_name without
allowing the choice to be overridden, then that's a problem with that
library, not with the server's behavior.
        regards, tom lane

* Magnus Hagander (magnus@hagander.net) wrote:
> Isn't CAP_BACKUP pretty much the REPLICATION privilege?

Not unless we change it to allow read-access to all tables to allow for
pg_dump to work...
Thanks,
    Stephen

* Magnus Hagander (magnus@hagander.net) wrote:
> On Tue, Jan 21, 2014 at 5:18 PM, Stephen Frost <sfrost@snowman.net> wrote:
> > Not unless we change it to allow read-access to all tables to allow for
> > pg_dump to work...
>
> That sounds more like CAP_DUMP than CAP_BACKUP :)

Well, perhaps CAP_READONLY (or READALL?), there are auditor-type roles
which could be reduced to that level instead of superuser.  I'm on the
fence about if this needs to be seperate from REPLICATION though- how
many different such options are we going to have and how ugly is it
going to get to litter the code with if(superuser || read-only || ...)?

Perhaps a way to say "this role has X-privilege on all objects of this
type" which could then be used to GRANT SELECT and would be a single
point where we need to add those checks (in the ACL code for each
object type)?  One of the key points would be that the privilege apply
to newly created objects as well..
Thanks,
    Stephen

On Tue, Jan 21, 2014 at 12:31 AM, Craig Ringer <craig@2ndquadrant.com> wrote:
>
> On 01/21/2014 04:19 PM, Heikki Linnakangas wrote:
> > On 01/21/2014 07:22 AM, Harold Giménez wrote:
> >> First of all, I apologize for submitting a patch and missing the
> >> commitfest
> >> deadline. Given the size of the patch, I thought I'd submit it for your
> >> consideration regardless.
> >>
> >> This patch prevents non-superusers from viewing other user's
> >> pg_stat_activity.application_name.  This topic was discussed some time
> >> ago
> >> [1] and consequently application_name was made world readable [2].
> >>
> >> I would like to propose that we hide it instead by reverting to the
> >> original behavior.  There is a very large number of databases on the same
> >> cluster shared across different users who can easily view each other's
> >> application_name values.  Along with that, there are some libraries that
> >> default application_name to the name of the running process [3], which
> >> can
> >> leak information about what web servers applications are running, queue
> >> systems, etc. Furthermore leaking application names in a multi-tenant
> >> environment is more information than an attacker should have access to on
> >> services like Heroku and other similar providers.
> >
> > I don't find these arguments compelling to change it now. It's
> > well-documented that application_name is visible to everyone. Just don't
> > put sensitive information there.
> >
> > For those users that don't mind advertising application_name, the patch
> > would be highly inconvenient. For example, the database owner could no
> > longer see the application_name of other users connected to her database.
>
> It also means that monitoring tools must run as superuser to see
> information they require, which to me is a total showstopper.


Well, the fact is that if you don't run monitoring tools as superuser,
there may not be enough connection slots available anyways, in cases
where actual usage is consuming all of max_connections, and only the
reserved slots are available. So in a way it's already unreliable to
run monitoring as non-superuser unfortunately.

>
>
> If you want control over visibility of application_name, it should be
> done with a column privilige granted to a system role, or something like
> that - so the ability to see it can be given to "public" on default
> (thus not breaking BC) and if it's revoked from "public", given to roles
> that need to see it.


Something along these lines sounds like would solve the problem.

On Tue, Jan 21, 2014 at 03:57:37PM -0800, Harold Giménez wrote:
> > It also means that monitoring tools must run as superuser to see
> > information they require, which to me is a total showstopper.
> 
> 
> Well, the fact is that if you don't run monitoring tools as superuser,
> there may not be enough connection slots available anyways, in cases
> where actual usage is consuming all of max_connections, and only the
> reserved slots are available. So in a way it's already unreliable to
> run monitoring as non-superuser unfortunately.

You might need to run as superuser in these cases, but it is hard to see
why would need to do that in the normal case.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +

On Tue, Jan 21, 2014 at 7:25 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Stephen Frost <sfrost@snowman.net> writes:
>> * Craig Ringer (craig@2ndquadrant.com) wrote:
>>> If you want control over visibility of application_name, it should be
>>> done with a column privilige granted to a system role, or something like
>>> that - so the ability to see it can be given to "public" on default
>>> (thus not breaking BC) and if it's revoked from "public", given to roles
>>> that need to see it.
>
>> I agree with this- individuals should be able to control access to this
>> information for their databases/clusters.
>
> I think that'd be much more complexity than the case justifies.  The
> argument that application_name might contain sensitive information seems
> ludicrously weak to me: whatever a client is exposing as application_name
> is its own darn choice.  If you don't like it, go fix the client.
> If there is some client library that sets application_name without
> allowing the choice to be overridden, then that's a problem with that
> library, not with the server's behavior.

I don't know of a client where it can't be overridden. The friction
occurs when by default it sets it to something useful to a developer
(useful eg: to find what process is holding a lock), but is not
possible to conceal from other users on the same cluster. If this were
an in-premise or private cluster the point is moot.

Furthermore consider when even using application_name for it's
original intended use. On a shared environment as I'm describing here,
that makes it possible for an attacker to identify what apps connect
to a given server, or on the other hand is a way to find out where a
given application stores its data, which can be used for a more
targeted attack.

Beyond that yes, it's definitely possible to fix the client, but the
cited is but one example of defaults people are using in the wild, and
if the trend continues we'll be facing fanout of this behavior. I feel
like being conservative and fixing the issue on the server side is
worthwhile.

Regards,

-Harold

On Tue, Jan 21, 2014 at 4:01 PM, Bruce Momjian <bruce@momjian.us> wrote:
> On Tue, Jan 21, 2014 at 03:57:37PM -0800, Harold Giménez wrote:
>> > It also means that monitoring tools must run as superuser to see
>> > information they require, which to me is a total showstopper.
>>
>>
>> Well, the fact is that if you don't run monitoring tools as superuser,
>> there may not be enough connection slots available anyways, in cases
>> where actual usage is consuming all of max_connections, and only the
>> reserved slots are available. So in a way it's already unreliable to
>> run monitoring as non-superuser unfortunately.
>
> You might need to run as superuser in these cases, but it is hard to see
> why would need to do that in the normal case.

Definitely agree with you. This is just an example of how running
monitoring as superuser is not necessarily the worst thing, and there
are other reasons to do it already.

-Harold

On Tue, Jan 21, 2014 at 04:06:46PM -0800, Harold Giménez wrote:
> I don't know of a client where it can't be overridden. The friction
> occurs when by default it sets it to something useful to a developer
> (useful eg: to find what process is holding a lock), but is not
> possible to conceal from other users on the same cluster. If this were
> an in-premise or private cluster the point is moot.
> 
> Furthermore consider when even using application_name for it's
> original intended use. On a shared environment as I'm describing here,
> that makes it possible for an attacker to identify what apps connect
> to a given server, or on the other hand is a way to find out where a
> given application stores its data, which can be used for a more
> targeted attack.

So security through obscurity?  Why wouldn't the attacker just try all
the app methods at once and not even bother looking at the application
name?

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +

On Tue, Jan 21, 2014 at 4:19 PM, Bruce Momjian <bruce@momjian.us> wrote:
> On Tue, Jan 21, 2014 at 04:06:46PM -0800, Harold Giménez wrote:
>> I don't know of a client where it can't be overridden. The friction
>> occurs when by default it sets it to something useful to a developer
>> (useful eg: to find what process is holding a lock), but is not
>> possible to conceal from other users on the same cluster. If this were
>> an in-premise or private cluster the point is moot.
>>
>> Furthermore consider when even using application_name for it's
>> original intended use. On a shared environment as I'm describing here,
>> that makes it possible for an attacker to identify what apps connect
>> to a given server, or on the other hand is a way to find out where a
>> given application stores its data, which can be used for a more
>> targeted attack.
>
> So security through obscurity?  Why wouldn't the attacker just try all
> the app methods at once and not even bother looking at the application
> name?

A malicious attacker may want to attack or harm `app1`. They write a
script that provisions databases and check in pg_stat_activity until
they find an application_name of `app1`. Having found the database
holding app1's data, they then use a targeted attack on postgres, say
a privilege escalation attack or any other vulnerability we don't know
exists yet. Without application_name, the attacker would be unable to
find the target database host to attack.

-Harold

* Harold Giménez (harold@heroku.com) wrote:
> Definitely agree with you. This is just an example of how running
> monitoring as superuser is not necessarily the worst thing, and there
> are other reasons to do it already.

It's a horrible thing and that isn't a good reason- if my database isn't
accepting connections, I probably don't care one bit how bloated a table
is.  Indeed, I care *more* that I'm out of connections and would want to
know that ASAP.

That said, I'm not against the general idea that the 'reserved'
connections be opened up to roles beyond superuser (or have some kind of
priority system, etc), but that's an independent concern and should not
be a justification for making monitoring require superuser privs.
Thanks,
    Stephen

On Tue, Jan 21, 2014 at 4:38 PM, Stephen Frost <sfrost@snowman.net> wrote:
> * Harold Giménez (harold@heroku.com) wrote:
>> Definitely agree with you. This is just an example of how running
>> monitoring as superuser is not necessarily the worst thing, and there
>> are other reasons to do it already.
>
> It's a horrible thing and that isn't a good reason- if my database isn't
> accepting connections, I probably don't care one bit how bloated a table
> is.  Indeed, I care *more* that I'm out of connections and would want to
> know that ASAP.

This is a separate topic, but in such a case I'd want to know that
I've reached max_connections, which may not be a problem if I just
don't need any more connections, but I still need something connecting
to make sure the service is available at all and can respond to simple
SELECT 1 queries and a myriad of other things you'd want to keep track
of.

>
> That said, I'm not against the general idea that the 'reserved'
> connections be opened up to roles beyond superuser (or have some kind of
> priority system, etc), but that's an independent concern and should not
> be a justification for making monitoring require superuser privs.

+1

-Harold

* Harold Giménez (harold@heroku.com) wrote:
> This is a separate topic, but in such a case I'd want to know that
> I've reached max_connections, which may not be a problem if I just
> don't need any more connections, but I still need something connecting
> to make sure the service is available at all and can respond to simple
> SELECT 1 queries and a myriad of other things you'd want to keep track
> of.

I've never heard of an environment where you can be absolutely confident
that you need exactly max_connections and zero more.  I seriously doubt
one exists.

The service is not available if only a superuser can connect, imv.
Thanks,
    Stephen

On 01/21/2014 04:12 AM, Stephen Frost wrote:
>> It also means that monitoring tools must run as superuser to see
>> > information they require, which to me is a total showstopper.
> We've already got *far* too much of that going on for my taste.  I'd
> love to see a comprehensive solution to this problem which allows
> monitoring systems to run w/o superuser privileges.
> 

Agreed.  I just ran into the issue that getting data out of
pg_stat_replication on replication lag requires superuser privs, which
is annoying; without that, I can run Handyrep entirely as an
unprivileged user, but that one fact requires the handyrep database user
to be a superuser.

It would be really nice to be able to GRANT/REVOKE on some of these
special system views ...

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Tue, Jan 21, 2014 at 4:46 PM, Stephen Frost <sfrost@snowman.net> wrote:
> * Harold Giménez (harold@heroku.com) wrote:
>> This is a separate topic, but in such a case I'd want to know that
>> I've reached max_connections, which may not be a problem if I just
>> don't need any more connections, but I still need something connecting
>> to make sure the service is available at all and can respond to simple
>> SELECT 1 queries and a myriad of other things you'd want to keep track
>> of.
>
> I've never heard of an environment where you can be absolutely confident
> that you need exactly max_connections and zero more.  I seriously doubt
> one exists.
>
> The service is not available if only a superuser can connect, imv.

People push the limit all the time. They may run at 80% of their max
and occasionally (and temporarily) scale up to a known bounded level,
but no more.

-Harold

On Tue, Jan 21, 2014 at 4:53 PM, Josh Berkus <josh@agliodbs.com> wrote:
> It would be really nice to be able to GRANT/REVOKE on some of these
> special system views ...

I think this would be ideal, too.

-Harold

* Josh Berkus (josh@agliodbs.com) wrote:
> It would be really nice to be able to GRANT/REVOKE on some of these
> special system views ...

Well, we actually *can* issue grant/revoke against the underlying
function calls, but we are also doing permissions checks *in* those
functions, ignoring our own GRANT system.

Don't know what folks think of removing those in-the-function checks in
favor of trusting the grant/revoke system to not allow those functions
to be called unless you have EXECUTE privileges on them..  I've not
really tried to look at if that'd work or not, but if we could do that,
it'd certainly give admins a great deal more flexibility to control who
has access to what calls.
Thanks,
    Stephen

On 2014-01-21 20:00:51 -0500, Stephen Frost wrote:
> * Josh Berkus (josh@agliodbs.com) wrote:
> > It would be really nice to be able to GRANT/REVOKE on some of these
> > special system views ...

Just define a security definer wrapper function + view, that afair works
perfectly fine.

> Well, we actually *can* issue grant/revoke against the underlying
> function calls, but we are also doing permissions checks *in* those
> functions, ignoring our own GRANT system.

> Don't know what folks think of removing those in-the-function checks in
> favor of trusting the grant/revoke system to not allow those functions
> to be called unless you have EXECUTE privileges on them..

Well, they *do* return some information when called without superuser
privileges. Just not all columns for all sessions. I don't think you can
achieve that with anything in our permission system.

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services

* Andres Freund (andres@2ndquadrant.com) wrote:
> On 2014-01-21 20:00:51 -0500, Stephen Frost wrote:
> > * Josh Berkus (josh@agliodbs.com) wrote:
> > > It would be really nice to be able to GRANT/REVOKE on some of these
> > > special system views ...
>
> Just define a security definer wrapper function + view, that afair works
> perfectly fine.

Yes, it does, but it *sucks* to have to create a bunch of security
definer wrapper functions, and as I think we've seen, getting those
right can also be tricky...

> > Don't know what folks think of removing those in-the-function checks in
> > favor of trusting the grant/revoke system to not allow those functions
> > to be called unless you have EXECUTE privileges on them..
>
> Well, they *do* return some information when called without superuser
> privileges. Just not all columns for all sessions. I don't think you can
> achieve that with anything in our permission system.

We'd have to address those issues somehow, certainly.  The general
thrust of my thought was if we'd ever feel comfortable trusting the
GRANT/REVOKE permission system instead of places what we currently have
if(superuser()) checks or similar.

Of course, if we had RLS, we could actually support such a difference in
results based on user with that.. ;)
Thanks,
    Stephen

On 2014-01-21 20:18:54 -0500, Stephen Frost wrote:

> > > Don't know what folks think of removing those in-the-function checks in
> > > favor of trusting the grant/revoke system to not allow those functions
> > > to be called unless you have EXECUTE privileges on them..
> > 
> > Well, they *do* return some information when called without superuser
> > privileges. Just not all columns for all sessions. I don't think you can
> > achieve that with anything in our permission system.
> 
> We'd have to address those issues somehow, certainly.  The general
> thrust of my thought was if we'd ever feel comfortable trusting the
> GRANT/REVOKE permission system instead of places what we currently have
> if(superuser()) checks or similar.

I think the only realistic thing is a "monitoring" capability, like we
have "replication". GRANT/REVOKE doesn't even come close to being able
to generically allow to grant permissions of even the moderate
complexity pg_stat_get_activity() has.

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services

On 22/01/14 13:32, Harold Giménez wrote:
> On Tue, Jan 21, 2014 at 4:19 PM, Bruce Momjian <bruce@momjian.us> wrote:
>> On Tue, Jan 21, 2014 at 04:06:46PM -0800, Harold Giménez wrote:
>>> I don't know of a client where it can't be overridden. The friction
>>> occurs when by default it sets it to something useful to a developer
>>> (useful eg: to find what process is holding a lock), but is not
>>> possible to conceal from other users on the same cluster. If this were
>>> an in-premise or private cluster the point is moot.
>>>
>>> Furthermore consider when even using application_name for it's
>>> original intended use. On a shared environment as I'm describing here,
>>> that makes it possible for an attacker to identify what apps connect
>>> to a given server, or on the other hand is a way to find out where a
>>> given application stores its data, which can be used for a more
>>> targeted attack.
>>
>> So security through obscurity?  Why wouldn't the attacker just try all
>> the app methods at once and not even bother looking at the application
>> name?
>
> A malicious attacker may want to attack or harm `app1`. They write a
> script that provisions databases and check in pg_stat_activity until
> they find an application_name of `app1`. Having found the database
> holding app1's data, they then use a targeted attack on postgres, say
> a privilege escalation attack or any other vulnerability we don't know
> exists yet. Without application_name, the attacker would be unable to
> find the target database host to attack.
>

If said malicious attacker can log into postgres and issue its own 
queries, and connect to other database then you are in serious trouble 
already.

I also wonder that if such an attacker knows the application name, that 
would suggest that they have access to the application server and are 
able to read its config files...which would probably also contain the 
host and db name too (and possibly the password in some unfortunate cases)!

In addition people normally name app users and app dbs in a fairly 
predictable way (e.g 'finance' app uses 'finance' user to connect to 
'finance' db), so I'm not seeing much value in trying to hide 
application name.


Regards

Mark

On Tue, Jan 21, 2014 at 5:22 PM, Mark Kirkwood
<mark.kirkwood@catalyst.net.nz> wrote:
> On 22/01/14 13:32, Harold Giménez wrote:
>>
>> On Tue, Jan 21, 2014 at 4:19 PM, Bruce Momjian <bruce@momjian.us> wrote:
>>>
>>> On Tue, Jan 21, 2014 at 04:06:46PM -0800, Harold Giménez wrote:
>>>>
>>>> I don't know of a client where it can't be overridden. The friction
>>>> occurs when by default it sets it to something useful to a developer
>>>> (useful eg: to find what process is holding a lock), but is not
>>>> possible to conceal from other users on the same cluster. If this were
>>>> an in-premise or private cluster the point is moot.
>>>>
>>>> Furthermore consider when even using application_name for it's
>>>> original intended use. On a shared environment as I'm describing here,
>>>> that makes it possible for an attacker to identify what apps connect
>>>> to a given server, or on the other hand is a way to find out where a
>>>> given application stores its data, which can be used for a more
>>>> targeted attack.
>>>
>>>
>>> So security through obscurity?  Why wouldn't the attacker just try all
>>> the app methods at once and not even bother looking at the application
>>> name?
>>
>>
>> A malicious attacker may want to attack or harm `app1`. They write a
>> script that provisions databases and check in pg_stat_activity until
>> they find an application_name of `app1`. Having found the database
>> holding app1's data, they then use a targeted attack on postgres, say
>> a privilege escalation attack or any other vulnerability we don't know
>> exists yet. Without application_name, the attacker would be unable to
>> find the target database host to attack.
>>
>
> If said malicious attacker can log into postgres and issue its own queries,
> and connect to other database then you are in serious trouble already.

They can connect to their database, but not other databases in the cluster.

>
> I also wonder that if such an attacker knows the application name, that
> would suggest that they have access to the application server and are able
> to read its config files...which would probably also contain the host and db
> name too (and possibly the password in some unfortunate cases)!

No, I am considering something along the lines of www.foobar.com would
have an application_name =~ '%foobar%'

>
> In addition people normally name app users and app dbs in a fairly
> predictable way (e.g 'finance' app uses 'finance' user to connect to
> 'finance' db), so I'm not seeing much value in trying to hide application
> name.

Some database service providers like Heroku Postgres generate role
names and passwords, which are not predictable and users cannot
change.

Regards,
-Harold

Andres Freund <andres@2ndquadrant.com> writes:
> On 2014-01-21 20:00:51 -0500, Stephen Frost wrote:
>> Don't know what folks think of removing those in-the-function checks in
>> favor of trusting the grant/revoke system to not allow those functions
>> to be called unless you have EXECUTE privileges on them..

> Well, they *do* return some information when called without superuser
> privileges. Just not all columns for all sessions. I don't think you can
> achieve that with anything in our permission system.

Yeah.  We could replace blanket works-or-throws-error cases with
permission-to-call-the-function (and I see no reason not to).
But there are lots of cases where the current behavior is more
fine-grained than that.
        regards, tom lane

On 01/21/2014 05:21 PM, Andres Freund wrote:
> I think the only realistic thing is a "monitoring" capability, like we
> have "replication". GRANT/REVOKE doesn't even come close to being able
> to generically allow to grant permissions of even the moderate
> complexity pg_stat_get_activity() has.

That would work for me, personally.   I don't know how it would work for
anyone else.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 01/21/2014 05:22 PM, Mark Kirkwood wrote:
> If said malicious attacker can log into postgres and issue its own
> queries, and connect to other database then you are in serious trouble
> already.
> 
> I also wonder that if such an attacker knows the application name, that
> would suggest that they have access to the application server and are
> able to read its config files...which would probably also contain the
> host and db name too (and possibly the password in some unfortunate cases)!

Common case:

Multitenant shared hosting on a public cloud.

1. attacker writes a tool which exploits VulnerableApplication and takes
it over.

2. they exploit SiteX, running that web app.

3. using SiteX's database credentials, they check pg_stat_activity and
see what other hosts are running VulnerableApplication.

4. They then infect the other hosts running VulnerableApplication.

Alternately:

4. They use VulnerableApplication's predictable password-generating flaw
to log into the other databases, or to try the default password which
ships with the app.

However, thinking about the case above, there are a number of caveats
and workarounds which make the above not that interesting of an exploit
case:

A. it's easy to find VulnerableApplication simply by scanning the web.
Easier, in fact, than the above, if you have an IP block to start with,
and you would.

B. Most applications don't actually set application-specific application
names anyway ("psycopg2", "libpq").

C. It would be trivially easy for a DBA concerned about security to
obfuscate application names in a way which would not be easy for an
attacker to analyze.

D. Don't use default passwords.  Also, the attacker merely needs to try
each database in turn anyway.

Given the above, I think this specific patch falls into the broad class
of things we would like to have in a "multi-tenant toolkit" (and is far
from the most useful of those), which would include:

* hiding application_name, user_name, and database_names from users of
other databases

* "local superuser" who can create per-database users and extensions
from an approved list

* ability to block users from changing some resource limits (work_mem,
for example).

* per-database logging (could be done with log hooks, just needs a contrib).

It seems to me that it's not terribly useful to "fix" one item on the
above list without having at least a *plan* to address the others. This
really needs to be part of a comprehensive system, not piecework, or
we'll end up with a bunch of little security options which don't work
together.

Probably Heroku has some more specific exploit case to be concerned
about here; if so, might I suggest taking it up with the -security list?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Stephen Frost <sfrost@snowman.net> wrote:
> Harold Giménez (harold@heroku.com) wrote:
>
>> This is just an example of how running monitoring as superuser
>> is not necessarily the worst thing, and there are other reasons
>> to do it already.
>
> It's a horrible thing and that isn't a good reason- if my
> database isn't accepting connections, I probably don't care one
> bit how bloated a table is.  Indeed, I care *more* that I'm out
> of connections and would want to know that ASAP.

+1

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Jan 21, 2014 at 7:38 PM, Stephen Frost <sfrost@snowman.net> wrote:
> * Harold Giménez (harold@heroku.com) wrote:
>> Definitely agree with you. This is just an example of how running
>> monitoring as superuser is not necessarily the worst thing, and there
>> are other reasons to do it already.
>
> It's a horrible thing and that isn't a good reason- if my database isn't
> accepting connections, I probably don't care one bit how bloated a table
> is.  Indeed, I care *more* that I'm out of connections and would want to
> know that ASAP

This is a silly argument. When things are failing that's precisely
when you need your monitoring system to work. Of course you are
interested in the fact that you're out of connections but you're
trying to figure out *why* you're out of connections. Maybe you have
queries consuming all iops, maybe disks have filled up and that's
cascaded into a postgres problem, maybe you have lots of similar
queries running, etc.

The first step in debugging is always gathering data which is why it's
so frustrating if the first thing to go down is your monitoring
software.


--
greg

On Wed, Jan 22, 2014 at 1:03 PM, Josh Berkus <josh@agliodbs.com> wrote:
> Probably Heroku has some more specific exploit case to be concerned
> about here; if so, might I suggest taking it up with the -security list?

I don't think there's a specific vulnerability that needs to be kept
secret here.

Here's an example. I just created a new "hobby" database which is on a
multi-tenant cluster and ran select * from pg_stat_activity. Here are
two of the more interesting examples:
463752 | de5nmf0gbii3u5 | 32250 |   463751 | qspfkgrwgqtbcu | unicorn
worker[1] -p 30390 -c ./config/unicorn.rb |                |     |             |                              |
 |                               |    |         |        | <insufficient privilege>463752 | de5nmf0gbii3u5 | 32244 |
463751| qspfkgrwgqtbcu | unicorn
 
worker[0] -p 30390 -c ./config/unicorn.rb |                |     |             |                              |
 |                               |    |         |        | <insufficient privilege>
 


Note that the contents of the ARGV array are being set by the
"unicorn" task queuing library. It knows it's making this information
visible to other users with shell access on this machine. But the
decision to stuff the ARGV information into the application_name is
being made by the Pg driver. Neither is under the control of the
application author who may not even be aware this is happening.
Neither component has the complete information to make a competent
decision about whether this information is safe to be in
application_name or not.

Note that the query is showing as "<insufficient privilege>" even
though it is listed in the ps output -- the same ps output that is
listing the unicorn ARGV that is being shown in the
application_name....

You might say that the Pg gem is at fault for making a blanket policy
decision for applications that the ARGV is safe to show to other
database users but realistically it's so useful to see this
information for your own connections that it's probably the right
decision. Without it it's awfully hard to tell which worker is on
which connection. It would just be nice to be able to treat
application_name the same as query.

On Fri, Jan 24, 2014 at 6:46 AM, Magnus Hagander <magnus@hagander.net> wrote:
>
> On Thu, Jan 23, 2014 at 2:01 AM, Greg Stark <stark@mit.edu> wrote:
>>
>> On Wed, Jan 22, 2014 at 1:03 PM, Josh Berkus <josh@agliodbs.com> wrote:
>> > Probably Heroku has some more specific exploit case to be concerned
>> > about here; if so, might I suggest taking it up with the -security list?
>>
>> I don't think there's a specific vulnerability that needs to be kept
>> secret here.
>>
>> Here's an example. I just created a new "hobby" database which is on a
>> multi-tenant cluster and ran select * from pg_stat_activity. Here are
>> two of the more interesting examples:
>>
>>  463752 | de5nmf0gbii3u5 | 32250 |   463751 | qspfkgrwgqtbcu | unicorn
>> worker[1] -p 30390 -c ./config/unicorn.rb |                |
>>       |             |                              |
>>             |                               |
>>      |         |        | <insufficient privilege>
>>  463752 | de5nmf0gbii3u5 | 32244 |   463751 | qspfkgrwgqtbcu | unicorn
>> worker[0] -p 30390 -c ./config/unicorn.rb |                |
>>       |             |                              |
>>             |                               |
>>      |         |        | <insufficient privilege>
>>
>>
>> Note that the contents of the ARGV array are being set by the
>> "unicorn" task queuing library. It knows it's making this information
>> visible to other users with shell access on this machine. But the
>> decision to stuff the ARGV information into the application_name is
>> being made by the Pg driver. Neither is under the control of the
>> application author who may not even be aware this is happening.
>> Neither component has the complete information to make a competent
>> decision about whether this information is safe to be in
>> application_name or not.
>>
>> Note that the query is showing as "<insufficient privilege>" even
>> though it is listed in the ps output -- the same ps output that is
>> listing the unicorn ARGV that is being shown in the
>> application_name....
>>
>> You might say that the Pg gem is at fault for making a blanket policy
>> decision for applications that the ARGV is safe to show to other
>> database users but realistically it's so useful to see this
>> information for your own connections that it's probably the right
>> decision. Without it it's awfully hard to tell which worker is on
>> which connection. It would just be nice to be able to treat
>> application_name the same as query.
>
>
> I would say that yes, this is clearly broken in the Pg gem. I can see it
> having such a default, but not allowing an override...

Uhm, it does allow an override as I said before.

>
> The application can of course issue a SET application_name, assuming there
> is a hook somewhere in the system that will run after the connection has
> been established. I've had customers use that many times in java based
> systems for example, but I don't know enough about the pg gem, or unicorn,
> to have a clue if anything like it exists there. This is also a good way to
> track how connections are used throughout a pooled system where the same
> connection might be used for different things at different times.
>
> What actually happens if you set the application_name in the connection
> string in that environment? Does it override it to it's own default? If so,
> the developers there clearly need to be taught about
> fallback_application_name.

It can be overridden using any of these methods. It does in fact use
fallback_application_name when it defaults to $0, see
https://bitbucket.org/ged/ruby-pg/src/6c2444dc63e17eb695363993e8887cc5d67750bc/lib/pg/connection.rb?at=default#cl-46

>
> And what happens if you set it in PGAPPNAME?

It works fine:

```
PGAPPNAME=this_is_a_custom_app_name ruby -w -rpg -e
"PG.connect(dbname: 'hgmnz', host: 'localhost').exec('SELECT
application_name FROM pg_stat_activity') { |res| res.each { |row| puts
row.values_at('application_name') } }"
this_is_a_custom_app_name
```

-Harold

On Fri, Jan 24, 2014 at 6:46 AM, Magnus Hagander <magnus@hagander.net> wrote:
> What actually happens if you set the application_name in the connection
> string in that environment? Does it override it to it's own default? If so,
> the developers there clearly need to be taught about
> fallback_application_name.
>
> And what happens if you set it in PGAPPNAME?

My point wasn't that an application couldn't control this. The point
is that this isn't so easy to manage and users might not realize
there's anything to do.

And it's not necessarily the case that the library could warn users.
No one of the parts of the code here has the whole picture. In this
case one part of the code is stuffing the information in $0 and
another part is defaulting application_name to $0.

> Long term I agree we should really have some way of controlling these
> permissions more fine grained, but I just blanket hiding application name
> for non-superusers seems like a bad solution that still only fixes a small
> part of the problem.

It makes a lot of sense to me to treat it the same way as sql_query.
It's pretty similar (especially in the above given that we put the sql
query in $0 after all)


-- 
greg

On Sat, Jan 25, 2014 at 2:33 AM, Magnus Hagander <magnus@hagander.net> wrote:
>
> With that many options of "hiding" it, I would still argue for just picking
> one of those.
>
> For example, of Heroku wants to protect their customers against the
> behaviour of the pg gem, you can for example set PGAPPNAME in the
> environment. That will override what the gem sets in
> fallback_application_name, but those users that actually use it and specify
> it in their connection string, will override that default.

The problem with that is that it doesn't just hide it. It removes the
debugging information altogether. Even the administrator of the
application itself and the DBA won't have this information. The reason
the Gem is putting that information in application_name is precisely
because it's useful. In fact it was a patch from Heroku that added
that information to application_name in the first place because it's
useful.

> And all of that without removing a valuable debugging/tracing tool from
> other users.

Why is application_name useful for users who aren't the DBA and aren't
the user in question. The sql_query would probably be more useful than
application_name but we hide that...

-- 
greg

Greg Stark <stark@mit.edu> writes:
> The problem with that is that it doesn't just hide it. It removes the
> debugging information altogether. Even the administrator of the
> application itself and the DBA won't have this information. The reason
> the Gem is putting that information in application_name is precisely
> because it's useful. In fact it was a patch from Heroku that added
> that information to application_name in the first place because it's
> useful.

Oh really.  So, to clean up after their own ill-considered decision,
they'd like to take useful information away from everybody.
        regards, tom lane

On 2014-01-28 07:27:52 -0800, Greg Stark wrote:
> > And all of that without removing a valuable debugging/tracing tool from
> > other users.
> 
> Why is application_name useful for users who aren't the DBA and aren't
> the user in question. The sql_query would probably be more useful than
> application_name but we hide that...

It is useful to find out what the backend blocking you by taking out
locks or using all CPU is doing. And a sql query obviously can contain
actually sensitive data that you can't really hide.

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services

On Tue, Jan 28, 2014 at 7:49 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Oh really.  So, to clean up after their own ill-considered decision,
> they'd like to take useful information away from everybody.

Well maybe. Or we want this useful information at a finer granularity
than "everyone or nobody" and given the choice we prefer to have it
than not.


-- 
greg

On Tue, Jan 28, 2014 at 11:28 AM, Greg Stark <stark@mit.edu> wrote:
> Well maybe. Or we want this useful information at a finer granularity
> than "everyone or nobody" and given the choice we prefer to have it
> than not.

Anyways, I don't feel incredibly strongly about this. I think we
should default any user-data to being visible only that user as a
general principle but I also think a system predicated on data like
argv or application_name being kept private is pretty fragile and
should be avoided so I'm not super tense about additional ways these
things can leak. I feel like this is an example where -hackers has a
bit of a blind spot when it comes to smaller databases by users who
aren't expert DBAs and don't need a dedicated box.


-- 
greg

On 01/28/2014 07:27 AM, Greg Stark wrote:
> Why is application_name useful for users who aren't the DBA and aren't
> the user in question. The sql_query would probably be more useful than
> application_name but we hide that...

I have non-privileged monitoring scripts do counts of connections by
application name all the time as a way of checking for runaway
applications, and would be quite put out by restricting this to superusers.

Really the only way we're going to solve this is to make column
permissions on special system views fully configurable.

For example, I would really like to GRANT an unpriv user access to the
WAL columns in pg_stat_replication so that I can monitor replication
delay without granting superuser permissions.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Tue, Jan 28, 2014 at 11:56 AM, Josh Berkus <josh@agliodbs.com> wrote:
> Really the only way we're going to solve this is to make column
> permissions on special system views fully configurable.
>
> For example, I would really like to GRANT an unpriv user access to the
> WAL columns in pg_stat_replication so that I can monitor replication
> delay without granting superuser permissions.

So you can do this now by defining a security definer function that
extracts precisely the information you need and grant execute access
to precisely the users you want. There was some concern upthread about
defining security definer functions being tricky but I'm not sure what
conclusion to draw from that argument.

Even if we had column level privileges this would still be necessary
in many cases and might be preferable to keep things consistent. For
example, you might not want the monitor account to have access to
sql_query but be able to check for backends running specific queries
(perhaps vacuum or ddl or a known problematic query).


-- 
greg

Josh Berkus <josh@agliodbs.com> writes:
> For example, I would really like to GRANT an unpriv user access to the
> WAL columns in pg_stat_replication so that I can monitor replication
> delay without granting superuser permissions.

Just out of curiosity, why is that superuser-only at all?  AFAICS the
hidden columns are just some LSNs ... what is the security argument
for hiding them in the first place?
        regards, tom lane

Josh,

* Josh Berkus (josh@agliodbs.com) wrote:
> Really the only way we're going to solve this is to make column
> permissions on special system views fully configurable.

We really need to fully support column and row-level security to provide
the kind of granularty which we do today (but force on users through
using C functions which hide data depending on who you are instead of
giving them the ability to configure it themselves).

> For example, I would really like to GRANT an unpriv user access to the
> WAL columns in pg_stat_replication so that I can monitor replication
> delay without granting superuser permissions.

+1000
Thanks,
    Stephen

On 01/28/2014 12:10 PM, Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
>> For example, I would really like to GRANT an unpriv user access to the
>> WAL columns in pg_stat_replication so that I can monitor replication
>> delay without granting superuser permissions.
> 
> Just out of curiosity, why is that superuser-only at all?  AFAICS the
> hidden columns are just some LSNs ... what is the security argument
> for hiding them in the first place?

Beats me, I can't find any discussion on it at all.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Greg,

* Greg Stark (stark@mit.edu) wrote:
> On Tue, Jan 28, 2014 at 11:56 AM, Josh Berkus <josh@agliodbs.com> wrote:
> > For example, I would really like to GRANT an unpriv user access to the
> > WAL columns in pg_stat_replication so that I can monitor replication
> > delay without granting superuser permissions.
>
> So you can do this now by defining a security definer function that
> extracts precisely the information you need and grant execute access
> to precisely the users you want. There was some concern upthread about
> defining security definer functions being tricky but I'm not sure what
> conclusion to draw from that argument.

Yeah, but that sucks if you want to build a generic monitoring system
like check_postgres.pl.  Telling users to grant certain privileges may
work out, telling them to install these pl/pgsql things you write as
security-definer-to-superuser isn't going to be nearly as easy when
these users are (understandably, imv) uncomfortable having a monitor
role have superusr privs.
Thanks,
    Stephen

On Tue, Jan 28, 2014 at 8:17 PM, Stephen Frost <sfrost@snowman.net> wrote:
> Greg,
>
> * Greg Stark (stark@mit.edu) wrote:
>> On Tue, Jan 28, 2014 at 11:56 AM, Josh Berkus <josh@agliodbs.com> wrote:
>> > For example, I would really like to GRANT an unpriv user access to the
>> > WAL columns in pg_stat_replication so that I can monitor replication
>> > delay without granting superuser permissions.
>>
>> So you can do this now by defining a security definer function that
>> extracts precisely the information you need and grant execute access
>> to precisely the users you want. There was some concern upthread about
>> defining security definer functions being tricky but I'm not sure what
>> conclusion to draw from that argument.
>
> Yeah, but that sucks if you want to build a generic monitoring system
> like check_postgres.pl.  Telling users to grant certain privileges may
> work out, telling them to install these pl/pgsql things you write as
> security-definer-to-superuser isn't going to be nearly as easy when
> these users are (understandably, imv) uncomfortable having a monitor
> role have superusr privs.

I couldn't agree more. Whatever we do here we need a standard
mechanism that tool developers can expect to be present and the same
on all servers. Otherwise, we make it extremely difficult to build
tools like pgAdmin, check_postgres.pl and so on.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 28 January 2014 20:14, Josh Berkus <josh@agliodbs.com> wrote:
> On 01/28/2014 12:10 PM, Tom Lane wrote:
>> Josh Berkus <josh@agliodbs.com> writes:
>>> For example, I would really like to GRANT an unpriv user access to the
>>> WAL columns in pg_stat_replication so that I can monitor replication
>>> delay without granting superuser permissions.
>>
>> Just out of curiosity, why is that superuser-only at all?  AFAICS the
>> hidden columns are just some LSNs ... what is the security argument
>> for hiding them in the first place?
>
> Beats me, I can't find any discussion on it at all.

No specific reason that I can recall but replication is heavily
protected by layers of security.

pg_stat_replication is a join with pg_stat_activity, so some of the
info is open, some closed. It seems possible to relax that.


Presumably the current patch is returned with feedback? Or can we fix
these problems by inventing a new user aspect called MONITOR (similar
to REPLICATION)? We can grant application_name and replication details
to that.

-- Simon Riggs                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services

On 01/29/2014 10:19 AM, Simon Riggs wrote:
> No specific reason that I can recall but replication is heavily
> protected by layers of security.
> 
> pg_stat_replication is a join with pg_stat_activity, so some of the
> info is open, some closed. It seems possible to relax that.

I'm all for the idea of "restrict, then open up".  That is, it made
sense to start with data restricted, but then unrestrict is as we know
it's OK.  Going the other way generally isn't possible, as this patch
demonstrates.

> Presumably the current patch is returned with feedback? Or can we fix
> these problems by inventing a new user aspect called MONITOR (similar
> to REPLICATION)? We can grant application_name and replication details
> to that.

Yeah, except I don't see doing the MONITOR thing for 9.4.  We'd need a
spec for it first.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com