Обсуждение: Remote Authentication
I have been using strictly TRUST access through hba up to now because all my SQL boxes have been inside of firewalled systems. However, now I need to allow access to a postgreSQL box that is on the Internet. This box needs to grant access to two Linux boxes and one Windows computer that have dynamic IP addresses. I have been trying to work with the password system. I cannot authenticate and don't see any log to find out what the problem is. I copied the shadow file to a file called /usr/local/pgsql/data/sqlpasswd. Then, I typed in pg_passwd and changed one of the passwords for a user. Then, I changed my hba config to say password sqlpasswd at the end, and could not auth. Then, I tried crypt sqlpasswd, and still no luck. Is there something I need to do at compile time, am I doing something wrong ? The sql box is Slackware Linux 3.6 2.0.36, and I only did ./configure --with-tcl --with-odbc --with-perl at compile time. Any response appreciated, Sean Ellis sean.ellis@oscenter.com
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?
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? > > ************
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? > > ************