View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0000528Pgpool-IIBugpublic2019-07-10 17:152019-07-24 09:46
Reportervan Assigned Tot-ishii  
PriorityhighSeveritymajorReproducibilityalways
Status closedResolutionopen 
Product Version4.0.2 
Summary0000528: pgpool authentication for cached connection
DescriptionHi,

In my case
enable_pool_hba = off

And DB level authentication is on .

According to my understanding, for new connection from client, pgpool will not do any authentication from client , only at DB level authentication should happen.
But for cached connections since connection already present between DB and pgpool
pgpool need to authenticate client via pool_passwd file

What i did is delete my pool_passwd file, still pgpool is successfully able to authenticate client for cachec connection

Attaching the config files
TagsNo tags attached.

Activities

van

2019-07-10 17:15

reporter  

pg_hba.conf (4,236 bytes)
pgpool.conf (40,134 bytes)
pool_hba.conf (3,241 bytes)

t-ishii

2019-07-10 17:26

developer   ~0002700

I am confused. If enable_pool_hba = off, then any attempt to authenticate should fail except backend's authentication is "trust".
Are you sure that you have successfully authenticated with enable_pool_hba = off?

van

2019-07-10 18:07

reporter   ~0002702

enable_pool_hba is just to enable the authentication at pgpool level , right ?
I want authentication at DB level not at pgpool, so I disable this parameter (i. e. enable_pool_hba = off)

van

2019-07-10 18:21

reporter   ~0002703

Hi ,
Yes, in y configuration

enable_pool_hba = off
pool_passwd = ' '

still authentication is successful.

Please find the logs with debug5
Also attaching the pgpool.conf
logs (210,101 bytes)    


 logs (210,101 bytes)   
 pgpool-2.conf (40,183 bytes)
			




	




		
		
		

		

		

			 t-ishii		

		

						2019-07-11 09:09					

		

			developer			 
									
				~0002704			
					

				

		

		

				

		

			
	
	I noticed that your pgpool.conf includes following lines:



enable_shared_relcache = off

                                   # If on, relation cache stored in memory cache,

                                   # the cache is shared among child process.

                                   # Default is off.

                                   # (change requires restart)

These are in only Pgpool-II 4.1 and 4.1 is *not* released yet. So I am confused because you said the pgpool version is 4.0.2.

How did you install pgpool?			




	




		
		
		

		

		

			 van		

		

						2019-07-11 13:45					

		

			reporter			 
									
				~0002707			
					

				

		

		

				

		

			
	
	Hi t-ishii, 



We are using container image provided by crunchy DB open source  (which already has pgpool installed for particuler version ),			




	




		
		
		

		

		

			 van		

		

						2019-07-11 13:47					

		

			reporter			 
									
				~0002708			
					

				

		

		

				

		

			
	
	But its off, how will it affect the issue' functionality	




	




		
		
		

		

		

			 t-ishii		

		

						2019-07-11 13:55					

		

			developer			 
									
				~0002709			
					

				

		

		

				

		

			
	
	Don't know. Since we don't how crunchy people build the container (it is likely that they made their own modifications on Pgpool-II), it's best to ask crunchy support.	




	




		
		
		

		

		

			 van		

		

						2019-07-11 14:05					

		

			reporter			 
									
				~0002710			
					

				

		

		

				

		

			
	
	Let me install pgpool by myself and reproduce the issue.	




	




		
		
		

		

		

			 van		

		

						2019-07-11 14:49					

		

			reporter			 
									
				~0002711			
					

				

		

		

				

		

			
	
	Hi t-ishii,



I install it from tar file 4.0.2  and reproduce the issue with same condition.



