Postgres has a really confusing authentication structure (at least for those of us used to the MySQL authentication). It’s obviously very powerful, but like many Linux tools, that power comes at a price – unless you spend hours reading documentation, experimenting with setups, etc., you’re never going to fully understand what you’re doing. At least, that’s my experience.
Using psql is pretty easy – su to the postgres user, then launch, since the postgres user by default has complete access locally. Connecting over TCP/IP is a different matter. By default it’s locked down, so phppgadmin doesn’t allow you to log in as anyone by default. I suppose that’s why the local (non-web) pgAdmin 3 is the default GUI administration tool. Unfortunately for me, I’m very accustomed to phpmyadmin, and I’m running my postgres install on a mostly headless box, so the standard tool doesn’t really cut it.
I’ve set up the package before using this great phppgadmin setup walkthrough, but this time it just didn’t work for me. Messing with pg_hba.conf files, etc. was getting me nowhere, until a random forum post made me realize something really straightforward – I have Webmin installed! After that, it took about 2 minutes to set up and log in.
PhpPGAdmin setup with Webmin
- Log in to Webmin
- Select Servers -> PostgreSQL Database Server
- Select PostgreSQL Users
- Under the list of users, select Create a new user
- Enter username, password, & general permissions, then click Create
- Go back to the database list, then select Allowed Hosts
- Click on Create a new allowed host
- Check Host Address -> Network/CIDR. If your local network is like mine (most devices assigned an IP via DHCP, you’ll want to put in the IP address for our local machine (192.168.11.5 for me), then set CIDR length to 24. This tells postgres to allow users from 192.168.11.x; the CIDR length specifies how many parts of the IP address to ignore (32 means it matches against the entire number; 24 = first 3 parts; 16 = first 2; 8 = first 1).
- I allowed access to all databases
- Under Users select listed users, and type in the name of the user you just created
- Set the authentication mode to MD5 password
- Click Save
Worked perfectly.