From: Antonin Houska <ah@cybertec.at>
To: "Euler Taveira" <euler@eulerto.com>
cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Privileges on PUBLICATION
In-reply-to: <8a00e0e1-dc4e-422c-9dcf-664b16b18625@www.fastmail.com>
References: <20330.1652105397@antos>
 <8a00e0e1-dc4e-422c-9dcf-664b16b18625@www.fastmail.com>
Comments: In-reply-to "Euler Taveira" <euler@eulerto.com>
   message dated "Mon, 09 May 2022 15:44:45 -0300."
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2387.1652169778.1@antos>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 10 May 2022 10:02:58 +0200
Message-ID: <2388.1652169778@antos>
Archived-At: <https://www.postgresql.org/message-id/2388.1652169778%40antos>
Precedence: bulk

Euler Taveira <euler@eulerto.com> wrote:

> On Mon, May 9, 2022, at 11:09 AM, Antonin Houska wrote:
> =

>  Now that the user can specify rows and columns to be omitted from the l=
ogical
>  replication [1], I suppose hiding rows and columns from the subscriber =
is an
>  important use case. However, since the subscription connection user (i.=
e. the
>  user specified in the CREATE SUBSCRIPTION ... CONNECTION ... command) n=
eeds
>  SELECT permission on the replicated table (on the publication side), he=
 can
>  just use another publication (which has different filters or no filters=
 at
>  all) to get the supposedly-hidden data replicated.
> =

> The required privileges were not relaxed on publisher after the row filt=
er and =

> column list features. It is not just to "create another publication". Cr=
eate
> publications require CREATE privilege on databases (that is *not* grante=
d to
> PUBLIC).If you have an untrusted user that could bypass your rules about=
 hidden
> data, it is better to review your user privileges.
> =

> postgres=3D# CREATE ROLE foo REPLICATION LOGIN;
> CREATE ROLE
> postgres=3D# \c - foo
> You are now connected to database "postgres" as user "foo".
> postgres=3D> CREATE PUBLICATION pub1;
> ERROR:  permission denied for database postgres
> =

> The documentation [1] says
> =

> "The role used for the replication connection must have the REPLICATION
> attribute (or be a superuser)."
> =

> You can use role foo for the replication connection but role foo couldn'=
t be a
> superuser. In this case, even if role foo open a connection to database
> postgres, a publication cannot be created due to lack of privileges.
> =

>  Don't we need privileges on publication (e.g GRANT USAGE ON PUBLICATION=
 ...)
>  now?
> =

> Maybe. We rely on CREATE privilege on databases right now. If you say th=
at
> GRANT USAGE ON PUBLICATION is just a command that will have the same eff=
ect as
> REPLICATION property [1] has right now, I would say it won't. Are you ai=
ming a
> fine-grained access control on publisher?

The configuration I'm thinking of is multiple replicas reading data from t=
he
same master.

For example, consider "foo" and "bar" roles, used by "subscr_foo" and
"subscr_bar" subscriptions respectively. (Therefore, both roles need the
REPLICATION option.) The subscriptions "subscr_foo" and "subscr_bar" are
located in "db_foo" and "db_bar" databases respectively.

On the master side, there are two publications: "pub_foo" and "pub_bar", t=
o be
used by "subscr_foo" and "subscr_bar" subscriptions respectively. The
publications replicate the same table, but each with a different row filte=
r.

The problem is that the admin of "db_foo" can add the "pub_bar" publicatio=
n to
the "subscr_foo" subscription, and thus get the data that his "pub_foo" wo=
uld
filter out. Likewise, the admin of "db_bar" can "steal" the data from
"pub_foo" by adding that publication to "subscr_bar".

In this case, the existing publications are misused, so the CREATE PUBLICA=
TION
privileges do not help. Since the REPLICATION option of a role is
cluster-wide, but I need specific roles to be restricted to specific
publications, it can actually be called fine-grained access control as you
say.


> [1] https://www.postgresql.org/docs/devel/logical-replication-security.h=
tml

-- =

Antonin Houska
Web: https://www.cybertec-postgresql.com