aiven / aiven-extras Goto Github PK

Hello,
I am asking for help with the installation of aiven-extras extension to database which is running in docker container. I am trying to dump some data from the staging database (running on digital ocean) to my local db which is running using Postgres docker image
Apparently, aiven-extras extension is installed on the staging database (which is running on digital ocean) and this extension is not a part of Postgres docker image.
Error thrown during docker build:

2023-08-01 05:24:14.374 UTC [51] ERROR:  extension "aiven_extras" is not available
2023-08-01 05:24:14.374 UTC [51] DETAIL:  Could not open extension control file "/usr/local/share/postgresql/extension/aiven_extras.control": No such file or directory.
2023-08-01 05:24:14.374 UTC [51] HINT:  The extension must first be installed on the system where PostgreSQL is running.

This error is triggered by one of these lines in schema.sql file

--
-- PostgreSQL database dump
--

-- Dumped from database version 15.3
-- Dumped by pg_dump version 15.3

-- Started on 2023-07-31 19:43:44 UTC

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- TOC entry 6 (class 2615 OID 16985)
-- Name: aiven_extras; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA aiven_extras;


ALTER SCHEMA aiven_extras OWNER TO postgres;

--
-- TOC entry 2 (class 3079 OID 16986)
-- Name: aiven_extras; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS aiven_extras WITH SCHEMA aiven_extras;


--
-- TOC entry 4611 (class 0 OID 0)
-- Dependencies: 2
-- Name: EXTENSION aiven_extras; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION aiven_extras IS 'aiven_extras';

PS: I generated the schema.sql file by this line in my Dockerfile

RUN PGPASSWORD=${PROD_DB_PASSWORD} pg_dump --verbose --no-privileges -U doadmin -h ****.b.db.ondigitalocean.com -p 25060 -s core > /docker-entrypoint-initdb.d/schema.sql

Not really sure if this is an issue or not. But I had created publication on our old DB in AWS RDS, created a subscription on a new Aiven DB, made the switch to Aiven, then terminated the RDS before dropping the subscription on Aiven.

Now the subscription fails because the old server doesn't exists and I can't drop the subscription because the Aiven function can't drop the slot on the old server.

So on executing SELECT * FROM aiven_extras.pg_drop_subscription('subscription');

ERROR:  could not establish connection
DETAIL:  could not translate host name "data.cps7haw5lzpy.eu-central-1.rds.amazonaws.com" to address: Name or service not known
CONTEXT:  SQL statement "SELECT res FROM public.dblink('host=data.cps7haw5lzpy.eu-central-1.rds.amazonaws.com password=xxx port=5432 dbname=marketing user=xxx', 'SELECT TRUE FROM pg_catalog.pg_replication_slots WHERE slot_name = ''slot''') AS d(res BOOLEAN)"
PL/pgSQL function aiven_extras.dblink_slot_create_or_drop(text,text,text) line 14 at EXECUTE
SQL statement "SELECT aiven_extras.dblink_slot_create_or_drop(l_subconninfo, l_slot_name, 'drop')"
PL/pgSQL function aiven_extras.pg_drop_subscription(text) line 14 at PERFORM
SQL state: 08001

Anything I can do to drop the subscription while the old server is down?

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.

It would be easier to manage this extension locally if it was published on https://pgxn.org/. Would be great to have a local environment similar to one of on production/staging that we get with Aiven.

We currently support ALTER SUBSCRIPTION ... REFRESH... (source) via a secutiry definer function. Unfortunately, pg14 changed this to require that this is run outside a transaction block: postgres/postgres@ce0fdbf#diff-d729c809ce03180c391eaba8a%5B%E2%80%A6%5Da292ad4daa2d06f16ce43ba1deR985 This check also prevents it to run in a function: https://github.com/postgres/postgres/blob/7b5d4c29ed0262e537026cb3a85161d6cf98abcc/src/backend/access/transam/xact.c#L3389-L3425
So If we still want to support our user to be able to run this on their own, we have to find a way to run this.

Possible options:

1. Allow pg_cron to superuser queries and use that (by submitting a cron job with SECURITY DEFINER) -> would make this functionality depend on pg_cron 😞 and leave a potential security problem in pg_cron (if someone can submit a job and get it to be run as superuser...) 😞
2. Go the pgextwlist way and hook into the SQL execution and intercept the refresh call and allow it for certain users and then execute it with superuser privs -> means changing the aiven-extra package to do C code; given the nature of this, also needs some security review and stuff...
3. Patch pg itself to ignore this check if it is run as superuser (given that only we run as superuser, we can assume/make sure that we would never run it in a transaction...)
4. (a variant of 3+4) Write our own c level function which more or less does the same as ALTER SUBSCRIPTION ... REFRESH just without the check for the function -> we basically take care to do run this outside a transaction and the code takes care to not run more stuff in the function. Needs aiven-extras to support c code...
5. USe dblink to connect to ourself (via the cert) and run the command that way.

Hello,

Could you add some documentation around using auto_explain with Aiven's Postgres service? Searching help.aiven.io I only found https://help.aiven.io/en/articles/489557-postgresql-superuser-access which links here, and while this extension does seem to define some helper functions, they are not documented as far as I can tell.

Hello. I'm trying to get data without using super user. I stumbled upon your solution, added your extension created a publication as indicated in the instructions, but after running I get the error io.debezium.jdbc.JdbcConnectionException: ERROR: permission denied for database project

name=debezium-test-01
tasks.max=1
plugin.name=pgoutput
publication.name=dbz_test
connector.class=io.debezium.connector.postgresql.PostgresConnector
database.hostname=10.10.10.172
database.port=5432
database.user=debezium
database.password=debezium
database.dbname=project
database.server.name=test
table.whitelist=public.test
heartbeat.interval.ms=5000
session.timeout.ms=120000
publication.autocreate.mode=filtered
table.include.list=public.test

All read and access to the use of this database is granted to the user