pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Amit Kapila <amit.kapila16@gmail.com>
To: Jeff Davis <pgsql@j-davis.com>
Cc: pgsql-bugs@postgresql.org, Robert Haas <robertmhaas@gmail.com>
Subject: Re: [16+] subscription can end up in inconsistent state
Date: Mon, 11 Sep 2023 08:59:00 +0530
Message-ID: <CAA4eK1LJEMfdxpV7xDhR3qOFoSt3LuKbbu5kRMBfYJ2imT8Few@mail.gmail.com> (raw)
In-Reply-To: <e5892973ae2a80a1a3e0266806640dae3c428100.camel@j-davis.com>
References: <e5892973ae2a80a1a3e0266806640dae3c428100.camel@j-davis.com>

On Sun, Sep 10, 2023 at 9:15 PM Jeff Davis <pgsql@j-davis.com> wrote:
>
> [ Moved from -security, where it was initially posted as a precaution.
> It was determined that the normal bug process is the right way to fix
> this. ]
>
> If I understand correctly, the security requirements for checking a
> non-superuser's connection string are that (a) the connection string
> itself specifies a password, thereby ensuring it won't come from a
> PGPASSFILE; and (b) checking PQconnectionUsedPassword() after the
> connection to ensure that the password was actually used for
> authentication, rather than some other method.
>
> If subpasswordrequired=true, that causes (b) to be consistently checked
> for all connections.
>
> The checking of (a) is more complicated -- it's checked at CREATE/ALTER
> time, but only if the user is not a superuser. If the superuser gives
> the subscription to a non-superuser, or if the subowner loses their
> superuser status, then the subscription is in a weird state:
> subpasswordrequired may be true even if subconninfo doesn't have a
> password. That means that (a) is not being checked, and (b) is being
> checked.
>
> Repro (16+):
>
>   Publisher:
>    CREATE USER repl REPLICATION PASSWORD 'secret';
>    CREATE TABLE t(i INT);
>    INSERT INTO t VALUES(1);
>    GRANT SELECT ON t TO repl;
>    CREATE PUBLICATION p1 FOR TABLE t;
>
>   Subscriber (has a PGPASSFILE for user "repl"):
>    CREATE USER u1;
>    CREATE TABLE t(i INT);
>    ALTER TABLE t OWNER TO u1;
>    -- no password specified; relies on passfile
>    CREATE SUBSCRIPTION s1
>      CONNECTION 'dbname=postgres user=repl'
>      PUBLICATION p1 WITH (enabled=false);
>    ALTER SUBSCRIPTION s1 OWNER TO u1;
>    SELECT COUNT(*) FROM t; -- 0
>    \c - u1
>    -- still using passfile
>    ALTER SUBSCRIPTION s1 ENABLE;
>    SELECT pg_sleep(2);
>    SELECT COUNT(*) FROM t; -- 1
>    ALTER SUBSCRIPTION s1 REFRESH PUBLICATION;
>
> It's also possible to get into this state by the superuser modifying a
> non-superuser's subscription.
>
> The purpose of the "password_required" option is to handle the case
> where a subscription was formerly owned by a superuser, or has been
> modified by a superuser. The docs say:
>
>   "[password_required] ... This setting is ignored when the
> subscription is owned by a superuser... Only superusers can set this
> value to false."
>   https://www.postgresql.org/docs/16/sql-createsubscription.html
>
> So a superuser changing the owner or modifying another user's
> subscription is a supported operation, and not just a superuser doing
> the wrong thing. (Perhaps the docs should be more clear on this point?)
>
> We could address this either by:
>
>  * maintaining the invariant that if subpasswordrequired=true, then
> subconninfo specifies a password; or
>  * calling walrcv_check_conninfo() before each connection
>

Can we think of calling walrcv_check_conninfo() when the following
check is true (MySubscription->passwordrequired &&
!superuser_arg(MySubscription->owner))? IIUC this has to be done for
both apply_worker and tablesync_worker.

-- 
With Regards,
Amit Kapila.





view thread (25+ messages)  latest in thread

Message-ID: <CAA4eK1LJEMfdxpV7xDhR3qOFoSt3LuKbbu5kRMBfYJ2imT8Few@mail.gmail.com>
Permalink:  ../CAA4eK1LJEMfdxpV7xDhR3qOFoSt3LuKbbu5kRMBfYJ2imT8Few@mail.gmail.com/
Also on:    postgresql.org/message-id/CAA4eK1LJEMfdxpV7xDhR3qOFoSt3LuKbbu5kRMBfYJ2imT8Few@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: amit.kapila16@gmail.com, pgsql@j-davis.com, robertmhaas@gmail.com
  Subject: Re: [16+] subscription can end up in inconsistent state
  In-Reply-To: <CAA4eK1LJEMfdxpV7xDhR3qOFoSt3LuKbbu5kRMBfYJ2imT8Few@mail.gmail.com>
  References: <e5892973ae2a80a1a3e0266806640dae3c428100.camel@j-davis.com> <CAA4eK1LJEMfdxpV7xDhR3qOFoSt3LuKbbu5kRMBfYJ2imT8Few@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox