Nodejs - Trying to execute postgre sql file

Question

I'm trying to run the following command via nodejs:

psql -U postgres -d dbName -f import.sql

But I got the following prompt in the console:

Password for user postgres:

I tried to set the password via environnment variable, like this:

SET PGPASSWORD=mypassword && psql -U postgres -d dbName -f import.sql

But for some reason I get the following error:

psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL: password authentication failed for user "postgres"

I tripled check and it is the correct password, I also tried to change the password to use something without any special characters, suspecting an encoding issue, but still fails.

Then I tried with node-postgres, like this:

const file = (await readFile("./download/import.sql")).toString();

const pool = new Pool({
  user: "postgres",
  host: "localhost",
  database: "dbName",
  password: "myPassword",
  port: 5432,
});

await pool.query(file);

But it fails because my script contains lines like this: \copy tableA from 'db/tableA' and I have no idea how to solve this.

Next, I tried with spawn, like this:

const child = spawn("psql", ["-h", "localhost", "-U", "postgres", "-d", "dbName", "-f", "./download/import.sql"]);

child.stdin.write("myPassword");
child.stdin.end();

But I can still see the prompt asking for a password.

I'm running out of idea

EDIT: I'm using Windows

Answer 1

I found a solution!

It works if I use the connection string to connect:

execSync(`psql -f ./download/import.sql "postgresql://postgres:myPassword@localhost/dbName"`);

Here is the format: postgresql://<username>:<password>@<server>/<db>

Answer 2

If the password you are using is correct and the psql command works when you enter the password at the prompt, maybe the problem is with the PGPASSWORD not beeing correctly set.

on the command line, try (no SET, no &&):

PGPASSWORD='mypassword' psql -U postgres -d dbName -f import.sql

this is the usual way for me to pass a tmp env variable to a script. This could need some more escaping if you have single quotes in your password.

Answer 3

Postgres / libpq has a feature that allows you to put the credentials used by psql in a specific ".pgpass" file

cf https://www.postgresql.org/docs/current/libpq-pgpass.html

this could help you at the price of creating a file with specific user rights on the filesystem.

Depending on the project and the security needed around the credentials, I advise you to

• create the file before usage, and remove after the psql operation is finished
• create a specific postgres role/user for this task with constrained rights. this user could be created / deleted before and after the operation

Note what the documentation says otherwise it will not work:

On Unix systems, the permissions on a password file must disallow any access to world or group; achieve this by a command such as chmod 0600 ~/.pgpass. If the permissions are less strict than this, the file will be ignored. On Microsoft Windows, it is assumed that the file is stored in a directory that is secure, so no special permissions check is made.