Allowing client access

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

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

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