After Google sign in with custom Postgres adapter I'm still unauthenticated

[craig-bowers]

Question 💬

I used this as a starting point to set up my NextAuth Postgres adapter, but I made a few changes to use pg-promise instead of pg and JavaScript instead of TS.

I am running into an issue though. When I use my custom adapter and try signing into the site via GoogleProvider I'm getting a new database entry into each of my users, accounts, and sessions tables. However, the next-auth.session-token cookie is not getting set when I'm redirected back to http://localhost:3000 and when I try using useSession() on the front-end the status says I'm unauthenticated.

When I bypass the adapter it lets me log in just fine, the next-auth.session-token cookie is set, and I can use useSession() to see my Google email, image, etc., and it tells me that I'm authenticated. Obviously none of the data makes its way into Postgres with it bypassed though.
// adapter: PostgresAdapter(db),

Any idea on what the problem might be?

How to reproduce ☕️

[...nextauth].js

import NextAuth from 'next-auth';
import GoogleProvider from 'next-auth/providers/google';
import PostgresAdapter from '../../../common/utils/pgp/nextauth/adapter';

import { getDB } from '../../../common/utils/pgp';
const { db } = getDB();

export const authOptions = {
  debug: process.env.NODE_ENV !== 'production',
  providers: [
    GoogleProvider({
      clientId: process.env.GOOGLE_ID,
      clientSecret: process.env.GOOGLE_SECRET,
    }),
  ],
  secret: process.env.NEXTAUTH_SECRET,
  adapter: PostgresAdapter(db),
};

export default NextAuth(authOptions);

adapter.js

export function mapExpiresAt(account) {
  const expires_at = parseInt(account.expires_at);
  return {
    ...account,
    expires_at,
  };
}

