Home > PostgreSQL > Using pg_hba.d folder for HBA rules

Using pg_hba.d folder for HBA rules

Its been quite a while since I did post something and that was due to a very busy schedule at work but got lucky today to get a few mins and sharing something I always wanted to write about.

We got an environment at work where we have a quite a huge number of databases on each PostgreSQL development database server and it is because of the static images of the production DBs we need to create for QA and Dev most of the time. In such a scenario if we keep on adding rules to the pg_hba.conf file it can be a big mess with around 200-300 lines in there and thats a nightmare to manage for a DBA.

Solution – What we ended up doing for this was by having a folder pg_hba.d like this:


pg_hba.conf
pg_hba.d
postgresql.conf
postmaster.opts

and under pg_hba.d folder:


host1-dbuser1-trust.db
host2-dbuser2-md5.db

The above two files then contain the list of databases that need to given access for those hosts.


host1-dbuser1-trust.db:
db1
db2
db3

This way you can have host files for each of your staging servers that are used by QA and then have a list of databases that need to be given access from those.

Last step is adding details for using these pg_hba.d files, this can be done in pg_hba.conf file the following way:


host @pg_hba.d/host1-dbuser1-trust.db dbuser1 192.168.2.5/32 trust
host @pg_hba.d/host2-dbuser2-trust.db dbuser2 192.168.2.6/32 md5

This way instead of having the whole list of databases in pg_hba.conf file we now just have two entries and making it very easy to manage. When ever you need to give a new database access to those hosts just add it to the specific file for that host under pg_hba.d folder and reload the DB server.


Shoaib Mir
shoaibmir[@]gmail.com

Advertisements
Categories: PostgreSQL Tags: , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: