I'm developing a NodeJS/Express application which need to exchange information with a Postgres database for my Cybersecurity exam; both the Node app and the Postgres database will be living in a docker container for them self using docker compose and a common network to exchange data.
The problem: Seems I cannot manage to establish a connection with the npm PG client (Vers. 8+) to the database hosted on the docker container. Each time I run the connection procedure from a JS file I get the following error:
node .\test.mjs
connect ECONNREFUSED 127.0.0.1:5432
The testing environment: for now I'm trying to establish a connection from a test Node app on my local machine with the local installation of NodeJS, meanwhile the docker container holds the database built with a compose file(Docker is being executed on the same machine).
The test.mjs for connection testing:
import pg from "pg";
const client = new pg.Client({
user: "db_root",
password: "root",
host: "localhost",
port: 5432,
});
client
.connect()
.then(() => {
console.log("[INFO]: DB CONNECTION SUCCESSFUL");
client.query("SELECT NOW()").then((res) => {
console.log(res.rows);
client.end();
});
})
.catch((err) => {
console.log("[ERROR] " + err);
});
This is the docker compose file which builds the database, there are few scripts SQL in a folder called db which are executed during the building of the container, they are used to create some tables, user with privileges and access to some tables and some table population scripts.
version: "3.9"
services:
frontend:
build: .
container_name: node-server
restart: unless-stopped
depends_on:
- database
ports:
- "3000:3000"
- "3443:3443"
networks:
- my-net
database:
image: postgres:latest
container_name: postgredb
restart: unless-stopped
networks:
- my-net
environment:
- POSTGRES_USER=db_root
- POSTGRES_PASSWORD=root
- POSTGRES_DB=securedb
expose:
- 5432
volumes:
- ./db:/docker-entrypoint-initdb.d
admin:
image: adminer
restart: unless-stopped
ports:
- "8080:8080"
networks:
- my-net
networks:
my-net:
driver: bridge
This is the docker container log:
postgredb | The files belonging to this database system will be owned by user "postgres".
postgredb | This user must also own the server process.
postgredb |
postgredb | The database cluster will be initialized with locale "en_US.utf8".
postgredb | The default database encoding has accordingly been set to "UTF8".
postgredb | The default text search configuration will be set to "english".
postgredb |
postgredb | Data page checksums are disabled.
postgredb |
postgredb | fixing permissions on existing directory /var/lib/postgresql/data ... ok
postgredb | creating subdirectories ... ok
postgredb | selecting dynamic shared memory implementation ... posix
postgredb | selecting default max_connections ... 100
postgredb | selecting default shared_buffers ... 128MB
postgredb | selecting default time zone ... Etc/UTC
postgredb | creating configuration files ... ok
postgredb | running bootstrap script ... ok
postgredb | performing post-bootstrap initialization ... ok
postgredb | initdb: warning: enabling "trust" authentication for local connections
postgredb | You can change this by editing pg_hba.conf or using the option -A, or
postgredb | --auth-local and --auth-host, the next time you run initdb.
postgredb | syncing data to disk ... ok
postgredb |
postgredb |
postgredb | Success. You can now start the database server using:
postgredb |
postgredb | pg_ctl -D /var/lib/postgresql/data -l logfile start
postgredb |
postgredb | waiting for server to start....2022-05-25 15:24:12.850 UTC [48] LOG: starting PostgreSQL 14.3 (Debian 14.3-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
postgredb | 2022-05-25 15:24:12.853 UTC [48] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgredb | 2022-05-25 15:24:12.862 UTC [49] LOG: database system was shut down at 2022-05-25 15:24:12 UTC
postgredb | 2022-05-25 15:24:12.867 UTC [48] LOG: database system is ready to accept connections
postgredb | done
postgredb | server started
postgredb | CREATE DATABASE
postgredb |
postgredb |
postgredb | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/createTable.sql
postgredb | CREATE TABLE
postgredb | CREATE TABLE
postgredb | CREATE TABLE
postgredb |
postgredb |
postgredb | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/createUser.sql
postgredb | CREATE ROLE
postgredb | CREATE ROLE
postgredb | GRANT
postgredb | GRANT
postgredb | GRANT
postgredb | GRANT
postgredb | GRANT
postgredb | GRANT
postgredb |
postgredb |
postgredb | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/populateTable.sql
postgredb | INSERT 0 1
postgredb | INSERT 0 1
postgredb | INSERT 0 1
postgredb | INSERT 0 1
postgredb | INSERT 0 1
postgredb | INSERT 0 1
postgredb | INSERT 0 1
postgredb | INSERT 0 1
postgredb | INSERT 0 1
postgredb | INSERT 0 1
postgredb |
postgredb |
postgredb | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/setTimezone.sql
postgredb | SET
postgredb |
postgredb |
postgredb | 2022-05-25 15:24:13.317 UTC [48] LOG: received fast shutdown request
postgredb | waiting for server to shut down....2022-05-25 15:24:13.321 UTC [48] LOG: aborting any active transactions
postgredb | 2022-05-25 15:24:13.322 UTC [48] LOG: background worker "logical replication launcher" (PID 55) exited with exit code 1
postgredb | 2022-05-25 15:24:13.322 UTC [50] LOG: shutting down
postgredb | 2022-05-25 15:24:13.358 UTC [48] LOG: database system is shut down
postgredb | done
postgredb | server stopped
postgredb |
postgredb | PostgreSQL init process complete; ready for start up.
postgredb |
postgredb | 2022-05-25 15:24:13.435 UTC [1] LOG: starting PostgreSQL 14.3 (Debian 14.3-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
postgredb | 2022-05-25 15:24:13.435 UTC [1] LOG: listening on IPv4 address "0.0.0.0", port 5432
postgredb | 2022-05-25 15:24:13.435 UTC [1] LOG: listening on IPv6 address "::", port 5432
postgredb | 2022-05-25 15:24:13.440 UTC [1] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgredb | 2022-05-25 15:24:13.446 UTC [70] LOG: database system was shut down at 2022-05-25 15:24:13 UTC
postgredb | 2022-05-25 15:24:13.451 UTC [1] LOG: database system is ready to accept connections
Since I also have a container with Adminer I tested all my user and root logins using the browser GUI and it can make connections fine , with the only difference that I don't use localhost for Adminer but the container name for host field since they are on two different docker container within the same network, but at least I know that is not a "password"/"user" misspelling or problem with SQL scripts at least.
Started from scratch: Since I could not understand where the problem could reside I tried to start from Zero, I've created an empty folder somewhere else on my machine, created a new docker compose and index.js file for testing , installed only the module pg with NPM, built the container from compose and the connection from a node local machine works( and it angers me much).
The index.js:
const pg = require("pg");
const client = new pg.Client({
user: "admin",
password: "password",
database: "mydb",
port: 5432,
host: "localhost",
});
client
.connect()
.then(() => {
console.log("Connected to database");
client.query("SELECT NOW()", (err, res) => {
console.log(res.rows);
client.end();
if (err) {
console.log(err);
}
});
})
.catch((err) => {
console.log(err);
});
Docker compose file:
version: "3.9"
services:
database:
image: "postgres:latest"
container_name: postgresdatabase
ports:
- 5432:5432
restart: unless-stopped
environment:
POSTGRES_USER: admin
POSTGRES_PASSWORD: password
POSTGRES_DB: mydb
volumes:
- ./user.sql:/docker-entrypoint-initdb.d/user.sql
networks:
- default
node:
build: .
container_name: node
ports:
- 3000:3000
depends_on:
- database
networks:
- default
networks:
default:
driver: bridge
Shell result:
node .\index.js
Connected to database
[ { now: 2022-05-25T16:21:44.591Z } ]