Re: Streaming replication as a separate permissions

On Mon, Dec 27, 2010 at 10:53, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> On Fri, Dec 24, 2010 at 05:46, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>> * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
>>> I think I agree with Florian about the confusing-ness of the proposed
>>> semantics.  Aren't you saying you want NOLOGIN mean "not allowed to
>>> log in for the purposes of issuing SQL commands, but allowed to log in
>>> for replication"?  Uggh.
>>
>> I like the general idea of a replication-only "role" or "login".  Maybe
>> implementing that as a role w/ all the things that come along with it
>> being a role isn't right, but we don't want to have to reinvent all the
>> supported auth mechanisms (and please don't propose limiting the auth
>> options for the replication login!).  Is there a way we can leverage the
>> auth mechanisms, etc, while forcing the 'replication role' to only be
>> able to do what a 'replication role' should do?
>
> We could quite easily make a replication role *never* be able to
> connect to a non-walsender backend. That would mean that if you set
> your role to WITH REPLICATION, it can *only* be used for replication
> and nothing else (well, you could still SET ROLE to it, but given that
> it's not a superuser (anymore), that doesn't have any security
> implications. Though we could perhaps restrict that as well in the SET
> ROLE processing code).
>
> This requires there to be a separate user for replication in all cases
> - which isn't a bad thing IMHO. And it also doesn't "abuse"/confuse
> the NOLOGIN attribute.

Actually, having implemented that and tested it, I realize that's a
pretty bad idea. For one thing, it broke my own pg_streamrecv program,
since it requires the ability to connect to the master and select a
pg_current_xlog_location().

We could allow NOLOGIN connections to do replication, but I agree with
those saying that's pretty confusing - to the point of making it very
easy for a DBA to think he's secure when he's not. And having the
*ability* to use the same role as replication and superuser isn't a
bad thing - it's either *requiring* it or *having it so by default*
that's the problem, IMHO.

I think it's Ok to ship with replication off and require people to
either create a replication role or assign the permissions to their
superuser - as long as this is well documented in the manual.

The compromise I think would be to ship with a replication role
installed and *enabled*. There's no win at all in shipping with a
disabled replication role - it would still require the "extra step"
that people want to avoid.

What I'd rather do is make the "all" keyword for databases in
pg_hba.conf actually include replication connections - now that we
have a separate permissions - thus removing the need to configure a
specific row in pg_hba.conf for those users who just put a "0.0.0.0/0
md5" row in there to dumb it down. That still let's those who care
about higher security lock it down if they want to, while still
removing a step for those users who don't care.

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