export default function PostgresAdapter(db) {
  return {
    async createVerificationToken(verificationToken) {
      try {
        const { identifier, expires, token } = verificationToken;
        await db.none(
          `
          INSERT INTO verification_tokens
          (identifier, expires, token)
          VALUES ($/identifier/, $/expires/, $/token/);`,
          {
            identifier: identifier,
            expires: expires,
            token: token,
          }
        );
        return verificationToken;
      } catch (err) {
        console.error(err);
        return;
      }
    },
    async useVerificationToken({ identifier, token }) {
      try {
        let deleted = await db.oneOrNone(
          `
          DELETE FROM verification_tokens
          WHERE identifier = $1 and token = $2
          RETURNING identifier, expires, token;`,
          [identifier, token]
        );
        return deleted;
      } catch (err) {
        console.error(err);
        return;
      }
    },
    async createUser(user) {
      try {
        let newUser = await db.one(
          `
          INSERT INTO users
          (name, email, "emailVerified", image, display_name)
          VALUES ($/name/, $/email/, $/emailVerified/, $/image/, $/name/)
          RETURNING id, name, email, "emailVerified", image;`,
          {
            name: user.name,
            email: user.email,
            emailVerified: user.emailVerified,
            image: user.image,
          }
        );
        return newUser;
      } catch (err) {
        console.error(err);
        return;
      }
    },
    async getUser(id) {
      try {
        let user = await db.oneOrNone(
          `
          SELECT *
          FROM users
          WHERE id = $1;`,
          [id]
        );
        return user;
      } catch (err) {
        console.error(err);
        return;
      }
    },
    async getUserByEmail(email) {
      try {
        let user = await db.oneOrNone(
          `
          SELECT *
          FROM users
          WHERE email = $1;`,
          [email]
        );
        return user;
      } catch (err) {
        console.error(err);
        return;
      }
    },
    async getUserByAccount({ providerAccountId, provider }) {
      try {
        let user = await db.oneOrNone(
          `
          SELECT users.*
          FROM users
          JOIN accounts ON users.id = accounts."userId"
          WHERE accounts.provider = $1
            AND accounts."providerAccountId" = $2`,
          [provider, providerAccountId]
        );
        return user;
      } catch (err) {
        console.error(err);
      }
    },
    async updateUser(user) {
      try {
        await db.task(async (t) => {
          let oldUser = await t.oneOrNone(
            `
            SELECT *
            FROM users
            WHERE id = $1;`,
            [user.id]
          );
          const newUser = {
            ...oldUser,
            ...user,
          };
          const { id, name, email, emailVerified, image } = newUser;
          let updatedUser = await t.oneOrNone(
            `
            UPDATE users
            SET name = $/name/,
            email = $/email/,
            "emailVerified" = $/emailVerified/,
            image = $/image/
            WHERE id = $/id/
            RETURNING name, email, "emailVerified", image;`,
            {
              name: name,
              email: email,
              emailVerified: emailVerified,
              image: image,
              id: id,
            }
          );
          return updatedUser;
        });
      } catch (err) {
        console.error(err);
        return;
      }
    },
    async linkAccount(account) {
      try {
        let linkedAccount = await db.oneOrNone(
          `
          INSERT INTO accounts 
          (
            "userId",
            type,
            provider,
            "providerAccountId",
            refresh_token,
            access_token,
            expires_at,
            token_type,
            scope,
            id_token,
            session_state
          )
          VALUES (
            $/userId/,
            $/type/,
            $/provider/,
            $/providerAccountId/,
            $/refresh_token/,
            $/access_token/,
            $/expires_at/,
            $/token_type/,
            $/scope/,
            $/id_token/,
            $/session_state/
            )
          RETURNING
            id,  
            "userId",
            type,
            provider,
            "providerAccountId",
            refresh_token,
            access_token,
            expires_at,
            token_type,
            scope,
            id_token,
            session_state;`,
          {
            userId: account.userId,
            type: account.type,
            provider: account.provider,
            providerAccountId: account.providerAccountId,
            refresh_token: account.refresh_token,
            access_token: account.access_token,
            expires_at: account.expires_at,
            token_type: account.token_type,
            scope: account.scope,
            id_token: account.id_token,
            session_state: account.session_state,
          }
        );
        return mapExpiresAt(linkedAccount);
      } catch (err) {
        console.error(err);
        return;
      }
    },
    async createSession({ sessionToken, userId, expires }) {
      try {
        if (userId === undefined) {
          throw Error(`userId is undefined in createSession`);
        }
        let session = await db.oneOrNone(
          `
          INSERT INTO sessions
          ("userId", expires, "sessionToken")
          VALUES ($/userId/, $/expires/, $/sessionToken/)
          RETURNING id, "sessionToken", "userId", expires;`,
          {
            userId: userId,
            expires: expires,
            sessionToken: sessionToken,
          }
        );
        return session;
      } catch (err) {
        console.error(err);
        return;
      }
    },
    async getSessionAndUser(sessionToken) {
      try {
        if (sessionToken === undefined) {
          return null;
        }
        await db.task(async (t) => {
          let session = await t.oneOrNone(
            `
            SELECT *
            FROM sessions
            WHERE "sessionToken" = $1;`,
            [sessionToken]
          );
          if (!session) return null;
          let user = await t.oneOrNone(
            `
            SELECT *
            FROM users
            WHERE id = $1;`,
            [session.userId]
          );
          return {
            session,
            user,
          };
        });
      } catch (err) {
        console.error(err);
        return;
      }
    },
    async updateSession(session) {
      try {
        const { sessionToken } = session;
        await db.task(async (t) => {
          let originalSession = await t.oneOrNone(
            `
            SELECT *
            FROM sessions
            WHERE "sessionToken" = $1;`,
            [sessionToken]
          );
          if (!originalSession) return null;
          const newSession = {
            ...originalSession,
            ...session,
          };
          let updatedSession = await t.oneOrNone(
            `
            UPDATE sessions
            SET
              "userId" = $/userId/,
              expires = $/expires/
            WHERE "sessionToken" = $/sessionToken/;`,
            {
              userId: newSession.userId,
              expires: newSession.expires,
              sessionToken: newSession.sessionToken,
            }
          );
          return updatedSession;
        });
      } catch (err) {
        console.error(err);
        return;
      }
    },
    async deleteSession(sessionToken) {
      try {
        await db.oneOrNone(
          `
          DELETE FROM sessions
          WHERE "sessionToken" = $1;`,
          [sessionToken]
        );
      } catch (err) {
        console.error(err);
        return;
      }
    },
    async unlinkAccount(partialAccount) {
      try {
        const { provider, providerAccountId } = partialAccount;
        return await db.oneOrNone(
          `
          DELETE FROM accounts
          WHERE "providerAccountId" = $1
            AND provider = $2;`,
          [providerAccountId, provider]
        );
      } catch (err) {
        console.error(err);
        return;
      }
    },
    async deleteUser(userId) {
      try {
        await db.task(async (t) => {
          await t.none(
            `
            DELETE FROM users
            WHERE id = $1;`,
            [userId]
          );
          await t.none(
            `
            DELETE FROM sessions
            WHERE "userId" = $1;`,
            [userId]
          );
          await t.none(
            `
            DELETE FROM accounts
            WHERE "userId" = $1;`,
            [userId]
          );
        });
      } catch (err) {
        console.error(err);
        return;
      }
    },
  };
}

Contributing 🙌🏽

Yes, I am willing to help answer this question in a PR

[craig-bowers]

