16133530720

Committed 08 Jul 2025 03:53AM UTC coverage: 81.117%. First build

Build # 16133530720

Build Type

push

github

Committed by

web-flow

Commit Message

refactor: replace Knex with Prisma for readonly database connection (#1648)

* refactor: replace Knex with Prisma for readonly database connection

* fix: e2e Injectable module

* fix: base sql executor readonly init

* fix: retry-on-dead-lock.spec timeout

* fix: remove readonly password

* chore: more base-sql-excutor e2e it

Run Details

8144 of 8676 branches covered (93.87%)

24 of 26 new or added lines in 1 file covered. (92.31%)

38721 of 47735 relevant lines covered (81.12%)

1704.13 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

75.4

/apps/nestjs-backend/src/features/base-sql-executor/base-sql-executor.service.ts

import { BadRequestException, Injectable } from '@nestjs/common';
import { ConfigService } from '@nestjs/config';
import type { IDsn } from '@teable/core';
import { DriverClient, parseDsn } from '@teable/core';
import type { Prisma } from '@teable/db-main-prisma';
import { PrismaService, PrismaClient } from '@teable/db-main-prisma';
import { Knex } from 'knex';
import { InjectModel } from 'nest-knexjs';
import { BASE_READ_ONLY_ROLE_PREFIX, BASE_SCHEMA_TABLE_READ_ONLY_ROLE_NAME } from './const';
import { checkTableAccess, validateRoleOperations } from './utils';

@Injectable()
export class BaseSqlExecutorService {
  private db?: PrismaClient;
  private readonly dsn: IDsn;
  readonly driver: DriverClient;
  private hasPgReadAllDataRole?: boolean;

  constructor(
    private readonly prismaService: PrismaService,
    private readonly configService: ConfigService,
    @InjectModel('CUSTOM_KNEX') private readonly knex: Knex
  ) {
    this.dsn = parseDsn(this.getDatabaseUrl());
    this.driver = this.dsn.driver as DriverClient;
  }

  private getDatabaseUrl() {
    return this.configService.getOrThrow<string>('PRISMA_DATABASE_URL');
  }

  private getDisablePreSqlExecutorCheck() {
    return this.configService.get<string>('DISABLE_PRE_SQL_EXECUTOR_CHECK') === 'true';
  }

  private async getReadOnlyDatabaseConnectionConfig(): Promise<string | undefined> {
    if (this.driver === DriverClient.Sqlite) {
      return;
    }
    if (!this.hasPgReadAllDataRole) {
      return;
    }
    const isExistReadOnlyRole = await this.roleExits(BASE_SCHEMA_TABLE_READ_ONLY_ROLE_NAME);
    if (!isExistReadOnlyRole) {
      await this.prismaService.$tx(async (prisma) => {
        await prisma.$executeRawUnsafe(
          this.knex
            .raw(
              `CREATE ROLE ?? WITH LOGIN PASSWORD ? NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION`,
              [BASE_SCHEMA_TABLE_READ_ONLY_ROLE_NAME, this.dsn.pass]
            )
            .toQuery()
        );
        await prisma.$executeRawUnsafe(
          this.knex
            .raw(`GRANT pg_read_all_data TO ??`, [BASE_SCHEMA_TABLE_READ_ONLY_ROLE_NAME])
            .toQuery()
        );
      });
    }
    return `postgresql://${BASE_SCHEMA_TABLE_READ_ONLY_ROLE_NAME}:${this.dsn.pass}@${this.dsn.host}:${this.dsn.port}/${this.dsn.db}${
      this.dsn.params
        ? `?${Object.entries(this.dsn.params)
            .map(([key, value]) => `${key}=${value}`)
            .join('&')}`
        : ''
    }`;
  }

  async onModuleInit() {
    if (this.driver !== DriverClient.Pg) {
      return;
    }
    if (this.getDisablePreSqlExecutorCheck()) {
      return;
    }
    // if pg_read_all_data role not exist, no need to create read only role
    this.hasPgReadAllDataRole = await this.roleExits('pg_read_all_data');
    if (!this.hasPgReadAllDataRole) {
      return;
    }
    this.db = await this.createConnection();
  }

  async onModuleDestroy() {
    await this.db?.$disconnect();
  }

  private async createConnection(): Promise<PrismaClient | undefined> {
    if (this.db) {
      return this.db;
    }
    const connectionConfig = await this.getReadOnlyDatabaseConnectionConfig();
    if (!connectionConfig) {
      return;
    }
    const connection = new PrismaClient({
      datasources: {
        db: {
          url: connectionConfig,
        },
      },
    });
    await connection.$connect();

    // validate connection
    try {
      await connection.$queryRawUnsafe('SELECT 1');
      return connection;
      // eslint-disable-next-line @typescript-eslint/no-explicit-any
    } catch (error: any) {
      await connection.$disconnect();
      throw new Error(`database connection failed: ${error.message}`);
    }
  }

  private getReadOnlyRoleName(baseId: string) {
    return `${BASE_READ_ONLY_ROLE_PREFIX}${baseId}`;
  }

  async createReadOnlyRole(baseId: string) {
    const roleName = this.getReadOnlyRoleName(baseId);
    await this.prismaService
      .txClient()
      .$executeRawUnsafe(
        this.knex
          .raw(
            `CREATE ROLE ?? WITH NOLOGIN NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE NOREPLICATION`,
            [roleName]
          )
          .toQuery()
      );
    await this.prismaService
      .txClient()
      .$executeRawUnsafe(
        this.knex.raw(`GRANT USAGE ON SCHEMA ?? TO ??`, [baseId, roleName]).toQuery()
      );
    await this.prismaService
      .txClient()
      .$executeRawUnsafe(
        this.knex.raw(`GRANT SELECT ON ALL TABLES IN SCHEMA ?? TO ??`, [baseId, roleName]).toQuery()
      );
    await this.prismaService
      .txClient()
      .$executeRawUnsafe(
        this.knex
          .raw(`ALTER DEFAULT PRIVILEGES IN SCHEMA ?? GRANT SELECT ON TABLES TO ??`, [
            baseId,
            roleName,
          ])
          .toQuery()
      );
  }

  async dropReadOnlyRole(baseId: string) {
    const roleName = this.getReadOnlyRoleName(baseId);
    await this.prismaService
      .txClient()
      .$executeRawUnsafe(
        this.knex.raw(`REVOKE USAGE ON SCHEMA ?? FROM ??`, [baseId, roleName]).toQuery()
      );
    await this.prismaService
      .txClient()
      .$executeRawUnsafe(
        this.knex
          .raw(`REVOKE SELECT ON ALL TABLES IN SCHEMA ?? FROM ??`, [baseId, roleName])
          .toQuery()
      );
    await this.prismaService
      .txClient()
      .$executeRawUnsafe(
        this.knex
          .raw(`ALTER DEFAULT PRIVILEGES IN SCHEMA ?? REVOKE ALL ON TABLES FROM ??`, [
            baseId,
            roleName,
          ])
          .toQuery()
      );
    await this.prismaService
      .txClient()
      .$executeRawUnsafe(this.knex.raw(`DROP ROLE IF EXISTS ??`, [roleName]).toQuery());
  }

  async grantReadOnlyRole(baseId: string) {
    const roleName = this.getReadOnlyRoleName(baseId);
    await this.prismaService
      .txClient()
      .$executeRawUnsafe(
        this.knex.raw(`GRANT USAGE ON SCHEMA ?? TO ??`, [baseId, roleName]).toQuery()
      );
    await this.prismaService
      .txClient()
      .$executeRawUnsafe(
        this.knex.raw(`GRANT SELECT ON ALL TABLES IN SCHEMA ?? TO ??`, [baseId, roleName]).toQuery()
      );
    await this.prismaService
      .txClient()
      .$executeRawUnsafe(
        this.knex
          .raw(`ALTER DEFAULT PRIVILEGES IN SCHEMA ?? GRANT SELECT ON TABLES TO ??`, [
            baseId,
            roleName,
          ])
          .toQuery()
      );
  }

  private async roleExits(role: string): Promise<boolean> {
    const roleExists = await this.prismaService.$queryRaw<
      { count: bigint }[]
    >`SELECT count(*) FROM pg_roles WHERE rolname=${role}`;
    return Boolean(roleExists[0].count);
  }

  private async roleCheckAndCreate(baseId: string) {
    if (this.driver !== DriverClient.Pg) {
      return;
    }
    const roleName = this.getReadOnlyRoleName(baseId);
    if (!(await this.roleExits(roleName))) {
      await this.createReadOnlyRole(baseId);
    }
  }

  private async setRole(prisma: Prisma.TransactionClient, baseId: string) {
    const roleName = this.getReadOnlyRoleName(baseId);
    await prisma.$executeRawUnsafe(this.knex.raw(`SET ROLE ??`, [roleName]).toQuery());
  }

  private async resetRole(prisma: Prisma.TransactionClient) {
    await prisma.$executeRawUnsafe(this.knex.raw(`RESET ROLE`).toQuery());
  }

  private async readonlyExecuteSql(sql: string) {
    return this.db?.$queryRawUnsafe(sql);
  }

  /**
   * check sql is safe
   * 1. role operations validation
   * 2. parse sql to valid table names
   * 3. read only role check table access
   */
  private async safeCheckSql(
    baseId: string,
    sql: string,
    opts?: { projectionTableDbNames?: string[]; projectionTableIds?: string[] }
  ) {
    const { projectionTableDbNames = [] } = opts ?? {};
    // 1. role operations keywords validation, only pg support
    if (this.driver == DriverClient.Pg) {
      validateRoleOperations(sql);
    }
    let tableNames = projectionTableDbNames;
    if (!projectionTableDbNames.length) {
      const tables = await this.prismaService.tableMeta.findMany({
        where: {
          baseId,
        },
        select: {
          dbTableName: true,
        },
      });
      tableNames = tables.map((table) => table.dbTableName);
    }
    // 2. parse sql to valid table names
    checkTableAccess(sql, {
      tableNames,
      database: this.driver,
    });
    // 3. read only role check table access, only pg and pg version > 14 support
    await this.readonlyExecuteSql(sql);
  }

  async executeQuerySql<T = unknown>(
    baseId: string,
    sql: string,
    opts?: {
      projectionTableDbNames?: string[];
      projectionTableIds?: string[];
    }
  ) {
    await this.safeCheckSql(baseId, sql, opts);
    await this.roleCheckAndCreate(baseId);
    return this.prismaService.$tx(async (prisma) => {
      try {
        await this.setRole(prisma, baseId);
        return await prisma.$queryRawUnsafe<T>(sql);
        // eslint-disable-next-line @typescript-eslint/no-explicit-any
      } catch (error: any) {
        throw new BadRequestException(error?.meta?.message || error?.message);
      } finally {
        await this.resetRole(prisma).catch((error) => {
          console.log('resetRole error', error);
        });
      }
    });
  }
}

1	import { BadRequestException, Injectable } from '@nestjs/common';	2✔
2	import { ConfigService } from '@nestjs/config';
3	import type { IDsn } from '@teable/core';
4	import { DriverClient, parseDsn } from '@teable/core';
5	import type { Prisma } from '@teable/db-main-prisma';
6	import { PrismaService, PrismaClient } from '@teable/db-main-prisma';
7	import { Knex } from 'knex';
8	import { InjectModel } from 'nest-knexjs';
9	import { BASE_READ_ONLY_ROLE_PREFIX, BASE_SCHEMA_TABLE_READ_ONLY_ROLE_NAME } from './const';
10	import { checkTableAccess, validateRoleOperations } from './utils';
11
12	@Injectable()
13	export class BaseSqlExecutorService {	2✔
14	private db?: PrismaClient;	122✔
15	private readonly dsn: IDsn;	122✔
16	readonly driver: DriverClient;	122✔
17	private hasPgReadAllDataRole?: boolean;	122✔
18
19	constructor(	122✔
20	private readonly prismaService: PrismaService,	122✔
21	private readonly configService: ConfigService,	122✔
22	@InjectModel('CUSTOM_KNEX') private readonly knex: Knex	122✔
23	) {	122✔
24	this.dsn = parseDsn(this.getDatabaseUrl());	122✔
25	this.driver = this.dsn.driver as DriverClient;	122✔
26	}	122✔
27
28	private getDatabaseUrl() {	122✔
29	return this.configService.getOrThrow<string>('PRISMA_DATABASE_URL');	122✔
30	}	122✔
31
32	private getDisablePreSqlExecutorCheck() {	122✔
33	return this.configService.get<string>('DISABLE_PRE_SQL_EXECUTOR_CHECK') === 'true';	62✔
34	}	62✔
35
36	private async getReadOnlyDatabaseConnectionConfig(): Promise<string \| undefined> {	122✔
37	if (this.driver === DriverClient.Sqlite) {	62!
38	return;	×
39	}	×
40	if (!this.hasPgReadAllDataRole) {	62!
41	return;	×
42	}	×
43	const isExistReadOnlyRole = await this.roleExits(BASE_SCHEMA_TABLE_READ_ONLY_ROLE_NAME);	62✔
44	if (!isExistReadOnlyRole) {	62✔
45	await this.prismaService.$tx(async (prisma) => {	1✔
46	await prisma.$executeRawUnsafe(	1✔
47	this.knex	1✔
48	.raw(	1✔
49	`CREATE ROLE ?? WITH LOGIN PASSWORD ? NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION`,	1✔
50	[BASE_SCHEMA_TABLE_READ_ONLY_ROLE_NAME, this.dsn.pass]	1✔
51	)
52	.toQuery()	1✔
53	);
54	await prisma.$executeRawUnsafe(	1✔
55	this.knex	1✔
56	.raw(`GRANT pg_read_all_data TO ??`, [BASE_SCHEMA_TABLE_READ_ONLY_ROLE_NAME])	1✔
57	.toQuery()	1✔
58	);
59	});	1✔
60	}	1✔
61	return `postgresql://${BASE_SCHEMA_TABLE_READ_ONLY_ROLE_NAME}:${this.dsn.pass}@${this.dsn.host}:${this.dsn.port}/${this.dsn.db}${	62✔
62	this.dsn.params	62✔
63	? `?${Object.entries(this.dsn.params)	62✔
64	.map(([key, value]) => `${key}=${value}`)	62✔
65	.join('&')}`	62✔
NEW 66	: ''	×
67	}`;	62✔
68	}	62✔
69
70	async onModuleInit() {	122✔
71	if (this.driver !== DriverClient.Pg) {	122!
72	return;	60✔
73	}	60!
74	if (this.getDisablePreSqlExecutorCheck()) {	62!
75	return;	×
76	}	×
77	// if pg_read_all_data role not exist, no need to create read only role	62✔
78	this.hasPgReadAllDataRole = await this.roleExits('pg_read_all_data');	62✔
79	if (!this.hasPgReadAllDataRole) {	62!
80	return;	×
81	}	×
82	this.db = await this.createConnection();	62✔
83	}	62✔
84
85	async onModuleDestroy() {	122✔
86	await this.db?.$disconnect();	120!
87	}	120✔
88
89	private async createConnection(): Promise<PrismaClient \| undefined> {	122✔
90	if (this.db) {	62!
91	return this.db;	×
92	}	×
93	const connectionConfig = await this.getReadOnlyDatabaseConnectionConfig();	62✔
94	if (!connectionConfig) {	62!
95	return;	×
96	}	×
97	const connection = new PrismaClient({	62✔
98	datasources: {	62✔
99	db: {	62✔
100	url: connectionConfig,	62✔
101	},	62✔
102	},	62✔
103	});	62✔
104	await connection.$connect();	62✔
105
106	// validate connection	62✔
107	try {	62✔
108	await connection.$queryRawUnsafe('SELECT 1');	62✔
109	return connection;	62✔
110	// eslint-disable-next-line @typescript-eslint/no-explicit-any	62✔
111	} catch (error: any) {	62!
NEW 112	await connection.$disconnect();	×
113	throw new Error(`database connection failed: ${error.message}`);	×
114	}	×
115	}	62✔
116
117	private getReadOnlyRoleName(baseId: string) {	122✔
118	return `${BASE_READ_ONLY_ROLE_PREFIX}${baseId}`;	6✔
119	}	6✔
120
121	async createReadOnlyRole(baseId: string) {	122✔
122	const roleName = this.getReadOnlyRoleName(baseId);	2✔
123	await this.prismaService	2✔
124	.txClient()	2✔
125	.$executeRawUnsafe(	2✔
126	this.knex	2✔
127	.raw(	2✔
128	`CREATE ROLE ?? WITH NOLOGIN NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE NOREPLICATION`,	2✔
129	[roleName]	2✔
130	)
131	.toQuery()	2✔
132	);
133	await this.prismaService	2✔
134	.txClient()	2✔
135	.$executeRawUnsafe(	2✔
136	this.knex.raw(`GRANT USAGE ON SCHEMA ?? TO ??`, [baseId, roleName]).toQuery()	2✔
137	);
138	await this.prismaService	2✔
139	.txClient()	2✔
140	.$executeRawUnsafe(	2✔
141	this.knex.raw(`GRANT SELECT ON ALL TABLES IN SCHEMA ?? TO ??`, [baseId, roleName]).toQuery()	2✔
142	);
143	await this.prismaService	2✔
144	.txClient()	2✔
145	.$executeRawUnsafe(	2✔
146	this.knex	2✔
147	.raw(`ALTER DEFAULT PRIVILEGES IN SCHEMA ?? GRANT SELECT ON TABLES TO ??`, [	2✔
148	baseId,	2✔
149	roleName,	2✔
150	])	2✔
151	.toQuery()	2✔
152	);
153	}	2✔
154
155	async dropReadOnlyRole(baseId: string) {	122✔
156	const roleName = this.getReadOnlyRoleName(baseId);	×
157	await this.prismaService	×
158	.txClient()	×
159	.$executeRawUnsafe(	×
160	this.knex.raw(`REVOKE USAGE ON SCHEMA ?? FROM ??`, [baseId, roleName]).toQuery()	×
161	);
162	await this.prismaService	×
163	.txClient()	×
164	.$executeRawUnsafe(	×
165	this.knex	×
166	.raw(`REVOKE SELECT ON ALL TABLES IN SCHEMA ?? FROM ??`, [baseId, roleName])	×
167	.toQuery()	×
168	);
169	await this.prismaService	×
170	.txClient()	×
171	.$executeRawUnsafe(	×
172	this.knex	×
173	.raw(`ALTER DEFAULT PRIVILEGES IN SCHEMA ?? REVOKE ALL ON TABLES FROM ??`, [	×
174	baseId,	×
175	roleName,	×
176	])	×
177	.toQuery()	×
178	);
179	await this.prismaService	×
180	.txClient()	×
181	.$executeRawUnsafe(this.knex.raw(`DROP ROLE IF EXISTS ??`, [roleName]).toQuery());	×
182	}	×
183
184	async grantReadOnlyRole(baseId: string) {	122✔
185	const roleName = this.getReadOnlyRoleName(baseId);	×
186	await this.prismaService	×
187	.txClient()	×
188	.$executeRawUnsafe(	×
189	this.knex.raw(`GRANT USAGE ON SCHEMA ?? TO ??`, [baseId, roleName]).toQuery()	×
190	);
191	await this.prismaService	×
192	.txClient()	×
193	.$executeRawUnsafe(	×
194	this.knex.raw(`GRANT SELECT ON ALL TABLES IN SCHEMA ?? TO ??`, [baseId, roleName]).toQuery()	×
195	);
196	await this.prismaService	×
197	.txClient()	×
198	.$executeRawUnsafe(	×
199	this.knex	×
200	.raw(`ALTER DEFAULT PRIVILEGES IN SCHEMA ?? GRANT SELECT ON TABLES TO ??`, [	×
201	baseId,	×
202	roleName,	×
203	])	×
204	.toQuery()	×
205	);
206	}	×
207
208	private async roleExits(role: string): Promise<boolean> {	122✔
209	const roleExists = await this.prismaService.$queryRaw<	126✔
210	{ count: bigint }[]
211	>`SELECT count(*) FROM pg_roles WHERE rolname=${role}`;	126✔
212	return Boolean(roleExists[0].count);	126✔
213	}	126✔
214
215	private async roleCheckAndCreate(baseId: string) {	122✔
216	if (this.driver !== DriverClient.Pg) {	2!
217	return;	×
218	}	×
219	const roleName = this.getReadOnlyRoleName(baseId);	2✔
220	if (!(await this.roleExits(roleName))) {	2✔
221	await this.createReadOnlyRole(baseId);	2✔
222	}	2✔
223	}	2✔
224
225	private async setRole(prisma: Prisma.TransactionClient, baseId: string) {	122✔
226	const roleName = this.getReadOnlyRoleName(baseId);	2✔
227	await prisma.$executeRawUnsafe(this.knex.raw(`SET ROLE ??`, [roleName]).toQuery());	2✔
228	}	2✔
229
230	private async resetRole(prisma: Prisma.TransactionClient) {	122✔
231	await prisma.$executeRawUnsafe(this.knex.raw(`RESET ROLE`).toQuery());	2✔
232	}	1✔
233
234	private async readonlyExecuteSql(sql: string) {	122✔
235	return this.db?.$queryRawUnsafe(sql);	2✔
236	}	2✔
237
238	/**	122✔
239	* check sql is safe
240	* 1. role operations validation
241	* 2. parse sql to valid table names
242	* 3. read only role check table access
243	*/	122✔
244	private async safeCheckSql(	122✔
245	baseId: string,	2✔
246	sql: string,	2✔
247	opts?: { projectionTableDbNames?: string[]; projectionTableIds?: string[] }	2✔
248	) {	2✔
249	const { projectionTableDbNames = [] } = opts ?? {};	2✔
250	// 1. role operations keywords validation, only pg support	2✔
251	if (this.driver == DriverClient.Pg) {	2✔
252	validateRoleOperations(sql);	2✔
253	}	2✔
254	let tableNames = projectionTableDbNames;	2✔
255	if (!projectionTableDbNames.length) {	2✔
256	const tables = await this.prismaService.tableMeta.findMany({	1✔
257	where: {	1✔
258	baseId,	1✔
259	},	1✔
260	select: {	1✔
261	dbTableName: true,	1✔
262	},	1✔
263	});	1✔
264	tableNames = tables.map((table) => table.dbTableName);	1✔
265	}	1✔
266	// 2. parse sql to valid table names	2✔
267	checkTableAccess(sql, {	2✔
268	tableNames,	2✔
269	database: this.driver,	2✔
270	});	2✔
271	// 3. read only role check table access, only pg and pg version > 14 support	2✔
272	await this.readonlyExecuteSql(sql);	2✔
273	}	2✔
274
275	async executeQuerySql<T = unknown>(	122✔
276	baseId: string,	2✔
277	sql: string,	2✔
278	opts?: {	2✔
279	projectionTableDbNames?: string[];
280	projectionTableIds?: string[];
281	}	2✔
282	) {	2✔
283	await this.safeCheckSql(baseId, sql, opts);	2✔
284	await this.roleCheckAndCreate(baseId);	2✔
285	return this.prismaService.$tx(async (prisma) => {	2✔
286	try {	2✔
287	await this.setRole(prisma, baseId);	2✔
288	return await prisma.$queryRawUnsafe<T>(sql);	2✔
289	// eslint-disable-next-line @typescript-eslint/no-explicit-any	2✔
290	} catch (error: any) {	2✔
291	throw new BadRequestException(error?.meta?.message \|\| error?.message);	1!
292	} finally {	2!
293	await this.resetRole(prisma).catch((error) => {	2✔
294	console.log('resetRole error', error);	1✔
295	});	1✔
296	}	2✔
297	});	2✔
298	}	2✔
299	}	122✔

teableio / teable / 16133530720

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous