public inbox for [email protected]
help / color / mirror / Atom feedRe: Issue with Password Authentication for Pgpool
7+ messages / 3 participants
[nested] [flat]
* Re: Issue with Password Authentication for Pgpool
@ 2025-01-09 05:31 vijay patil <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: vijay patil @ 2025-01-09 05:31 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: [email protected] <[email protected]>; [email protected] <[email protected]>
Thanks David,
I tested the configuration by setting allow_clear_text_frontend_auth = on
and disabling the pool_hba. I made the corresponding entry in the
pg_hba.conf file. However, while connecting through the database port
(5432), it prompts for the password, but when connecting through Pgpool
(port 9999), it does not ask for a password.
Here is the content of pg_hba.conf:
bash
Copy code
# TYPE DATABASE USER ADDRESS
METHOD# "local" is for Unix domain socket connections onlylocal all
all trust# IPv4 local
connections:
host all all 127.0.0.1/32 trust#
IPv6 local connections:
host all all ::1/128 trust#
Allow replication connections from localhost, by a user with the#
replication privilege.local replication all
trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
host repmgr repmgr 127.0.0.1/32 trust
host repmgr repmgr 10.125.0.90/32 trust
# Primary
host replication repmgr 10.125.0.90/32 trust
host repmgr repmgr 10.125.0.91/32 trust
# Standby
host replication repmgr 10.125.0.91/32 trust
host all all 10.125.0.90/32 trust # Node 1
host all all 10.125.0.91/32 trust
# Node 2#host all all 0.0.0.0/26 trust
host all all 10.125.0.79/32 scram-sha-256
host all all 0.0.0.0/0 scram-sha-256
When I connect via the database port (5432), it prompts for the password as
expected:
[postgres@scrbtrheldbaas002 ~]$ psql -h 10.125.0.79 -U vkp -d postgres -p 5432
Password for user vkp:
psql (15.3)
Type "help" for help.
postgres=>
postgres=>
postgres=> exit
However, when connecting through Pgpool (port 9999), it does not prompt for
the password:
[postgres@scrbtrheldbaas002 ~]$ psql -h 10.125.0.79 -U vkp -d postgres -p 9999
psql (15.3)
Type "help" for help.
postgres=>
This behavior might be related to how Pgpool handles authentication. Let me
know if you need further investigation or configuration changes!
Thanks
Vijay
On Thu, Jan 9, 2025 at 10:47 AM David G. Johnston <
[email protected]> wrote:
> On Wednesday, January 8, 2025, vijay patil <[email protected]>
> wrote:
>
>> Could you please advise how I can achieve password authentication without
>> using the pool_password file, while ensuring that Pgpool prompts for the
>> password during connection?
>>
>
>
> https://www.pgpool.net/docs/latest/en/html/runtime-config-connection.html#GUC-ALLOW-CLEAR-TEXT-FRONT...
>
> David J.
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Issue with Password Authentication for Pgpool
@ 2025-01-09 05:44 David G. Johnston <[email protected]>
parent: vijay patil <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2025-01-09 05:44 UTC (permalink / raw)
To: vijay patil <[email protected]>; +Cc: [email protected] <[email protected]>
On Wednesday, January 8, 2025, vijay patil <[email protected]> wrote:
> This behavior might be related to how Pgpool handles authentication.
>
Having never used pgpool I very well could be wrong but from what I can
gather what you want to do is simply not possible within the current design
of pgpool. Namely you have multiple nodes so cannot rely on raw mode but
raw mode is required for the kind of pass-through auth you are trying.
Instead you require pool_hba.conf which itself requires the password file.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Issue with Password Authentication for Pgpool
@ 2025-01-09 06:04 vijay patil <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: vijay patil @ 2025-01-09 06:04 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: [email protected] <[email protected]>
If we plan to use pool_hba.conf, then we must use the pool_passwd file to
maintain passwords for all database users. This approach requires that
every time a new user is created, their password is added to the pool_passwd
file. The challenge with this method is that it becomes difficult to
maintain, as we need to manually update the pool_passwd file for every user
creation or password change. Additionally, after updating the pool_passwd
file, Pgpool needs to be restarted for the changes to take effect.
Thanks
Vijay
On Thu, Jan 9, 2025 at 11:14 AM David G. Johnston <
[email protected]> wrote:
> On Wednesday, January 8, 2025, vijay patil <[email protected]>
> wrote:
>
>> This behavior might be related to how Pgpool handles authentication.
>>
>
> Having never used pgpool I very well could be wrong but from what I can
> gather what you want to do is simply not possible within the current design
> of pgpool. Namely you have multiple nodes so cannot rely on raw mode but
> raw mode is required for the kind of pass-through auth you are trying.
> Instead you require pool_hba.conf which itself requires the password file.
>
> David J.
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Issue with Password Authentication for Pgpool
@ 2025-01-09 06:12 David G. Johnston <[email protected]>
parent: vijay patil <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2025-01-09 06:12 UTC (permalink / raw)
To: vijay patil <[email protected]>; +Cc: [email protected] <[email protected]>
On Wednesday, January 8, 2025, vijay patil <[email protected]> wrote:
> Pgpool needs to be restarted for the changes to take effect.
>
You should look for and leverage the difference between when something must
be “restarted” and when it has provided a mechanism for “reloading”.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Issue with Password Authentication for Pgpool
@ 2025-01-09 07:19 Tatsuo Ishii <[email protected]>
parent: vijay patil <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: Tatsuo Ishii @ 2025-01-09 07:19 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]; [email protected]; [email protected]
> Thanks David,
>
> I tested the configuration by setting allow_clear_text_frontend_auth = on
> and disabling the pool_hba. I made the corresponding entry in the
> pg_hba.conf file. However, while connecting through the database port
> (5432), it prompts for the password, but when connecting through Pgpool
> (port 9999), it does not ask for a password.
>
> Here is the content of pg_hba.conf:
>
> bash
> Copy code
> # TYPE DATABASE USER ADDRESS
> METHOD# "local" is for Unix domain socket connections onlylocal all
> all trust# IPv4 local
> connections:
> host all all 127.0.0.1/32 trust#
> IPv6 local connections:
> host all all ::1/128 trust#
> Allow replication connections from localhost, by a user with the#
> replication privilege.local replication all
> trust
> host replication all 127.0.0.1/32 trust
> host replication all ::1/128 trust
> host repmgr repmgr 127.0.0.1/32 trust
> host repmgr repmgr 10.125.0.90/32 trust
> # Primary
> host replication repmgr 10.125.0.90/32 trust
> host repmgr repmgr 10.125.0.91/32 trust
> # Standby
> host replication repmgr 10.125.0.91/32 trust
> host all all 10.125.0.90/32 trust # Node 1
> host all all 10.125.0.91/32 trust
> # Node 2#host all all 0.0.0.0/26 trust
> host all all 10.125.0.79/32 scram-sha-256
> host all all 0.0.0.0/0 scram-sha-256
>
> When I connect via the database port (5432), it prompts for the password as
> expected:
>
> [postgres@scrbtrheldbaas002 ~]$ psql -h 10.125.0.79 -U vkp -d postgres -p 5432
> Password for user vkp:
> psql (15.3)
> Type "help" for help.
>
> postgres=>
> postgres=>
> postgres=> exit
>
> However, when connecting through Pgpool (port 9999), it does not prompt for
> the password:
>
> [postgres@scrbtrheldbaas002 ~]$ psql -h 10.125.0.79 -U vkp -d postgres -p 9999
> psql (15.3)
> Type "help" for help.
>
> postgres=>
>
> This behavior might be related to how Pgpool handles authentication. Let me
> know if you need further investigation or configuration changes!
I guess pgpool is running on IP which is accepted by PostgreSQL using
trust auth method, which does not ask a password. Probably you set
backend_hostname to 'localhost'? Then it matches with the line in
pg_hba.conf:
host all all 127.0.0.1/32 trust
or
host all all ::1/128 trust
in which a password is never asked (or maybe other entries whose auth
method is trust).
Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Issue with Password Authentication for Pgpool
@ 2025-01-09 07:23 Tatsuo Ishii <[email protected]>
parent: vijay patil <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: Tatsuo Ishii @ 2025-01-09 07:23 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]; [email protected]
> If we plan to use pool_hba.conf, then we must use the pool_passwd file to
> maintain passwords for all database users. This approach requires that
> every time a new user is created, their password is added to the pool_passwd
> file.
Not really. You can use pool_hba.conf with the auth method to
'password'. With this setting, if a passowrd is not found in
pool_passwd, then pgpool asks the password to PostgreSQL.
Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Issue with Password Authentication for Pgpool
@ 2025-01-09 07:36 vijay patil <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: vijay patil @ 2025-01-09 07:36 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: [email protected] <[email protected]>
Thanks David.
On Thu, Jan 9, 2025 at 11:42 AM David G. Johnston <
[email protected]> wrote:
> On Wednesday, January 8, 2025, vijay patil <[email protected]>
> wrote:
>
>> Pgpool needs to be restarted for the changes to take effect.
>>
>
> You should look for and leverage the difference between when something
> must be “restarted” and when it has provided a mechanism for “reloading”.
>
> David J.
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-01-09 07:36 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-09 05:31 Re: Issue with Password Authentication for Pgpool vijay patil <[email protected]>
2025-01-09 05:44 ` David G. Johnston <[email protected]>
2025-01-09 06:04 ` vijay patil <[email protected]>
2025-01-09 06:12 ` David G. Johnston <[email protected]>
2025-01-09 07:36 ` vijay patil <[email protected]>
2025-01-09 07:23 ` Tatsuo Ishii <[email protected]>
2025-01-09 07:19 ` Tatsuo Ishii <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox