r53db is a Foreign Data Wrapper for PostgreSQL that enables you to access AWS Route53 Database Zones like SQL tables.
r53db supports reading from (SELECT) and writing to (INSERT/UPDATE/DELETE) your Route53
Database for simple records and ALIAS Targets.
More advanced Route53 Database features like weights, geo-location etc. are not supported... yet!
For several examples, see EXAMPLES.md.
Many folks prefer video, and I like asciinema, so here we go:
You'll need:
- An AWS environment (working
~/.aws/configor assigned Instance Role etc.) with the necessary permissions (see below) - Golang 1.13+
- AWS Go SDK (v1)
- PostgreSQL 9.6+, including
pg_configand development files (headers)
r53db has been successfully tested on:
- FreeBSD 12.1
- Amazon Linux 2
- Debian Buster
- Ubuntu Focal 20.04
Other OS should work as well, if they meet the above list.
You'll be happy to know that it's working on 64-bit ARM as well, so you can use those nice AWS Graviton instances!
The following permissions are required:
- ListHostedZones
- ListResourceRecordSets
- ChangeResourceRecordSets (you can leave this out if you want to start with read-only access to Route53)
You can create an IAM Policy using the following document:
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"route53:ListHostedZones",
"route53:ListResourceRecordSets",
"route53:ChangeResourceRecordSets"
],
"Resource": [
"*"
]
}
]
}
Then simply attach this IAM Policy to your IAM Role / User / Instance Role.
When all prerequisites have been met, the general procedure is just:
make clean all install.
PostgreSQL installation directory and header files will be located using pg_config. If you have multiple
PostgreSQL installations, you can point to the right one by setting $PG_CONFIG.
After installation, use the psql client to connect to your database. Install the extension and hook
it up to your Route53 Database:
CREATE EXTENSION r53db;
CREATE SERVER route53 FOREIGN DATA WRAPPER r53db;
CREATE SCHEMA route53;
IMPORT FOREIGN SCHEMA dummy FROM SERVER route53 INTO route53;
The schema name route53 is just a suggestion, you can change it if you like (or don't create a separate
schema and import into the default public schema).
The IMPORT FOREIGN SCHEMA command will create a Foreign Table for each Hosted Zone in your Route53 Database.
DNS Names are adjusted to be easy-to-use PostgreSQL table names (e.g. the Hosted Zone example.com would
become example_com).
Run \dE+ route53. (note the terminal .) afterwards to verify that the foreign tables have been added.
Some hints for specific OS.
Usually your PostgreSQL will already be there, of course. The installation for PostgreSQL is shown anyway, commented-out, as a reference for testing and possibly to locate the package containing the header files.
Afterwards continue with the in-database installation (see above).
pkg install go git-lite gettext
#pkg install postgresql12-server # for FreeBSD's PostgreSQL package
go get github.com/aws/aws-sdk-go
git clone https://github.com/apparentorder/r53db.git
cd r53db
make clean all install
yum install golang git make
#amazon-linux-extras install postgresql11 # for AWS-provided PostgreSQL
#yum install libpq-devel postgresql-server{,-devel} # for AWS-provided PostgreSQL
go get github.com/aws/aws-sdk-go
git clone https://github.com/apparentorder/r53db.git
cd r53db
make clean all install
Adjust the installation of -devel package names when using the official PostgreSQL repositories.
apt-get install make golang-1.14 git
#apt-get install postgresql-11 postgresql-server-dev-11 # for Debian-provided PostgreSQL
PATH=/usr/lib/go-1.14/bin:$PATH
go get github.com/aws/aws-sdk-go
git clone https://github.com/apparentorder/r53db.git
cd r53db
make clean all install
Adjust the installation of -dev package names when using the official PostgreSQL repositories.
apt-get install make golang git
#apt-get install postgresql-12 postgresql-server-dev-12 # for Ubuntu-provided PostgreSQL
go get github.com/aws/aws-sdk-go
git clone https://github.com/apparentorder/r53db.git
cd r53db
make clean all install
In no particular order:
- Support explicit AWS authentication (using access/secret key / different profiles; possibly with PostgreSQL User Mappings.)
- Performance improvements (especially grouping of multi-row operations)
- Proper testing framework
- Support more advanced Route53 record types
- Reading values from DNS instead of the API, to leverage the full power of the Route53 Database
- Implement FDW callbacks for
EXPLAIN,ANALYZEetc. - Improved error reporting using
ereport()
This project was born from a silly running gag.
In that spirit, this project was meant as a gag as well. Unfortunately, I've severely underestimated how much time I'd have to invest if I make bad design decisions -- for example, rolling my own in Golang, instead of doing the Right Thing and using Multicorn, or using the AWS Go SDK v2 which gets a full makeover literally the day before I wanted to release this baby. So now we're here, and now it feels more like a project than like a gag.
And who knows -- I can actually imagine this being useful in some contexts, like the use-case described in that link. After all, if you have a lot of configuration data in PostgreSQL anyway, it's a small jump from there to maintaining DNS data as well.
Either way, let me know if you find this useful at all!
Similar projects that I'm aware of:
I'm sure there's more.
For suggestions, bugs, pull requests etc. please use Github.
For everything else: I'm trying to get used to Twitter as @apparentorder.
Or try legacy message delivery using [email protected].
Also I'm old enough to use IRC -- I'm hiding somewhere in ##aws (Freenode).