CODEWITHSUNDEEP
postgresqldockerubuntu-server
Denis Steinman
Denis Steinman

Reputation: 7799

Can't connect to PostgreSQL on host machine from Docker container

I use VM with Ubuntu Server 20.04 LTS where I set up the next Docker network:

[
    {
        "Name": "my-net",
        "Id": "d06d15cbc443df8565b76e30aa13da05e26cd3bfc8d33551020d2ce3fe94a118",
        "Created": "2022-05-29T22:08:12.618759894Z",
        "Scope": "local",
        "Driver": "bridge",
        "EnableIPv6": false,
        "IPAM": {
            "Driver": "default",
            "Options": {},
            "Config": [
                {
                    "Subnet": "172.18.0.0/16",
                    "Gateway": "172.18.0.1"
                }
            ]
        },
        "Internal": false,
        "Attachable": false,
        "Ingress": false,
        "ConfigFrom": {
            "Network": ""
        },
        "ConfigOnly": false,
        "Containers": {
            "119b4767f4ec8fc7b5d8adcaab1a71999df2f79b32a02f8ba1a66270c7531a70": {
                "Name": "server",
                "EndpointID": "4c8cb2a54a82b0092dd677cdf8ce9264812b4f3e31bef59c723a085928cb0441",
                "MacAddress": "02:42:ac:12:00:03",
                "IPv4Address": "172.18.0.3/16",
                "IPv6Address": ""
            },
            "c55c205642ebe8f5eb511af071a6f3183277d871ed4b66df9e00fe53e6eb9c54": {
                "Name": "sso",
                "EndpointID": "aff4f8ddfea45d718d73ba609f035ac871d3633ee7c47d318cb84a757f92e9ed",
                "MacAddress": "02:42:ac:12:00:02",
                "IPv4Address": "172.18.0.2/16",
                "IPv6Address": ""
            },
            "cf80d38ab854f9d8fdb66129632a38fce575fc47de1044088871ff8a6e67016a": {
                "Name": "gateway",
                "EndpointID": "c98bd162d5383ff3e46753c0c9c4101a87dd189af8c0144bf999b64cf63691be",
                "MacAddress": "02:42:ac:12:00:04",
                "IPv4Address": "172.18.0.4/16",
                "IPv6Address": ""
            }
        },
        "Options": {},
        "Labels": {}
    }
]

I need to connect from the sso container to PostgreSQL instance on the host machine localhost:5432 (just default).

Below is pg_hba.conf:

local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

