Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 204511336971 for ; Tue, 8 Feb 2011 18:11:15 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 78754-03 for ; Tue, 8 Feb 2011 22:11:05 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.autorevenue.com (mail.autorevenue.com [208.178.167.106]) by mail.postgresql.org (Postfix) with ESMTP id E73211337B83 for ; Tue, 8 Feb 2011 18:11:04 -0400 (AST) Received: (qmail 3885 invoked from network); 8 Feb 2011 17:12:14 -0500 Received: from unknown (HELO dei26g028534) (msteben@10.10.6.164) by mail.autorevenue.com with SMTP; 8 Feb 2011 17:12:14 -0500 From: "Mark Steben" To: Subject: pgpool authenticatin issues Date: Tue, 8 Feb 2011 17:10:45 -0500 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_003D_01CBC7B3.1F928300" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 Thread-Index: AcvH3QhOTnOqHJKNQfOfw7TkQK1uwA== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5994 Message-Id: <20110208221104.E73211337B83@mail.postgresql.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.603 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, TRACKER_ID=1.306, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201102/38 X-Sequence-Number: 36677 This is a multi-part message in MIME format. ------=_NextPart_000_003D_01CBC7B3.1F928300 Content-Type: multipart/alternative; boundary="----=_NextPart_001_003E_01CBC7B3.1F928300" ------=_NextPart_001_003E_01CBC7B3.1F928300 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit Hi, I am running postgresql 8.3.11 and pgpool II 3.0 I am trying to get pg_pool to run on a development server. Any userid defined with 'trust' level Access works fine, but having issues with ids defined with md5 level therefore requiring a password. Reading the pgpool doc (http://pgpool.projects.postgresql.org/pgpool-II/doc/) I see that in order to enable md5 access I need to use the pg_md5 function to initialize the /usr/local/etc/pool_passwd file: Login as DB user and type "pg_md5 --md5auth " user name and md5 encrypted password is registered into pool_passwd. If pool_passwd does not exist yet, pg_md5 command will automatically create it for you I have set my postgres database password as 'steben' I bolden my comments here simply for clarity. My attempt to follow the directions above results in: [postgres@festiva data]$ pg_md5 --md5auth Usage: pg_md5 [OPTIONS] pg_md5 --prompt, -p Prompt password using standard input. --md5auth, -m Produce md5 authentication password. --help, -h This help menu. Warning: At most 32 characters are allowed for input. Warning: Plain password argument is deprecated for security concerns and kept for compatibility. Please prefer using password prompt. So I try putting my password in first: pg_md5 steben --md5auth 2011-02-08 16:33:44 DEBUG: pid 27217: num_backends: 0 total_weight: 0.000000 This gives the following result: [postgres@festiva etc]$ less pool_passwd postgres:md553b0e3d546770470d94e0e0bb3b5d24f But when I use the -p switch on pg_md5 I get a different result: [postgres@festiva etc]$ pg_md5 -p password: (I enter "steben" here) 67800ffdc09d3badf5a3e8a4fd728952 Could you shed any light as to what I'm doing wrong? Thank you, Mark Steben Database Administrator @utoRevenue | Autobase | AVV The CRM division of Dominion Dealer Solutions ------=_NextPart_001_003E_01CBC7B3.1F928300 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

Hi,

I am running postgresql 8.3.11 and pgpool II = 3.0

I am trying to get pg_pool to run on a development server. Any userid defined with ‘trust’ level =

Access works fine, but having issues with ids defined = with md5 level therefore requiring a password.

