Обсуждение: Logical replication subscription owner

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/10/sql-altersubscription.html
Description:

If the logical replication subscription is owned by a role that is not
allowed to login (for example, if the LOGIN privilege is removed after the
subscription is created) then the logical replication worker (which uses the
owner to connect to the database) will start to fail with this error
(repeated every 5 seconds), which is pretty much undocumented:

FATAL:  role "XXX" is not permitted to log in
LOG:  background worker "logical replication worker" (PID X) exited with
exit code 1

You might want to include that error message in the docs, to ensure that web
searches for it bring the user to this documentation.

On 2020-Apr-15, PG Doc comments form wrote:

> If the logical replication subscription is owned by a role that is not
> allowed to login (for example, if the LOGIN privilege is removed after the
> subscription is created) then the logical replication worker (which uses the
> owner to connect to the database) will start to fail with this error
> (repeated every 5 seconds), which is pretty much undocumented:
> 
> FATAL:  role "XXX" is not permitted to log in
> LOG:  background worker "logical replication worker" (PID X) exited with
> exit code 1
> 
> You might want to include that error message in the docs, to ensure that web
> searches for it bring the user to this documentation.

I wonder if a better answer is to allow the connection when the
REPLICATION priv is granted, ignoring the LOGIN prov.

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

Greetings,

* Alvaro Herrera (alvherre@2ndquadrant.com) wrote:
> On 2020-Apr-15, PG Doc comments form wrote:
> > If the logical replication subscription is owned by a role that is not
> > allowed to login (for example, if the LOGIN privilege is removed after the
> > subscription is created) then the logical replication worker (which uses the
> > owner to connect to the database) will start to fail with this error
> > (repeated every 5 seconds), which is pretty much undocumented:
> >
> > FATAL:  role "XXX" is not permitted to log in
> > LOG:  background worker "logical replication worker" (PID X) exited with
> > exit code 1
> >
> > You might want to include that error message in the docs, to ensure that web
> > searches for it bring the user to this documentation.
>
> I wonder if a better answer is to allow the connection when the
> REPLICATION priv is granted, ignoring the LOGIN prov.

Erm, no, I wouldn't have thought that'd make sense- maybe someone
specifically wants to stop allowing that role to login and they remove
LOGIN?  That REPLICATION would override that would surely be surprising
and counter-intuitive..

Thanks,

Stephen

On 2020-Apr-22, Stephen Frost wrote:
> * Alvaro Herrera (alvherre@2ndquadrant.com) wrote:

> > I wonder if a better answer is to allow the connection when the
> > REPLICATION priv is granted, ignoring the LOGIN prov.
> 
> Erm, no, I wouldn't have thought that'd make sense- maybe someone
> specifically wants to stop allowing that role to login and they remove
> LOGIN?  That REPLICATION would override that would surely be surprising
> and counter-intuitive..

Well, I guess if somebody wants to stop replication, they can remove
the REPLICATION priv.

I had it in my mind that LOGIN was for regular (SQL-based) login, and
REPLICATION was for replication login, and that they were orthogonal.

You're saying that there's no way a role can have REPLICATION privs but
no LOGIN.  Is that sensible?

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

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> I had it in my mind that LOGIN was for regular (SQL-based) login, and
> REPLICATION was for replication login, and that they were orthogonal.

Yeah, that's what I would've expected.  Otherwise, is REPLICATION
without LOGIN useful at all?

            regards, tom lane

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> > I had it in my mind that LOGIN was for regular (SQL-based) login, and
> > REPLICATION was for replication login, and that they were orthogonal.
>
> Yeah, that's what I would've expected.  Otherwise, is REPLICATION
> without LOGIN useful at all?

No, but it's less surprising, at least to me, for all roles that login
to require having the LOGIN right.  Having REPLICATION ignore that would
be surprising (and a change from today).  Maybe if we called it
REPLICATIONLOGIN or something along those lines it would be less
surprising, but it still seems pretty awkward.

I view REPLICATION as allowing a specific kind of connection, but you
first need to be able to login.

Also- what about per-database connections?  Does having REPLICATION mean
you get to override the CONNECT privileges on a database, if you're
connecting for the purposes of doing logical replication?

