Обсуждение: pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working

I can't get md5 authentication working with postgres 9rc1 and pgpool-II 3.0.  I see references to "pool_passwd" in the pgpool documentation, but I see nothing indicating *where* this file should exist and how pgpool finds it.  I've set my accounts up in pcp.conf, however, I do not believe this is what is expected.

When I try to access the database with md5 turned on, I see the following error in my pgpool debug log: read_password_packet_password: failed to read password packet "p"

I can get everything working with trust.

postgres log:
LOG:  unexpected EOF on client connection
LOG:  disconnection: session time: 0:00:00.007 user=test database=test host=10.201.121.204 port=42812

psql output:
psql -h 127.0.0.1 -p 9999 test test
Password for user test:
psql: server closed the connection unexpectedly
       This probably means the server terminated abnormally
       before or while processing the request.

pgpool debug log:
2010-09-14 21:38:51 DEBUG: pid 6633: I am 6633 accept fd 6
2010-09-14 21:38:51 LOG:   pid 6633: connection received: host=127.0.0.1 port=48336
2010-09-14 21:38:51 DEBUG: pid 6633: Protocol Major: 3 Minor: 0 database: test user: test
2010-09-14 21:38:51 DEBUG: pid 6633: new_connection: connecting 0 backend
2010-09-14 21:38:51 DEBUG: pid 6633: pool_ssl: SSL requested but SSL support is not available
2010-09-14 21:38:51 DEBUG: pid 6633: pool_read_message_length: slot: 0 length: 12
2010-09-14 21:38:51 DEBUG: pid 6633: trying md5 authentication
2010-09-14 21:38:51 DEBUG: pid 6633: DB node id: 0 salt: d3da5234
2010-09-14 21:38:51 DEBUG: pid 6633: read_password_packet_password: failed to read password packet "p"
2010-09-14 21:38:51 ERROR: pid 6633: do_md5: read_password_packet failed
2010-09-14 21:38:51 DEBUG: pid 6633: do_md5failed in slot 0
2010-09-14 21:38:53 DEBUG: pid 6633: I am 6633 accept fd 6
2010-09-14 21:38:53 LOG:   pid 6633: connection received: host=127.0.0.1 port=48338
2010-09-14 21:38:53 DEBUG: pid 6633: Protocol Major: 3 Minor: 0 database: test user: test
2010-09-14 21:38:53 DEBUG: pid 6633: new_connection: connecting 0 backend
2010-09-14 21:38:53 DEBUG: pid 6633: pool_ssl: SSL requested but SSL support is not available
2010-09-14 21:38:53 DEBUG: pid 6633: pool_read_message_length: slot: 0 length: 12
2010-09-14 21:38:53 DEBUG: pid 6633: trying md5 authentication
2010-09-14 21:38:53 DEBUG: pid 6633: DB node id: 0 salt: 855592aa
2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler called
2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler: call wait3
2010-09-14 21:38:53 ERROR: pid 6603: Child process 6633 was terminated by segmentation fault
2010-09-14 21:38:53 DEBUG: pid 6603: child 6633 exits with status 11 by signal 11
2010-09-14 21:38:53 DEBUG: pid 6603: fork a new child pid 6671
2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler: normally exited

> I can't get md5 authentication working with postgres 9rc1 and pgpool-II 3.0.
>  I see references to "pool_passwd" in the pgpool documentation, but I see
> nothing indicating *where* this file should exist and how pgpool finds it.
>  I've set my accounts up in pcp.conf, however, I do not believe this is what
> is expected.

Sorry for not enough description about pool_passwd. It's located under
the same directory as pgpool.conf.  So the default is
/usr/local/etc/pool_passwd.

You need to create /usr/local/etc/pool_passwd if the uid to run pgpool
server does not have the right to create a new file under
/usr/local/etc, you need to create an empty /usr/local/etc/pool_passwd
owned by the same uid as pgpool. After that you should be able to
register your password as decribed in the doc.

