Not able to disable/drop subscriptions created by aiven-db-migrate

[teksuo]

Hi there,

I was about to post this on the aiven-db-migrate project, but probably makes more sense here, I'm not sure:

Description of the issue

I am trying to reset a logical replication process from the target DB (Aiven) using aiven_extras and avnadminuser, but when I use the name (SELECT subname FROM aiven_extras.pg_list_all_subscriptions();) of the subscription in the aiven_extras.pg_drop_subscription() function, I get (redacted):

ERROR:  subscription "<subscription_name>" does not exist
CONTEXT:  SQL statement "ALTER SUBSCRIPTION <subscription_name> DISABLE"
PL/pgSQL function pg_drop_subscription(text,boolean) line 13 at EXECUTE 

SQL state: 42704

When trying to manually disable the subscription I get same error:

ERROR:  subscription "<subscription_name>" does not exist 

SQL state: 42704

And when I try to query the subscriptions, I get:

ERROR:  permission denied for table pg_subscription 

SQL state: 42501

Issue-Type (put a x sign in the square brackets)

• bug report
• feature request
• Documentation improvement
• Other

Checklist

• Running latest version of code.
• This issue has not been reported earlier.

Your environment

• OS: Alpine 3.19
• Python Version: 3.12.2
• Release tag/commit of the code: 0.1.3 (aiven-db-migrate)

Expected behaviour

the subscription should get deleted along with the publications and slots from the source database

Actual behaviour

the command fails

Steps to reproduce

Initiate a logical replication process and try to delete the subscription afterwards.

Any extra info ( for eg. code snippet to reproduce, logs, screenshots etc. )

In my case source DB is an self-managed Postgres with sudo access running on a self-managed Kubernetes cluster, target DB is an Aiven DB (Postgresql 15.5 on both) which is accessed by the aiven-db-migrate tool from an AKS cluster using VPC peering.