Обсуждение: Unable to login with phppgadmin on a remote host
I have installed postgresql 9.0.4 from source on aUbuntu 10.04 LTS
64-bit remote host.
I have installed phppgadmin, Apache2, and other required programs and
libraries via the Ubuntu package manager.
I have successfully created the user posgtres, executed initdb
successfully, and can execute pqsql to connect to template1, all while
logged in via ssh onto the remote host
I have set postgresql to listen on all.
I have these lines in my pg_hba.conf file:
host all myuser <a remote host IP>/32 md5
host all postgres <a remote host IP>/32 md5
However, I cannot successfully login with phppgadmin on the remote host.
I always get "login failed" whether I use myuser or postgres.
I have made these changes in /etc/phppgadmin files:
apache.conf:
config.inc.php:
$conf['servers'][0]['desc'] = 'mars-postgresql';
$conf['servers'][0]['host'] = 'local host ip address...';
//$conf['extra_login_security'] = true;
$conf['extra_login_security'] = false;
I have asked this of the phppgadmin group but I think it may be my
postgresql setup.
Any help is appreciated.
Best regards,
-Tom
On 18/08/2011 7:20 AM, Tom Browder wrote: > I have installed postgresql 9.0.4 from source on aUbuntu 10.04 LTS > 64-bit remote host. > > I have installed phppgadmin, Apache2, and other required programs and > libraries via the Ubuntu package manager. > > I have successfully created the user posgtres, executed initdb > successfully, and can execute pqsql to connect to template1, all while > logged in via ssh onto the remote host > > I have set postgresql to listen on all. > > I have these lines in my pg_hba.conf file: > > host all myuser<a remote host IP>/32 md5 > host all postgres<a remote host IP>/32 md5 > > However, I cannot successfully login with phppgadmin on the remote host. Given the config you showed, that *should* work. Did you restart apache after altering your phppgadmin config? It's usually best to have phppgadmin (or whatever) connect to 127.0.0.1 for localhost, rather than the public IP address anyway. I'd recommend letting phppgadmin connect to localhost (127.0.0.1/32) and setting that to md5 auth in pg_hba.conf . Most web-based database apps don't work well with "ident" authentication because they're all running under the apache or www-data user, so you'll need to add a like for the database(s) and user(s) of interest that specifies md5 auth. For example, if your admin app uses the "postgres" user and you want it to access all databases: host all postgres 127.0.0.1/32 md5 (or the "local" clause instead if your app uses a unix socket). Personally I wish Pg would permit the client to use md5 auth when ident fails - support something like "ident_or_md5" as an authmode. That'd solve a lot of usability issues for new admins around configuring auth. -- Craig Ringer
Re: Unable to login with phppgadmin on a remote host
От
"Jehan-Guillaume (ioguix) de Rorthais"
Дата:
Hello, On 18/08/2011 01:20, Tom Browder wrote: > [...] > > I have these lines in my pg_hba.conf file: > > host all myuser <a remote host IP>/32 md5 > host all postgres <a remote host IP>/32 md5 > > However, I cannot successfully login with phppgadmin on the remote host. > > I always get "login failed" whether I use myuser or postgres. Do you have errors in your postgresql log files about this connections attempts ? > > I have made these changes in /etc/phppgadmin files: > > apache.conf: > > config.inc.php: > $conf['servers'][0]['desc'] = 'mars-postgresql'; > $conf['servers'][0]['host'] = 'local host ip address...'; > //$conf['extra_login_security'] = true; > $conf['extra_login_security'] = false; What do you mean by 'local host ip address...' ? 127.0.0.1 ? Please, show us your full pg_hba.conf (without comments and public IP address). > > I have asked this of the phppgadmin group but I think it may be my > postgresql setup. Looks like I missed it...where ? > Any help is appreciated. > > Best regards, > > -Tom >
On Wed, Aug 17, 2011 at 19:14, Craig Ringer <ringerc@ringerc.id.au> wrote: > On 18/08/2011 7:20 AM, Tom Browder wrote: >> >> I have installed postgresql 9.0.4 from source on aUbuntu 10.04 LTS >> 64-bit remote host. >> >> I have installed phppgadmin, Apache2, and other required programs and >> libraries via the Ubuntu package manager. >> >> I have successfully created the user posgtres, executed initdb >> successfully, and can execute pqsql to connect to template1, all while >> logged in via ssh onto the remote host >> >> I have set postgresql to listen on all. >> >> I have these lines in my pg_hba.conf file: >> >> host all myuser<a remote host IP>/32 md5 >> host all postgres<a remote host IP>/32 md5 >> >> However, I cannot successfully login with phppgadmin on the remote host. > > Given the config you showed, that *should* work. Did you restart apache > after altering your phppgadmin config? Yes, I did. > It's usually best to have phppgadmin (or whatever) connect to 127.0.0.1 for > localhost, rather than the public IP address anyway. I'd recommend letting > phppgadmin connect to localhost (127.0.0.1/32) and setting that to md5 auth > in pg_hba.conf . I've just done that and that doesn't help. > Most web-based database apps don't work well with "ident" authentication > because they're all running under the apache or www-data user, so you'll > need to add a like for the database(s) and user(s) of interest that > specifies md5 auth. For example, if your admin app uses the "postgres" user > and you want it to access all databases: > > host all postgres 127.0.0.1/32 md5 > > (or the "local" clause instead if your app uses a unix socket). I have set both. I have an admin user now and neither are allowed to login: "login failed" Best, -Tom
On Thu, Aug 18, 2011 at 05:40, (ioguix) de Rorthais <ioguix@free.fr> wrote: > Hello, > On 18/08/2011 01:20, Tom Browder wrote: ... >> I have these lines in my pg_hba.conf file: >> >> host all myuser <a remote host IP>/32 md5 >> host all postgres <a remote host IP>/32 md5 >> >> However, I cannot successfully login with phppgadmin on the remote host. >> >> I always get "login failed" whether I use myuser or postgres. > > Do you have errors in your postgresql log files about this connections > attempts ? No, only some sql errors from an early user creation. >> I have made these changes in /etc/phppgadmin files: >> >> apache.conf: >> >> config.inc.php: >> $conf['servers'][0]['desc'] = 'mars-postgresql'; >> $conf['servers'][0]['host'] = 'local host ip address...'; >> //$conf['extra_login_security'] = true; >> $conf['extra_login_security'] = false; > > What do you mean by 'local host ip address...' ? 127.0.0.1 ? No, I had put in the actual server IP address but have changed and tried '', and 'localhost'--none have worked. > Please, show us your full pg_hba.conf (without comments and public IP > address). My pg_hba.conf: local all all md5 host all all 127.0.0.1/32 md5 host all all ::1/128 md5 host all admin xx.xxx.xxx.xx/32 md5 host all postgres xx.xxx.xxx.xx/32 md5 >> I have asked this of the phppgadmin group but I think it may be my >> postgresql setup. > Looks like I missed it...where ? Oops, I never sent the message, sorry, my mistake. Thanks, Jehan-Guillaume. -Tom
Re: Unable to login with phppgadmin on a remote host
От
"Jehan-Guillaume (ioguix) de Rorthais"
Дата:
On 18/08/2011 13:54, Tom Browder wrote: > On Thu, Aug 18, 2011 at 05:40, (ioguix) de Rorthais <ioguix@free.fr> wrote: >> Hello, >> On 18/08/2011 01:20, Tom Browder wrote: > ... >>> I have these lines in my pg_hba.conf file: >>> >>> host all myuser <a remote host IP>/32 md5 >>> host all postgres <a remote host IP>/32 md5 >>> >>> However, I cannot successfully login with phppgadmin on the remote host. >>> >>> I always get "login failed" whether I use myuser or postgres. >> >> Do you have errors in your postgresql log files about this connections >> attempts ? > > No, only some sql errors from an early user creation. You should have some FATAL or ERROR entries about your attempts to connect. That means phpPgAdmin doesn't even reach PostgreSQL. >>> I have made these changes in /etc/phppgadmin files: >>> >>> apache.conf: >>> >>> config.inc.php: >>> $conf['servers'][0]['desc'] = 'mars-postgresql'; >>> $conf['servers'][0]['host'] = 'local host ip address...'; >>> //$conf['extra_login_security'] = true; >>> $conf['extra_login_security'] = false; >> >> What do you mean by 'local host ip address...' ? 127.0.0.1 ? > > No, I had put in the actual server IP address but have changed and > tried '', and 'localhost'--none have worked. > >> Please, show us your full pg_hba.conf (without comments and public IP >> address). > > My pg_hba.conf: > > local all all md5 > host all all 127.0.0.1/32 md5 > host all all ::1/128 md5 > host all admin xx.xxx.xxx.xx/32 md5 > host all postgres xx.xxx.xxx.xx/32 md5 sounds good. That means that you did set a password to user postgres ? did you try to login with the following commands using your own system user (not postgres): psql postgres postgres psql -h 127.0.0.1 postgres myuser first one use unix socket, second one inet. >>> I have asked this of the phppgadmin group but I think it may be my >>> postgresql setup. > >> Looks like I missed it...where ? > > Oops, I never sent the message, sorry, my mistake. > > Thanks, Jehan-Guillaume. > > -Tom
On Thu, Aug 18, 2011 at 07:37, Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr> wrote: > On 18/08/2011 13:54, Tom Browder wrote: >> On Thu, Aug 18, 2011 at 05:40, (ioguix) de Rorthais <ioguix@free.fr> wrote: >>> On 18/08/2011 01:20, Tom Browder wrote: ... >>>> However, I cannot successfully login with phppgadmin on the remote host. >>>> >>>> I always get "login failed" whether I use myuser or postgres. >>> >>> Do you have errors in your postgresql log files about this connections >>> attempts ? >> >> No, only some sql errors from an early user creation. > You should have some FATAL or ERROR entries about your attempts to > connect. That means phpPgAdmin doesn't even reach PostgreSQL. ... >>> Please, show us your full pg_hba.conf (without comments and public IP >>> address). >> >> My pg_hba.conf: >> >> local all all md5 >> host all all 127.0.0.1/32 md5 >> host all all ::1/128 md5 >> host all admin xx.xxx.xxx.xx/32 md5 >> host all postgres xx.xxx.xxx.xx/32 md5 > > sounds good. > That means that you did set a password to user postgres ? did you try to > login with the following commands using your own system user (not postgres): > > psql postgres postgres That exact command works. (Note that I am logged in via ssh onto the remote postgresql host as my normal system user 'tom'.) > psql -h 127.0.0.1 postgres myuser That exact command works. > first one use unix socket, second one inet. Is there a possibility my postgresql.conf file needs changing to get good logging? I originally changed no defaults and my system postgresql start file puts all info into "server.log" in $PGDATA. This morning I changed one line in that conf file (and then did a reload): #logging_collector = off # Enable capturing of stderr and csvlog logging_collector = on # Enable capturing of stderr and csvlog I also checked /var/log/auth.log and see no evidence of login attempts from my two postgres users except vial local logins. Thanks, Jehan-Guillaume. -Tom
Re: Unable to login with phppgadmin on a remote host
От
"Jehan-Guillaume (ioguix) de Rorthais"
Дата:
On 18/08/2011 15:37, Tom Browder wrote: > On Thu, Aug 18, 2011 at 07:37, Jehan-Guillaume (ioguix) de Rorthais > <ioguix@free.fr> wrote: >> On 18/08/2011 13:54, Tom Browder wrote: >>> On Thu, Aug 18, 2011 at 05:40, (ioguix) de Rorthais <ioguix@free.fr> wrote: >>>> On 18/08/2011 01:20, Tom Browder wrote: > ... >>>>> However, I cannot successfully login with phppgadmin on the remote host. >>>>> >>>>> I always get "login failed" whether I use myuser or postgres. >>>> >>>> Do you have errors in your postgresql log files about this connections >>>> attempts ? >>> >>> No, only some sql errors from an early user creation. >> You should have some FATAL or ERROR entries about your attempts to >> connect. That means phpPgAdmin doesn't even reach PostgreSQL. > ... >>>> Please, show us your full pg_hba.conf (without comments and public IP >>>> address). >>> >>> My pg_hba.conf: >>> >>> local all all md5 >>> host all all 127.0.0.1/32 md5 >>> host all all ::1/128 md5 >>> host all admin xx.xxx.xxx.xx/32 md5 >>> host all postgres xx.xxx.xxx.xx/32 md5 >> >> sounds good. >> That means that you did set a password to user postgres ? did you try to >> login with the following commands using your own system user (not postgres): >> >> psql postgres postgres > > That exact command works. > > (Note that I am logged in via ssh onto the remote postgresql host as > my normal system user 'tom'.) > >> psql -h 127.0.0.1 postgres myuser > > That exact command works. make sure you can connect to the "$conf['servers'][0]['defaultdb']" using myuser: psql -h 127.0.0.1 template1 myuser Then, it should work with the following line in your "conf/config.inc.php" using user myuser: $conf['servers'][0]['host'] = '127.0.0.1'; > >> first one use unix socket, second one inet. > > Is there a possibility my postgresql.conf file needs changing to get > good logging? I originally changed no defaults and my system > postgresql start file puts all info into "server.log" in $PGDATA. > > This morning I changed one line in that conf file (and then did a reload): > > #logging_collector = off # Enable capturing of stderr and csvlog > logging_collector = on # Enable capturing of stderr and csvlog with * log_destination = 'stderr' * logging_collector = on You will find your log file in the folder defined in "log_directory" with name set in parameter "log_filename". > > I also checked /var/log/auth.log and see no evidence of login > attempts from my two postgres users except vial local logins. > > Thanks, Jehan-Guillaume. > > -Tom
Re: Unable to login with phppgadmin on a remote host
От
"Jehan-Guillaume (ioguix) de Rorthais"
Дата:
On 18/08/2011 17:55, Tom Browder wrote: > On Thu, Aug 18, 2011 at 08:48, Jehan-Guillaume (ioguix) de Rorthais > <ioguix@free.fr> wrote: >> On 18/08/2011 15:37, Tom Browder wrote: >>> On Thu, Aug 18, 2011 at 07:37, Jehan-Guillaume (ioguix) de Rorthais >>> <ioguix@free.fr> wrote: >>>> On 18/08/2011 13:54, Tom Browder wrote: >>>>> On Thu, Aug 18, 2011 at 05:40, (ioguix) de Rorthais <ioguix@free.fr> wrote: >>>>>> On 18/08/2011 01:20, Tom Browder wrote: > ... >>>>>>> However, I cannot successfully login with phppgadmin on the remote host. >>>>>>> >>>>>>> I always get "login failed" whether I use myuser or postgres. > ... >> make sure you can connect to the "$conf['servers'][0]['defaultdb']" >> using myuser: >> >> psql -h 127.0.0.1 template1 myuser > > I can connect okay. > >> Then, it should work with the following line in your >> "conf/config.inc.php" using user myuser: >> >> $conf['servers'][0]['host'] = '127.0.0.1'; > > And that works okay. great ! >>> Is there a possibility my postgresql.conf file needs changing to get >>> good logging? I originally changed no defaults and my system >>> postgresql start file puts all info into "server.log" in $PGDATA. >>> >>> This morning I changed one line in that conf file (and then did a reload): >>> >>> #logging_collector = off # Enable capturing of stderr and csvlog >>> logging_collector = on # Enable capturing of stderr and csvlog >> >> with >> * log_destination = 'stderr' >> * logging_collector = on > >> You will find your log file in the folder defined in "log_directory" >> with name set in parameter "log_filename". > > I've set those explicitly now. > > And now all works! I'm not sure which step worked but I think it was > after I set > >> $conf['servers'][0]['host'] = '127.0.0.1'; Yes, I think this is where you had a mistake. Your previous attempts might have an error here somehow. > and restarted Apache. It shouldn't be necessary. > Thanks, so much, Jehan-Guillaume, for your patience and your hand-holding. You are welcome. > Best regards, > > -Tom