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]https://www.postgresql.org/docs/devel/logical-replication-security.html 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]https://www.postgresql.org/docs/devel/logical-replication-security.html 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/

On Tue, May 10, 2022 at 12:16 AM 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.

Also, to create a subscription (which combines multiple publications
to bypass rules), a user must be a superuser. So, isn't that a
sufficient guarantee that users shouldn't be able to bypass such
rules?

--
With Regards,
Amit Kapila.

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

Amit Kapila <amit.kapila16@gmail.com> wrote:

On Tue, May 10, 2022 at 12:16 AM 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.

Also, to create a subscription (which combines multiple publications
to bypass rules), a user must be a superuser. So, isn't that a
sufficient guarantee that users shouldn't be able to bypass such
rules?

My understanding is that the rows/columns filtering is a way for the
*publisher* to control which data is available to particular replica. From
this point of view, the publication privileges would just make the control
complete.

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

On 10.05.22 10:37, Antonin Houska wrote:

My understanding is that the rows/columns filtering is a way for the
*publisher* to control which data is available to particular replica. From
this point of view, the publication privileges would just make the control
complete.

I think privileges on publications would eventually be useful. But are
you arguing that we need them for PG15 to make the new features usable
safely?

On Tue, May 10, 2022, at 5:37 AM, Antonin Houska wrote:

My understanding is that the rows/columns filtering is a way for the
*publisher* to control which data is available to particular replica. From
this point of view, the publication privileges would just make the control
complete.

I agree. IMO it is a new feature. We already require high privilege for logical
replication. Hence, we expect the replication user to have access to all data.
Unfortunately, nobody mentioned about this requirement during the row filter /
column list development; someone could have written a patch for GRANT ... ON
PUBLICATION.

I understand your concern. Like I said in my last sentence in the previous
email: it is a fine-grained access control on the publisher. Keep in mind that
it will *only* work for non-superusers (REPLICATION attribute). It is not
exposing something that we didn't expose before. In this particular case, there
is no mechanism to prevent the subscriber to obtain data provided by the
various row filters if they know the publication names. We could probably add a
sentence to "Logical Replication > Security" section:

There is no privileges for publications. If you have multiple publications in a
database, a subscription can use all publications available.

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

Euler Taveira <euler@eulerto.com> wrote:

On Tue, May 10, 2022, at 5:37 AM, Antonin Houska wrote:

My understanding is that the rows/columns filtering is a way for the
*publisher* to control which data is available to particular replica. From
this point of view, the publication privileges would just make the control
complete.

I agree. IMO it is a new feature. We already require high privilege for logical
replication. Hence, we expect the replication user to have access to all data.
Unfortunately, nobody mentioned about this requirement during the row filter /
column list development; someone could have written a patch for GRANT ... ON
PUBLICATION.

I can try that for PG 16, unless someone is already working on it.

I understand your concern. Like I said in my last sentence in the previous
email: it is a fine-grained access control on the publisher. Keep in mind that
it will *only* work for non-superusers (REPLICATION attribute). It is not
exposing something that we didn't expose before. In this particular case, there
is no mechanism to prevent the subscriber to obtain data provided by the
various row filters if they know the publication names. We could probably add a
sentence to "Logical Replication > Security" section:

There is no privileges for publications. If you have multiple publications in a
database, a subscription can use all publications available.

Attached is my proposal. It tries to be more specific and does not mention the
absence of the privileges explicitly.

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

Peter Eisentraut <peter.eisentraut@enterprisedb.com> wrote:

On 10.05.22 10:37, Antonin Houska wrote:

My understanding is that the rows/columns filtering is a way for the
*publisher* to control which data is available to particular replica. From
this point of view, the publication privileges would just make the control
complete.

I think privileges on publications would eventually be useful. But are you
arguing that we need them for PG15 to make the new features usable safely?

I didn't think that far, but user should be aware of the problem. My proposal
of documentation is in /messages/by-id/5859.1652423797@antos

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

On Fri, May 13, 2022, at 3:36 AM, Antonin Houska wrote:

