How to access Postgres DB used by apps?

Apps and Virtualization
1

Using Community Edition 25.04.2.6

I have several apps installed that use a Postgres DB, by default:

  • home-assistant
  • immich
  • n8n
  • nextcloud
  • vaultwarden

My question is, how can I access any of these DBs from a local machine on my network?

I have been able to search online for help and have made some progress. I was able to find out that I can pull up the container shell in the dashboard and type ‘env’ and get the DB information needed (this one is from home-assistant, for example):

  • PGPORT=5432
  • HOSTNAME=009367e4b9c1
  • USER_ID=568
  • HOME=/var/lib/postgresql
  • UMASK_SET=002
  • PG_VERSION=18.1-1.pgdg13+2
  • PGID=568
  • GROUP_ID=568
  • TERM=xterm
  • POSTGRES_PASSWORD=********
  • PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/lib/postgresql/18/bin
  • GID=568
  • POSTGRES_USER=home-assistant
  • LANG=en_US.utf8
  • UID=568
  • PUID=568
  • UMASK=002
  • PG_MAJOR=18
  • GOSU_VERSION=1.19
  • PWD=/
  • POSTGRES_DB=home-assistant
  • PGDATA=/var/lib/postgresql/18/docker
  • TZ=America/Chicago
  • NVIDIA_VISIBLE_DEVICES=void

However, when I try to connect on my local network with my laptop to the DB using DBeaver Community Edition and a PostgreSQL connection, using the information discovered in the app container shell, I get the following error:

Connection to truenas.local:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
Connection refused: getsockopt

I did also find some other information online regarding modifying a few .conf files, like so:

/mnt/rustysea/apps/home_assistant/postgres/18/docker/postgresql.conf

listen_addresses = ‘*’

/mnt/rustysea/apps/home_assistant/postgres/18/docker/pg_hba.conf

host all all 192.168.8.0/24 md5

No change, still get the error after restarting postgres with the app container shell.

I believe I’m running into a firewall or port issue in TrueNAS itself, as I get a failure on my laptop when using PowerShell 7:

PS C:\Users\> Test-NetConnection -ComputerName truenas.local -Port 5432
WARNING: TCP connect to (192.168.8.5 : 5432) failed

ComputerName : truenas.local
RemoteAddress : 192.168.8.5
RemotePort : 5432
InterfaceAlias : Wi-Fi
SourceAddress : 192.168.8.144
PingSucceeded : True
PingReplyDetails (RTT) : 2 ms
TcpTestSucceeded : False

My confusion is that my laptop is on the same network and subnet as my TrueNAS server, and there shouldn’t be a firewall issue.

Does anyone have any other ideas or suggestions?

1 Like
2

Don’t have an answer for accessing it locally, but if you’re ok with an app from the catalogue you can try PgAdmin

3

Basically you can’t access them directly without some trickery, since the ports for each DB container aren’t exposed.

What you could do is run something like PGadmin or another container and then use Portainer and hit up the Networking section and add that container to each app and you should be able to access them

1 Like
4

Really, you can’t. Those databases are internal to the apps they serve, and aren’t exposed to the rest of your network.

Why are you wanting to do this? What is it you’re hoping to accomplish?

1 Like
5

More or less, I was just curious to see if I could access the DB’s raw data.

Thanks everyone for your replies and ideas.

1 Like
6

This can be done now with the Networks feature that apps have recently gotten this month, you can attach the DB to a different network using the GUI now, so my advice above still applies, just now it’s supported by the TrueNAS GUI as well now

1 Like