Kysely•2y ago

Raw query for RLS not working

I am trying to migrate a raw query from TypeORM's builtin query builder to Kysely. It's required to be raw as Kysely unfortunately does not fully implement Postgres's SQL language. Original query:

    const query = `
    DROP POLICY IF EXISTS tenant_isolation_policy ON ${tableName};
    CREATE POLICY tenant_isolation_policy ON ${tableName} USING ("tenantId"::uuid = current_setting('project.current_tenant')::uuid);
    ALTER TABLE ${tableName} ENABLE ROW LEVEL SECURITY;
    ALTER TABLE ${tableName} FORCE ROW LEVEL SECURITY;';
    return await repository.query(query);

    const query = `
    DROP POLICY IF EXISTS tenant_isolation_policy ON ${tableName};
    CREATE POLICY tenant_isolation_policy ON ${tableName} USING ("tenantId"::uuid = current_setting('project.current_tenant')::uuid);
    ALTER TABLE ${tableName} ENABLE ROW LEVEL SECURITY;
    ALTER TABLE ${tableName} FORCE ROW LEVEL SECURITY;';
    return await repository.query(query);

What I wrote:

    const query = sql`
    DROP POLICY IF EXISTS tenant_isolation_policy ON ${tableName};
    CREATE POLICY tenant_isolation_policy ON ${tableName} USING ("tenantId"::uuid = current_setting('project.current_tenant')::uuid);
    ALTER TABLE ${tableName} ENABLE ROW LEVEL SECURITY;
    ALTER TABLE ${tableName} FORCE ROW LEVEL SECURITY;
  `.compile(this.db);
    return await this.db.executeQuery(query);

    const query = sql`
    DROP POLICY IF EXISTS tenant_isolation_policy ON ${tableName};
    CREATE POLICY tenant_isolation_policy ON ${tableName} USING ("tenantId"::uuid = current_setting('project.current_tenant')::uuid);
    ALTER TABLE ${tableName} ENABLE ROW LEVEL SECURITY;
    ALTER TABLE ${tableName} FORCE ROW LEVEL SECURITY;
  `.compile(this.db);
    return await this.db.executeQuery(query);

Error:

backend:dev: /Users/jam/tmp_proj/node_modules/pg-protocol/src/parser.ts:369
backend:dev:       name === 'notice' ? new NoticeMessage(length, messageValue) : new DatabaseError(messageValue, length, name)
backend:dev:                                                                     ^
backend:dev: error: syntax error at or near "$1"
backend:dev:     at Parser.parseErrorMessage (/Users/jam/tmp_proj/node_modules/pg-protocol/src/parser.ts:369:69)

backend:dev: /Users/jam/tmp_proj/node_modules/pg-protocol/src/parser.ts:369
backend:dev:       name === 'notice' ? new NoticeMessage(length, messageValue) : new DatabaseError(messageValue, length, name)
backend:dev:                                                                     ^
backend:dev: error: syntax error at or near "$1"
backend:dev:     at Parser.parseErrorMessage (/Users/jam/tmp_proj/node_modules/pg-protocol/src/parser.ts:369:69)

Any idea?

2 Replies

wirekang•2y ago

sql template string automatically generates parameter placeholders: https://kyse.link/?p=s&i=BIWYEWyxKcZCWJ0i0XBk BUT you can't parameterize entity names in postgres. use sql.raw instead: https://kyse.link/?p=s&i=HD2An3oWQLWVDPYmZO4m As you know, beware of SQL injection in this case.

Jam•2y ago

Thank you. Too bad.

Gaming

Programming

Raw query for RLS not working