Authentication / Access Controls
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> When I try to access the database with md5 turned on, I see the following
> error in my pgpool debug log: read_password_packet_password: failed to read
> password packet "p"
>
> I can get everything working with trust.
>
> postgres log:
> *LOG:  unexpected EOF on client connection*
> LOG:  disconnection: session time: 0:00:00.007 user=test database=test
> host=10.201.121.204 port=42812
>
> psql output:
> psql -h 127.0.0.1 -p 9999 test test
> Password for user test:
> *psql: server closed the connection unexpectedly
>        This probably means the server terminated abnormally
>        before or while processing the request.
> *
> pgpool debug log:
> 2010-09-14 21:38:51 DEBUG: pid 6633: I am 6633 accept fd 6
> 2010-09-14 21:38:51 LOG:   pid 6633: connection received: host=127.0.0.1
> port=48336
> 2010-09-14 21:38:51 DEBUG: pid 6633: Protocol Major: 3 Minor: 0 database:
> test user: test
> 2010-09-14 21:38:51 DEBUG: pid 6633: new_connection: connecting 0 backend
> 2010-09-14 21:38:51 DEBUG: pid 6633: pool_ssl: SSL requested but SSL support
> is not available
> 2010-09-14 21:38:51 DEBUG: pid 6633: pool_read_message_length: slot: 0
> length: 12
> 2010-09-14 21:38:51 DEBUG: pid 6633: trying md5 authentication
> 2010-09-14 21:38:51 DEBUG: pid 6633: DB node id: 0 salt: d3da5234
> *2010-09-14 21:38:51 DEBUG: pid 6633: read_password_packet_password: failed
> to read password packet "p"
> 2010-09-14 21:38:51 ERROR: pid 6633: do_md5: read_password_packet failed
> *2010-09-14 21:38:51 DEBUG: pid 6633: do_md5failed in slot 0
> 2010-09-14 21:38:53 DEBUG: pid 6633: I am 6633 accept fd 6
> 2010-09-14 21:38:53 LOG:   pid 6633: connection received: host=127.0.0.1
> port=48338
> 2010-09-14 21:38:53 DEBUG: pid 6633: Protocol Major: 3 Minor: 0 database:
> test user: test
> 2010-09-14 21:38:53 DEBUG: pid 6633: new_connection: connecting 0 backend
> 2010-09-14 21:38:53 DEBUG: pid 6633: pool_ssl: SSL requested but SSL support
> is not available
> 2010-09-14 21:38:53 DEBUG: pid 6633: pool_read_message_length: slot: 0
> length: 12
> 2010-09-14 21:38:53 DEBUG: pid 6633: trying md5 authentication
> 2010-09-14 21:38:53 DEBUG: pid 6633: DB node id: 0 salt: 855592aa
> 2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler called
> 2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler: call wait3
> *2010-09-14 21:38:53 ERROR: pid 6603: Child process 6633 was terminated by
> segmentation fault
> 2010-09-14 21:38:53 DEBUG: pid 6603: child 6633 exits with status 11 by
> signal 11
> *2010-09-14 21:38:53 DEBUG: pid 6603: fork a new child pid 6671
> 2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler: normally exited
>
> pg_hba.conf:
> local   all             all                                     trust
> host    all             all             127.0.0.1/32            trust
> host    all             all             ::1/128                 trust
> host    all             all             0.0.0.0/0               md5
>
> pool_hba.conf:
> local   all         all                               trust
> host   all                     all                     0.0.0.0/0
>             md5
>
> pgpool.conf:
> listen_addresses = '*'
> port = 9999
> pcp_port = 9898
> socket_dir = '/srv/pgpool/run'
> pcp_socket_dir = '/srv/pgpool/run'
> backend_socket_dir = '/srv/pgpool/run'
> pcp_timeout = 10
> num_init_children = 64
> max_pool = 4
> child_life_time = 300
> connection_life_time = 0
> child_max_connections = 0
> client_idle_limit = 0
> authentication_timeout = 60
> logdir = '/srv/pgpool/log'
> pid_file_name = '/srv/pgpool/run/pgpool.pid'
> replication_mode = false
> load_balance_mode = false
> replication_stop_on_mismatch = false
> failover_if_affected_tuples_mismatch = false
> replicate_select = false
> reset_query_list = 'ABORT; DISCARD ALL'
> white_function_list = ''
> black_function_list = 'nextval,setval'
> print_timestamp = true
> master_slave_mode = false
> master_slave_sub_mode = 'slony'
> delay_threshold = 0
> log_standby_delay = 'none'
> connection_cache = true
> health_check_timeout = 20
> health_check_period = 0
> health_check_user = 'nobody'
> failover_command = ''
> failback_command = ''
> fail_over_on_backend_error = true
> insert_lock = true
> ignore_leading_white_space = true
> log_statement = false
> log_per_node_statement = false
> log_connections = true
> log_hostname = false
> parallel_mode = false
> enable_query_cache = false
> pgpool2_hostname = ''
> system_db_hostname = 'localhost'
> system_db_port = 5432
> system_db_dbname = 'pgpool'
> system_db_schema = 'pgpool_catalog'
> system_db_user = 'pgpool'
> system_db_password = ''
> backend_hostname0 = '10.215.74.98'
> backend_port0 = 5432
> backend_weight0 = 1
> *enable_pool_hba = true
> *recovery_user = 'nobody'
> recovery_password = ''
> recovery_1st_stage_command = ''
> recovery_2nd_stage_command = ''
> recovery_timeout = 90
> client_idle_limit_in_recovery = 0
> lobj_lock_table = ''
> ssl = false
> debug_level = 100

On Tue, Sep 14, 2010 at 6:55 PM, Tatsuo Ishii <ishii@sraoss.co.jp> wrote:

Sorry for delay. I had a trip outside Japan.

> Thanks, I should have thought to try that location. :)
>
> I put the pool_passwd file in the expected location (custom build,
> /opt/postgres/etc/pool_passwd and /etc/pool_passwd) and reran my test.  I'm
> still having the same connection problem.

I found nasty bug with pgpool. Please try attached patches.

> I think it would be good to add an additional command line parameter to
> pgpool to allow overriding the location of that file (the same way the
> location of pcp.conf, pgpool.conf, and pgpool_hba.conf can be overriden).

Thanks for the suggestion. I'm thinking about to include your idea in
the next major pgpool version up.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
Index: pool_auth.c
===================================================================
RCS file: /cvsroot/pgpool/pgpool-II/pool_auth.c,v
retrieving revision 1.25
diff -c -r1.25 pool_auth.c
*** pool_auth.c    17 Aug 2010 02:22:17 -0000    1.25
--- pool_auth.c    20 Sep 2010 04:27:36 -0000
***************
*** 5,11 ****
   * pgpool: a language independent connection pool server for PostgreSQL
   * written by Tatsuo Ishii
   *
!  * Copyright (c) 2003-2009    PgPool Global Development Group
   *
   * Permission to use, copy, modify, and distribute this software and
   * its documentation for any purpose and without fee is hereby
--- 5,11 ----
   * pgpool: a language independent connection pool server for PostgreSQL
   * written by Tatsuo Ishii
   *
!  * Copyright (c) 2003-2010    PgPool Global Development Group
   *
   * Permission to use, copy, modify, and distribute this software and
   * its documentation for any purpose and without fee is hereby
***************
*** 131,136 ****
--- 131,138 ----

      authkind = ntohl(authkind);

