Postgres SET runtime variables with TypeORM, how to persist variable during the life of the connection between calls

user_4457396 0
Viewed 437

I have NodeJS web server using GraphQL using 2 connections. One has admin access, the other crud access.

Underlying Postgres DB has a Row Level Security policy, i.e.:

ALTER TABLE main.user ENABLE ROW LEVEL SECURITY;
CREATE POLICY user_isolation_policy ON main.user USING (id = current_setting('app.current_user_id')::UUID);

Before I login a user, I need to get their id from the db, then set the current_user_id variable in Postgres session while logging in.

However, when I try to fetch users back, I am expecting to get back only the logged in user, not everyone - this is how it behaves using pgAdmin. However, here I am getting the following error:

error: error: unrecognized configuration parameter "app.current_user_id"

Here is how I log a user in:

@Resolver()
export class LoginResolver {
  @Mutation(() => LoginResponse)
  public async login(
    @Arg('email') email: string,
    @Arg('password') password: string,
    @Ctx() { res }: AppContext
  ): Promise<LoginResponse> {
    try {
      // get user from the admin repo so we can get their ID
      const userRepository = (await adminConnection).getRepository(User)
      const user = await userRepository.findOne({ where: { email } })
      if (!user) throw new Error('user not found')

      // using the api repo (not admin), set the variable
      User.getRepository().query(`SET app.current_user_id TO "${user.id}"`)
      
      const isValid = await bcrypt.compare(password, user.password)
      if (!isValid) throw new Error('incorrect password')
      if (!user.isConfirmed) throw new Error('email not confirmed')
      sendRefreshToken(res, user.createRefreshToken())
      return { token: user.createAccessToken() }
    } catch (error) {
      throw new Error(error)
    }
  }
}

Here is how I try to fetch back users:

@Resolver()
export class UsersResolver {
  @Authorized(UserRole.admin, UserRole.super)
  @Query(() => [User])
  public users(): Promise<User[]> {
    return User.find()
  }
}

Please note that, if I remove the policy, GraphQL runs normally without errors.

The set variable is not persisting. How do I persist the variable while the user is logged in?

typeorm postgresql
1 Answers

This approach works for me:


import { EntityManager, getConnection, getConnectionManager, getManager } from "typeorm";
import { EventSubscriber, EntitySubscriberInterface, InsertEvent, UpdateEvent, RemoveEvent } from "typeorm";

@EventSubscriber()
export class CurrentUserSubscriber implements EntitySubscriberInterface {

    // get the userId from the current http request/headers/wherever you store it (currently I'm typeorm only, not as part of nest/express app)
    async setUserId(mng: EntityManager, userId: string) {
        await mng.query(`SELECT set_config('app.current_user_id', '${userId}', true);`)
    }

    async beforeInsert(event: InsertEvent<any>) {
        await this.setUserId(event.manager, 'myUserId');
    }
    async beforeUpdate(event: UpdateEvent<any>) {
        await this.setUserId(event.manager, 'myUserId');
    }
    async beforeRemove(event: RemoveEvent<any>) {
        await this.setUserId(event.manager, 'myUserId');
    }
}

Don't forget to configure the subscribers property in ormconfig.js, e.g. :

   "subscribers": [
      "src/subscribers/CurrentUserSubscriber.ts",
   ],
user_58680440
Related