[GLLUG] Postgres Cracking the safe

[Andrew Beverley]

On Tue, 2015-04-07 at 18:15 +0100, Ken Smith wrote:
> I'm butting my head against the pg_hba.conf settings and usernames and 
> passwords in order to get phpPgAdmin to connect.

Each line in the pg_hba.conf file is a type of connection followed by a
type of authentication. The types of authentication are listed here:

http://www.postgresql.org/docs/9.0/static/auth-methods.html

The 2 "easy" ones are:

- password/md5 authentication, which is what you will be familiar with
for MySQL

- trust, which basically means allow anybody to connect

> LOG:  provided username (ken) and authenticated username (apache) don't 
> match
> FATAL:  Ident authentication failed for user "ken"

That looks like you have trust authentication, and it's authenticating
via the username apache. You're then trying to use "ken", which hasn't
been authenticated.

> Is there a step by step, blow by blow guide that some kind soul could 
> direct me to. Or is there a utility to convert a postgres database dump 
> file into a form that MySQL can import.

I can't help with phpPgAdmin, but if you just want to import a SQL dump
and you have shell access, then you should just be able to do something
like:

In pg_hba.conf:

# Allow any local user access
local   all             postgres                    trust

Then:

    psql -U ken db_name < dump.sql

FWIW, I was "forced" to use Postgres recently, having used MySQL for 10+
years. I am now a convert. Once you get used to it, it really is a nice
database server: rock solid, performs well, and actually more logical to
configure.

Andy