[Pdns-users] Intercepting domainmetadata with Postgresql

[Klaus Darilion]

Hi!

I just wanted to share my results in intercepting the domainmetadata 
query. I wanted to have "ALSO-NOTIFY" not only "per-zone", but also 
"per-customer". Therefore I wrote a small table function which seems to 
work nicely.

Maybe someone can use such a feature too.

regards
Klaus

CREATE OR REPLACE FUNCTION intercept_domainmetadata(domain text, kind 
text) RETURNS TABLE (content text) AS $$
BEGIN
   IF ( (kind = 'ALSO-NOTIFY') OR (kind = 'ALLOW-AXFR-FROM') ) THEN
     RETURN QUERY SELECT unnest(string_to_array(secondaries, ','))
                  FROM domains,accounts
                  WHERE soap_domains.name=domain AND 
soap_domains.account=accounts.username AND accounts.secondaries IS NOT NULL;
   END IF;
   RETURN QUERY SELECT domainmetadata.content
                FROM domains, domainmetadata
                WHERE domainmetadata.domain_id=domains.id AND 
domains.name=domain AND domainmetadata.kind=kind;
END;
$$ LANGUAGE plpgsql;

(Table domains were extended with column "account", which is a foreign 
key to "username" in the accounts table. The accounts table has a column 
"secondaries", which allows to specify the to be notified secondaries as 
coma separated list, e.g: 1.2.3.4,1::F,3.3.3.3

Then change the PowerDNS query from
select content from domains, domainmetadata where 
domainmetadata.domain_id=domains.id and name='%s' and 
domainmetadata.kind='%s'
--->
select content from intercept_domainmetadata(%s,%s)