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 1qBhek-0002I1-R7 for pgsql-general@arkaria.postgresql.org; Tue, 20 Jun 2023 20:08:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qBhej-0002sX-Kj for pgsql-general@arkaria.postgresql.org; Tue, 20 Jun 2023 20:08:49 +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 1qBhei-0002qs-5i for pgsql-general@lists.postgresql.org; Tue, 20 Jun 2023 20:08:49 +0000 Received: from wout4-smtp.messagingengine.com ([64.147.123.20]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qBhed-003gWQ-9R for pgsql-general@lists.postgresql.org; Tue, 20 Jun 2023 20:08:47 +0000 Received: from compute1.internal (compute1.nyi.internal [10.202.2.41]) by mailout.west.internal (Postfix) with ESMTP id A9F2932007F9; Tue, 20 Jun 2023 16:08:39 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute1.internal (MEProxy); Tue, 20 Jun 2023 16:08:39 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= 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= 1687291719; x=1687378119; bh=wwytcYtQltaTva9eY/QkrVBep788EMuLB4S VNyDgPyQ=; b=V+jhGRj0Ha9ks+C3wz5dZNWsLE9piKnqPJxhvtnd6FLHITfxEXH YmY4e6xQKzYvJeN8fbXB3qnfZywVxw0DgvhJFonfxYwN9cQiotwhWx/sCSWp1HxK oLpvY7wlTD08NBMz3D59lYNDkRESUjc+bYdBkACgzpQrhZTzVClGCvdVwHKjgeca pa32UId/ejgYG5WVxZPb2yhSMMZW98VINsfq/hnL5gBs8vc2xL2dEgvcC/C439vp BgElb8GufZ4wt2nRWBtg2YYGY70rKx2V+8HZlNp/PJ+hWA7hmRTe18sEz7eXqx/n AgL7E3xBfMpLF57mseBZVyGg87eaBkGccnA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=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=1687291719; x= 1687378119; bh=wwytcYtQltaTva9eY/QkrVBep788EMuLB4SVNyDgPyQ=; b=h 9Spqtdmeb5KjTHht/qq/qwZxW5hZjM/NMmczWHlztovcnmesmY5afocRMy+nMTrK sEVzsei2n//Ub3NRNjrmN9HyxKTGtSzaf3/c4qU8EcEuuQC4gb+h/T63fsWdaqTE yq+rhVRw6KflVChNtqvZpu7IGoHzE/r0zIETmiVNeq6Q5uHL0XWAeVonxg8cA5tR t/PoyxEfSLTBxnj+0kBr6r4ODv8tEtN0tTVOS8s63CpYmtfBfAej9aO6XrEXd7RF JxIOx9LjAxiRFm/qKmUmFKm50IimHKHdxFqcukZVz5JwspaWOmIK66umjX/MF6eC 99xXqcBo4o2BlhEO80SmQ== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrgeefhedguddvhecutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmd enucfjughrpefkffggfgfuvfhfhfgjtgfgsehtkeertddtfeejnecuhfhrohhmpeetughr ihgrnhcumfhlrghvvghruceorggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtg homheqnecuggftrfgrthhtvghrnhepleekgeejhfekteehfeduveetheeivdduheekudel teefvdffueehtddvgedtfeefnecuffhomhgrihhnpehpohhsthhgrhgvshhqlhdrohhrgh enucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrughr ihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Tue, 20 Jun 2023 16:08:38 -0400 (EDT) Message-ID: <9398cb7d-5614-a311-6449-a97bb7dc4bf9@aklaver.com> Date: Tue, 20 Jun 2023 13:08:37 -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 , pgsql-general References: Content-Language: en-US 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 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