I agree we could do better in these areas, but I'd argue that's mostly
around improving the documentation rather than baking in implications
that one privilege implies another.  We certainly get people who
complain about getting a permission denied error when they have UPDATE
rights on a table (but not SELECT) and they include a WHERE clause in
their update statement, but that doesn't mean we should assume that
having UPDATE rights means you also get SELECT rights, just because
UPDATE is next to useless without SELECT.

Thanks,

Stephen

I'd welcome input from other people on this issue; only now I noticed
that it's buried in pgsql-docs, so CCing pgsql-hackers now.


On 2020-Apr-23, Stephen Frost wrote:

> Greetings,
> 
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> > Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> > > I had it in my mind that LOGIN was for regular (SQL-based) login, and
> > > REPLICATION was for replication login, and that they were orthogonal.
> > 
> > Yeah, that's what I would've expected.  Otherwise, is REPLICATION
> > without LOGIN useful at all?
> 
> No, but it's less surprising, at least to me, for all roles that login
> to require having the LOGIN right.  Having REPLICATION ignore that would
> be surprising (and a change from today).  Maybe if we called it
> REPLICATIONLOGIN or something along those lines it would be less
> surprising, but it still seems pretty awkward.
> 
> I view REPLICATION as allowing a specific kind of connection, but you
> first need to be able to login.
> 
> Also- what about per-database connections?  Does having REPLICATION mean
> you get to override the CONNECT privileges on a database, if you're
> connecting for the purposes of doing logical replication?
> 
> I agree we could do better in these areas, but I'd argue that's mostly
> around improving the documentation rather than baking in implications
> that one privilege implies another.  We certainly get people who
> complain about getting a permission denied error when they have UPDATE
> rights on a table (but not SELECT) and they include a WHERE clause in
> their update statement, but that doesn't mean we should assume that
> having UPDATE rights means you also get SELECT rights, just because
> UPDATE is next to useless without SELECT.
> 
> Thanks,
> 
> Stephen




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

I'd welcome input from other people on this issue; only now I noticed
that it's buried in pgsql-docs, so CCing pgsql-hackers now.


On 2020-Apr-23, Stephen Frost wrote:

> Greetings,
> 
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> > Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> > > I had it in my mind that LOGIN was for regular (SQL-based) login, and
> > > REPLICATION was for replication login, and that they were orthogonal.
> > 
> > Yeah, that's what I would've expected.  Otherwise, is REPLICATION
> > without LOGIN useful at all?
> 
> No, but it's less surprising, at least to me, for all roles that login
> to require having the LOGIN right.  Having REPLICATION ignore that would
> be surprising (and a change from today).  Maybe if we called it
> REPLICATIONLOGIN or something along those lines it would be less
> surprising, but it still seems pretty awkward.
> 
> I view REPLICATION as allowing a specific kind of connection, but you
> first need to be able to login.
> 
> Also- what about per-database connections?  Does having REPLICATION mean
> you get to override the CONNECT privileges on a database, if you're
> connecting for the purposes of doing logical replication?
> 
> I agree we could do better in these areas, but I'd argue that's mostly
> around improving the documentation rather than baking in implications
> that one privilege implies another.  We certainly get people who
> complain about getting a permission denied error when they have UPDATE
> rights on a table (but not SELECT) and they include a WHERE clause in
> their update statement, but that doesn't mean we should assume that
> having UPDATE rights means you also get SELECT rights, just because
> UPDATE is next to useless without SELECT.
> 
> Thanks,
> 
> Stephen




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

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> I'd welcome input from other people on this issue; only now I noticed
> that it's buried in pgsql-docs, so CCing pgsql-hackers now.

FWIW, I would argue that LOGIN permits logging in on a regular SQL
connection, while REPLICATION should permit logging in on a
replication connection, and there's no reason for either to depend on
or require the other.

> On 2020-Apr-23, Stephen Frost wrote:
>> Also- what about per-database connections?  Does having REPLICATION mean
>> you get to override the CONNECT privileges on a database, if you're
>> connecting for the purposes of doing logical replication?

No, why would it?  Should LOGIN privilege mean you can override
CONNECT?  That's nonsense.  You need the respective privilege
to connect with the protocol you want to connect with, and you
also need CONNECT on the DB you want to connect to.

            regards, tom lane

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> I'd welcome input from other people on this issue; only now I noticed
> that it's buried in pgsql-docs, so CCing pgsql-hackers now.