+     pool_debug("pool_do_auth: auth kind:%d", authkind);
+
      /* trust? */
      if (authkind == 0)
      {
***************
*** 833,838 ****
--- 835,848 ----

      if (!RAW_MODE && NUM_BACKENDS > 1)
      {
+         /* Read password entry from pool_passwd */
+         pool_passwd = pool_get_passwd(frontend->username);
+         if (!pool_passwd)
+         {
+             pool_debug("do_md5: %s does not exist in pool_passwd", frontend->username);
+             return -1;
+         }
+
          /* master? */
          if (IS_MASTER_NODE_ID(backend->db_node_id))
          {
***************
*** 852,864 ****
              }

              /* Check the password using my salt + pool_passwd */
-             pool_passwd = pool_get_passwd(frontend->username);
-             if (!pool_passwd)
-             {
-                 pool_debug("do_md5: %s does not exist in pool_passwd", frontend->username);
-                 return -1;
-             }
-
              pg_md5_encrypt(pool_passwd+strlen("md5"), salt, sizeof(salt), encbuf);
              if (strcmp(password, encbuf))
              {
--- 862,867 ----

> I tried the patch file and I still cannot connect.  The only other
> difference is that I've already upgraded our images to Postgres 9.0.0 from
> rc1.
>
> Here is the pgpool debug log:
>
> 2010-09-20 19:43:19 DEBUG: pid 1329: I am 1329 accept fd 6
> 2010-09-20 19:43:19 LOG:   pid 1329: connection received: host=127.0.0.1
> port=39064
> 2010-09-20 19:43:19 DEBUG: pid 1329: Protocol Major: 3 Minor: 0 database:
> template1 user: test
> 2010-09-20 19:43:19 DEBUG: pid 1329: new_connection: connecting 0 backend
> 2010-09-20 19:43:19 DEBUG: pid 1329: pool_ssl: SSL requested but SSL support
> is not available
> 2010-09-20 19:43:19 DEBUG: pid 1329: pool_read_message_length: slot: 0
> length: 12
> 2010-09-20 19:43:19 DEBUG: pid 1329: pool_do_auth: auth kind:5
> 2010-09-20 19:43:19 DEBUG: pid 1329: trying md5 authentication
> 2010-09-20 19:43:19 DEBUG: pid 1329: DB node id: 0 salt: 89d02c38
> 2010-09-20 19:43:19 DEBUG: pid 1329: read_password_packet_password: failed
> to read password packet "p"
> 2010-09-20 19:43:19 ERROR: pid 1329: do_md5: read_password_packet failed
> 2010-09-20 19:43:19 DEBUG: pid 1329: do_md5failed in slot 0
> 2010-09-20 19:43:21 DEBUG: pid 1350: I am 1350 accept fd 6
> 2010-09-20 19:43:21 LOG:   pid 1350: connection received: host=127.0.0.1
> port=39066
> 2010-09-20 19:43:21 DEBUG: pid 1350: Protocol Major: 3 Minor: 0 database:
> template1 user: test
> 2010-09-20 19:43:21 DEBUG: pid 1350: new_connection: connecting 0 backend
> 2010-09-20 19:43:21 DEBUG: pid 1350: pool_ssl: SSL requested but SSL support
> is not available
> 2010-09-20 19:43:21 DEBUG: pid 1350: pool_read_message_length: slot: 0
> length: 12
> 2010-09-20 19:43:21 DEBUG: pid 1350: pool_do_auth: auth kind:5
> 2010-09-20 19:43:21 DEBUG: pid 1350: trying md5 authentication
> 2010-09-20 19:43:21 DEBUG: pid 1350: DB node id: 0 salt: 474a91ef
> 2010-09-20 19:43:21 DEBUG: pid 1350: send_password_packet: backend does not
> return R
> 2010-09-20 19:43:21 DEBUG: pid 1350: do_md5failed in slot 0

I have used PostgreSQL 9.0 + pgpool-II 3.0 and they work fine with md5
auth.  Your log seems to indicate that the password in pool_passwd and
the one in pg_shadow are not identical. Can you verify that?
The query result:

select passwd from pg_shadow where usename = 'test';

must be identical the password in pool_passwd for user test.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

On Tue, Sep 21, 2010 at 10:45 AM, Bryan Murphy <bmurphy1976@gmail.com> wrote:

> On Tue, Sep 21, 2010 at 10:45 AM, Bryan Murphy <bmurphy1976@gmail.com>wrote:
>
>> I'm sorry, when I went back over to double check my steps I realized I ran
>> the wrong command.  I am *still* having the problem.  It appears that the
>> MD5 hashes now match, but it's still failing.  I have postgres and pgpool
>> installed in /opt/postgresql, but I have the same problem when I put
>> pool_passwd in /etc and /opt/postgresql/etc.
>>
>
> Here is a gdb backtrace from the child process when it sigsegs after I enter
> the correct password.  I'm digging in a little deeper now and I thought this
> might be useful.

Unfortunately the gdb backtrace does not show enough information
because of optimization, I guess.  Can you take a backtrace with
optimization disabled binary?

You can obtain this by editing Makefile around line 147.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> (gdb) bt
> #0  0xefcbf24f in ?? ()
> #1  0x08052ac5 in do_md5 (backend=0xefcbf24f, frontend=0x9dfc5c0,
> reauth=<value optimized out>, protoMajor=3)
>     at /usr/include/bits/string3.h:52
> #2  0x08052e7b in pool_do_auth (frontend=0x9df97c0, cp=0x9df8fa8) at
> pool_auth.c:222
> #3  0x080509a9 in connect_backend (unix_fd=4, inet_fd=5) at child.c:1143
> #4  do_child (unix_fd=4, inet_fd=5) at child.c:293
> #5  0x0804bbdf in fork_a_child (unix_fd=4, inet_fd=5, id=<value optimized
> out>) at main.c:1024
> #6  0x0804ddef in main (argc=3, argv=0xbfab6754) at main.c:514
>
>
>>
>>

On Tue, Sep 21, 2010 at 8:08 PM, Tatsuo Ishii <ishii@postgresql.org> wrote: