pg_stat_replication security

pg_stat_replication shows all replication information to all users, no
requirement to be a superuser or anything. That leaks a bunch of
information that regular pg_stat_activity doesn't - such as clients IP
addresses. And also of course all the replication info itself, which
may or may not be a problem.

I suggest pg_stat_replication do just like pg_stat_activity, which is
return NULL in most fields if the user isn't
(superuser||same_user_as_that_session).

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

> I suggest pg_stat_replication do just like pg_stat_activity, which is
> return NULL in most fields if the user isn't
> (superuser||same_user_as_that_session).

What session would that be, exactly?

I suggest instead either "superuser" or "replication" permissions.

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

On Sun, Jan 16, 2011 at 21:51, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
>> I suggest pg_stat_replication do just like pg_stat_activity, which is
>> return NULL in most fields if the user isn't
>> (superuser||same_user_as_that_session).
>
> What session would that be, exactly?

The user doing the query to pg_stat_replication being the same as the
user running the replication.

> I suggest instead either "superuser" or "replication" permissions.

That's another idea.

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

>> I suggest instead either "superuser" or "replication" permissions.
>
> That's another idea.

Oh, wait. I take that back ... we're trying to encourage users NOT to
use the "replication" user as a login, yes?

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

On Sun, Jan 16, 2011 at 21:57, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
>>> I suggest instead either "superuser" or "replication" permissions.
>>
>> That's another idea.
>
> Oh, wait.  I take that back ... we're trying to encourage users NOT to
> use the "replication" user as a login, yes?

yeah.

Here's a patch that limits it to superuser only. We can't easily match
it to the user of the session given the way the walsender data is
returned - it doesn't contain the user information. But limiting it to
superuser only seems perfectly reasonable and in line with the
encouragement not to use the replication user for login.

Objections?

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

On Mon, Jan 17, 2011 at 19:51, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> Here's a patch that limits it to superuser only. We can't easily match
> it to the user of the session given the way the walsender data is
> returned - it doesn't contain the user information. But limiting it to
> superuser only seems perfectly reasonable and in line with the
> encouragement not to use the replication user for login.
>
> Objections?

It hides all fields in pg_stat_wal_senders(). Instead, can we just
revoke usage of the function and view? Or, do we have some plans
to add fields which normal users can see?

--
Itagaki Takahiro

On Mon, Jan 17, 2011 at 12:11, Itagaki Takahiro
<itagaki(dot)takahiro(at)gmail(dot)com> wrote:
> On Mon, Jan 17, 2011 at 19:51, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>> Here's a patch that limits it to superuser only. We can't easily match
>> it to the user of the session given the way the walsender data is
>> returned - it doesn't contain the user information. But limiting it to
>> superuser only seems perfectly reasonable and in line with the
>> encouragement not to use the replication user for login.
>>
>> Objections?
>
> It hides all fields in pg_stat_wal_senders(). Instead, can we just
> revoke usage of the function and view?  Or, do we have some plans
> to add fields which normal users can see?

Yes, for consistency with pg_stat_activity. We let all users see which
other sessions are there, but not what they're doing - seems
reasonable to have the same definitions for replication sessions as
other sessions.

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

On Mon, Jan 17, 2011 at 13:14, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> On Mon, Jan 17, 2011 at 12:11, Itagaki Takahiro
> <itagaki(dot)takahiro(at)gmail(dot)com> wrote:
>> On Mon, Jan 17, 2011 at 19:51, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>>> Here's a patch that limits it to superuser only. We can't easily match
>>> it to the user of the session given the way the walsender data is
>>> returned - it doesn't contain the user information. But limiting it to
>>> superuser only seems perfectly reasonable and in line with the
>>> encouragement not to use the replication user for login.
>>>
>>> Objections?
>>
>> It hides all fields in pg_stat_wal_senders(). Instead, can we just
>> revoke usage of the function and view?  Or, do we have some plans
>> to add fields which normal users can see?
>
> Yes, for consistency with pg_stat_activity. We let all users see which
> other sessions are there, but not what they're doing - seems
> reasonable to have the same definitions for replication sessions as
> other sessions.

Committed.

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