From: Antonin Houska <ah@cybertec.at>
To: Euler Taveira <euler@eulerto.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Privileges on PUBLICATION
Date: Tue, 10 May 2022 10:02:58 +0200
Message-ID: <2388.1652169778@antos> (raw)
In-Reply-To: <8a00e0e1-dc4e-422c-9dcf-664b16b18625@www.fastmail.com>
References: <20330.1652105397@antos>
<8a00e0e1-dc4e-422c-9dcf-664b16b18625@www.fastmail.com>
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
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 using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: ah@cybertec.at, euler@eulerto.com, pgsql-hackers@lists.postgresql.org
Subject: Re: Privileges on PUBLICATION
In-Reply-To: <2388.1652169778@antos>
* 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