Обсуждение: Unable to login with phppgadmin on a remote host

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

Unable to login with phppgadmin on a remote host

От
Tom Browder
Дата:
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

Re: Unable to login with phppgadmin on a remote host

От
Craig Ringer
Дата:
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
>


Re: Unable to login with phppgadmin on a remote host

От
Tom Browder
Дата:
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

Re: Unable to login with phppgadmin on a remote host

От
Tom Browder
Дата:
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


Re: Unable to login with phppgadmin on a remote host

От
Tom Browder
Дата:
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