Re: Cannot connect to remote postgres database

Stephen Carville wrote:
> I have been asked to evaluate Oracle, mysql and postgresql as a possible
> replacement for our existing Oracle and MsSQL databases. Oracle and
> mysql I pretty much have covered. Postgresql, OTOH, is somewhat less
> cooperative.
>
> I have the software (v 8.4.13) installed on 64 bit Centos 6. It is
> listening on all available interfaces and netstat confirms this. I
> created an additional user for the postgres db:
If you want to evaluate PostgreSQL, you should evaluate v9.2. 8.4 is
pretty ancient and lacks lots of cool features so your comparison won't
be "fair" to PostgreSQL.
> postgres=# \du
> List of roles
> Role name | Attributes | Member of
> -----------+-------------+-----------
> postgres | Superuser | {}
> : Create role
> : Create DB
> stephen | Superuser | {}
> : Create role
> : Create DB
>
> I assigned passwords using "alter role etc.."
Which exact ALTER ROLE did you use? Feel free to redact the actual
password, of course.
> The problem is that no authentication method except trust seems to work.
>
> in pg_hba.conf:
>
> local all all trust
> host all all 198.204.114.0/24 md5
>
> I've tried both of the above users and get the same error each time:
>
> psql: FATAL: password authentication failed for user "<username>"
>
> I tried changing "md5" to "password" and "pam" without success. Onlt
> "trust" works As near as I can tell by reading the documentation, it is
> setup correctly but I have, obviously, done something wrong.
"md5" is the standard. "password" is plain text (which you don't want)
and "pam" will try to authenticate against OS users, which is probably
not what you want.
> Any hints on where to start looking?
Is there any NAT happening between the client and the server? Check the
server's log for a "LOG: connection received: host=x.x.x.x" message so
you can check which IP is reaching the server.

Regards,
Daniel Serodio