NB! PostgreSQL is not available from outer network (it was so by default), if I will connect to my VM by IP address, the connection will be refused (I need it, I don't want to my database was visible for the Internet).

I run sso container by the next command:

docker run --name sso \
  --network my-net \
  --add-host host.docker.internal:host-gateway \
  -e DB_URL=jdbc:postgresql://host.docker.internal:5432/sso?user=my_user&password=my_password \
  -d sso:latest

Also I've tested a connection to my database from DBeaver using SSH tunnel, everything works.

However, when I send a request to sso it crashes with error:

com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: Connection to host.docker.internal:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
    at com.zaxxer.hikari.pool.HikariPool.throwPoolInitializationException(HikariPool.java:596)
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:582)
    at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:100)
    at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:81)
    at com.kleinstein.sso.data.gateways.db.DatabaseGateway.<init>(DatabaseGateway.kt:27)
    at com.kleinstein.sso.DependencyInjectionKt.initDatabase(DependencyInjection.kt:47)
    at com.kleinstein.sso.DependencyInjectionKt$installDi$1$2$1.invoke(DependencyInjection.kt:20)
    at com.kleinstein.sso.DependencyInjectionKt$installDi$1$2$1.invoke(DependencyInjection.kt:20)
    at org.kodein.di.bindings.Singleton$getFactory$1$1$1.invoke(standardBindings.kt:134)
    at org.kodein.di.bindings.SingletonReference.make(references.kt:34)
    at org.kodein.di.bindings.Singleton$getFactory$1$1.invoke(standardBindings.kt:134)
    at org.kodein.di.bindings.Singleton$getFactory$1$1.invoke(standardBindings.kt:134)
    at org.kodein.di.bindings.StandardScopeRegistry.getOrCreate(scopes.kt:66)
    at org.kodein.di.bindings.Singleton$getFactory$1.invoke(standardBindings.kt:134)
    at org.kodein.di.bindings.Singleton$getFactory$1.invoke(standardBindings.kt:131)
    at org.kodein.di.DIContainer$DefaultImpls$provider$$inlined$toProvider$1.invoke(curry.kt:14)
    at org.kodein.di.internal.DirectDIBaseImpl.Instance(DirectDIImpl.kt:30)
    at com.kleinstein.sso.DependencyInjectionKt$installDi$1$5$1.invoke(DependencyInjection.kt:67)
    at com.kleinstein.sso.DependencyInjectionKt$installDi$1$5$1.invoke(DependencyInjection.kt:23)
    at org.kodein.di.bindings.Provider$getFactory$1.invoke(standardBindings.kt:89)
    at org.kodein.di.bindings.Provider$getFactory$1.invoke(standardBindings.kt:89)
    at org.kodein.di.DIContainer$DefaultImpls$provider$$inlined$toProvider$1.invoke(curry.kt:14)
    at org.kodein.di.DIAwareKt$Instance$1.invoke(DIAware.kt:209)
    at org.kodein.di.DIAwareKt$Instance$1.invoke(DIAware.kt:207)
    at org.kodein.di.DIProperty$provideDelegate$1.invoke(properties.kt:57)
    at kotlin.SynchronizedLazyImpl.getValue(LazyJVM.kt:74)
    at com.kleinstein.sso.presentation.handlers.AuthenticationHandlersKt.installAuthHandlers$lambda-1(AuthenticationHandlers.kt:18)
    at com.kleinstein.sso.presentation.handlers.AuthenticationHandlersKt.access$installAuthHandlers$lambda-1(AuthenticationHandlers.kt:1)
    at com.kleinstein.sso.presentation.handlers.AuthenticationHandlersKt$installAuthHandlers$1$1$1.invokeSuspend(AuthenticationHandlers.kt:26)
    at com.kleinstein.sso.presentation.handlers.AuthenticationHandlersKt$installAuthHandlers$1$1$1.invoke(AuthenticationHandlers.kt)
    at com.kleinstein.sso.presentation.handlers.AuthenticationHandlersKt$installAuthHandlers$1$1$1.invoke(AuthenticationHandlers.kt)
    at io.ktor.auth.BasicAuthKt$basic$1.invokeSuspend(BasicAuth.kt:81)
    at io.ktor.auth.BasicAuthKt$basic$1.invoke(BasicAuth.kt)
    at io.ktor.auth.BasicAuthKt$basic$1.invoke(BasicAuth.kt)
    at io.ktor.util.pipeline.SuspendFunctionGun.loop(SuspendFunctionGun.kt:248)
    at io.ktor.util.pipeline.SuspendFunctionGun.proceed(SuspendFunctionGun.kt:116)
    at io.ktor.util.pipeline.SuspendFunctionGun.execute(SuspendFunctionGun.kt:136)
    at io.ktor.util.pipeline.Pipeline.execute(Pipeline.kt:78)
    at io.ktor.auth.Authentication.processAuthentication(Authentication.kt:235)
    at io.ktor.auth.Authentication.access$processAuthentication(Authentication.kt:19)
    at io.ktor.auth.Authentication$interceptPipeline$2.invokeSuspend(Authentication.kt:125)
    at io.ktor.auth.Authentication$interceptPipeline$2.invoke(Authentication.kt)
    at io.ktor.auth.Authentication$interceptPipeline$2.invoke(Authentication.kt)
    at io.ktor.util.pipeline.SuspendFunctionGun.loop(SuspendFunctionGun.kt:248)
    at io.ktor.util.pipeline.SuspendFunctionGun.proceed(SuspendFunctionGun.kt:116)
    at io.ktor.util.pipeline.SuspendFunctionGun.execute(SuspendFunctionGun.kt:136)
    at io.ktor.util.pipeline.Pipeline.execute(Pipeline.kt:78)
    at io.ktor.routing.Routing.executeResult(Routing.kt:155)
    at io.ktor.routing.Routing.interceptor(Routing.kt:39)
    at io.ktor.routing.Routing$Feature$install$1.invokeSuspend(Routing.kt:107)
    at io.ktor.routing.Routing$Feature$install$1.invoke(Routing.kt)
    at io.ktor.routing.Routing$Feature$install$1.invoke(Routing.kt)
    at io.ktor.util.pipeline.SuspendFunctionGun.loop(SuspendFunctionGun.kt:248)
    at io.ktor.util.pipeline.SuspendFunctionGun.proceed(SuspendFunctionGun.kt:116)
    at io.ktor.features.ContentNegotiation$Feature$install$1.invokeSuspend(ContentNegotiation.kt:145)
    at io.ktor.features.ContentNegotiation$Feature$install$1.invoke(ContentNegotiation.kt)
    at io.ktor.features.ContentNegotiation$Feature$install$1.invoke(ContentNegotiation.kt)
    at io.ktor.util.pipeline.SuspendFunctionGun.loop(SuspendFunctionGun.kt:248)
    at io.ktor.util.pipeline.SuspendFunctionGun.proceed(SuspendFunctionGun.kt:116)
    at io.ktor.util.pipeline.SuspendFunctionGun.execute(SuspendFunctionGun.kt:136)
    at io.ktor.util.pipeline.Pipeline.execute(Pipeline.kt:78)
    at io.ktor.server.engine.DefaultEnginePipelineKt$defaultEnginePipeline$2.invokeSuspend(DefaultEnginePipeline.kt:127)
    at io.ktor.server.engine.DefaultEnginePipelineKt$defaultEnginePipeline$2.invoke(DefaultEnginePipeline.kt)
    at io.ktor.server.engine.DefaultEnginePipelineKt$defaultEnginePipeline$2.invoke(DefaultEnginePipeline.kt)
    at io.ktor.util.pipeline.SuspendFunctionGun.loop(SuspendFunctionGun.kt:248)
    at io.ktor.util.pipeline.SuspendFunctionGun.proceed(SuspendFunctionGun.kt:116)
    at io.ktor.util.pipeline.SuspendFunctionGun.execute(SuspendFunctionGun.kt:136)
    at io.ktor.util.pipeline.Pipeline.execute(Pipeline.kt:78)
    at io.ktor.server.netty.NettyApplicationCallHandler$handleRequest$1.invokeSuspend(NettyApplicationCallHandler.kt:123)
    at io.ktor.server.netty.NettyApplicationCallHandler$handleRequest$1.invoke(NettyApplicationCallHandler.kt)
    at io.ktor.server.netty.NettyApplicationCallHandler$handleRequest$1.invoke(NettyApplicationCallHandler.kt)
    at kotlinx.coroutines.intrinsics.UndispatchedKt.startCoroutineUndispatched(Undispatched.kt:55)
    at kotlinx.coroutines.CoroutineStart.invoke(CoroutineStart.kt:112)
    at kotlinx.coroutines.AbstractCoroutine.start(AbstractCoroutine.kt:126)
    at kotlinx.coroutines.BuildersKt__Builders_commonKt.launch(Builders.common.kt:56)
    at kotlinx.coroutines.BuildersKt.launch(Unknown Source)
    at io.ktor.server.netty.NettyApplicationCallHandler.handleRequest(NettyApplicationCallHandler.kt:43)
    at io.ktor.server.netty.NettyApplicationCallHandler.channelRead(NettyApplicationCallHandler.kt:34)
    at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
    at io.netty.channel.AbstractChannelHandlerContext.access$600(AbstractChannelHandlerContext.java:61)
    at io.netty.channel.AbstractChannelHandlerContext$7.run(AbstractChannelHandlerContext.java:370)
    at io.netty.util.concurrent.AbstractEventExecutor.safeExecute(AbstractEventExecutor.java:164)
    at io.netty.util.concurrent.SingleThreadEventExecutor.runAllTasks(SingleThreadEventExecutor.java:469)
    at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:503)
    at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:986)
    at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74)
    at io.ktor.server.netty.EventLoopGroupProxy$Companion.create$lambda-1$lambda-0(NettyApplicationEngine.kt:251)
    at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
    at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: org.postgresql.util.PSQLException: Connection to host.docker.internal:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:319)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
    at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:223)
    at org.postgresql.Driver.makeConnection(Driver.java:400)
    at org.postgresql.Driver.connect(Driver.java:259)
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:121)
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:359)
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:201)
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:470)
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:561)
    ... 87 common frames omitted
