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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* 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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Jan 21, 2014 at 2:41 PM, Alvaro Herrera <alvherre@2ndquadrant.com>wrote:

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

Isn't CAP_BACKUP pretty much the REPLICATION privilege?

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

* 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

On Tue, Jan 21, 2014 at 5:18 PM, Stephen Frost <sfrost@snowman.net> wrote:

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

That sounds more like CAP_DUMP than CAP_BACKUP :)

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* 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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers