16086993915

Committed 05 Jul 2025 09:59AM UTC coverage: 80.687% (-0.02%) from 80.703%

Build # 16086993915

Build Type

push

github

Committed by

web-flow

Commit Message

feat: add host query and disable option in pre-SQL check (#1646)

Run Details

8084 of 8596 branches covered (94.04%)

0 of 7 new or added lines in 1 file covered. (0.0%)

3 existing lines in 2 files now uncovered.

38486 of 47698 relevant lines covered (80.69%)

1703.25 hits per line

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

0.0

/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, getRandomString, parseDsn } from '@teable/core';
import type { Prisma } from '@teable/db-main-prisma';
import { PrismaService } from '@teable/db-main-prisma';
import knex, { 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?: Knex;
  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<
    Knex.PgConnectionConfig | 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 {
      ...this.dsn,
      database: this.dsn.db,
      password: this.dsn.pass,
      query_timeout: 10000,
      user: BASE_SCHEMA_TABLE_READ_ONLY_ROLE_NAME,
      host: (this.dsn.params?.host as string) ?? this.dsn.host,
    };
  }

  async onModuleInit() {
    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?.destroy();
  }

  private async createConnection(): Promise<Knex | undefined> {
    if (this.db) {
      return this.db;
    }
    const connectionConfig = await this.getReadOnlyDatabaseConnectionConfig();
    if (!connectionConfig) {
      return;
    }
    const connection = knex({
      client: this.driver,
      connection: connectionConfig,
    });

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

teableio / teable / 16086993915

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