Re: primary_conninfo missing from pg_stat_wal_receiver

Stephen Frost wrote:
> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> > Michael Paquier <michael(dot)paquier(at)gmail(dot)com> writes:
> > > On Tue, Jun 21, 2016 at 11:29 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > >> What I would want to know is whether this specific change is actually a
> > >> good idea. In particular, I'm concerned about the possible security
> > >> implications of exposing primary_conninfo --- might it not contain a
> > >> password, for example?
> >
> > > Yes it could, as a connection string, but we make the information of
> > > this view only visible to superusers. For the others, that's just
> > > NULL.
> >
> > Well, that's okay for now, but I'm curious to hear Stephen Frost's
> > opinion on this. He's been on the warpath to decrease our dependence
> > on superuser-ness for protection purposes. Seems to me that having
> > one column in this view that is a lot more security-sensitive than
> > the others is likely to be an issue someday.
>
> Ugh. I would certainly rather not have yet another special, hard-coded,
> bit of logic that magically makes things available to a superuser when
> it's not available to regular users.
>
> What that results in is the need to have a new default role to control
> access to that column for the non-superuser case.

FWIW we already have a superuser() check for the walsender stats view
since 9.1 -- see commit f88a6381. To appease this we could create our
second predefined role that controls access to both
pg_stat_get_wal_senders and pg_stat_get_wal_receiver. I don't think
my commit in 9.6 creates this problem, only exacerbates a pre-existing
one, but I also think it's fair to fix both cases for 9.6.

Not sure what to name the new predefined role though -- pg_wal_stats_reader?
(I don't suppose we want to create it to cover *any* future privileged
stats reads rather than just those WAL related, do we?)

> As for the password showing up, sorry, but we need a solution for *that*
> regardless of the rest- the password shouldn't be exposed to anyone, nor
> should it be sent and kept in the backend's memory for longer than
> necessary. I'm not suggesting we've got that figured out already, but
> that's where we should be trying to go.

I suppose Michael's proposed patch to copy the conninfo obscuring the
password should be enough for this, but I'll go have a closer look.

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