[Pdns-users] Intercepting domainmetadata with Postgresql

[Klaus Darilion]

On 21.03.2014 15:31, Klaus Darilion wrote:
> 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)

The name of the query is: gpgsql-get-domain-metadata-query. So the full 
config line is:


gpgsql-get-domain-metadata-query=select content from 
intercept_domainmetadata(%s,%s)