[pgpool-general: 1493] Re: pgpool and md5 auth

[uruvela of india]

I agree with 2nd and 3rd trys, i created it to show something wrong with last one . 
I disagree with 4th because the file is only :

toto:md5

Nothing behind md5. So pgpool didn't check the password and let connection going to PG and be checked.

Regards.
----- Original Message -----
From: Lazaro Ruben Garcia Martinez
Sent: 03/14/13 05:05 PM
To: uruvela of india
Subject: Re: [pgpool-general: 1485] pgpool and md5 auth

2nd launch : changing md5 to reject in pool_hba.conf (restarting pgpool) : DBI connect('dbname=mydb','toto',...) failed: ERROR: no pool_hba.conf entry for host "[local]", user "toto", database "mydb" at dbi.pl http://dbi.pl&lang=en  line 5
If you use md5, You have to use md5 between clients and pgpool and between pgpool and postgresql. That is the reason for 
2nd launch error.

3rd launch : back to md5 in pool_hba.conf and changing toto by tito in pool_passwd (restarting pgpool) : DBI connect('dbname=mydb','toto',...) failed: ERROR: "MD5" authentication with pgpool failed for user "toto" at dbi.pl http://dbi.pl&lang=en  line 5
Role tito is not present in postgresql server, that is the reason of this error.

4th launch : back to toto vs tito in pool_passwd and deleted everything after md5 (now pool_passwd is like this 'toto:md5') + restarting pgpool : working.
This is the expected behavior.

