30182342037

Committed 26 Jul 2026 01:06AM UTC coverage: 50.492% (-8.2%) from 58.659%

Build # 30182342037

Build Type

Pull #3616

github

Committed by

web-flow

Commit Message

Merge 55b8263fd into cce429cb4

Pull Request Pull Request #3616: [sync] T6406 fix filtered link propagation and guard conditional lookup inserts

Coverage Stats

7085 of 10523 branches covered (67.33%)

31177 of 61746 relevant lines covered (50.49%)

1664.27 hits per line

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

79.1

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

import { Injectable } from '@nestjs/common';
import { ConfigService } from '@nestjs/config';
import type { IDsn } from '@teable/core';
import { DriverClient, HttpErrorCode, parseDsn } from '@teable/core';
import { PrismaService, getDatabaseUrl } from '@teable/db-main-prisma';
import { Knex } from 'knex';
import { InjectModel } from 'nest-knexjs';
import { IThresholdConfig, ThresholdConfig } from '../../configs/threshold.config';
import { CustomHttpException } from '../../custom.exception';
import {
  DatabaseRouter,
  type IDataPrismaQueryExecutor,
} from '../../global/database-router.service';
import { DATA_KNEX } from '../../global/knex';
import { BASE_READ_ONLY_ROLE_PREFIX } from './const';
import { checkTableAccess, validateRoleOperations } from './utils';

@Injectable()
export class BaseSqlExecutorService {
  private readonly dsn: IDsn;
  readonly driver: DriverClient;

  constructor(
    private readonly prismaService: PrismaService,
    private readonly databaseRouter: DatabaseRouter,
    private readonly configService: ConfigService,
    @InjectModel(DATA_KNEX) private readonly knex: Knex,
    @ThresholdConfig() private readonly thresholdConfig: IThresholdConfig
  ) {
    this.dsn = parseDsn(this.getDatabaseUrl());
    this.driver = this.dsn.driver as DriverClient;
  }

  private getDatabaseUrl() {
    return (
      this.configService.get<string>('PRISMA_DATABASE_URL_FOR_SQL_EXECUTOR') ||
      getDatabaseUrl('meta')
    );
  }

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

  private async dataPrismaForBase(baseId: string) {
    return await this.databaseRouter.dataPrismaExecutorForBase(baseId);
  }

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

  async createReadOnlyRole(baseId: string) {
    const dataPrisma = await this.dataPrismaForBase(baseId);
    await this.createReadOnlyRoleWithPrisma(baseId, dataPrisma);
  }

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

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

  private async roleExists(role: string, dataPrisma: IDataPrismaQueryExecutor): Promise<boolean> {
    const roleExists = await dataPrisma.$queryRawUnsafe<{ count: bigint }[]>(
      this.knex.raw('SELECT count(*) FROM pg_roles WHERE rolname = ?', [role]).toQuery()
    );
    return Boolean(roleExists[0].count);
  }

  private async roleCheckAndCreate(baseId: string): Promise<boolean> {
    if (this.driver !== DriverClient.Pg) {
      return false;
    }
    const resolvedDataDb = await this.databaseRouter.getDataDatabaseForBase(baseId);
    if (!resolvedDataDb.isMetaFallback) {
      return false;
    }
    const roleName = this.getReadOnlyRoleName(baseId);
    const dataPrisma = await this.dataPrismaForBase(baseId);
    if (await this.roleExists(roleName, dataPrisma)) {
      return true;
    }
    await this.databaseRouter.dataPrismaTransactionForBase(baseId, async (dataPrisma) => {
      await dataPrisma.$executeRawUnsafe(
        this.knex.raw('SELECT pg_advisory_xact_lock(hashtextextended(?, 0))', [roleName]).toQuery()
      );
      if (!(await this.roleExists(roleName, dataPrisma))) {
        await this.createReadOnlyRoleWithPrisma(baseId, dataPrisma);
      }
    });
    return true;
  }

  private async setLocalRole(
    prisma: { $executeRawUnsafe(query: string): Promise<unknown> },
    baseId: string
  ) {
    const roleName = this.getReadOnlyRoleName(baseId);
    await prisma.$executeRawUnsafe(this.knex.raw(`SET LOCAL ROLE ??`, [roleName]).toQuery());
  }

  private async setTransactionReadOnly(prisma: {
    $executeRawUnsafe(query: string): Promise<unknown>;
  }) {
    await prisma.$executeRawUnsafe('SET TRANSACTION READ ONLY');
  }

