public inbox for [email protected]
help / color / mirror / Atom feedFrom: Rui Zhao <[email protected]>
To: Akshay Joshi <[email protected]>
Cc: solai v <[email protected]>
Cc: Ilmar Y <[email protected]>
Cc: [email protected]
Subject: Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement
Date: Thu, 9 Jul 2026 00:58:10 +0800
Message-ID: <CAHWVJhEpUPQkR0rkeTz88Z06aLxfxrv8-OiAt6Tmhd2q+HTBrA@mail.gmail.com> (raw)
In-Reply-To: <CANxoLDfUeoh+X1AeazXAQjYL312nTa6bY+=iPNJjmN4bXwSKWw@mail.gmail.com>
References: <CANxoLDdJsRJqnjMXV3yjsk07Z5iRWxG-c2hZJC7bAKqf8ZXj_A@mail.gmail.com>
<CANxoLDffrZGRTGpW_sPQ-hPEYs0hgjaFgJQh3PJFpPu5Zsbgvg@mail.gmail.com>
<177997571870.313758.10720313850275742354.pgcf@coridan.postgresql.org>
<CANxoLDe7xiCWY-UEmzoK_uQdKh3PPNcGXJ3qdzFy3c0o_F+P_Q@mail.gmail.com>
<SY7PR01MB1092118DD149062E230159D89B6162@SY7PR01MB10921.ausprd01.prod.outlook.com>
<CAF0whuefRgV3_xgMhPoKaid1LN34CTLhTU-r5dgfFyEGVAFrPg@mail.gmail.com>
<CANxoLDfBQn+q32DKo5yvb63eD+965umMi8zxqqV8GcsxdN6Trg@mail.gmail.com>
<CANxoLDf80gXFLSqWM7d-idA=Grot49HsmP9YXZw49nCFe+Mi+g@mail.gmail.com>
<CANxoLDeqR6wVCoXjsEWv4M_mhng-GCtt1KxFdfM-8PKwushUCA@mail.gmail.com>
<CANxoLDcQyd=ojcmLCmU5zFT8j475AbXHpjSWHoVP4a5-vdY3Jw@mail.gmail.com>
<CAF0whue4v=bH8beH0sBxu9UoFKRcZHTW-zH0qeVEt0jW2x7JiQ@mail.gmail.com>
<CANxoLDd+Pfi=Do-+1ECG-eZg4=DXfxuUK+yX1KhjAMnQzyidwQ@mail.gmail.com>
<CAF0whuc22Tdf-V=1g21jgmKG0YiGk3ZhLoO7Ttog6tD5P91a2Q@mail.gmail.com>
<CANxoLDfUeoh+X1AeazXAQjYL312nTa6bY+=iPNJjmN4bXwSKWw@mail.gmail.com>
Hi Akshay,
I tested v15 on current master (57f93af36f): builds, make check passes, and
basic reconstruction, pretty mode, NULL handling, and default-clause omission
all look good. Two things.
1) Back in the v3/v4 discussion you decided the ON <table> name should always
be schema-qualified for safety (the pg_get_triggerdef_worker thread with
jian he), and that pretty here only controls formatting, not schema (Phil's
point). That reasoning didn't reach the USING / WITH CHECK expressions,
though: object references inside them are still qualified only by the caller's
search_path, so they can lose their schema and rebind to a different object
when the DDL is replayed elsewhere:
CREATE FUNCTION s1.f(int) RETURNS bool LANGUAGE sql AS 'SELECT $1 > 0';
CREATE FUNCTION s2.f(int) RETURNS bool LANGUAGE sql AS 'SELECT $1 < 0';
CREATE POLICY pf ON t2 USING (s1.f(a));
SET search_path = public, s1;
SELECT ddl FROM pg_get_policy_ddl('t2', 'pf') AS ddl;
-- CREATE POLICY pf ON public.t2 USING (f(a)); -- s1. dropped
SET search_path = public, s2;
CREATE POLICY pf ON t2 USING (f(a)); -- now s2.f,
opposite meaning
So within one statement the ON clause is always qualified but the expression
body isn't -- and as Phil noted, the pretty flag can't fix this, since
pg_get_expr qualifies by search_path visibility regardless of pretty.
Worth settling the contract, given where this function sits. The old
pg_get_viewdef / ruledef / indexdef functions are search_path-aware and leave
qualification to the caller (pg_dump sets search_path empty around them so the
output is portable). The new pg_get_*_ddl functions committed so far (role,
database, tablespace) are on global, schemaless objects, so the question never
came up -- pg_get_policy_ddl is the first of the family whose output embeds
schema-qualifiable references. Your own pg_get_table_ddl patch already hit
this and deparses under a controlled search_path (narrowed to pg_catalog), so
the most consistent fix is to do the same here (NewGUCNestLevel + set_config);
an empty search_path already yields the fully-qualified s1.f(a). If instead
the intent is to follow the caller's search_path, that's fine too, but it
should be documented (and SET search_path = '' noted as the way to get
portable DDL).
2) The doc calls the second parameter policy_name, but the actual argument is
policyname, so the documented named-argument call fails:
SELECT * FROM pg_get_policy_ddl("table" => 't'::regclass,
policy_name => 'p_all');
ERROR: function pg_get_policy_ddl(table => regclass, policy_name
=> unknown) does not exist
Regards,
Rui
view thread (44+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement
In-Reply-To: <CAHWVJhEpUPQkR0rkeTz88Z06aLxfxrv8-OiAt6Tmhd2q+HTBrA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox