K
Join ServerKysely
help
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:
What I wrote:
Error:
Any idea?
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);
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);
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)
Any idea?
sql
template string automatically generates parameter placeholders: https://kyse.link/?p=s&i=BIWYEWyxKcZCWJ0i0XBkBUT you can't parameterize entity names in postgres.
use
sql.raw
instead: https://kyse.link/?p=s&i=HD2An3oWQLWVDPYmZO4mAs you know, beware of SQL injection in this case.
Thank you. Too bad.