I was wrong about the next-auth.session-token cookie not getting set when using the 'database' strategy. I tested this with the Application tab open in Chrome developer tools and I noticed the cookie getting set after I click my Google account during the sign in process. However, the cookie gets deleted / disappears almost as soon as it's set as long as the browser tab has focus. Otherwise if I click my Google account and switch to a different tab before the page loads the cookie will remain until I refocus the tab. From console logs that I added in my adapter it seems that the next-auth.session-token cookie disappears right after the getSessionAndUser is called (successfully btw... before returning the session and user in getSessionAndUser I checked these two objects with console.log() and everything looked fine).

This picture shows my cookies after clicking my Google account and before getSessionAndUser is called. After getSessionAndUser is called the next-auth.session-token cookie disappears.

I've tried fiddling around with various callbacks and other session settings in [...nextauth].js. I've tried changing some of my database columns & types (I'm still not completely sure if I'm supposed to storing my id/userId as text or an integer or if my timestamps should be stored as text instead of timestamptz). Through all of these attempts NextAuth still keeps deleting my next-auth.session-token while using the 'database' strategy, so for now I've given up and adopted the jwt strategy.

These updated authOptions allow me to pass both essential user.id data along with optional data for my app e.g. user.display_name along to the client.

export const authOptions = {
  // debug: process.env.NODE_ENV !== 'production',
  providers: [
    GoogleProvider({
      clientId: process.env.GOOGLE_ID,
      clientSecret: process.env.GOOGLE_SECRET,
    }),
  ],
  adapter: PostgresAdapter(db),
  secret: process.env.NEXTAUTH_SECRET,
  session: {
    strategy: 'jwt',
  },
  callbacks: {
    async session({ session, user, token }) {
      if (session?.user) {
        session.user.id = token.sub;
        session.user.display_name = token.display_name;
      }
      return session;
    },
    async jwt({ token, user, account, profile, isNewUser }) {
      if (user) {
        // user contains the database info
        token.display_name = user.display_name;
      }
      return token;
    },
  },
};

If anybody has a fix for the 'database' strategy please let me know.

[AlexandreRoba]

Hi @craig-bowers did you find why your session is being deleted? I'm facing the same issue with a much simpler scenario. I'm using the credentials provider and when using signin()I'm receiving a successfull login result but the session is created and deleted right away. This has been driving me nuts for the last couple of days.

This is my configuration

export const authOptions: NextAuthOptions = {
  secret:env.NEXTAUTH_SECRET,
  callbacks: {
    session({ session, user }) {
      console.log({ sessionEx: { session, user } });
      return session;
    },
    jwt({token,user }){
      console.log({ jwt: { token, user} })
      return token
    }
  },
  // Configure one or more authentication providers
  adapter: PrismaAdapter(prisma),
  pages:{
    signIn:"/login"
  },
  providers: [
    CredentialsProvider({
      name: "Credentials",
      credentials: {},
      async authorize(credentials, req) {
        const creds = await loginSchema.parseAsync(credentials)

        const user = await prisma.user.findFirst({
          where: { email: creds.email },
        });

        if (!user) {
          return null;
        }

        if(!user.password){
          return null;
        }

        const isValidPassword = await verify(user.password, creds.password);

        if (!isValidPassword) {
          return null;
        }

        return {
           id: user.id,
           email: user.email,
           name: user.name,
         };
      }
    })
  ],
};

This is the server log when logging in... I was expecting session() to be called...

prisma:query SELECT 1
prisma:query SELECT `saas`.`User`.`id`, `saas`.`User`.`name`, `saas`.`User`.`password`, `saas`.`User`.`email`, `saas`.`User`.`emailVerified`, `saas`.`User`.`image` FROM `saas`.`User` WHERE `saas`.`User`.`email` = ? LIMIT ? OFFSET ?
{
  jwt: {
    token: {
      name: 'arob',
      email: 'alex@eample.com,
      picture: undefined,
      sub: 'clbmktw020000v28sx41502d3'
    },
    user: {
      id: 'clbmktw020000v28sx41502d3',
      email: 'alex@example.com',
      name: 'arob'
    }
  }
}
prisma:query SELECT `saas`.`Session`.`id`, `saas`.`Session`.`sessionToken`, `saas`.`Session`.`userId`, `saas`.`Session`.`expires` FROM `saas`.`Session` WHERE (`saas`.`Session`.`sessionToken` = ? AND 1=1) LIMIT ? OFFSET ?

[craig-bowers]

I have tested this after a few different next-auth updates hoping that this issue might get fixed or go away, but no such luck. Same issues. The 'jwt' strategy is still working fine. Did you ever figure it out?