Please find the config file and logs


 logs-2 (210,101 bytes)   



 logs-2 (210,101 bytes)   
 pg_hba-2.conf (4,236 bytes)
 pgpool-3.conf (40,183 bytes)
			




	




		
		
		

		

		

			 t-ishii		

		

						2019-07-11 14:54					

		

			developer			 
									
				~0002712			
					

		
 Last edited: 2019-07-11 14:57
		

		

		

				

		

			
	
	Can you show me the exact step (SQL) to reproduce the problem? It's not clear to me because you seemed to remove or modify pool_password and other files after starting Pgpool-II. Or you don't have pool_passwd from the very beginning? Also I need pg_hba.conf.



			




	




		
		
		

		

		

			 van		

		

						2019-07-11 14:54					

		

			reporter			 
									
				~0002713			
					

				

		

		

				

		

			
	
	Sorry , this one is the correct config file and logs


 logs-3 (104,802 bytes)   



 logs-3 (104,802 bytes)   
 pgpool-4.conf (38,028 bytes)
 pool_hba-2.conf (3,241 bytes)
			




	




		
		
		

		

		

			 van		

		

						2019-07-11 14:57					

		

			reporter			 
									
				~0002714			
					

				

		

		

				

		

			
	
	I don't have pool_passwd from the start .



psql  -U postgres -h localhost -p 5432  mpsdb			




	




		
		
		

		

		

			 t-ishii		

		

						2019-07-11 15:06					

		

			developer			 
									
				~0002715			
					

				

		

		

				

		

			
	
	I need pg_hba.conf.	




	




		
		
		

		

		

			 van		

		

						2019-07-11 15:25					

		

			reporter			 
					

				

		

		

				

		

			
	
	
 pg_hba-3.conf (4,236 bytes)
			




	




		
		
		

		

		

			 van		

		

						2019-07-11 19:01					

		

			reporter			 
									
				~0002716			
					

				

		

		

				

		

			
	
	Hi t-ishii,



Attached the pg_hba.conf (check  pg_hba-3.conf )			




	




		
		
		

		

		

			 t-ishii		

		

						2019-07-12 09:14					

		

			developer			 
									
				~0002717			
					

		
 Last edited: 2019-07-12 09:18
		

		

		

				

		

			
	
	I wasn't able to locate the session in question in log-3. Also I failed to reproduce the problem here.

Can you restart pgpool and do *only* following so that I can find the log in question:



psql -U postgres -h localhost -p 5432 mpsdb  # password asked

psql -U postgres -h localhost -p 5432 mpsdb  # password not asked



			




	




		
		
		

		

		

			 van		

		

						2019-07-12 12:56					

		

			reporter			 
									
				~0002718			
					

				

		

		

				

		

			
	
	I t-ishii,



Please find the logs attracted



I did following 



psql -U postgres -h localhost -p 5432 mpsdb

(password asked )



again



psql -U postgres -h localhost -p 5432 mpsdb

(password asked )


 log_latest (50,430 bytes)   



 log_latest (50,430 bytes)   
			




	




		
		
		

		

		

			 t-ishii		

		

						2019-07-12 13:07					

		

			developer			 
									
				~0002719			
					

				

		

		

				

		

			
	
	So which pid I should look into? It seems there are at least 4 sessions in the log.	




	




		
		
		

		

		

			 van		

		

						2019-07-12 14:50					

		

			reporter			 
									
				~0002720			
					

				

		

		

				

		

			
	
	pid of which pgpool child process you should check  ?	




	




		
		
		

		

		

			 t-ishii		

		

						2019-07-12 14:56					

		

			developer			 
									
				~0002721			
					

				

		

		

				

		

			
	
	That's what I am asking:-) If you are not sure, you can set num_init_children = 1, restart pgpool and take log.

BTW, you wrote:



psql -U postgres -h localhost -p 5432 mpsdb

(password asked )



again



psql -U postgres -h localhost -p 5432 mpsdb

(password asked )



I assume you were *not* asked password in the second time.			




	




		
		
		

		

		

			 van		

		

						2019-07-12 15:01					

		

			reporter			 
									
				~0002722			
					

				

		

		

				

		

			
	
	Second time also it ask for password

Let me make num_init_children = 1 and take a log			




	




		
		
		

		

		

			 van		

		

						2019-07-12 15:06					

		

			reporter			 
									
				~0002723			
					

				

		

		

				

		

			
	
	Hi t-ishii,



