Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qBjtc-0000sz-Nt for pgsql-general@arkaria.postgresql.org; Tue, 20 Jun 2023 22:32:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qBjtb-0004MR-HS for pgsql-general@arkaria.postgresql.org; Tue, 20 Jun 2023 22:32:19 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qBjtZ-0004MI-P1 for pgsql-general@lists.postgresql.org; Tue, 20 Jun 2023 22:32:19 +0000 Received: from wout3-smtp.messagingengine.com ([64.147.123.19]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qBjtU-003hgz-L3 for pgsql-general@postgresql.org; Tue, 20 Jun 2023 22:32:17 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailout.west.internal (Postfix) with ESMTP id 3B132320091E; Tue, 20 Jun 2023 18:32:09 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute2.internal (MEProxy); Tue, 20 Jun 2023 18:32:09 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-transfer-encoding:content-type:content-type:date :date:from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:sender:subject:subject:to:to; s=fm1; t= 1687300328; x=1687386728; bh=Z+/S92Jzf0mdUypUNS/uHJE4FiHQm+tQRg4 E72/NFiA=; b=hGJTk9sMi0dM7j7YO4CwRaNLqUxXnJOmTbOoAwCaMcEIs6zynP7 6/1Fy8CPywCGm3hF9EURiskbufFz2I7uqEMDoociu2nmtPMbdfOzNKMq/E/PZWUp 9bw6FK5ng0K9MmierhXMgW98dtxrYhR+sZ5uZn9uojSuTWhB4E5OU5sxViq/zTo+ hsrD5Ql/Lgp8QwTKwmpk86nt1q+FyN224Y5OPpF6pW8BG9S5Jhk3E2tW6rozJgeZ m5awsu0A1Ki3uQP3LwnygfHIRUgXOrTjUPHlSq2bV2NLrWaFW014ayU6Mb2vGXPM jJ2rs5lwE8xBmEXjPl8ZZPqK8QlXdzaKC7A== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:sender:subject:subject:to:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm2; t= 1687300328; x=1687386728; bh=Z+/S92Jzf0mdUypUNS/uHJE4FiHQm+tQRg4 E72/NFiA=; b=ALOM+p3ZWSUOYj/rL2IbBWD9GylA49HJ4ls8BfyZQExdbICP/cg BwwhymGIlCG8goJ+pzPObOhCnxn3LQxHNNvkRm3F0roHTTTsPe9fwn0AcO/K7cTx 0FFnHi57bZkcJDlxwkiOgLMp9NUv/GKISyG1FCyIEQmbekk5SPAdPTEVndOlM4Nh fwVZN197fpsvztkpa9h96oFh2udzHN41sNH7Azp0cJAE0HTmh9pmDUxy7J1Qqu9E 8UsShPWyhbX+oZ4+2ra4zluMe77HNeWhoNzT33F5sAWbS/vEhyBGtbgZuHOPxX+2 0jRdy7a33JVaox80rQ/gajU8Iuuj4VWIGiw== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrgeefiedguddvucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvfhevhfgjtgfgsehtkeertddtfeejnecuhfhrohhmpeetughr ihgrnhcumfhlrghvvghruceorggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtg homheqnecuggftrfgrthhtvghrnhepfedtffegueeuudevjedvgffgvddvheffieehtdeg tedvleegkeefledttefgieegnecuffhomhgrihhnpehpohhsthhgrhgvshhqlhdrohhrgh enucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrughr ihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Tue, 20 Jun 2023 18:32:08 -0400 (EDT) Message-ID: <0f79c611-ba29-0c32-e28a-ec903885dd77@aklaver.com> Date: Tue, 20 Jun 2023 15:32:07 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.12.0 Subject: Re: strange behavior of .pgpass file To: Atul Kumar References: <9398cb7d-5614-a311-6449-a97bb7dc4bf9@aklaver.com> Content-Language: en-US Cc: pgsql-general From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 6/20/23 13:32, Atul Kumar wrote: Please reply to list also. Ccing list. > Th both pgpass files contains details as below: > > *:5432:*:postgres: There are no other lines in the file? More information below. > > I couldn't find anything wrong there in pgpass. > > and if the issue would have been with .pgpass file only then I would not > have got the same error with -W option. > > But I am getting the same issue when I use -W option as well on standby > side only. > > While on master node I don't get any error while using the -W option > along with standby host name. > > > What could be the reason for this? As same passwords are replicating to > standby. From here: https://www.postgresql.org/docs/12/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-AUTHENTICATION password_encryption (enum) When a password is specified in CREATE ROLE or ALTER ROLE, this parameter determines the algorithm to use to encrypt the password. The default value is md5, which stores the password as an MD5 hash (on is also accepted, as alias for md5). Setting this parameter to scram-sha-256 will encrypt the password with SCRAM-SHA-256. Note that older clients might lack support for the SCRAM authentication mechanism, and hence not work with passwords encrypted with SCRAM-SHA-256. See Section 20.5 for more details. 1) Verify what the password method is on both servers. 2) Check the version of psql you are using in each case. > > > > Regards > Atul > > > > On Wed, 21 Jun 2023, 01:38 Adrian Klaver, > wrote: > > On 6/20/23 11:59, Atul Kumar wrote: > > Hi, > > > > I found some strange behaviour of postgres superuser password in my > > existing cluster, below is the basic outline of the setup. > > 1. version - postgres 12 > > 2. replication - streaming replication async > > 3. OS- centos7 > > 4. One Master, One Standby > > > > I have identical pgpass files on both server postgres home directory. > > > > So when I execute below command on slave node: > > psql -h slave_hostname/ip -U postgres -d postgres -p 5432 -c "select > > pg_is_in_recovery" > > > > I get error of password authentication: > > > > psql: error: FATAL:  password authentication failed for user > "postgres" > > > > password retrieved from file "/homedirectorypath/.pgpass" > > I'm going to say this is failing because per: > > https://www.postgresql.org/docs/15/libpq-pgpass.html > > > hostname:port:database:username:password > > and when you are running it the hostname is not matching what you think > it is and the wrong password is being returned. Whereas the example > below is matching correctly. > > > > > > > But when I run the same command on master node: > > > > psql -h slave_hostname/ip -U postgres -d postgres -p 5432 -c "select > > pg_is_in_recovery" > > > > > > I don't get any errors and get the expected output as "t". > > > > > > *_Note: the passwords in and path of both pgpass files are > identical._* > > > > > > then why am I getting errors on the slave node for the same > command even > > after having everything the same ? > > > > > > Also, I tried the -W to enforce the password of postgres user but > got > > the same issue on slave and no issue on master although the > password is > > the same. > > > > > > What am I missing here ? Please suggest. > > > > > > > > > > Regards, > > Atul > > > > > > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > -- Adrian Klaver adrian.klaver@aklaver.com