  private async setLocalStatementTimeout(prisma: {
    $executeRawUnsafe(query: string): Promise<unknown>;
  }) {
    const timeoutMs = this.thresholdConfig.searchTimeout;
    await prisma.$executeRawUnsafe(
      this.knex.raw(`SET LOCAL statement_timeout = ?`, [timeoutMs]).toQuery()
    );
  }

  /**
   * 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) {
      // Exclude archived tables: their physical tables remain until permanent
      // deletion, and reading them would bypass the table|trash_read permission.
      const tables = await this.prismaService.tableMeta.findMany({
        where: {
          baseId,
          deletedTime: null,
        },
        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
    // TODO: need read only db connection for better security
  }

  async executeQuerySql<T = unknown>(
    baseId: string,
    sql: string,
    opts?: {
      projectionTableDbNames?: string[];
      projectionTableIds?: string[];
    }
  ) {
    await this.safeCheckSql(baseId, sql, opts);
    const shouldSetLocalRole = await this.roleCheckAndCreate(baseId);
    return this.databaseRouter.dataPrismaTransactionForBase(baseId, async (prisma) => {
      try {
        await this.setLocalStatementTimeout(prisma);
        await this.setTransactionReadOnly(prisma);
        if (shouldSetLocalRole) {
          await this.setLocalRole(prisma, baseId);
        }
        return await prisma.$queryRawUnsafe<T>(sql);
        // eslint-disable-next-line @typescript-eslint/no-explicit-any
      } catch (error: any) {
        throw new CustomHttpException(
          `execute query sql failed: ${error?.meta?.message || error?.message}`,
          HttpErrorCode.VALIDATION_ERROR,
          {
            localization: {
              i18nKey: 'httpErrors.baseSqlExecutor.executeQuerySqlFailed',
              context: {
                message: error?.meta?.message || error?.message,
              },
            },
          }
        );
      }
    });
  }
}

1	import { Injectable } from '@nestjs/common';
2	import { ConfigService } from '@nestjs/config';
3	import type { IDsn } from '@teable/core';
4	import { DriverClient, HttpErrorCode, parseDsn } from '@teable/core';
5	import { PrismaService, getDatabaseUrl } from '@teable/db-main-prisma';
6	import { Knex } from 'knex';
7	import { InjectModel } from 'nest-knexjs';
8	import { IThresholdConfig, ThresholdConfig } from '../../configs/threshold.config';
9	import { CustomHttpException } from '../../custom.exception';
10	import {
11	DatabaseRouter,
12	type IDataPrismaQueryExecutor,
13	} from '../../global/database-router.service';
14	import { DATA_KNEX } from '../../global/knex';
15	import { BASE_READ_ONLY_ROLE_PREFIX } from './const';
16	import { checkTableAccess, validateRoleOperations } from './utils';
17
18	@Injectable()
19	export class BaseSqlExecutorService {
20	private readonly dsn: IDsn;
21	readonly driver: DriverClient;
22
23	constructor(
24	private readonly prismaService: PrismaService,	23✔
25	private readonly databaseRouter: DatabaseRouter,	23✔
26	private readonly configService: ConfigService,	23✔
27	@InjectModel(DATA_KNEX) private readonly knex: Knex,	23✔
28	@ThresholdConfig() private readonly thresholdConfig: IThresholdConfig	23✔
29	) {
30	this.dsn = parseDsn(this.getDatabaseUrl());	23✔
31	this.driver = this.dsn.driver as DriverClient;	23✔
32	}
33
34	private getDatabaseUrl() {
35	return (	23✔
36	this.configService.get<string>('PRISMA_DATABASE_URL_FOR_SQL_EXECUTOR') \|\|
37	getDatabaseUrl('meta')
38	);
39	}
40
41	private getReadOnlyRoleName(baseId: string) {
42	return `${BASE_READ_ONLY_ROLE_PREFIX}${baseId}`;	8✔
43	}
44
45	private async dataPrismaForBase(baseId: string) {
46	return await this.databaseRouter.dataPrismaExecutorForBase(baseId);	3✔
47	}
48
49	private async createReadOnlyRoleWithPrisma(baseId: string, dataPrisma: IDataPrismaQueryExecutor) {
50	const roleName = this.getReadOnlyRoleName(baseId);	2✔
51	await dataPrisma.$executeRawUnsafe(	2✔
52	this.knex
53	.raw(
54	`CREATE ROLE ?? WITH NOLOGIN NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE NOREPLICATION`,
55	[roleName]
56	)
57	.toQuery()
58	);
59	await dataPrisma.$executeRawUnsafe(	2✔
60	this.knex.raw(`GRANT USAGE ON SCHEMA ?? TO ??`, [baseId, roleName]).toQuery()
61	);
62	await dataPrisma.$executeRawUnsafe(	2✔
63	this.knex.raw(`GRANT SELECT ON ALL TABLES IN SCHEMA ?? TO ??`, [baseId, roleName]).toQuery()
64	);
65	await dataPrisma.$executeRawUnsafe(	2✔
66	this.knex
67	.raw(`ALTER DEFAULT PRIVILEGES IN SCHEMA ?? GRANT SELECT ON TABLES TO ??`, [
68	baseId,
69	roleName,
70	])
71	.toQuery()
72	);
73	}
74
75	async createReadOnlyRole(baseId: string) {
76	const dataPrisma = await this.dataPrismaForBase(baseId);	×
77	await this.createReadOnlyRoleWithPrisma(baseId, dataPrisma);	×
78	}
79
80	async dropReadOnlyRole(baseId: string) {
81	const roleName = this.getReadOnlyRoleName(baseId);	×
82	const dataPrisma = await this.dataPrismaForBase(baseId);	×
83	await dataPrisma.$executeRawUnsafe(	×
84	this.knex.raw(`REVOKE USAGE ON SCHEMA ?? FROM ??`, [baseId, roleName]).toQuery()
85	);
86	await dataPrisma.$executeRawUnsafe(	×
87	this.knex
88	.raw(`REVOKE SELECT ON ALL TABLES IN SCHEMA ?? FROM ??`, [baseId, roleName])
89	.toQuery()
90	);
91	await dataPrisma.$executeRawUnsafe(	×
92	this.knex
93	.raw(`ALTER DEFAULT PRIVILEGES IN SCHEMA ?? REVOKE ALL ON TABLES FROM ??`, [
94	baseId,
95	roleName,
96	])
97	.toQuery()
98	);
99	await dataPrisma.$executeRawUnsafe(	×
100	this.knex.raw(`DROP ROLE IF EXISTS ??`, [roleName]).toQuery()
101	);
102	}
103
104	async grantReadOnlyRole(baseId: string) {
105	const roleName = this.getReadOnlyRoleName(baseId);	×
106	const dataPrisma = await this.dataPrismaForBase(baseId);	×
107	await dataPrisma.$executeRawUnsafe(	×
108	this.knex.raw(`GRANT USAGE ON SCHEMA ?? TO ??`, [baseId, roleName]).toQuery()
109	);
110	await dataPrisma.$executeRawUnsafe(	×
111	this.knex.raw(`GRANT SELECT ON ALL TABLES IN SCHEMA ?? TO ??`, [baseId, roleName]).toQuery()
112	);
113	await dataPrisma.$executeRawUnsafe(	×
114	this.knex
115	.raw(`ALTER DEFAULT PRIVILEGES IN SCHEMA ?? GRANT SELECT ON TABLES TO ??`, [
116	baseId,
117	roleName,
118	])
119	.toQuery()
120	);
121	}
122
123	private async roleExists(role: string, dataPrisma: IDataPrismaQueryExecutor): Promise<boolean> {
124	const roleExists = await dataPrisma.$queryRawUnsafe<{ count: bigint }[]>(	5✔
125	this.knex.raw('SELECT count(*) FROM pg_roles WHERE rolname = ?', [role]).toQuery()
126	);
127	return Boolean(roleExists[0].count);	5✔
128	}
129
130	private async roleCheckAndCreate(baseId: string): Promise<boolean> {
131	if (this.driver !== DriverClient.Pg) {	4✔
132	return false;	×
133	}
134	const resolvedDataDb = await this.databaseRouter.getDataDatabaseForBase(baseId);	4✔
135	if (!resolvedDataDb.isMetaFallback) {	4✔
136	return false;	1✔
137	}
138	const roleName = this.getReadOnlyRoleName(baseId);	3✔
139	const dataPrisma = await this.dataPrismaForBase(baseId);	3✔
140	if (await this.roleExists(roleName, dataPrisma)) {	3✔
141	return true;	1✔
142	}
143	await this.databaseRouter.dataPrismaTransactionForBase(baseId, async (dataPrisma) => {	2✔
144	await dataPrisma.$executeRawUnsafe(	2✔
145	this.knex.raw('SELECT pg_advisory_xact_lock(hashtextextended(?, 0))', [roleName]).toQuery()
146	);
147	if (!(await this.roleExists(roleName, dataPrisma))) {	2✔
148	await this.createReadOnlyRoleWithPrisma(baseId, dataPrisma);	2✔
149	}
150	});
151	return true;	2✔
152	}
153
154	private async setLocalRole(
155	prisma: { $executeRawUnsafe(query: string): Promise<unknown> },
156	baseId: string
157	) {
158	const roleName = this.getReadOnlyRoleName(baseId);	3✔
159	await prisma.$executeRawUnsafe(this.knex.raw(`SET LOCAL ROLE ??`, [roleName]).toQuery());	3✔
160	}
161
162	private async setTransactionReadOnly(prisma: {
163	$executeRawUnsafe(query: string): Promise<unknown>;
164	}) {
165	await prisma.$executeRawUnsafe('SET TRANSACTION READ ONLY');	4✔
166	}
167
168	private async setLocalStatementTimeout(prisma: {
169	$executeRawUnsafe(query: string): Promise<unknown>;
170	}) {
171	const timeoutMs = this.thresholdConfig.searchTimeout;	4✔
172	await prisma.$executeRawUnsafe(	4✔
173	this.knex.raw(`SET LOCAL statement_timeout = ?`, [timeoutMs]).toQuery()
174	);
175	}
176
177	/**
178	* check sql is safe
179	* 1. role operations validation
180	* 2. parse sql to valid table names
181	* 3. read only role check table access
182	*/
183	private async safeCheckSql(
184	baseId: string,
185	sql: string,
186	opts?: { projectionTableDbNames?: string[]; projectionTableIds?: string[] }
187	) {
188	const { projectionTableDbNames = [] } = opts ?? {};	5✔
189	// 1. role operations keywords validation, only pg support
190	if (this.driver == DriverClient.Pg) {	5✔
191	validateRoleOperations(sql);	5✔
192	}
193	let tableNames = projectionTableDbNames;	5✔
194	if (!projectionTableDbNames.length) {	5✔
195	// Exclude archived tables: their physical tables remain until permanent
196	// deletion, and reading them would bypass the table\|trash_read permission.
197	const tables = await this.prismaService.tableMeta.findMany({	4✔
198	where: {
199	baseId,
200	deletedTime: null,
201	},
202	select: {
203	dbTableName: true,
204	},
205	});
206	tableNames = tables.map((table) => table.dbTableName);	4✔
207	}
208	// 2. parse sql to valid table names
209	checkTableAccess(sql, {	5✔
210	tableNames,
211	database: this.driver,
212	});
213	// 3. read only role check table access, only pg and pg version > 14 support
214	// TODO: need read only db connection for better security
215	}
216
217	async executeQuerySql<T = unknown>(
218	baseId: string,
219	sql: string,
220	opts?: {
221	projectionTableDbNames?: string[];
222	projectionTableIds?: string[];
223	}
224	) {
225	await this.safeCheckSql(baseId, sql, opts);	5✔
226	const shouldSetLocalRole = await this.roleCheckAndCreate(baseId);	4✔
227	return this.databaseRouter.dataPrismaTransactionForBase(baseId, async (prisma) => {	4✔
228	try {	4✔
229	await this.setLocalStatementTimeout(prisma);	4✔
230	await this.setTransactionReadOnly(prisma);	4✔
231	if (shouldSetLocalRole) {	4✔
232	await this.setLocalRole(prisma, baseId);	3✔
233	}
234	return await prisma.$queryRawUnsafe<T>(sql);	4✔
235	// eslint-disable-next-line @typescript-eslint/no-explicit-any
236	} catch (error: any) {
237	throw new CustomHttpException(	1✔
238	`execute query sql failed: ${error?.meta?.message \|\| error?.message}`,
239	HttpErrorCode.VALIDATION_ERROR,
240	{
241	localization: {
242	i18nKey: 'httpErrors.baseSqlExecutor.executeQuerySqlFailed',
243	context: {
244	message: error?.meta?.message \|\| error?.message,
245	},
246	},
247	}
248	);
249	}
250	});
251	}
252	}

teableio / teable / 30182342037

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