From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephen Frost <sfrost@snowman.net>
cc: Michael Paquier <michael@paquier.xyz>,
        Kyotaro Horiguchi <horikyota.ntt@gmail.com>, alvherre@2ndquadrant.com,
        chris+postgresql@qwirx.com, pgsql-docs@lists.postgresql.org,
        pgsql-hackers@lists.postgresql.org, peter.eisentraut@2ndquadrant.com
Subject: Re: Logical replication subscription owner
In-reply-to: <20200509180933.GN13712@tamriel.snowman.net>
References: <17709.1588908623@sss.pgh.pa.us>
 <20200508050211.GA6945@alvherre.pgsql>
 <20200508.150326.909507514141434296.horikyota.ntt@gmail.com>
 <20200509085701.GM11539@paquier.xyz> <3747.1589037446@sss.pgh.pa.us>
 <20200509180933.GN13712@tamriel.snowman.net>
Comments: In-reply-to Stephen Frost <sfrost@snowman.net>
	message dated "Sat, 09 May 2020 14:09:33 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <6883.1589048538.1@sss.pgh.pa.us>
Date: Sat, 09 May 2020 14:22:18 -0400
Message-ID: <6884.1589048538@sss.pgh.pa.us>
Precedence: bulk

Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> ISTM those statements are contradictory.  The two privileges could
>> only be called orthogonal if it's possible to make use of one without
>> having the other.  As things stand, REPLICATION without LOGIN is an
>> entirely useless setting.

> Allowing a login to the system by a role that doesn't have the LOGIN
> privilege isn't sensible though.

The fundamental issue here is whether a replication connection is a
"login".  I'd argue that it is not; "login" ought to mean a normal
SQL connection.

I realize that a replication connection can issue SQL commands (which,
as I recall, Robert has blasted as a crappy design --- and I agree).
But it's already the case that a replication connection has much greater
privileges than plain SQL, so I don't think that that aspect ought to
compel us to design the privilege bits as they are set up now.  If
you think that LOGIN should be required to issue SQL commands, then
shouldn't doing SET ROLE to a non-LOGIN role disable your ability
to issue SQL?

> Perhaps a middle ground would be to set LOGIN on a role when REPLICATION
> is set on it, if it's not already set (maybe with a NOTICE or WARNING or
> such saying "also enabling LOGIN for role X", or maybe not if people
> really think it should be obvious).

It seems to me that there's value in having a role that can only
connect for replication purposes and not as a regular SQL user.
The existing definition doesn't support that, and the rather silly
kluge you're proposing doesn't fix it.

			regards, tom lane