Re: Privileges on PUBLICATION

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 logical
>  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) needs
>  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 filter and
> column list features. It is not just to "create another publication". Create
> publications require CREATE privilege on databases (that is *not* granted 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=# CREATE ROLE foo REPLICATION LOGIN;
> CREATE ROLE
> postgres=# \c - foo
> You are now connected to database "postgres" as user "foo".
> postgres=> 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 that
> GRANT USAGE ON PUBLICATION is just a command that will have the same effect as
> REPLICATION property [1] has right now, I would say it won't. Are you aiming a
> fine-grained access control on publisher?

The configuration I'm thinking of is multiple replicas reading data from the
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", to be
used by "subscr_foo" and "subscr_bar" subscriptions respectively. The
publications replicate the same table, but each with a different row filter.

The problem is that the admin of "db_foo" can add the "pub_bar" publication to
the "subscr_foo" subscription, and thus get the data that his "pub_foo" would
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 PUBLICATION
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.html

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