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.

JamOP•2y ago

Thank you. Too bad.

Gaming

Programming

Raw query for RLS not working

Did you find this page helpful?