Attached is my proposal. It tries to be more specific and does not mention the
absence of the privileges explicitly.

You explained the current issue but say nothing about the limitation. This
information will trigger a question possibly in one of the MLs. IMO if you say
something like the sentence above at the end, it will make it clear why that
setup expose all data (there is no access control to publications) and
explicitly say there is a TODO here.

Additional privileges might be added to control access to table data in a
future version of <productname>PostgreSQL</productname>.

I also wouldn't use the warning tag because it fits in the same category as the
other restrictions listed in the page.

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

Antonin Houska <ah@cybertec.at> wrote:

Euler Taveira <euler@eulerto.com> wrote:

On Tue, May 10, 2022, at 5:37 AM, Antonin Houska wrote:

My understanding is that the rows/columns filtering is a way for the
*publisher* to control which data is available to particular replica. From
this point of view, the publication privileges would just make the control
complete.

I agree. IMO it is a new feature. We already require high privilege for logical
replication. Hence, we expect the replication user to have access to all data.
Unfortunately, nobody mentioned about this requirement during the row filter /
column list development; someone could have written a patch for GRANT ... ON
PUBLICATION.

I can try that for PG 16, unless someone is already working on it.

The patch is attached to this message.

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

Euler Taveira <euler@eulerto.com> wrote:

On Fri, May 13, 2022, at 3:36 AM, Antonin Houska wrote:

Attached is my proposal. It tries to be more specific and does not mention the
absence of the privileges explicitly.

You explained the current issue but say nothing about the limitation. This
information will trigger a question possibly in one of the MLs. IMO if you say
something like the sentence above at the end, it will make it clear why that
setup expose all data (there is no access control to publications) and
explicitly say there is a TODO here.

Additional privileges might be added to control access to table data in a
future version of <productname>PostgreSQL</productname>.

I thought it sound too negative if absence of some feature was mentioned
explicitly. However it makes sense to be clear from technical point of view.

I also wouldn't use the warning tag because it fits in the same category as the
other restrictions listed in the page.

ok, please see the next version.

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

On Wed, May 18, 2022, at 6:16 AM, Antonin Houska wrote:

The patch is attached to this message.

Great. Add it to the next CF. I'll review it when I have some spare time.

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

On Wed, May 18, 2022, at 6:44 AM, Antonin Houska wrote:

ok, please see the next version.

The new paragraph looks good to me. I'm not sure if the CREATE PUBLICATION is
the right place to provide such information. As I suggested in a previous email
[1]: /messages/by-id/d96103fe-99e2-4119-bd76-952d326b7539@www.fastmail.com

[1]: /messages/by-id/d96103fe-99e2-4119-bd76-952d326b7539@www.fastmail.com

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

Euler Taveira <euler@eulerto.com> wrote:

On Wed, May 18, 2022, at 6:16 AM, Antonin Houska wrote:

The patch is attached to this message.

Great. Add it to the next CF. I'll review it when I have some spare time.

https://commitfest.postgresql.org/38/3641/

Thanks!

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

Euler Taveira <euler@eulerto.com> wrote:

--eeab359ad6094efd84562cddd7fb9e89
Content-Type: text/plain

On Wed, May 18, 2022, at 6:44 AM, Antonin Houska wrote:

ok, please see the next version.

The new paragraph looks good to me. I'm not sure if the CREATE PUBLICATION is
the right place to provide such information. As I suggested in a previous email
[1], you could add it to "Logical Replication > Security".

ok, I missed that. The next version moves the text there.

[1] /messages/by-id/d96103fe-99e2-4119-bd76-952d326b7539@www.fastmail.com

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

On 20.06.22 16:01, Antonin Houska wrote:

On Wed, May 18, 2022, at 6:44 AM, Antonin Houska wrote:

ok, please see the next version.

The new paragraph looks good to me. I'm not sure if the CREATE PUBLICATION is
the right place to provide such information. As I suggested in a previous email
[1], you could add it to "Logical Replication > Security".

ok, I missed that. The next version moves the text there.

