Unable to connect to PostGIS container outside of localhost

[kartoza]

Unable to connect to PostGIS container outside of localhost

[modulitos]

@isuftin Since docker-compose version 2 uses container linking, you'll need to update your pg-hba.conf file to accept the new hosts. On this image, it is located under /etc/postgresql/9.4/main/pg_hba.conf.

On docker-compose version 2, you can probably add this as a workaround in your compose file for this image:

command: sh -c "echo \"host all all 0.0.0.0/0 md5\" >> /etc/postgresql/9.4/main/pg_hba.conf && /start-postgis.sh"

It allows all hosts to connect to your database, then starts the postgis container as usual.

Hope that helps!

[Gustry]

You should use the command docker inspect and get the IP address of the container.

[modulitos]

This image should have the /etc/postgresql/9.4/main/pg_hba.conf file already, so it seems that your file has been removed or overwritten somehow (tested on the 9.4-2.1 branch).

But this is definitely an interesting issue, especially with the new Docker Compose version 2 networking implementation. Instead of linking containers and connecting to them via localhost and port, the new version of compose creates a hostname for that container based on the container's name. So if we have a container named db, and another named app, we would connect to db from app via psql -U postgres -p 5432 -h db (assuming the default postgres username and port 5432). But notice that our host is now named db instead of localhost, which requires us to update pg_hba.conf to allow connecting to our new hostname.

I think a great solution to this problem would be to add something to the /etc/postgresql/9.4/main/pg_hba.conf config that allows containers within the same docker compose network to connect. I wasn't able to figure out how to allow only a specific hostname to connect without allowing all addresses, as shown above. But here are the pg_hba.conf docs: http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html Any help would be great!

[isuftin]

This is now an issue for me as well. I am unable to connect any containers using the docker-compose version 2 networking.

Example:

Caused by: org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host "<IP of other docker container>", user "dsas", database "gis", SSL off
    at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:291)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:108)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
    at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125)
    at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
    at org.postgresql.jdbc3.Jdbc3Connection.<init>(Jdbc3Connection.java:24)
    at org.postgresql.Driver.makeConnection(Driver.java:393)
    at org.postgresql.Driver.connect(Driver.java:267)
    at org.apache.tomcat.dbcp.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
    at org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
    ... 109 more

[timlinux]

Hi @erikschlegel

There is a patch here to support using a custom pg_hba.conf #30 I am still evaluating this.

It is not really a design intent that the PG instance is accessible by default outside of the docker composed architecture. My intent was that the PG container gets used as a linked container with linked services being allowed in the pg_hba.conf because they are on the same private subnet that docker sets up. For debugging I also allowed the host to access the PG instance (so you can use the docker inspect trick provided by @Gustry above) but that won't work if you are using docker machine because of the extra layer of indirection.

Using a custom pg_hba.conf at run time is an option as per #30 - I just need time to test it out.

[isuftin]

@Lukeswart Ah good point. I didn't think of using the command attribute. I actually am using the DB container within a higher level container and ended up running that exact echo command as part of the build step for the higher level container ;)

[timlinux]

command: sh -c "echo \"host all all 0.0.0.0/0 md5\" >> /etc/postgresql/9.4/main/pg_hba.conf && /start-postgis.sh"

Just make sure you understand the security implications of this - make sure the PG port is blocked in your firewall to hosts you don't want to be accessing it.

[isuftin]

@timlinux Agreed, this is very much insecure (as far as allowing outside traffic in goes). For my purposes, this is for local development in a larger project and is not meant for production purposes. Good point.

[modulitos]

I referenced this issue in my first comment, but I just wanted to clarify that I would love to figure out how to restrict access to just a few select containers (using docker-compose version 2). For example, if my app has the name my-app, and I want to allow it to connect to Kartoza's docker-postgis container, perhaps I can enter host all all my-app md5 into /etc/postgresql/9.4/main/pg_hba.conf. However, I tried that and I couldn't gain access. I also tried tweaking Docker's hostname attribute, but no luck. These doc's were helpful, but I still couldn't figure it out: http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

Any help would be great!

[AlexandreRoba]

I'm having the same problem and no proposition here seems to solve the issue. :(
I'm deploying on Kubernetes. I have two pods. One with GeoServer and one with Postgis.
Postgis is exposed to all other pods in the clusters and the ports is properly opened.
I've added the line to pg_hba.conf:

host all all 0.0.0.0/0 md5

When I try to add a store on geoserver I'm getting the following error:
"Error creating data store, check the parameters. Error message: Unable to obtain connection: Cannot create PoolableConnectionFactory (FATAL: no pg_hba.conf entry for host "10.4.1.6", user "admin", database "geoserver", SSL off)"

Any idea?

[rajanski]

@AlexandreRoba as far as I undestand, there is only one postgis-enabled db prepared in the postgis container: 'gis'

[AlexandreRoba]

You are right. But I can connect to it :( I've drop this since and now running everything from the same container...

[NyakudyaA]

I think this issue no longer exists. I can connect to a DB running on a different host and GeoServer works fine