I make  num_init_children = 1 and reproduce it.



psql -U postgres -h localhost -p 5432 mpsdb

(password asked )



again



psql -U postgres -h localhost -p 5432 mpsdb

(password asked )



both time it ask for password.


 logs-4 (45,460 bytes)   



 logs-4 (45,460 bytes)   
			




	




		
		
		

		

		

			 t-ishii		

		

						2019-07-12 15:08					

		

			developer			 
									
				~0002724			
					

				

		

		

				

		

			
	
	> Second time also it ask for password

I am confused. I thought you were complaining that you are not asked password for the second time with a cached connection.

So what's problem with you?			




	




		
		
		

		

		

			 van		

		

						2019-07-12 15:27					

		

			reporter			 
									
				~0002725			
					

				

		

		

				

		

			
	
	Hi t-ishii, 



Consider the following scenario:



 num_init_children = 1 

enable_pool_hba = off

pool_hba    trust

pg_hbd   md5 







First time login 

psql -U postgres -h localhost -p 5432 mpsdb

(password asked ) 

This authentication happen in DB not at pgpool



Now after first login that connection is cache (between pgpool and DB)



At second login 

psql -U postgres -h localhost -p 5432 mpsdb

1) Why it asking for authentication ? (because connection is already there between pgpool and DB)

2) if it asking password to authenticate client , how pgpool authenticating the client at second time because pool_passwd  is not present			




	




		
		
		

		

		

			 t-ishii		

		

						2019-07-12 15:43					

		

			developer			 
									
				~0002726			
					

		
 Last edited: 2019-07-12 15:46
		

		

		

				

		

			
	
	> 1) Why it asking for authentication ? (because connection is already there between pgpool and DB)

That's a security policy of Pgpool-II. Pgpool-II does not trust user until he/she provides correct password regardless whether it is a cached connection or not.



> 2) if it asking password to authenticate client , how pgpool authenticating the client at second time because pool_passwd is not present 

Because you are running in raw mode (both replication_mode and master_slave_mode = off). Also Pgpool-II caches the password created at the time when the connection to backend was created. That's why Pgpool-II can authenticate user with the cached connection.



			




	




		
		
		

		

		

			 van		

		

						2019-07-12 15:47					

		

			reporter			 
									
				~0002727			
					

				

		

		

				

		

			
	
	So if i run on replication_mode and master_slave_mode  = on ,

 how  it will affect ? 

It will still ask for password second time and validate with cached  password ?			




	




		
		
		

		

		

			 t-ishii		

		

						2019-07-12 16:08					

		

			developer			 
									
				~0002728			
					

				

		

		

				

		

			
	
	> So if i run on replication_mode and master_slave_mode = on ,

>  how it will affect ? 

The difference is usually you want to use multiple backends. In this case Pgpool-II needs to consult pool_passwd.

The reasoning for this is explained in:

https://pgpool.net/mediawiki/index.php/FAQ#How_does_pgpool-II_handle_md5_authentication.3F



In summary you need to enable enable_pool_hba. and have pool_hba.conf. And you may need pool_password depending on the auth method you would like to use.

See manual:

http://www.pgpool.net/docs/latest/en/html/auth-pool-hba-conf.html

for more details.



If you dislike to have pool_passwd, you could consider allow_clear_text_frontend_auth:

http://www.pgpool.net/docs/latest/en/html/runtime-config-connection.html#RUNTIME-CONFIG-AUTHENTICATION-SETTINGS



> It will still ask for password second time and validate with cached password ? 

yes.			




	




		
		
		

		

		

			 van		

		

						2019-07-12 16:24					

		

			reporter			 
									
				~0002729			
					

				

		

		

				

		

			
	
	>In summary you need to enable enable_pool_hba. and have pool_hba.conf. And you may need pool_password depending on the auth method you would like to use.

>See manual:

>http://www.pgpool.net/docs/latest/en/html/auth-pool-hba-conf.html

