Обсуждение: Remote Authentication

Поиск
Список
Период
Сортировка

Remote Authentication

От
"Sean W. Ellis"
Дата:
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

Re: [GENERAL] Remote Authentication

От
"Sean W. Ellis"
Дата:
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?

Re: [GENERAL] Remote Authentication

От
wim
Дата:
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?
>
> ************


Re: [GENERAL] Remote Authentication

От
wim
Дата:
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?
>
> ************