Sample Header Ad - 728x90

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