CODEWITHSUNDEEP
node.jspostgresql
Architect - Hitesh
Architect - Hitesh

Reputation: 1239

Unable to store session in database in node for postgresql

I am working on Node and PostgreSql (Version 10).

I am creating a sample application that has login functionality.

After I did the login work, I found once I updated the code and rerun the node application, the login session expires and logs me out.

I found how I can store the login session in postgresql database using connect-pg-simple, but when I try to do it its showing me following error:

Failed to prune sessions: password authentication failed for user "admin"

AND

Failed to prune sessions: role "admin" does not exist

said user in error "Admin" is my system user

Where is it leaking?

Code:

const conObject = {
    host: 'localhost',
    port: 5433,
    user: 'postgres',
    password: 'kashyap2468',
    database: 'partgator'
};

const pgSession = require('connect-pg-simple')(session);

const pgStoreConfig = {
    pgPromise: require('pg-promise')({ promiseLib: require('bluebird') })({
        conObject }), // user either this
}

app.use(session({
    store: new pgSession(pgStoreConfig),
    secret: 'jW8aor76jpPX', // session secret
    resave: true,
    saveUninitialized: true,
    cookie: { maxAge: 30 * 24 * 60 * 60 * 1000 } // 30 days
}));

app.use(flash());

Upvotes: 2

Views: 3828

Answers (2)

Eduardo Garcia
Eduardo Garcia

Reputation: 35

I had same problem. In my case problem was that express-session didn't have a connection string, so the error said: 

"Failed to prune sessions: password authentication failed for user "egarcia"

This is a correct sample code:

var PostgreSqlStore = require('connect-pg-simple')(session);
var sessionOptions = {
      secret: "secret",
      resave : true,
      saveUninitialized : false,
      store : new PostgreSqlStore({
        /*
        connection string is built by following the syntax:
        postgres://USERNAME:PASSWORD@HOST_NAME:PORT/DB_NAME
        */
        conString: "postgres://postgres:postgres@localhost:5433/postgres"
      })
    };

    app.use(session(sessionOptions));

If you have user problems probably the problem is on the connection string, it's missing or incorrect.

Upvotes: 2

Architect - Hitesh
Architect - Hitesh

Reputation: 1239

Connect Session Knex

This makes my work simpler

after trying lot of things i have done with following code and issue got sorted with a same logic

Login session will be stored in sessions table itself by Knex

const session = require('express-session');
const KnexSessionStore = require('connect-session-knex')(session);
const Knex = require('knex');

const db = require('./knexfile');
global.knex = Knex(db[environment]);


const store = new KnexSessionStore({
    knex: knex,
    tablename: 'sessions' // optional. Defaults to 'sessions'
});

app.use(session({
    secret: 'keyboard cat',
    store: store,
    resave: false,
    saveUninitialized: true,
    cookie: {
        maxAge: 365 * 24 * 60 * 60 * 1000,
        expires: false
    }
}));

Upvotes: -1

Related Questions