Subject: Logical replication subscription owner

From: PG Doc comments form <[email-masked]>
Date: Wed, 15 Apr 2020 14:02:18 +0000

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.

---

From: Alvaro Herrera <[email-masked]>
Date: Wed, 22 Apr 2020 13:40:29 -0400

> 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

---

From: Stephen Frost <[email-masked]>
Date: Wed, 22 Apr 2020 13:46:11 -0400

Greetings,

> 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

---

From: Alvaro Herrera <[email-masked]>
Date: Wed, 22 Apr 2020 18:59:11 -0400

> LOGIN?  That REPLICATION would override that would surely be surprising

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?

---

From: Tom Lane <[email-masked]>
Date: Wed, 22 Apr 2020 19:14:41 -0400

> 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

---

From: Stephen Frost <[email-masked]>
Date: Thu, 23 Apr 2020 07:31:55 -0400

Greetings,

> 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