Allowing client access

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

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

??
Reply | Threaded
Open this post in threaded view
|

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 <[hidden email]> 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 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
>
> ??


Reply | Threaded
Open this post in threaded view
|

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


Reply | Threaded
Open this post in threaded view
|

Re: Allowing client access

Adrian Klaver-4
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
[hidden email]