Reading the pgpool doc (http://pgpo= ol.projects.postgresql.org/pgpool-II/doc/)

I see that in order to enable md5 access I need to = use the pg_md5 function to initialize the /usr/local/etc/pool_passwd = file:

Login as DB user and type "pg_md5 --md5auth " user name and md5 encrypted = password is registered

into = pool_passwd. If pool_passwd does not exist yet, pg_md5 command will automatically create = it for you

I have set my postgres database password as = ‘steben’

I bolden my comments here simply for = clarity.

My attempt to follow the = directions above results in:

[postgres@festiva data]$ pg_md5 = --md5auth

Usage:

pg_md5 = [OPTIONS]

pg_md5 = <PASSWORD>

--prompt, -p Prompt password using standard = input.

--md5auth, = -m Produce md5 authentication password.

--help, -h This help = menu.

Warning: At most 32 = characters are allowed for input.

Warning: Plain password = argument is deprecated for security concerns

&nbs= p; and kept for compatibility. Please prefer using = password

&nbs= p; prompt.

So I try putting my password in = first:

pg_md5 steben --md5auth

2011-02-08 16:33:44 DEBUG: pid = 27217: num_backends: 0 total_weight: 0.000000

<= /p>

This gives the following = result:

[postgres@festiva etc]$ less = pool_passwd

postgres:md553b0e3d546770470d94e0e0b= b3b5d24f

But when I use the –p switch on pg_md5 I get a different = result:

[postgres@festiva etc]$ pg_md5 = -p

password: (I enter “steben” here) =

67800ffdc09d3badf5a3e8a4fd728952

Could you shed any light as to what I’m doing wrong? Thank you,

Mark Steben
Database Administrator
@utoRevenue | Autobase | AVV
The CRM division of Dominion Dealer Solutions

------=_NextPart_001_003E_01CBC7B3.1F928300-- ------=_NextPart_000_003D_01CBC7B3.1F928300 Content-Type: text/plain; name="pool_hba.conf" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="pool_hba.conf" # pgpool Client Authentication Configuration File # = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D # # The format rule in this file follows the rules in the PostgreSQL # Administrator's Guide. Refer to chapter "Client Authentication" for a # complete description. A short synopsis follows. # # This file controls: which hosts are allowed to connect, how clients # are authenticated, which user names they can use, which databases they # can access. Records take one of these forms: # # local DATABASE USER METHOD [OPTION] # host DATABASE USER CIDR-ADDRESS METHOD [OPTION] # hostnossl DATABASE USER CIDR-ADDRESS METHOD [OPTION] # # (The uppercase items must be replaced by actual values.) # # The first field is the connection type: "local" is a Unix-domain = socket, # "host" is a plain TCP/IP socket since pgpool currently doest not = support # SSL connection. "hostnossl" is also a plain TCP/IP socket. # # DATABASE can be "all", "sameuser", a database name, or a = comma-separated # list thereof. Note that "samegroup" like in PostgreSQL's pg_hba.conf # file is not supported, since pgpool does not know which group a user # belongs to. Also note that the database specified here may not exist = in # the backend PostgreSQL. pgpool will authenticate based on the = database's # name, not based on whether it exists or not. # # USER can be "all", a user name, or a comma-separated list thereof. In # both the DATABASE and USER fields you can also write a file name = prefixed # with "@" to include names from a separate file. Note that a group name # prefixed with "+" like in PostgreSQL's pg_hba.conf file is not = supported # because of the same reason as "samegroup" token. Also note that a user # name specified here may not exist in the backend PostgreSQL. pgpool = will # authenticate based on the user's name, not based on whether he/she = exists. # # CIDR-ADDRESS specifies the set of hosts the record matches. # It is made up of an IP address and a CIDR mask that is an integer # (between 0 and 32 (IPv4) that specifies the number of significant bits = in # the mask. Alternatively, you can write an IP address and netmask in # separate columns to specify the set of hosts. # # METHOD can be "trust", "reject", "md5" or "pam". Note that "pam" = sends passwords # in clear text. # # OPTION is the name of the PAM service. Default service name is = "pgpool" # # Database and user names containing spaces, commas, quotes and other = special # characters must be quoted. Quoting one of the keywords "all" or = "sameuser" # makes the name lose its special character, and just match a database = or # username with that name. # # This file is read on pgpool startup. If you edit the file on a = running # system, you have to restart the pgpool for the changes to take = effect. # Put your actual configuration here # ---------------------------------- # # If you want to allow non-local connections, you need to add more # "host" records. In that case you will also need to make pgpool listen # on a non-local interface via the listen_addresses configuration = parameter. # # TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all mavmail md5 local all postgres md5 local all msteben md5 # IPv4 local connections: host all mavmail 127.0.0.1/32 md5 host all mavmail 192.168.1.221/32 md5 host all mavmail 192.168.1.222/32 md5 host all postgres 127.0.0.1/32 md5=20 host all postgres 192.168.1.221/32 md5 host all postgres 192.168.1.222/32 md5 host all msteben 127.0.0.1/32 md5=20 host all msteben 192.168.1.221/32 md5 host all msteben 192.168.1.222/32 md5 =20 ------=_NextPart_000_003D_01CBC7B3.1F928300 Content-Type: text/plain; name="pcp.conf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="pcp.conf" # PCP Client Authentication Configuration File # ============================================ # # This file contains user ID and his password for pgpool # communication manager authentication. # # Note that users defined here do not need to be PostgreSQL # users. These users are authorized ONLY for pgpool # communication manager. # # File Format # =========== # # List one UserID and password on a single line. They must # be concatenated together using ':' (colon) between them. # No spaces or tabs are allowed anywhere in the line. # # Example: # postgres:e8a48653851e28c69d0506508fb27fc5 # # Be aware that there will be no spaces or tabs at the # beginning of the line! although the above example looks # like so. # # Lines beginning with '#' (pound) are comments and will # be ignored. Again, no spaces or tabs allowed before '#'. postgres:67800ffdc09d3badf5a3e8a4fd728952 ------=_NextPart_000_003D_01CBC7B3.1F928300 Content-Type: text/plain; name="pgpool.conf" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="pgpool.conf" # # pgpool-II configuration file sample # $Header: /cvsroot/pgpool/pgpool-II/pgpool.conf.sample,v 1.39 = 2010/09/01 04:58:47 kitagawa Exp $ # Host name or IP address to listen on: '*' for all, '' for no TCP/IP # connections listen_addresses =3D '*' # Port number for pgpool port =3D 5432 # Port number for pgpool communication manager pcp_port =3D 9898 # Unix domain socket path. (The Debian package defaults to # /var/run/postgresql.) socket_dir =3D '/tmp' # Unix domain socket path for pgpool communication manager. # (Debian package defaults to /var/run/postgresql) pcp_socket_dir =3D '/tmp' # Unix domain socket path for the backend. Debian package defaults to = /var/run/postgresql! backend_socket_dir =3D '/tmp' # pgpool communication manager timeout. 0 means no timeout. This = parameter is ignored now. pcp_timeout =3D 10 # number of pre-forked child process num_init_children =3D 20 # Number of connection pools allowed for a child process max_pool =3D 9 # If idle for this many seconds, child exits. 0 means no timeout. child_life_time =3D 300 # If idle for this many seconds, connection to PostgreSQL closes. # 0 means no timeout. connection_life_time =3D 0 # If child_max_connections connections were received, child exits. # 0 means no exit. child_max_connections =3D 0 # If client_idle_limit is n (n > 0), the client is forced to be # disconnected whenever after n seconds idle (even inside an explicit # transactions!) # 0 means no disconnect. client_idle_limit =3D 300=09 # Maximum time in seconds to complete client authentication. # 0 means no timeout. authentication_timeout =3D 60 # Logging directory logdir =3D '/var/log/pgpool' # pid file name pid_file_name =3D '/usr/local/postgresql-8.3.11/data/pgpool.pid' # Replication mode replication_mode =3D false # Load balancing mode, i.e., all SELECTs are load balanced. load_balance_mode =3D true # If there's a disagreement with the packet kind sent from backend, # then degenrate the node which is most likely "minority". If false, # just force to exit this session. replication_stop_on_mismatch =3D false # If there's a disagreement with the number of affected tuples in # UPDATE/DELETE, then degenrate the node which is most likely # "minority". # If false, just abort the transaction to keep the consistency. failover_if_affected_tuples_mismatch =3D false # If true, replicate SELECT statement when replication_mode or = parallel_mode is enabled. # A priority of replicate_select is higher than load_balance_mode. replicate_select =3D false # Semicolon separated list of queries to be issued at the end of a # session #reset_query_list =3D 'ABORT; DISCARD ALL' # for 8.2 or older this should be as follows.=20 reset_query_list =3D 'ABORT; RESET ALL; SET SESSION AUTHORIZATION = DEFAULT' # white_function_list is a comma separated list of function names # those do not write to database. Any functions not listed here # are regarded to write to database and SELECTs including such=20 # writer-functions will be executed on master(primary) in master/slave # mode, or executed on all DB nodes in replication mode. # # black_function_list is a comma separated list of function names # those write to database. Any functions not listed here # are regarded not to write to database and SELECTs including such=20 # read-only-functions will be executed on any DB nodes. # # You cannot make full both white_function_list and # black_function_list at the same time. If you specify something in # one of them, you should make empty other. # # Pre 3.0 pgpool-II recognizes nextval and setval in hard coded # way. Following setting will do the same as the previous version. # white_function_list =3D '' # black_function_list =3D 'nextval,setval' white_function_list =3D '' black_function_list =3D 'nextval,setval' # If true print timestamp on each log line. print_timestamp =3D true # If true, operate in master/slave mode. master_slave_mode =3D true # Master/slave sub mode. either 'slony' or 'stream'. Default is 'slony'. master_slave_sub_mode =3D 'slony' # If the standby server delays more than delay_threshold, # any query goes to the primary only. The unit is in bytes. # 0 disables the check. Default is 0. # Note that health_check_period required to be greater than 0 # to enable the functionality. delay_threshold =3D 0 # 'always' logs the standby delay whenever health check runs. # 'if_over_threshold' logs only if the delay exceeds delay_threshold. # 'none' disables the delay log. log_standby_delay =3D 'none' # If true, cache connection pool. connection_cache =3D true # Health check timeout. 0 means no timeout. health_check_timeout =3D 20 # Health check period. 0 means no health check. health_check_period =3D 0 # Health check user health_check_user =3D 'nobody' # Execute command by failover. # special values: %d =3D node id # %h =3D host name # %p =3D port number # %D =3D database cluster path # %m =3D new master node id # %M =3D old master node id # %% =3D '%' character # failover_command =3D '' # Execute command by failback. # special values: %d =3D node id # %h =3D host name # %p =3D port number # %D =3D database cluster path # %m =3D new master node id # %M =3D old master node id # %% =3D '%' character # failback_command =3D '' # If true, trigger fail over when writing to the backend communication # socket fails. This is the same behavior of pgpool-II 2.2.x or # earlier. If set to false, pgpool will report an error and disconnect # the session. fail_over_on_backend_error =3D true # If true, automatically locks a table with INSERT statements to keep # SERIAL data consistency. If the data does not have SERIAL data # type, no lock will be issued. An /*INSERT LOCK*/ comment has the # same effect. A /NO INSERT LOCK*/ comment disables the effect. insert_lock =3D true # If true, ignore leading white spaces of each query while pgpool judges # whether the query is a SELECT so that it can be load balanced. This # is useful for certain APIs such as DBI/DBD which is known to adding an # extra leading white space. ignore_leading_white_space =3D true # If true, print all statements to the log. Like the log_statement = option # to PostgreSQL, this allows for observing queries without engaging in = full # debugging. log_statement =3D true # If true, print all statements to the log. Similar to log_statement = except # that prints DB node id and backend process id info. log_per_node_statement =3D true # If true, incoming connections will be printed to the log. log_connections =3D true # If true, hostname will be shown in ps status. Also shown in # connection log if log_connections =3D true. # Be warned that this feature will add overhead to look up hostname. log_hostname =3D true # if non 0, run in parallel query mode parallel_mode =3D false # if non 0, use query cache enable_query_cache =3D false #set pgpool2 hostname=20 pgpool2_hostname =3D '' # system DB info system_db_hostname =3D 'localhost' system_db_port =3D 5433 system_db_dbname =3D 'pgpool' system_db_schema =3D 'pgpool_catalog' system_db_user =3D 'pgpool' system_db_password =3D '' # backend_hostname, backend_port, backend_weight # here are examples backend_hostname0 =3D '127.0.0.1' backend_port0 =3D 5433 backend_weight0 =3D 1 #backend_data_directory0 =3D '/data' backend_hostname1 =3D '192.168.1.221' backend_port1 =3D 5433 backend_weight1 =3D 1 #backend_data_directory1 =3D '/data1' # - HBA - # If true, use pool_hba.conf for client authentication. enable_pool_hba =3D true # - online recovery - # online recovery user recovery_user =3D 'nobody' # online recovery password recovery_password =3D '' # execute a command in first stage. recovery_1st_stage_command =3D '' # execute a command in second stage. recovery_2nd_stage_command =3D '' # maximum time in seconds to wait for the recovering node's postmaster # start-up. 0 means no wait. # this is also used as a timer waiting for clients disconnected before # starting 2nd stage recovery_timeout =3D 90 # If client_idle_limit_in_recovery is n (n > 0), the client is forced # to be disconnected whenever after n seconds idle (even inside an # explicit transactions!) in the second stage of online recovery. # n =3D -1 forces clients to be disconnected immediately. # 0 disables this functionality(wait forever). # This parameter only takes effect in recovery 2nd stage. client_idle_limit_in_recovery =3D 0 # Specify table name to lock. This is used when rewriting lo_creat # command in replication mode. The table must exist and has writable # permission to public. If the table name is '', no rewriting occurs. lobj_lock_table =3D '' # If true, enable SSL support for both frontend and backend connections. # note that you must also set ssl_key and ssl_cert for SSL to work in # the frontend connections. ssl =3D false # path to the SSL private key file #ssl_key =3D './server.key' # path to the SSL public certificate file #ssl_cert =3D './server.cert' # If either ssl_ca_cert or ssl_ca_cert_dir is set, then certificate # verification will be performed to establish the authenticity of the # certificate. If neither is set to a nonempty string then no such # verification takes place. ssl_ca_cert should be a path to a single # PEM format file containing CA root certificate(s), whereas = ssl_ca_cert_dir # should be a directory containing such files. These are analagous to = the # -CAfile and -CApath options to openssl verify(1), respectively. #ssl_ca_cert =3D '' #ssl_ca_cert_dir =3D '' # Debug message verbosity level. 0: no message, 1 <=3D : more verbose debug_level =3D 2 ------=_NextPart_000_003D_01CBC7B3.1F928300 Content-Type: application/octet-stream; name="pool_passwd" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="pool_passwd" postgres:md553b0e3d546770470d94e0e0bb3b5d24f ------=_NextPart_000_003D_01CBC7B3.1F928300--