Обсуждение: Allowing client access

Поиск
Список
Период
Сортировка

Allowing client access

От
Timmy Siu
Дата:

Dear All Users,

  • How do I allow an external client IP address access to my pgsql server?
According to my own experience and test, if I set the external client IP address to, for example, 111.222.333.444 in the file /etc/postgresql/11/main/pg_hba.conf, it will not be able to connect to the server:
#TYPE?????? DATABASE?????? ?? USER?????? ?????? ?????? ADDRESS?????? ?????? ?????? METHOD
host?????? all?????? ?????? ?????? testuser ???? ?????? 111.222.333.444?????? md5

I must set "address" to "any" as in the following:
#TYPE?????? DATABASE?????? ?? USER?????? ?????? ?????? ADDRESS?????? ?????? METHOD
host?????? all?????? ?????? ?????? testuser ???? ?????? 0.0.0.0/0?????? ?? md5


  • Can I limit access to my pgsql server through TCP Wrapper? What is the related executable?


Regards,
Timmy

??

Re: Allowing client access

От
Bob Jolliffe
Дата:
Hi Timmy

You need to use CIDR form in your pg_hba.conf.  So:

host all testuser  111.222.333.444/32  md5

Most likely you would probably want to ensure ssl connection if coming
over untrusted network.  So, at minimum, this is better:

hostssl all testuser  111.222.333.444/32  md5

This is better still:

hostssl testdb testuser  111.222.333.444/32  md5

Better still (IMHO) is to keep it local and use ssh tunnel, but I
understand that might be difficult and not necessarily desirable,
depending on the context.

Regards
Bob

On Wed, 9 Oct 2019 at 13:34, Timmy Siu <timmy.siu@aol.com> wrote:
>
> Dear All Users,
>
> How do I allow an external client IP address access to my pgsql server?
>
> According to my own experience and test, if I set the external client IP address to, for example, 111.222.333.444 in
thefile /etc/postgresql/11/main/pg_hba.conf, it will not be able to connect to the server:
 
> #TYPE?????? DATABASE?????? ?? USER?????? ?????? ?????? ADDRESS?????? ?????? ?????? METHOD
> host?????? all?????? ?????? ?????? testuser ???? ?????? 111.222.333.444?????? md5
>
> I must set "address" to "any" as in the following:
> #TYPE?????? DATABASE?????? ?? USER?????? ?????? ?????? ADDRESS?????? ?????? METHOD
> host?????? all?????? ?????? ?????? testuser ???? ?????? 0.0.0.0/0?????? ?? md5
>
>
> Can I limit access to my pgsql server through TCP Wrapper? What is the related executable?
>
>
> Regards,
> Timmy
>
> ??



Re: Allowing client access

От
Timmy Siu
Дата:
Dear Bob,
Thank you.?? hostssl works out of the box!?? It does not require extra 
configuration.?? I can connect to my own pgsql server via pgadmin 4.?? I 
personally feel that Postgresql v11 is much clever than Mysql v5.7 (I 
haven't tried its v8).

I also have tested postgres against TCP Wrappers but it is not compiled 
against TCP wrappers library.?? May I suggest the community to have 
postgres to work with TCP wrappers.?? Its security will be better.

Regards,
Timmy


> Hi Timmy
>
> You need to use CIDR form in your pg_hba.conf.  So:
>
> host all testuser  111.222.333.444/32  md5
>
> Most likely you would probably want to ensure ssl connection if coming
> over untrusted network.  So, at minimum, this is better:
>
> hostssl all testuser  111.222.333.444/32  md5
>
> This is better still:
>
> hostssl testdb testuser  111.222.333.444/32  md5
>
> Better still (IMHO) is to keep it local and use ssh tunnel, but I
> understand that might be difficult and not necessarily desirable,
> depending on the context.
>
> Regards
> Bob



Re: Allowing client access

От
Adrian Klaver
Дата:
On 10/9/19 11:39 AM, Timmy Siu wrote:
> Dear Bob,
> Thank you.?? hostssl works out of the box!?? It does not require extra 

That depends on how you installed Postgres. My guess is whatever package 
manager/installer you used did the setup/configuration for you. That 
would be:

1) In postgresql.conf set ssl = on (default is off)

https://www.postgresql.org/docs/11/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SSL

2) Created the SSL certificate and key and put them in the appropriate 
location:

https://www.postgresql.org/docs/11/ssl-tcp.html

There is also the chance that you may not be actually be connecting 
using SSL. If you connect via psql do see something like?:

psql (11.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, 
bits: 256, compression: off)

NOTE:
In pg_hba.conf the first line that matches wins, so if you have a line 
before the hostssl one that matches it will be used.


Also:

https://www.postgresql.org/docs/11/auth-pg-hba-conf.html

"hostssl

     This record matches connection attempts made using TCP/IP, but only 
when the connection is made with SSL encryption.

     To make use of this option the server must be built with SSL 
support. Furthermore, SSL must be enabled by setting the ssl 
configuration parameter (see Section 18.9 for more information). 
Otherwise, the hostssl record is ignored except for logging a warning 
that it cannot match any connections.
"


> configuration.?? I can connect to my own pgsql server via pgadmin 4.?? I 
> personally feel that Postgresql v11 is much clever than Mysql v5.7 (I 
> haven't tried its v8).
> 
> I also have tested postgres against TCP Wrappers but it is not compiled 
> against TCP wrappers library.?? May I suggest the community to have 
> postgres to work with TCP wrappers.?? Its security will be better.
> 
> Regards,
> Timmy
> 
> 
>> Hi Timmy
>>
>> You need to use CIDR form in your pg_hba.conf.  So:
>>
>> host all testuser  111.222.333.444/32  md5
>>
>> Most likely you would probably want to ensure ssl connection if coming
>> over untrusted network.  So, at minimum, this is better:
>>
>> hostssl all testuser  111.222.333.444/32  md5
>>
>> This is better still:
>>
>> hostssl testdb testuser  111.222.333.444/32  md5
>>
>> Better still (IMHO) is to keep it local and use ssh tunnel, but I
>> understand that might be difficult and not necessarily desirable,
>> depending on the context.
>>
>> Regards
>> Bob
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com