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

Lazaro Ruben Garcia Martinez lgarciam at uci.cu
Fri Mar 15 01:05:37 JST 2013 

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 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 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. 

----- Mensaje original -----

> 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.
> 
> > ----- Mensaje original -----
> 

> > > 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.
> > > 
> > 
> 
> > > > ----- Mensaje original -----
> > > 
> > 
> 

> > > > > 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.
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > ----- Mensaje original -----
> > > > > 
> > > > 
> > > 
> > 
> 

> > > > > > > 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 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 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
> > > > > > > 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/d910dcf7/attachment-0001.html>

More information about the pgpool-general mailing list