FWIW, I would argue that LOGIN permits logging in on a regular SQL
connection, while REPLICATION should permit logging in on a
replication connection, and there's no reason for either to depend on
or require the other.

> On 2020-Apr-23, Stephen Frost wrote:
>> Also- what about per-database connections?  Does having REPLICATION mean
>> you get to override the CONNECT privileges on a database, if you're
>> connecting for the purposes of doing logical replication?

No, why would it?  Should LOGIN privilege mean you can override
CONNECT?  That's nonsense.  You need the respective privilege
to connect with the protocol you want to connect with, and you
also need CONNECT on the DB you want to connect to.

            regards, tom lane

On 2020-May-07, Tom Lane wrote:

> FWIW, I would argue that LOGIN permits logging in on a regular SQL
> connection, while REPLICATION should permit logging in on a
> replication connection, and there's no reason for either to depend on
> or require the other.

I agree with this.

> >> Also- what about per-database connections?  Does having REPLICATION mean
> >> you get to override the CONNECT privileges on a database, if you're
> >> connecting for the purposes of doing logical replication?
> 
> No, why would it?  Should LOGIN privilege mean you can override
> CONNECT?  That's nonsense.  You need the respective privilege
> to connect with the protocol you want to connect with, and you
> also need CONNECT on the DB you want to connect to.

And this.

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

On 2020-May-07, Tom Lane wrote:

> FWIW, I would argue that LOGIN permits logging in on a regular SQL
> connection, while REPLICATION should permit logging in on a
> replication connection, and there's no reason for either to depend on
> or require the other.

I agree with this.

> >> Also- what about per-database connections?  Does having REPLICATION mean
> >> you get to override the CONNECT privileges on a database, if you're
> >> connecting for the purposes of doing logical replication?
> 
> No, why would it?  Should LOGIN privilege mean you can override
> CONNECT?  That's nonsense.  You need the respective privilege
> to connect with the protocol you want to connect with, and you
> also need CONNECT on the DB you want to connect to.

And this.

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

At Fri, 8 May 2020 01:02:11 -0400, Alvaro Herrera <alvherre@2ndquadrant.com> wrote in 
> On 2020-May-07, Tom Lane wrote:
> 
> > FWIW, I would argue that LOGIN permits logging in on a regular SQL
> > connection, while REPLICATION should permit logging in on a
> > replication connection, and there's no reason for either to depend on
> > or require the other.
> 
> I agree with this.

I agree, too.  Anyway, it is unreasonable that a user is banned for
the lack of replication-attribute after a successful *replication*
login.

LOG:  replication connection authorized: user=user1 application_name=psql
FATAL:  must be superuser or replication role to start walsender

> > >> Also- what about per-database connections?  Does having REPLICATION mean
> > >> you get to override the CONNECT privileges on a database, if you're
> > >> connecting for the purposes of doing logical replication?
> > 
> > No, why would it?  Should LOGIN privilege mean you can override
> > CONNECT?  That's nonsense.  You need the respective privilege
> > to connect with the protocol you want to connect with, and you
> > also need CONNECT on the DB you want to connect to.
> 
> And this.

A user can start physical replication without needing CONNECT on any
database if it has REPLICATION attribute.  That means any user that
is allowed logical replication on a specific database (or even no
databases) can replicate the whole cluster using physical replication.
I don't think it is a proper behavior from the security perspective.

It seems to me that we need to restrict physical replication to
require CONNECT privilege on all databases, or separate physical
replication privilege from logical replication privilege.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

At Fri, 8 May 2020 01:02:11 -0400, Alvaro Herrera <alvherre@2ndquadrant.com> wrote in 
> On 2020-May-07, Tom Lane wrote:
> 
> > FWIW, I would argue that LOGIN permits logging in on a regular SQL
> > connection, while REPLICATION should permit logging in on a
> > replication connection, and there's no reason for either to depend on
> > or require the other.
> 
> I agree with this.

I agree, too.  Anyway, it is unreasonable that a user is banned for
the lack of replication-attribute after a successful *replication*
login.

LOG:  replication connection authorized: user=user1 application_name=psql
FATAL:  must be superuser or replication role to start walsender

