CloudQuery postgres source CDC setup error must be owner of publication

CloudQuery Plugins
1

Hello team,

I am new to CloudQuery and I am attempting to set up a PostgreSQL source with CDC unsuccessfully. I am getting the error:

failed to sync unmanaged client: failed to alter publication: ERROR: must be owner of publication

I have granted the said user replication role and select permission at the schema level. I further granted him the create right for the said database. I am using Managed Database, Azure Flexible PostgreSQL.

Any help would be appreciated!

2

Hi Kevin :wave:, welcome to CloudQuery.

From the error message, it definitely appears as though some permissions are still not quite correct. To help troubleshoot this, I would like to point you to our CDC configuration documentation and, for Azure Flexible Postgres, the Microsoft docs on logical replication and logical decoding.

3

A more directed question I would like to ask is, what are the exact permissions required for the said users?

4

I’ve not tried with an Azure Postgres instance, but the required privileges seem to be covered by the Microsoft documentation here: Microsoft Documentation

Have you tried following the steps there?

5

@current-polecat, would it be possible for you to try manually creating a publication on a table using the same user as CloudQuery? For example:

CREATE PUBLICATION <publication_name> FOR TABLE <table_name>;

Please report the response from Postgres.

6

@martin Here is the response after my attempt:

CREATE PUBLICATION publication FOR TABLE account_emailaddress;
ERROR: must be owner of table account_emailaddress

This was the error I got from the CloudQuery side:

failed to sync v3 source dev-postgres: unexpected error from sync client receive: rpc error: code = Unknown desc = failed to sync records: failed to sync unmanaged client: failed to alter publication: ERROR: must be owner of publication testing (SQLSTATE 42501)

In terms of permissions and database configurations, I have done as per the shared Azure documentation.

7

So my interpretation of the error messages is that in the create publication case (e.g. must be owner of table account_emailaddress), for this to succeed, the user executing the command must also be the owner of the table you are associating a publication with.

In the case of the alter publication (must be owner of publication testing), the CloudQuery user is attempting to alter a publication it doesn’t own. In this case, could the publication have been created manually?

8

No, I do not have any publications present in my database.
And also on the permissions, I do not wish to grant CloudQuery any privileges apart from read rights for my tables. From what I am seeing, it seems that it wants owner rights.

9

For CDC to work, CloudQuery needs to be able to create a publication and create replication slots within the publication.

To create a publication, the invoking user must have the CREATE privilege for the current database.
To be able to create a subscription, you must have the privileges of the pg_create_subscription role, as well as CREATE privileges on the current database.

10

Also, according to the Postgres Create Publication docs (note section), you have to be a table owner to add it to a publication:

To add a table to a publication, the invoking user must have ownership rights on the table.