Re: PostgreSQL Qs

On Wednesday 07 May 2003 5:35 pm, Jon Earle wrote:
> Hey folks,
>
> I've been trying to figure out the user authentication stuff in
> PostgreSQL, and am finding it more confusing than mysql.

Yep - more options than you can shake a stick at. They all have fans though.

> My system runs Debian, I installed postgresql from apt. It seemed to
> proceed correctly (no errors were tossed my way). As user postgres, I
> created the db and table structure. Again, seemed to proceed correctly.
>
> I changed /etc/postgresql/pg_hba.conf to:
>
> local all md5
> host all 127.0.0.1 255.0.0.0 md5
> host dbname 0.0.0.0 255.255.255.255 md5

What version is this? I've got something like (note the extra column):
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
host all all 127.0.0.1 255.255.255.255 md5

> Then, I went to add the DBD-Pg module for Perl (compiled from scratch).
> I set the env vars:
>
> export DBI_DSN=dbi:Pg:dbname=template1
> export DBI_USER=postgres
> export DBI_PASS=xxxxxx
>
> Then I ran 'make test'. I got a lot of:
>
> ..
> t/99cleanup.........DBI connect('dbname=template1','postgres',...) failed:
> FATAL 1: Password authentication failed for user "postgres" at
> t/99cleanup.t line 11

I take it the password works when you use psql?
psql -Upostgres template1

If not, set your access to trust, start up psql as above and use:
ALTER USER postgres ENCRYPTED PASSWORD 'newpasshere';

Now back to md5 trust setting and see if that does it.

> Could someone explain or give me a link to a doc that explains what is
> going on as far as user authentication is concerned? Actually, a basic
> howto guide for PGSQL would be most helpful for many things.

One thing to bear in mind is that the database password has nothing to do with
your system password for user "postgres" (unless you're using PAM auth).
--
Richard Huxton