Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: Jon Earle <je_pgsql@kronos.honk.org>,
	pgsql-general@postgresql.org
Subject: Re: PostgreSQL Qs
Date: Wed, 7 May 2003 18:32:22 +0100
User-Agent: KMail/1.4.3
References: <Pine.LNX.4.53.0305071232370.25576@kronos.honk.org>
In-Reply-To: <Pine.LNX.4.53.0305071232370.25576@kronos.honk.org>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200305071832.22529.dev@archonet.com>

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=3Ddbi:Pg:dbname=3Dtemplate1
> export DBI_USER=3Dpostgres
> export DBI_PASS=3Dxxxxxx
>
> Then I ran 'make test'.  I got a lot of:
>
> ..
> t/99cleanup.........DBI connect('dbname=3Dtemplate1','postgres',...) fail=
ed:
> 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 w=
ith=20
your system password for user "postgres" (unless you're using PAM auth).
--=20
  Richard Huxton