Caused by: java.net.ConnectException: Connection refused
    at java.base/sun.nio.ch.Net.pollConnect(Native Method)
    at java.base/sun.nio.ch.Net.pollConnectNow(Net.java:672)
    at java.base/sun.nio.ch.NioSocketImpl.timedFinishConnect(NioSocketImpl.java:542)
    at java.base/sun.nio.ch.NioSocketImpl.connect(NioSocketImpl.java:597)
    at java.base/java.net.SocksSocketImpl.connect(SocksSocketImpl.java:327)
    at java.base/java.net.Socket.connect(Socket.java:633)
    at org.postgresql.core.PGStream.createSocket(PGStream.java:241)
    at org.postgresql.core.PGStream.<init>(PGStream.java:98)
    at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:109)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:235)
    ... 96 common frames omitted

Where can the problem be? Unfortunately, I am bad in networking.

P.S. If PostgreSQL is yet one Docker container, all works, also everything works on my local machine (without Docker).

Upvotes: 2

Views: 9933

Answers (1)

Denis Steinman
Denis Steinman

Reputation: 7799

Finally I realised how to fix my problem.

First, I had a look at my Docker network... At these strings:

"Subnet": "172.18.0.0/16",
"Gateway": "172.18.0.1"

It means Docker must define a new network interface on my host machine. Let's check by command ip address show:

98: br-d06d15cbc443: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP group default 
    link/ether 02:42:78:ab:0f:d2 brd ff:ff:ff:ff:ff:ff
    inet 172.18.0.1/16 brd 172.18.255.255 scope global br-d06d15cbc443
       valid_lft forever preferred_lft forever
    inet6 fe80::42:78ff:feab:fd2/64 scope link 
       valid_lft forever preferred_lft forever

Really, there is our interface. Well, it means we need to allow PostgreSQL to accept connections from this subnet.

There are two variants (/etc/postgresql/12/main/postgresql.conf file):

  1. Set listen_address to my-net gateway IP but I won't be able to connect using DBeaver with SSH Tunnel (it's convenient)
  2. Set listen_address to all * addresses but everyone will be able to connect to our PostgreSQL from outer network.

Fortunately, PostgreSQL has yet one configuration file: /etc/postgresql/12/main/pg_hba.conf where we can restrict allowed subnets. Great! Then set listen_address to * and edit pg_hba.conf as below:

# IPv4 local connections:
# my-net subnet
host    all             all             172.18.0.1/16           md5
# localhost to use SSH Tunnel in DBeaver
host    all             all             127.0.0.1/32            md5

Send a request to sso and Whoalya! It works!

Last step, check a direct connection to database from outer network:

FATAL: no pg_hba.conf entry for host "xx.xx.xx.xx", user "my_user", database "sso", SSL on

Profit!

P.S. I am not sure if it's secure and optimal solution but it's better than to open DBMS for all Internet. I hope this answer will help a someone who is the same noobie in networking as me.

Upvotes: 2

Related Questions