I have committed this patch that adds the additional documentation.

The CF entry is about privileges on publications. Please rebase that
patch and repost it so that the CF app and the CF bot are up to date.

Peter Eisentraut <peter.eisentraut@enterprisedb.com> wrote:

The CF entry is about privileges on publications. Please rebase that patch
and repost it so that the CF app and the CF bot are up to date.

The rebased patch (with regression tests added) is attached here.

There's still one design issue that I haven't mentioned yet: if the USAGE
privilege on a publication is revoked after the synchronization phase
completed, the missing privilege on a publication causes ERROR in the output
plugin. If the privilege is then granted, the error does not disappear because
the same (historical) snapshot we use to decode the failed data change again
is also used to check the privileges in the catalog, so the output plugin does
not see that the privilege has already been granted.

The only solution seems to be to drop the publication from the subscription
and add it again, or to drop and re-create the whole subscription. I haven't
added a note about this problem to the documentation yet, in case someone has
better idea how to approach the problem.

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

On 03.11.22 01:43, Antonin Houska wrote:

Peter Eisentraut <peter.eisentraut@enterprisedb.com> wrote:

The CF entry is about privileges on publications. Please rebase that patch
and repost it so that the CF app and the CF bot are up to date.

The rebased patch (with regression tests added) is attached here.

Some preliminary discussion:

What is the upgrade strategy? I suppose the options are either that
publications have a default acl that makes them publicly accessible,
thus preserving the existing behavior by default, or pg_dump would need
to create additional GRANT statements when upgrading from pre-PG16. I
don't see anything like either of these mentioned in the patch. What is
your plan?

You might be interested in this patch, which relates to yours:
https://commitfest.postgresql.org/40/3955/

Looking at your patch, I would also like to find a way to refactor away
the ExecGrant_Publication() function. I'll think about that.

I think you should add some tests under src/test/regress/ for the new
GRANT and REVOKE statements, just to have some basic coverage that it
works. sql/publication.sql would be appropriate, I think.

On Thu, Nov 3, 2022 at 11:12 AM Antonin Houska <ah@cybertec.at> wrote:

Peter Eisentraut <peter.eisentraut@enterprisedb.com> wrote:

The CF entry is about privileges on publications. Please rebase that patch
and repost it so that the CF app and the CF bot are up to date.

The rebased patch (with regression tests added) is attached here.

There's still one design issue that I haven't mentioned yet: if the USAGE
privilege on a publication is revoked after the synchronization phase
completed, the missing privilege on a publication causes ERROR in the output
plugin. If the privilege is then granted, the error does not disappear because
the same (historical) snapshot we use to decode the failed data change again
is also used to check the privileges in the catalog, so the output plugin does
not see that the privilege has already been granted.

We have a similar problem even when publication is dropped/created.
The replication won't be able to proceed.

The only solution seems to be to drop the publication from the subscription
and add it again, or to drop and re-create the whole subscription. I haven't
added a note about this problem to the documentation yet, in case someone has
better idea how to approach the problem.

I think one possibility is that the user advances the slot used in
replication by using pg_replication_slot_advance() at or after the
location where the privilege is granted. Some other ideas have been
discussed in the thread [1]/messages/by-id/CAHut+PvMbCsL8PAz1Qc6LNoL0Ag0y3YJtPVJ8V0xVXJOPb+0xw@mail.gmail.com, in particular, see email [2]/messages/by-id/CAA4eK1JTwOAniPua04o2EcOXfzRa8ANax=3bpx4H-8dH7M2p=A@mail.gmail.com and
discussion after that but we didn't reach any conclusion.

[1]: /messages/by-id/CAHut+PvMbCsL8PAz1Qc6LNoL0Ag0y3YJtPVJ8V0xVXJOPb+0xw@mail.gmail.com
[2]: /messages/by-id/CAA4eK1JTwOAniPua04o2EcOXfzRa8ANax=3bpx4H-8dH7M2p=A@mail.gmail.com

--
With Regards,
Amit Kapila.