>for more details.



So in case of multiple backends,  we have to enable  enable_pool_hba ?

What if enable_pool_hba is off and pool_hba is trust , will pgpool not validate client connection at all for cached connection ? 

If it will, why it need pook_passwd , if pgpool cached the password from first time connection ?			




	




		
		
		

		

		

			 t-ishii		

		

						2019-07-12 16:33					

		

			developer			 
									
				~0002730			
					

				

		

		

				

		

			
	
	I was talking about md5 auth because that's the question you had. If you are happy with "trust" at all, you don't need to enable enable_pool_hba and you don't need to have pool_password of course. Again please look into manual for more details.	




	




		
		
		

		

		

			 t-ishii		

		

						2019-07-17 17:19					

		

			developer			 
									
				~0002733			
					

				

		

		

				

		

			
	
	Can I close the issue?	




	




		
		
		

		

		

			 van		

		

						2019-07-22 16:48					

		

			reporter			 
									
				~0002735			
					

				

		

		

				

		

			
	
	Yes please, thanks for the help	




	














	

		
		

	
		

	

		

						Issue History		

		

			
							
		

	

	

	

	

	

		
			

				
					Date Modified				
				
					Username				
				
					Field				
				
					Change				
			

		
	
		
				

				
					2019-07-10 17:15				
				
					van				
				
					New Issue				
				
									
			

				

				
					2019-07-10 17:15				
				
					van				
				
					File Added: pg_hba.conf				
				
									
			

				

				
					2019-07-10 17:16				
				
					van				
				
					File Added: pgpool.conf				
				
									
			

				

				
					2019-07-10 17:16				
				
					van				
				
					File Added: pool_hba.conf				
				
									
			

				

				
					2019-07-10 17:26				
				
					t-ishii				
				
					Note Added: 0002700				
				
									
			

				

				
					2019-07-10 18:07				
				
					van				
				
					Note Added: 0002702				
				
									
			

				

				
					2019-07-10 18:21				
				
					van				
				
					File Added: logs				
				
									
			

				

				
					2019-07-10 18:21				
				
					van				
				
					File Added: pgpool-2.conf				
				
									
			

				

				
					2019-07-10 18:21				
				
					van				
				
					Note Added: 0002703				
				
									
			

				

				
					2019-07-11 09:09				
				
					t-ishii				
				
					Note Added: 0002704				
				
									
			

				

				
					2019-07-11 13:45				
				
					van				
				
					Note Added: 0002707				
				
									
			

				

				
					2019-07-11 13:47				
				
					van				
				
					Note Added: 0002708				
				
									
			

				

				
					2019-07-11 13:55				
				
					t-ishii				
				
					Note Added: 0002709				
				
									
			

				

				
					2019-07-11 14:05				
				
					van				
				
					Note Added: 0002710				
				
									
			

				

				
					2019-07-11 14:49				
				
					van				
				
					File Added: logs-2				
				
									
			

				

				
					2019-07-11 14:49				
				
					van				
				
					File Added: pg_hba-2.conf				
				
									
			

				

				
					2019-07-11 14:49				
				
					van				
				
					File Added: pgpool-3.conf				
				
									
			

				

				
					2019-07-11 14:49				
				
					van				
				
					Note Added: 0002711				
				
									
			

				

				
					2019-07-11 14:54				
				
					t-ishii				
				
					Note Added: 0002712				
				
									
			

				

				
					2019-07-11 14:54				
				
					van				
				
					File Added: logs-3				
				
									
			

				

				
					2019-07-11 14:54				
				
					van				
				
					File Added: pgpool-4.conf				
				
									
			

				

				
					2019-07-11 14:54				
				
					van				
				
					File Added: pool_hba-2.conf				
				
									
			

				

				
					2019-07-11 14:54				
				
					van				
				
					Note Added: 0002713				
				
									
			

				

				
					2019-07-11 14:55				
				
					t-ishii				
				
					Note Edited: 0002712				
				
									
			

				

				
					2019-07-11 14:57				
				
					van				
				
					Note Added: 0002714				
				
									
			

				

				
					2019-07-11 14:57				
				
					t-ishii				
				
					Note Edited: 0002712				
				
									
			

				

				
					2019-07-11 15:06				
				
					t-ishii				
				
					Note Added: 0002715				
				
									
			

				

				
					2019-07-11 15:25				
				
					van				
				
					File Added: pg_hba-3.conf				
				
									
			

				

				
					2019-07-11 16:55				
				
					t-ishii				
				
					Status				
				
					new => feedback				
			

				

				
					2019-07-11 16:55				
				
					t-ishii				
				
					Description Updated				
				
									
			

				

				
					2019-07-11 16:55				
				
					t-ishii				
				
					Assigned To				
				
					 => t-ishii				
			

				

				
					2019-07-11 16:55				
				
					t-ishii				
				
					Status				
				
					feedback => assigned				
			

				

				
					2019-07-11 19:01				
				
					van				
				
					Note Added: 0002716				
				
									
			

				

				
					2019-07-12 09:14				
				
					t-ishii				
				
					Note Added: 0002717				
				
									
			

				

				
					2019-07-12 09:16				
				
					t-ishii				
				
					Note Edited: 0002717				
				
									
			

				

				
					2019-07-12 09:18				
				
					t-ishii				
				
					Note Edited: 0002717				
				
									
			

				

				
					2019-07-12 09:18				
				
					t-ishii				
				
					Status				
				
					assigned => feedback				
			

				

				
					2019-07-12 12:56				
				
					van				
				
					File Added: log_latest				
				
									
			

				

				
					2019-07-12 12:56				
				
					van				
				
					Note Added: 0002718				
				
									
			

				

				
					2019-07-12 12:56				
				
					van				
				
					Status				
				
					feedback => assigned				
			

				

				
					2019-07-12 13:07				
				
					t-ishii				
				
					Note Added: 0002719				
				
									
			

				

				
					2019-07-12 14:50				
				
					van				
				
					Note Added: 0002720				
				
									
			

				

				
					2019-07-12 14:56				
				
					t-ishii				
				
					Note Added: 0002721				
				
									
			

				

				
					2019-07-12 15:01				
				
					van				
				
					Note Added: 0002722				
				
									
			

				

				
					2019-07-12 15:06				
				
					van				
				
					File Added: logs-4				
				
									
			

				

				
					2019-07-12 15:06				
				
					van				
				
					Note Added: 0002723				
				
									
			

				

				
					2019-07-12 15:08				
				
					t-ishii				
				
					Note Added: 0002724				
				
									
			

				

				
					2019-07-12 15:27				
				
					van				
				
					Note Added: 0002725				
				
									
			

				

				
					2019-07-12 15:43				
				
					t-ishii				
				
					Note Added: 0002726				
				
									
			

				

				
					2019-07-12 15:46				
				
					t-ishii				
				
					Note Edited: 0002726				
				
									
			

				

				
					2019-07-12 15:47				
				
					van				
				
					Note Added: 0002727				
				
									
			

				

				
					2019-07-12 16:08				
				
					t-ishii				
				
					Note Added: 0002728				
				
									
			

				

				
					2019-07-12 16:24				
				
					van				
				
					Note Added: 0002729				
				
									
			

				

				
					2019-07-12 16:33				
				
					t-ishii				
				
					Note Added: 0002730				
				
									
			

				

				
					2019-07-17 17:19				
				
					t-ishii				
				
					Note Added: 0002733				
				
									
			

				

				
					2019-07-17 17:19				
				
					t-ishii				
				
					Status				
				
					assigned => feedback				
			

				

				
					2019-07-22 16:48				
				
					van				
				
					Note Added: 0002735				
				
									
			

				

				
					2019-07-22 16:48				
				
					van				
				
					Status				
				
					feedback => assigned				
			

				

				
					2019-07-24 09:46				
				
					t-ishii				
				
					Status				
				
					assigned => closed