> > >> Also- what about per-database connections?  Does having REPLICATION mean
> > >> you get to override the CONNECT privileges on a database, if you're
> > >> connecting for the purposes of doing logical replication?
> > 
> > No, why would it?  Should LOGIN privilege mean you can override
> > CONNECT?  That's nonsense.  You need the respective privilege
> > to connect with the protocol you want to connect with, and you
> > also need CONNECT on the DB you want to connect to.
> 
> And this.

A user can start physical replication without needing CONNECT on any
database if it has REPLICATION attribute.  That means any user that
is allowed logical replication on a specific database (or even no
databases) can replicate the whole cluster using physical replication.
I don't think it is a proper behavior from the security perspective.

It seems to me that we need to restrict physical replication to
require CONNECT privilege on all databases, or separate physical
replication privilege from logical replication privilege.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

On Fri, May 08, 2020 at 03:03:26PM +0900, Kyotaro Horiguchi wrote:
> At Fri, 8 May 2020 01:02:11 -0400, Alvaro Herrera <alvherre@2ndquadrant.com> wrote in
>> On 2020-May-07, Tom Lane wrote:
>>> FWIW, I would argue that LOGIN permits logging in on a regular SQL
>>> connection, while REPLICATION should permit logging in on a
>>> replication connection, and there's no reason for either to depend on
>>> or require the other.
>>
>> I agree with this.
>
> I agree, too.  Anyway, it is unreasonable that a user is banned for
> the lack of replication-attribute after a successful *replication*
> login.

Not to make the life of everybody more complicated here, but I don't
agree.  LOGIN and REPLICATION are in my opinion completely orthogonal
and it sounds more natural IMO that a REPLICATION user should be able
to log into the server only if it has LOGIN defined.
--
Michael

On Fri, May 08, 2020 at 03:03:26PM +0900, Kyotaro Horiguchi wrote:
> At Fri, 8 May 2020 01:02:11 -0400, Alvaro Herrera <alvherre@2ndquadrant.com> wrote in
>> On 2020-May-07, Tom Lane wrote:
>>> FWIW, I would argue that LOGIN permits logging in on a regular SQL
>>> connection, while REPLICATION should permit logging in on a
>>> replication connection, and there's no reason for either to depend on
>>> or require the other.
>>
>> I agree with this.
>
> I agree, too.  Anyway, it is unreasonable that a user is banned for
> the lack of replication-attribute after a successful *replication*
> login.

Not to make the life of everybody more complicated here, but I don't
agree.  LOGIN and REPLICATION are in my opinion completely orthogonal
and it sounds more natural IMO that a REPLICATION user should be able
to log into the server only if it has LOGIN defined.
--
Michael

Michael Paquier <michael@paquier.xyz> writes:
> Not to make the life of everybody more complicated here, but I don't
> agree.  LOGIN and REPLICATION are in my opinion completely orthogonal
> and it sounds more natural IMO that a REPLICATION user should be able
> to log into the server only if it has LOGIN defined.

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.

            regards, tom lane

Michael Paquier <michael@paquier.xyz> writes:
> Not to make the life of everybody more complicated here, but I don't
> agree.  LOGIN and REPLICATION are in my opinion completely orthogonal
> and it sounds more natural IMO that a REPLICATION user should be able
> to log into the server only if it has LOGIN defined.

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.

            regards, tom lane

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Michael Paquier <michael@paquier.xyz> writes:
> > Not to make the life of everybody more complicated here, but I don't
> > agree.  LOGIN and REPLICATION are in my opinion completely orthogonal
> > and it sounds more natural IMO that a REPLICATION user should be able
> > to log into the server only if it has LOGIN defined.
>
> 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.

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

I don't think taking away login should take away replication though as
maybe there's some reason why someone would want that, nor should we
take away login if replication is taken away, this would strictly just
be a change for when REPLICATION is added to a role that doesn't have
LOGIN already.

Thanks,

Stephen

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Michael Paquier <michael@paquier.xyz> writes:
> > Not to make the life of everybody more complicated here, but I don't
> > agree.  LOGIN and REPLICATION are in my opinion completely orthogonal
> > and it sounds more natural IMO that a REPLICATION user should be able
> > to log into the server only if it has LOGIN defined.
>
> 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.

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

I don't think taking away login should take away replication though as
maybe there's some reason why someone would want that, nor should we
take away login if replication is taken away, this would strictly just
be a change for when REPLICATION is added to a role that doesn't have
LOGIN already.

Thanks,

Stephen

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

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