Postgresql – PowerDNS pgSQL Configure Different Schema

postgresqlpowerdns

I've been using PowerDNS for a few years now and finally decided to switch over to pgSQL instead of mySQL. I've been researching this question but I haven't found anything solid so I'm assuming it's probably not possible.

I'm curious if it's possible to install and run PowerDNS from within a schema called pdns instead of a database called pdns? I'm unable to find any option to tell PowerDNS to look in X database and Y schema.

For example I have a database called example and in this database I have two schema's called mydb and pdns. I'd like to install the PowerDNS table structure in the pdns schema and then run the PowerDNS service from that schema. It seems to work just fine when the tables are in the public schema.

Best Answer

This is most easily accomplished by changing the role's search path.

Create a new schema pdns within the target database. Create all the tables you need within that schema. When you create the pdns role (user), set their search path to search your pdns schema before public. Grant access on the pdns schema to the pdns role.

create schema pdns;
# create your tables as pdns.tablename
create role pdns with password 'yourpassword';
alter role pdns set search_path = "$user",pdns,public;
grant usage on schema pdns to pdns;
grant select,insert,update,delete on all tables in schema pdns to pdns;
grant select,update on all sequences in schema pdns to pdns;

If you revoke any access pdns might have to other tables, you shouldn't need to be too worried about separation. That should about do it.

Edit: A note; if you're new to postgres, you might not know that while you can grant access to all tables in a schema, if you add or re-add a table you must grant access to the new table (by re-running the grant command). That is why the grants in this answer are last.

Related Topic