Question 284827 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Nodejs – Trying to execute postgre sql file

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search