[pgpool-general: 1745] problems with md5 authentication and debugging pgpool

Jayadevan M jayadevan.maymala at ibsplc.com
Wed May 15 13:16:03 JST 2013


Hell all,

I had a tough time making pgpool work in master-slave mode. In the end, it turned out that by trying out different options, I had really messed up the environment and that led to the 'tough time'.  Thanks to Tatsuo Ishii for providing continuous help in troubleshooting.



Here are a few things I learned during the process.
1)Getting the location of the pool_passwd file right is critical to getting authentication working. Getting the right entries is equally important. When we use pg_md5 to generate the password, it asks for the password, but not user. pg_md5 assumes $USER. In my case, I had logged in as root and  executed pg_md5
pg_md5 -p -m
[root at n3170 etc]# more /usr/local/etc/pool_passwd
root:md5e7630668fa08e27bb93b5e7db30725f2

So you have to either login as postgres and then execute pg_md5 or pass postgres as the user.

[root at n3170 etc]# pg_md5 -p -m -u postgers
password:

[root at n3170 etc]# more /usr/local/etc/pool_passwd
root:md5e7630668fa08e27bb93b5e7db30725f2
postgers:md58f2fae75e1816d3d19e648e1bea21140

I wish pg_md5 could also verify against the database if the user exists.

2) You can also copy/paste the entry from pg_shadow.
postgres=# select  passwd  from pg_shadow where usename = 'postgres';
               passwd
-------------------------------------
md5c719888b07cdc2f8c50c01e8e16996b9

In this scenario, you have to use the entire string - 'md5c719888b07cdc2f8c50c01e8e16996b9' , not just the portion after md5. I was using just 'c719888b07cdc2f8c50c01e8e16996b9' and the authentication kept failing.
3) Use strace extensively to debug issues. To help identify the issues, set num_init_children = 1 in pgpool.conf. By default it is 32. When you run with 32, you will have so many process, you won't know which one to strace.

[root at n3170 etc]# ps -eaf | grep pgpool | wc -l
36

Once you set it to 1, you will have fewer processes -
[root at n3170 etc]# grep children pgpool.conf
num_init_children = 1

[root at n3170 etc]# ps -eaf | grep pgpool | wc -l
5

[root at n3170 etc]# ps -eaf | grep pgpool
root     30642     1  0 09:34 ?        00:00:00 pgpool
root     30643 30642  0 09:34 ?        00:00:00 pgpool: wait for connection request
root     30644 30642  0 09:34 ?        00:00:00 pgpool: PCP: wait for connection request
root     30645 30642  0 09:34 ?        00:00:00 pgpool: worker process
root     30681 14622  0 09:35 pts/0    00:00:00 grep pgpool

It is 30643, the process waiting for connection requests, that you want to strace.

When you strace, use -s option (string size ) to get more characters from the output. Default value of s is 32. So a lot of useful information may get truncated.
Something like

strace -p 30643 -s 500 -o connectattempts.txt

To make sure the program has picked the right files, you can use ls like this

ls -lv /proc/*pid*/fd

Here pid is 30642

[root at n3170 etc]# ls -lv /proc/*30642*/fd
total 0
lrwx------. 1 root root 64 May 15 09:44 0 -> /dev/null
lrwx------. 1 root root 64 May 15 09:44 1 -> /dev/null
lrwx------. 1 root root 64 May 15 09:44 2 -> /dev/null
lrwx------. 1 root root 64 May 15 09:44 3 -> /usr/local/etc/pool_passwd


Regards,
Jayadevan


DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20130515/5ca02c01/attachment.html>


More information about the pgpool-general mailing list