Re: Privileges on PUBLICATION

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?

[1] https://www.postgresql.org/docs/devel/logical-replication-security.html

--
Euler Taveira
EDB https://www.enterprisedb.com/