Databases – Practical PostgreSQL – The pg_hba.conf file
I recently had a job to do in which I was required to connect to a Postgresql server and run some queries to find some critical data for the team. I wasn’t provided with a logon or any other information about the tables or schema.
Well thank goodness for my old friend google or I wouldn’t have stood a chance against this problem.
What I learned is that without some modifications to a conf file, you will never, ever connect to a database remotely. You may have success connecting from the command line and might be wondering why you can login to the postgres console, but the exact same creds are failing constantly when trying to connect any other way.
Turns out remote connections require the connection mode to be set to ident or trust to either identify or allow the connection based on the user connection manner and credentials used. Without this knowledge you can easily spend a lot of time troubleshooting something that is actually very straight forward.
I didn’t completely understand the issue until I actually setup and installed Postgresql for myself on my vmware homelab.
These two sources were indispensable in my training:
Once I actually set everything up for myself the first it all made a lot more sense. I was able to try all the different scenarios without the fear of breaking production. All in all this was a great learning experience.
These articles are for setting up Postgresql on Centos 7 but there are many other articles out there with similar information for debian or earlier releases.
So get up in them guts and have a poke. I hope you go mildly insane with your new found sql query power.