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

Tatsuo Ishii ishii at postgresql.org
Wed May 15 13:30:19 JST 2013


Jayadevan,

Glad to hear that you fininally solved the problem. Thanks for the
feedback! This will greatly help those who would have similar problem
in the future.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

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


More information about the pgpool-general mailing list