How to confirm the pg_hba.conf service is correctly working

[shing dong]

Hello  all

A while ago, our company had an Postgres DB that was Hacked login db to
modify data
found that pg_hba.conf is not work
Any ip, user can log in to DB

1. The rules in pg_hba.conf are almost invalid
2. pg_hba.conf is only useful for METHOD = trust
3. check SHOW hba_file; the file location is correct
4. select * from pg_hba_file_rules;  checked  is correct
5.DB version :  PostgreSQL 10.19  on x86_64-pc-linux-gnu, compiled by gcc
(GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit


Even if you delete the text in pg_hba.conf
Keep only

host   VJ   VJ_USER   10.10.10.1/32 md5

After  pg_ctl reload and  Restart DB , any ip, user still can log in to DB


Please help how to check whether the pg_hba.conf service is defective
If you need any information, I will provide it

Thanks

Re: How to confirm the pg_hba.conf service is correctly working

[shing dong]

*DEAR  TOM*


just one PG instance in host

I did an experiment
When I remove pg and reinstall pg,  the function of pg_hba is working
,represent that the location of pg_hba is right


- remove
yum remove postgresql*

--- install
yum -y install
https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

yum install postgresql10 postgresql10-server postgresql10-contrib
postgresql10-libs postgresql10-dev* -y


---
I have Check again the content of pg_hba.conf and  "select * from
pg_hba_file_rules"  consistent

Yes, this question is very tricky





Tom Lane  於 2021年12月21日 週二 下午10:42寫道：

> shing dong  writes:
> > 1. The rules in pg_hba.conf are almost invalid
> > 2. pg_hba.conf is only useful for METHOD = trust
> > 3. check SHOW hba_file; the file location is correct
> > 4. select * from pg_hba_file_rules;  checked  is correct
> > 5.DB version :  PostgreSQL 10.19  on x86_64-pc-linux-gnu, compiled by gcc
> > (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
> > Even if you delete the text in pg_hba.conf
> > Keep only
> > host   VJ   VJ_USER   10.10.10.1/32 md5
> > After  pg_ctl reload and  Restart DB , any ip, user still can log in to
> DB
>
> It's hard to say where your mistake is, but probably the first
> thing to check is whether you're really restarting the postmaster.
> I'm wondering in particular if there's more than one PG instance
> on the machine and you're reconfiguring or restarting the wrong
> one.  Other than that, retrace your steps carefully, because at
> least one of the above statements must be wrong.
>
> (I guess if you were feeling *really* paranoid, you could wonder
> whether somebody replaced your postmaster executable with a hacked
> version that doesn't apply any pg_hba checks.  But pilot error
> seems like a far more probable explanation.)
>
> regards, tom lane
>

Re: How to confirm the pg_hba.conf service is correctly working

[shing dong]

>
> Your original post stated that you only had
> host   VJ   VJ_USER   10.10.10.1/32 md5
> in the pg_hba.conf file.
> However the result of the select is considerably more ?



DEAR

I  have tested this feature  ,  only had

host   VJ   VJ_USER   10.10.10.1/32 md5

in the pg_hba.conf file

Have checked  select * from pg_hba_file_rules results are consistent with
pg_hba.conf

any ip and user still can login in db

When I remove pg  software and reinstall pg software  ,  the function of
pg_hba is working ,represent  that the location and content of pg_hba.conf
are correct

Suspect that the function of pg_hba  is destroyed?










Dave Cramer  於 2021年12月22日 週三 下午6:58寫道：

>
>
> On Tue, 21 Dec 2021 at 22:57, shing dong  wrote:
>
>> *Dear  Dave *
>>
>> The result after reload is
>>
>> 2021-12-21 23:02:43.829 -04,,,36848,,61bf6ecf.8ff0,9,,2021-12-19 13:41:35
>> -04,,0,LOG,0,"received SIGHUP, reloading configuration files",""
>>
>> No other error message
>>
>> --
>>
>> result of  select * from pg_hba_file_rules
>>
>>
>>
>> line_number,type,database,user_name,address,netmask,auth_method,options,error
>> 84,local,{all},{all},,,md5,,
>> 86,host,{all},{all},127.0.0.1,255.255.255.255,md5,,
>> 87,host,{replication},{replica},127.0.0.1,255.255.255.255,md5,,
>> 88,host,{replication},{replica},10.34.21.85,255.255.255.255,md5,,
>> 89,host,{replication},{repl},10.37.12.13,255.255.255.255,md5,,
>> 92,host,{product},{querysysuser},13.75.66.131,255.255.255.255,md5,,
>> 93,host,{product},{collector},10.32.61.98,255.255.255.255,md5,,
>> 94,host,{product},{collector_new},10.34.61.98,255.255.255.255,md5,,
>>
>> 95,host,{product},"{collector,collector_new}",10.34.61.99,255.255.255.255,md5,,
>>
>> 96,host,{product},{MylIZ8UUIFO7KZBh1hXEnCPHqugzAm},10.21.99.177,255.255.255.255,md5,,
>> 99,host,{product},{product_member},10.33.132.41,255.255.255.255,md5,,
>> 100,host,{product},{product_member},10.33.132.42,255.255.255.255,md5,,
>> 101,host,{product},{product_member},10.33.132.43,255.255.255.255,md5,,
>> 102,host,{product},{product_member},10.33.132.44,255.255.255.255,md5,,
>> 103,host,{product},{product_member},10.33.132.45,255.255.255.255,md5,,
>> 104,host,{product},{product_member},10.33.132.51,255.255.255.255,md5,,
>> 105,host,{product},{product_member},10.33.132.52,255.255.255.255,md5,,
>> 106,host,{product},{product_member},10.33.132.53,255.255.255.255,md5,,
>> 107,host,{product},{product_member},10.33.132.54,255.255.255.255,md5,,
>> 108,host,{product},{product_member},10.33.132.55,255.255.255.255,md5,,
>> 109,host,{product},{product_member},10.33.132.61,255.255.255.255,md5,,
>> 110,host,{product},{product_member},10.33.132.62,255.255.255.255,md5,,
>> 111,host,{product},{product_member},10.33.132.63,255.255.255.255,md5,,
>> 112,host,{product},{product_member},10.33.132.64,255.255.255.255,md5,,
>> 113,host,{product},{product_member},10.33.132.65,255.255.255.255,md5,,
>> 114,host,{product},{product_member},10.34.32.41,255.255.255.255,md5,,
>> 115,host,{product},{product_member},10.34.32.42,255.255.255.255,md5,,
>> 116,host,{product},{product_member},10.34.32.43,255.255.255.255,md5,,
>> 117,host,{product},{product_member},10.34.32.44,255.255.255.255,md5,,
>> 118,host,{product},{product_member},10.34.32.45,255.255.255.255,md5,,
>> 119,host,{product},{product_member},10.34.32.46,255.255.255.255,md5,,
>> 120,host,{product},{product_member},10.34.32.51,255.255.255.255,md5,,
>> 121,host,{product},{product_member},10.34.32.52,255.255.255.255,md5,,
>> 122,host,{product},{product_member},10.34.32.53,255.255.255.255,md5,,
>> 123,host,{product},{product_member},10.34.32.54,255.255.255.255,md5,,
>> 124,host,{product},{product_member},10.34.32.55,255.255.255.255,md5,,
>> 125,host,{product},{product_member},10.34.32.56,255.255.255.255,md5,,
>> 126,host,{product},{product_member},10.34.32.61,255.255.255.255,md5,,
>> 127,host,{product},{product_member},10.34.32.62,255.255.255.255,md5,,
>> 128,host,{product},{product_member},10.34.32.63,255.255.255.255,md5,,
>> 129,host,{product},{product_member},10.34.32.64,255.255.255.255,md5,,
>> 130,host,{product},{product_member},10.34.32.65,255.255.255.255,md5,,
>> 131,host,{product},{product_member},10.34.32.66,255.255.255.255,md5,,
>> 132,host,{product},{product_member},10.34.32.57,255.255.255.255,md5,,
>> 133,host,{product},{product_member},10.34.32.64,255.255.255.255,md5,,
>> 135,host,{product},{product_agent},10.34.32.21,255.255.255.255,md5,,
>> 136,host,{product},{product_agent},10.34.32.22,255.255.255.255,md