pg_hba.conf

So, the first of my technical posts to serve as me "taking in" the information I'm putting down, and to help me understand by explaining it! 


Before anyone comments that this is taken from the documentation, I have done this on purpose as a learning aid!


So the pg_hba.conf file (host based authentication) is located in the $PGDATA directory, and is used to allow and disallow access to the database by host. Now this is something I like about PostgreSQL.


Each entry comprises of the following format:


#TYPE DATABASE USER ADDRESS METHOD 


Without going into too much detail, this breaks down as follows:


TYPE:
local (Unix domain sockets from the local machine)
host (TCP/IP connections from remote hosts)
hostssl (As above but only with SSL enabled)
hostnossl (Opposite of above)

DATABASE
Name of the database that the entry pertains to. Can be set to "all" for any, or "sameuser" to connect to a database with the same name as the user connecting. "samerole"|"samegroup" to connect to a database that has the same name as role|group allocated to the connecting user. can be a comma separated list, or a file with a list of databases located on the host by specified @<filename>


USER
"all" meaning all users, a "+<groupname>" to allow users assigned to a particular group, again comma serperated, or file with: "@<filename>"


ADDRESS
This can be a CIDR-Address. 
   (eg: 192.168.1.1/32, 
         192.168.0.0/16 or 
          0.0.0.0/0 etc.Although this can be set to any bit range 0-32, however, it's typically easier to set within 8 bit ranges in my opinion. Also, this will accept IPV6 addresses too... 0 is all hosts)
or an IP and Mask.
   (e.g 192.168.1.1    255.255.255.255, 
          192.168.0.0    255.255.0.0 or
          0.0.0.0            0.0.0.0)
  These only apply to non Unix socket connections


METHOD
This is where things get a little more complex!
options are:
trust
A straight allow reject
A straight denial md5
Encrypted password crypt
Legacy Encrypted password method password
Clear text password - Errm... not advised!
krb5
Kerberos V5 authentication, libraries required ident
Check against pg_ident map - requires <mapname> after "ident" pam
Pluggable authentication Module - OS Verification, libraries required




So a typical set of entries being:
#TYPE DATABASE USER ADDRESS METHOD
local all all trust
# localhost connections are all trusted
host all dba 192.168.1.1/32 trust
#dba connection trusted
host all user1 194.216.0.0/16 md5
#passwords from 194.216* for user1
host postgres postgres 194.216.0.0/16 trust
#trust from 194.216* for postgres database connection by postgres

host all baduser 0.0.0.0/0 reject

# baduser is banned from all dbs

host all all 0.0.0.0/0 pam
# all other users pam authenticate



The rows in the file are processed top to bottom, and when a rule is matched it is used. so in this example:
host all user2 0.0.0.0/0 reject
host all user2 194.218.65.9/32 trust


User2 will *always* be rejected access from every ip as the second rule will never be hit. In this instance, I always arrange the file in the following order:

• local entries Then

• 32 bit host entries, rejects first, then auths, then trusts
• 24 bit host entries, rejects first, then auths, then trusts
• 16 bit host entries, rejects first, then auths, then trusts
• 8 bit host entries, rejects first, then auths, then trusts
• 0 bit host entries, rejects first, then auths, then trusts

and this seems to work for me!


In a nutshell, the permissions aren't actually that difficult to work out and can be quite secure... Well, only as secure as the server itself of course!