Solved Can I access a postgres database of an app from outside?

[devtron]

Curious to know if this is possible? I am using a directus instance which is using a postgres database. Would be awesome to change and look in the postgres directly from a client or a terminal.

[doodlemania2]

@devtron terminal, yes, via the terminal app on an application. External to the app, not at present.

[devtron]

@doodlemania2 That's a shame, do you or anyone know when it will be possible or if it ever will be?

[doodlemania2]

@devtron up to the developers of course. It's technically possible to do it with a number of really complex workarounds involving port forwarding, but just hasn't been much of a demand for it I suspect.

[girish]

@devtron Depends on the app / client. For example, with mysql workbench , this is quite straight forward because it supports SSH tunneling . Please see https://forum.cloudron.io/topic/3237/remote-sql-support-on-a-per-app-basis/2 . Which client will you be using for postgresql ?

[girish]

pgadmin seems to support this - https://fedingo.com/pgadmin-connect-via-ssh-tunnel/

In general, the good tools should support SSH tunnels, since databases are almost never straight up exposed to the internet. They are in a VPC/Private network and you SSH/VPN into a bastion host.

[devtron]

@girish So the directus (tool) should support ssh tunnel or do you mean cloudron?

[girish]

@devtron neither. I thought you wanted to access the database that directus uses from outside? If so, what tool are you going to use from the outside to connect? That tool should support ssh tunneling.

[devtron]

@girish Ahh You mean the client (tool), where I connect to the database. I want to use TablePlus and I am sure it has ssh tunneling.

[shrey]

Hey @devtron .

I was curious about it too, so just tried the same using DBeaver.

I was able to successfully access Directus' Postgres database by:

1. configuring SSH connection to the server that Cloudron is hosted on
2. using:

• host = value gotten from the host server by using `docker inspect postgresql | grep IPAddress
• database = value gotten from Directus' 'terminal' (echo $CLOUDRON_POSTGRESQL_DATABASE)
• username = value gotten from Directus' 'terminal' (echo $CLOUDRON_POSTGRESQL_USERNAME)
• password = value gotten from Directus' 'terminal' (echo $CLOUDRON_POSTGRESQL_PASSWORD)

[girish]

@shrey Thanks, that motivated me to get started with a guide.

I wrote the mysql guide first - https://docs.cloudron.io/guides/connect-mysql/ . Working on the postgres one (but it's really very similar).

[girish]

PostgreSQL guide is up as well - https://docs.cloudron.io/guides/connect-postgresql/

[devtron]

Thanks @shrey and @girish for the guides

[devtron]

In my case, it also works with table plus

[shrey]

Hi @girish .

I do have some feedback regarding the currently suggested method, as described in these new guides.

After using this method for quite a few days, finding the internal ip address of the db containers has almost become the Achilles heel for any operation that uses this feature. That's because, the internal ip address keeps on changing, every now and then.

And so far, i haven't been able to identify a way to connect to the db by a static name/ip/uri.

Would appreciate if a solution for that is posted as well!

Thanks.

[girish]

@shrey that's a good point. I think docker can give those containers a different IP on each restart. For the app containers, we solved this by giving them static IPs. Maybe we should do the same for addons then.

[robi]

@girish ideally an internal DNS mapping if dynamic, and an IP pattern to use if static, both of which should be viewable from the UI as needed.

[girish]

@robi it already has internal names which is how apps access them with the current dynamic IP situation.

[girish]

I have given these stable IPs now - https://git.cloudron.io/cloudron/box/-/commit/582994b9d646989b42e27dde1ae0c27bbb964834