Regards.
-----------------------------------------------------------------
No error with pool_passwd (i'm sure the file is ok if you looking 3rd try and username change with error).

I create md5 hashing with tutorial (pg_md5 etc...)

postgresql log is ok (md5 checking is good)
----- Original Message -----
From: Lazaro Ruben Garcia Martinez
Sent: 03/14/13 04:39 PM
To: uruvela of india
Subject: Re: [pgpool-general: 1485] pgpool and md5 auth

Is there any line in the log file with some information related with pool_password file?

What does postgresql say in log file?

Do you create the pool_password file as pgpool tutorial says, or just copy and paste the md5 from pg_authid of postgres server to pool_password, my advice, delete the pool_password and create it again follows the steps in pgpool tutorial.

Regards.
-----------------------------------------------------------------
I m looking to install last stable version .

example of my log in debug mode :

 loading "/etc/pgpool2/pool_hba.conf" for client authentication configuration file
 pgpool-II successfully started. version 3.2.2 (namameboshi)
...
 Protocol Major: 3 Minor: 0 database: mydb user: toto
 new_connection: connecting 0 backend
 pool_ssl: SSL requested but SSL support is not available
 pool_read_message_length: slot: 0 length: 12
 pool_do_auth: auth kind:5
 trying md5 authentication
: DB node id: 0 salt: 657c38
 pool_read_message_length2: master slot: 0 length: 22
 0 th backend: name: application_name value:
pool_read_message_length2: master slot: 0 length: 25
 0 th backend: name: client_encoding value: UTF8
 pool_read_message_length2: master slot: 0 length: 23
 0 th backend: name: DateStyle value: ISO, DMY
 pool_read_message_length2: master slot: 0 length: 25
0 th backend: name: integer_datetimes value: on
 pool_read_message_length2: master slot: 0 length: 27
0 th backend: name: IntervalStyle value: postgres
pool_read_message_length2: master slot: 0 length: 21
 0 th backend: name: is_superuser value: off
 pool_read_message_length2: master slot: 0 length: 25
 0 th backend: name: server_encoding value: UTF8
 pool_read_message_length2: master slot: 0 length: 25
 0 th backend: name: server_version value: 9.2.3
 pool_read_message_length2: master slot: 0 length: 31
 0 th backend: name: session_authorization value: toto
 pool_read_message_length2: master slot: 0 length: 36
 0 th backend: name: standard_conforming_strings value: off
pool_read_message_length2: master slot: 0 length: 17
 0 th backend: name: TimeZone value: GMT
 pool_read_message_length: slot: 0 length: 12
 pool_do_auth: cp->info[i]:0x7fbc47d7c800 pid:29617
 pool_send_auth_ok: send pid 29617 to frontend
select_load_balancing_node: selected backend id is 0
 selected load balancing node: 0

----- Original Message -----
From: Lazaro Ruben Garcia Martinez
Sent: 03/14/13 04:05 PM
To: uruvela of india
Subject: Re: [pgpool-general: 1485] pgpool and md5 auth

Could you install the most stable release of 3.2 series, 3.2.3, because this realease has some bugs fixed.

Enabled the debug mode in the pgpool.conf.

start pgpool like this: pgpool -a pool_hba_file -d -f pgpool_config_file

In debug mode you should see any kind of information about the autentication process.

In any moments I had been the same problem but it was related with the configuration access (chmod and shown comands) over the pool_hba.conf and pool_password file.

Another reasons, If you have 2 pg servers or more, each server need to has the same autentication configuration.

Regards.

Sorry for my poor english.
-----------------------------------------------------------------
Pgpool log say everything ok, that's seems weird because md5 crypt is wrong so i cant' understand why it's working in this case. I feel a huge mistake from myself but i can't see where .

Pgpool connection vs pg server is ok (everything work well with pg client).

Regards.
----- Original Message -----
From: Lazaro Ruben Garcia Martinez
Sent: 03/14/13 03:27 PM
To: uruvela of india
Subject: Re: [pgpool-general: 1485] pgpool and md5 auth

What says pgpool log file?

Regards.

Would you try to connect to pg server from pgpool machine, but only from pgpool machine for testing is the pg service could accept connections from this machine??

Regards.
-----------------------------------------------------------------

Hi,

I'm looking for some help with MD5 authentification in pgpool.

1st :

pgpool-II version 3.2.2 (namameboshi)
PG 9.2.3

* On PG server : I create a toto role with encrypted password, and add this line in pg_hba.conf :
host mydb toto my_pgpool_ip/32 md5

Restarting PG.


* On pgpool server :

pgpool.conf :

enable_pool_hba = on 
pool_passwd = 'pool_passwd'

Create toto md5 password in pool_passwd :

toto:md5cb926329843e9a8f3eb15e0a2a5c9851

Checking same thing in PG in pg_authid :

| toto | md5cb926329843e9a8f3eb15e0a2a5c9851 |

pool_hba.conf :

add this line : local mydb toto md5

2nd :

Starting pgpool.

Using perl script dbi.pl http://dbi.pl&lang=en  to test on pgpool server :

use DBI;
$DB_name = 'mydb';
$DB_user = 'toto';
$DB_pwd = 'mypassword';
$dbh = DBI->connect("dbi:Pg:dbname=$DB_name","$DB_user","$DB_pwd");
if ( !defined $dbh ) { die "Cannot connect to database!\n"; }
$sth = $dbh->prepare("SELECT * FROM pg_user");
$sth->execute();
while ( ($id,$name) = $sth->fetchrow_array() ) { print "$id\t\t $name \n";
}
$sth->finish();
$dbh->disconnect();


1st launch : working

2nd launch : changing md5 to reject in pool_hba.conf (restarting pgpool) : DBI connect('dbname=mydb','toto',...) failed: ERROR: no pool_hba.conf entry for host "[local]", user "toto", database "mydb" at dbi.pl http://dbi.pl&lang=en  line 5

3rd launch : back to md5 in pool_hba.conf and changing toto by tito in pool_passwd (restarting pgpool) : DBI connect('dbname=mydb','toto',...) failed: ERROR: "MD5" authentication with pgpool failed for user "toto" at dbi.pl http://dbi.pl&lang=en  line 5

4th launch : back to toto vs tito in pool_passwd and deleted everything after md5 (now pool_passwd is like this 'toto:md5') + restarting pgpool : working.


I miss something i'm sure :) but why it's working in 4th try ? It's seems only PG server do it's authentification job's.
_______________________________________________
pgpool-general mailing list
pgpool-general at pgpool.net
http://www.pgpool.net/mailman/listinfo/pgpool-general
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20130314/e01f9ac4/attachment-0001.html>