Query postgres db in a Docker container from another container using peer auth

Question

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?

Answer 1

I was able to accomplish this via postgres_fdw.

This article describes the process.

Here are my steps:

-- Add postgres_fdw extension
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- Set up connection to the containerone database
CREATE SERVER IF NOT EXISTS containerone_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '/var/run/postgresql_containerone', port '5432', dbname 'containerone');

CREATE USER MAPPING IF NOT EXISTS FOR postgres
SERVER containerone_server;

IMPORT FOREIGN SCHEMA public LIMIT TO (mytable)
FROM SERVER containerone_server INTO public;

-- Give a non-superuser user access to the containerone database
-- Reference: https://www.percona.com/blog/postgresql_fdw-authentication-changes-in-postgresql-13/
CREATE USER MAPPING IF NOT EXISTS FOR nonsuperuser
SERVER containerone_server
OPTIONS(password_required 'false');

This works and I can query tables from either the own container's database or from the other container's database, with a non-superuser user, without needing password authentication.