Обсуждение: Allowing client access
Dear All Users,
- How do I allow an external client IP address access to my pgsql 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
??
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 > > ??
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
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