|
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 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
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 |
# pgpool Client Authentication Configuration File
# ===============================================
#
# 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
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
host all msteben 192.168.1.221/32 md5
host all msteben 192.168.1.222/32 md5
# 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
# # 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 = '*' # Port number for pgpool port = 5432 # Port number for pgpool communication manager pcp_port = 9898 # Unix domain socket path. (The Debian package defaults to # /var/run/postgresql.) socket_dir = '/tmp' # Unix domain socket path for pgpool communication manager. # (Debian package defaults to /var/run/postgresql) pcp_socket_dir = '/tmp' # Unix domain socket path for the backend. Debian package defaults to /var/run/postgresql! backend_socket_dir = '/tmp' # pgpool communication manager timeout. 0 means no timeout. This parameter is ignored now. pcp_timeout = 10 # number of pre-forked child process num_init_children = 20 # Number of connection pools allowed for a child process max_pool = 9 # If idle for this many seconds, child exits. 0 means no timeout. child_life_time = 300 # If idle for this many seconds, connection to PostgreSQL closes. # 0 means no timeout. connection_life_time = 0 # If child_max_connections connections were received, child exits. # 0 means no exit. child_max_connections = 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 = 300 # Maximum time in seconds to complete client authentication. # 0 means no timeout. authentication_timeout = 60 # Logging directory logdir = '/var/log/pgpool' # pid file name pid_file_name = '/usr/local/postgresql-8.3.11/data/pgpool.pid' # Replication mode replication_mode = false # Load balancing mode, i.e., all SELECTs are load balanced. load_balance_mode = 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 = 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 = 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 = false # Semicolon separated list of queries to be issued at the end of a # session #reset_query_list = 'ABORT; DISCARD ALL' # for 8.2 or older this should be as follows. reset_query_list = '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 # 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 # 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 = '' # black_function_list = 'nextval,setval' white_function_list = '' black_function_list = 'nextval,setval' # If true print timestamp on each log line. print_timestamp = true # If true, operate in master/slave mode. master_slave_mode = true # Master/slave sub mode. either 'slony' or 'stream'. Default is 'slony'. master_slave_sub_mode = '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 = 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 = 'none' # If true, cache connection pool. connection_cache = true # Health check timeout. 0 means no timeout. health_check_timeout = 20 # Health check period. 0 means no health check. health_check_period = 0 # Health check user health_check_user = 'nobody' # Execute command by failover. # special values: %d = node id # %h = host name # %p = port number # %D = database cluster path # %m = new master node id # %M = old master node id # %% = '%' character # failover_command = '' # Execute command by failback. # special values: %d = node id # %h = host name # %p = port number # %D = database cluster path # %m = new master node id # %M = old master node id # %% = '%' character # failback_command = '' # 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 = 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 = 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 = 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 = 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 = true # If true, incoming connections will be printed to the log. log_connections = true # If true, hostname will be shown in ps status. Also shown in # connection log if log_connections = true. # Be warned that this feature will add overhead to look up hostname. log_hostname = true # if non 0, run in parallel query mode parallel_mode = false # if non 0, use query cache enable_query_cache = false #set pgpool2 hostname pgpool2_hostname = '' # system DB info system_db_hostname = 'localhost' system_db_port = 5433 system_db_dbname = 'pgpool' system_db_schema = 'pgpool_catalog' system_db_user = 'pgpool' system_db_password = '' # backend_hostname, backend_port, backend_weight # here are examples backend_hostname0 = '127.0.0.1' backend_port0 = 5433 backend_weight0 = 1 #backend_data_directory0 = '/data' backend_hostname1 = '192.168.1.221' backend_port1 = 5433 backend_weight1 = 1 #backend_data_directory1 = '/data1' # - HBA - # If true, use pool_hba.conf for client authentication. enable_pool_hba = true # - online recovery - # online recovery user recovery_user = 'nobody' # online recovery password recovery_password = '' # execute a command in first stage. recovery_1st_stage_command = '' # execute a command in second stage. recovery_2nd_stage_command = '' # 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 = 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 = -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 = 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 = '' # 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 = false # path to the SSL private key file #ssl_key = './server.key' # path to the SSL public certificate file #ssl_cert = './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 = '' #ssl_ca_cert_dir = '' # Debug message verbosity level. 0: no message, 1 <= : more verbose debug_level = 2
Attachment:
pool_passwd
Description: Binary data
-- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
