Query postgres db in a Docker container from another container using peer auth
-1
votes
1
answer
386
views
I have two Docker containers with a Postgres db in each. I am exposing the Postgres socket of each container to the other one, using Docker volumes, like so:
docker run --rm -itd --name=containerone -p 127.0.0.1:3003:3000 -v containeronepgsocket:/var/run/postgresql -v containertwopgsocket:/var/run/postgresql_containertwo registry.gitlab.com/mycont/mycont
The above makes it so that in the container I have the regular socket /var/run/postgresql
, as well as the socket /var/run/postgresql_containertwo
My objective is to be able to query each container from within the other one, by using the Postgres peer authentication method (i.e. no need for a password).
I have managed to accomplish this when querying through javascript. Here is my code, using the Sequelize package from within container one to query container two:
const sequelize_containertwo = new Sequelize('containertwo', 'myuser', undefined, {
host: '/var/run/postgresql_containertwo',
dialect: 'postgres'
});
As you can see, the password is left as undefined. As long as the socket is in the OS, and I am using the right OS user, the peer auth method works and I am able to query the other container.
But I also have PL/pgSQL functions and I need to query the other container from within those functions. And this is where I am hitting a problem. From within the functions, I am querying the same container but I also need to query data from the other container.
To accomplish this, my first thought was to use dblink. I tried to make something like this:
SELECT *
FROM dblink('postgresql:///containertwo_db?host=/var/run/postgresql_containertwo',
SELECT mycol
FROM mytable
)
AS t1(mycol BIGINT);
This provides the host as a parameter in the connection string. I have already had success connecting to the socket using exactly this connection string but from the docker_entrypoint file. When I try from within dblink, I get the error that a password is required.
How can I query the other container from within a PL/pgSQL function and using peer authentication? Can I do it through dblink, and if so, how? Or is there some other way?
Asked by Borislav Zlatanov
(109 rep)
Apr 14, 2024, 02:20 PM
Last activity: Apr 27, 2024, 10:13 AM
Last activity: Apr 27, 2024, 10:13 AM