I want to use ansible tasks to make changes to a postgres database (ex, add user) running inside a docker container on a remote host which is only accessible via ssh.
I was only ably to make either a local docker connection or remote docker connection with exposed docker port. Which is no an option.
Is this possible at all? Should i use a different approach?
You're not going to get Ansible by itself to run tasks inside a container on a remote host. There are a few workarounds you could try:
Rather than exposting the Docker socket publically, forward it over your ssh connection. E.g:
ssh -L /tmp/docker.sock:/var/run/docker.sock remotehost
While this connection is active, you can access the remote docker over the local socket /tmp/docker.sock, e.g. by setting DOCKER_HOST=unix:///tmp/docker.sock in your environment.
This means you can use Ansible's docker connection driver to run tasks inside the remote container.
If you've got Postgres running inside a container on the remote host, you don't need to run tasks inside the container in order to talk to Postgres. With appropriate authentication, you can connect directly to postgres on the container's ip address, or to a port published on the host.
In either case, you would use Ansible's normal ssh connection driver.