Re: [GENERAL] Remote Authentication

Hi Sean,

I could reproduce and solve the problem here, so this might help you:

PGLIB=/var/lib/pgsql
PGDATA=/home/tango/db
PGPASSWORD=$PGLIB/test

the $PGLIB/test is the path to the file containing the users generated with
pg_passwd,
this environment variabele should in your case be set for the user www.

To connect to the database use the form:
psql -h hostnameORipaddress  -u dbname

Cheers Wim.

"Sean W. Ellis" wrote:

> wim wrote:
> >
> > Hi Sean,
> >
> > I'm approaching my database over the internet as well. I wondered what you
> > use to connect to the database,
> > are you using ODBC or is there a httpd server connecting to the database
> > using some module like php?
> > If the last one is the case, then the machine running the httpd server is
> > accessing your database, not the clients on the internet.
> > Depending on how you would like to access the database your
> > /etc/rc.d/init.d/postgresql file must contain something like this. Read the
> > manual page on  the option -i
> >
> > su postgres -c '/usr/bin/postmaster -i -S -D/var/lib/pgsql'
> >
> > the pg_hba.conf should contain something like this:
> >
> > local        dbname                                       password
> > ./data/sqlpasswd
> > host         all        0.0.0.0       0.0.0.0            password
> > ./data/sqlpasswd'
>
> I am running postgres as user www.  postmaster -i -S -D
> /usr/local/pgsql/data
>
> In my init I have: su www -c '/usr/local/pgsql/bin/postmaster -i -S -D
> /usr/local/pgsql/data'
>
> Everything works fine with my webserver connecting via perl.  Everything
> is fine for me connecting remotely using psql -h apollo if I have trust
> access on.  But, it all goes to pot when I do this in my pg_hba.conf:
> host    all     192.168.0.0     255.255.0.0     password sqlpasswd
> I have even tried full path and ./data/sqlpasswd, by your suggestion
> above.  My env vars are:
> PGLIB=/usr/local/pgsql/lib
> PGDATA=/usr/local/pgsql/data
> My sqlpasswd file is:
> apollo:~/sql/data$ cat sqlpasswd
> root:passwd:10794:0:::::
> bits:passwd:10796:0:99999:7:::
> asns:dfljskdflsjdf:10816:0:99999:7:::
> ednabuil:sdfsdfs:10812:0:99999:7:::
> asns2:sdfsdf:10816:0:99999:7:::
> frayhall:sdfsdfs:10819:0:99999:7:::
> www:sdfsdfsdf:10836:0:99999:7:::
>
> pg_passwd will allow me to change the passwords in the this file, so it
> is readable to postgres, but when I try remote connect I get:
>
> bits:~$ psql -a password -h apollo -u
> Username: www
> Password:
>
> Connection to database 'www' failed.
> User authentication failed
>
> Same when I exchange crypt for password.  Anything else you might know
> of?
>
> ************