Postgresql LXD Setup

i have installed postgresql in using lxd. how can i setup the interface to access in my host machine using table plus

Hi Andrew,

How have you setup your network? This will help answer the question how to setup connecting from your host machine to the LXD based image… namely because depending on your network setup you may need to forward a port and also open the postgres server to connections outside of itself (127.0.0.1)

I personally just install LXD and then Juju, bootstraping juju then allows me to use

juju deploy postgres

I made a network bridge in LXD which gets IP addresses from the routers NAT DHCP system… so from the host I just connect to the ip address shown to me by juju status … I do not know what the LXD command would be but maybe lxc list

Hi,
You can reach postgresql from your host to make a proxy definition, please follow the intructions.
https://blog.simos.info/how-to-use-the-lxd-proxy-device-to-map-ports-between-the-host-and-the-containers/
Thanks.

If you’re using the standard lxdbr0 private bridge, then as long as you’ve setup postresql to listen on its eth0 interface, then you should be able to connect to it from the LXD host using the IP shown in lxc ls <instance>.

Hi tomp
I managed to edit this file
“vi /etc/postgresql/12/main/postgresql.conf” for its listen_addresses = ‘*’
also the following file

vi /etc/postgresql/12/main/pg_hba.conf

IPv4 local connections:

“host all all all md5” from “host all all 127.0.0.1/32 md5”

then i was getting a connection ok status from my host

Use ss -tlpn inside the container to check its not just listening on 127.0.0.1

Oh, right, sorry, so does that mean its working now?

also guys i did spin up an lxd alpine container but when i do ssh from vc-code. The vscode tries to download the vscode server but it cannot unzip the tar.gz file downloaded but i have no issues with the lxc ubuntu container

ss -tlpn

LISTEN 0 4096 127.0.0.53%lo:53 0.0.0.0:* users:((“systemd-resolve”,pid=166,fd=13))

LISTEN 0 244 0.0.0.0:5432 0.0.0.0:* users:((“postgres”,pid=1667,fd=3))

LISTEN 0 244 [::]:5432 [::]:* users:((“postgres”,pid=1667,fd=4))

**

so apparently ist working but is it the right way to configure it??

**

You’ve not yet answered my question or @emcp question on how your network is configured, but assuming it is using the default private lxdbr0 bridge, then this means your postgresql server is only exposed to other instances connected to the lxdbr0 bridge and your LXD host itself.

If that is what you want to do then that would seem to be fine.

If on the other hand you’re using a different network config, such that eth0 in your container is connected to an external untrusted network, then exposing postgresql on that interface may not be a good idea.

In which case you could go with @cemzafer suggestion of changing postresql to only listen on 127.0.0.1 and then setup a proxy device that listens on a specific IP and port on the LXD host (possibly 127.0.0.1 also) and forwards the connectons to 127.0.0.1 inside your container.

i created the container using this profile:-
config:
user.user-data: |
run-cmd:
- ‘mkdir /postgresql’
description: Postgresql LXD profile
devices:
eth0:
name: eth0
network: lxdbr0
type: nic
postgresql:
path: /postgresql
source: /opt/postgresql
type: disk
root:
path: /
pool: default
type: disk
name: postgresql
used_by:

  • /1.0/instances/postgresql