• Home
  • Features
  • Pricing
  • Docs
  • Announcements
  • Sign In

teableio / teable / 18867179164

28 Oct 2025 07:21AM UTC coverage: 75.18% (-0.01%) from 75.19%
18867179164

push

github

web-flow
fix: copy paste relative (#2029)

* perf: view toolbutton readonly status when view is locked

* fix: filter component height is not at same

* fix: paste records is error when meet html text

* perf: add quick entry to enable person view

* chore: fix git file ignorecase

* fix: comment subscribe correctly

10277 of 11074 branches covered (92.8%)

51038 of 67888 relevant lines covered (75.18%)

4512.26 hits per line

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

72.24
/apps/nestjs-backend/src/features/base-sql-executor/base-sql-executor.service.ts
1
import { BadRequestException, Injectable, Logger } from '@nestjs/common';
1✔
2
import { ConfigService } from '@nestjs/config';
3
import type { IDsn } from '@teable/core';
4
import { DriverClient, parseDsn } from '@teable/core';
5
import { Prisma, PrismaService, PrismaClient } from '@teable/db-main-prisma';
6
import { Knex } from 'knex';
7
import { InjectModel } from 'nest-knexjs';
8
import { BASE_READ_ONLY_ROLE_PREFIX, BASE_SCHEMA_TABLE_READ_ONLY_ROLE_NAME } from './const';
9
import { checkTableAccess, validateRoleOperations } from './utils';
10

11
@Injectable()
12
export class BaseSqlExecutorService {
1✔
13
  private db?: PrismaClient;
87✔
14
  private readonly dsn: IDsn;
87✔
15
  readonly driver: DriverClient;
87✔
16
  private hasPgReadAllDataRole?: boolean;
87✔
17
  private readonly logger = new Logger(BaseSqlExecutorService.name);
87✔
18

19
  constructor(
87✔
20
    private readonly prismaService: PrismaService,
87✔
21
    private readonly configService: ConfigService,
87✔
22
    @InjectModel('CUSTOM_KNEX') private readonly knex: Knex
87✔
23
  ) {
87✔
24
    this.dsn = parseDsn(this.getDatabaseUrl());
87✔
25
    this.driver = this.dsn.driver as DriverClient;
87✔
26
  }
87✔
27

28
  private getDatabaseUrl() {
87✔
29
    return (
87✔
30
      this.configService.get<string>('PRISMA_DATABASE_URL_FOR_SQL_EXECUTOR') ||
87✔
31
      this.configService.getOrThrow<string>('PRISMA_DATABASE_URL')
87✔
32
    );
33
  }
87✔
34

35
  private getDisablePreSqlExecutorCheck() {
87✔
36
    return this.configService.get<string>('DISABLE_PRE_SQL_EXECUTOR_CHECK') === 'true';
87✔
37
  }
87✔
38

39
  private async getReadOnlyDatabaseConnectionConfig(): Promise<string | undefined> {
87✔
40
    if (this.driver === DriverClient.Sqlite) {
87!
41
      return;
×
42
    }
×
43
    if (!this.hasPgReadAllDataRole) {
87!
44
      return;
×
45
    }
×
46
    const isExistReadOnlyRole = await this.roleExits(BASE_SCHEMA_TABLE_READ_ONLY_ROLE_NAME);
87✔
47
    if (!isExistReadOnlyRole) {
87✔
48
      await this.prismaService.$tx(async (prisma) => {
1✔
49
        try {
1✔
50
          await prisma.$executeRawUnsafe(
1✔
51
            this.knex
1✔
52
              .raw(
1✔
53
                `CREATE ROLE ?? WITH LOGIN PASSWORD ? NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION`,
1✔
54
                [BASE_SCHEMA_TABLE_READ_ONLY_ROLE_NAME, this.dsn.pass]
1✔
55
              )
56
              .toQuery()
1✔
57
          );
58
          await prisma.$executeRawUnsafe(
1✔
59
            this.knex
1✔
60
              .raw(`GRANT pg_read_all_data TO ??`, [BASE_SCHEMA_TABLE_READ_ONLY_ROLE_NAME])
1✔
61
              .toQuery()
1✔
62
          );
63
        } catch (error) {
1!
64
          if (
×
65
            error instanceof Prisma.PrismaClientKnownRequestError &&
×
66
            (error?.meta?.code === '42710' || error?.meta?.code === '23505')
×
67
          ) {
×
68
            this.logger.warn(
×
69
              `read only role ${BASE_SCHEMA_TABLE_READ_ONLY_ROLE_NAME} already exists, error code: ${error?.meta?.code}`
×
70
            );
71
            return;
×
72
          }
×
73
          throw error;
×
74
        }
×
75
      });
1✔
76
    }
1✔
77
    return `postgresql://${BASE_SCHEMA_TABLE_READ_ONLY_ROLE_NAME}:${this.dsn.pass}@${this.dsn.host}:${this.dsn.port}/${this.dsn.db}${
87✔
78
      this.dsn.params
87✔
79
        ? `?${Object.entries(this.dsn.params)
87✔
80
            .map(([key, value]) => `${key}=${value}`)
87✔
81
            .join('&')}`
87✔
82
        : ''
×
83
    }`;
87✔
84
  }
87✔
85

86
  async onModuleInit() {
87✔
87
    if (this.driver !== DriverClient.Pg) {
87!
88
      return;
×
89
    }
×
90
    if (this.getDisablePreSqlExecutorCheck()) {
87!
91
      return;
×
92
    }
×
93
    // if pg_read_all_data role not exist, no need to create read only role
87✔
94
    this.hasPgReadAllDataRole = await this.roleExits('pg_read_all_data');
87✔
95
    if (!this.hasPgReadAllDataRole) {
87!
96
      return;
×
97
    }
×
98
    this.db = await this.createConnection();
87✔
99
  }
87✔
100

101
  async onModuleDestroy() {
87✔
102
    await this.db?.$disconnect();
86✔
103
  }
86✔
104

105
  private async createConnection(): Promise<PrismaClient | undefined> {
87✔
106
    if (this.db) {
87!
107
      return this.db;
×
108
    }
×
109
    const connectionConfig = await this.getReadOnlyDatabaseConnectionConfig();
87✔
110
    if (!connectionConfig) {
87!
111
      return;
×
112
    }
×
113
    const connection = new PrismaClient({
87✔
114
      datasources: {
87✔
115
        db: {
87✔
116
          url: connectionConfig,
87✔
117
        },
87✔
118
      },
87✔
119
    });
87✔
120
    await connection.$connect();
87✔
121

122
    // validate connection
87✔
123
    try {
87✔
124
      await connection.$queryRawUnsafe('SELECT 1');
87✔
125
      return connection;
87✔
126
      // eslint-disable-next-line @typescript-eslint/no-explicit-any
87✔
127
    } catch (error: any) {
87!
128
      await connection.$disconnect();
×
129
      throw new Error(`database connection failed: ${error.message}`);
×
130
    }
×
131
  }
87✔
132

133
  private getReadOnlyRoleName(baseId: string) {
87✔
134
    return `${BASE_READ_ONLY_ROLE_PREFIX}${baseId}`;
6✔
135
  }
6✔
136

137
  async createReadOnlyRole(baseId: string) {
87✔
138
    const roleName = this.getReadOnlyRoleName(baseId);
2✔
139
    await this.prismaService
2✔
140
      .txClient()
2✔
141
      .$executeRawUnsafe(
2✔
142
        this.knex
2✔
143
          .raw(
2✔
144
            `CREATE ROLE ?? WITH NOLOGIN NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE NOREPLICATION`,
2✔
145
            [roleName]
2✔
146
          )
147
          .toQuery()
2✔
148
      );
149
    await this.prismaService
2✔
150
      .txClient()
2✔
151
      .$executeRawUnsafe(
2✔
152
        this.knex.raw(`GRANT USAGE ON SCHEMA ?? TO ??`, [baseId, roleName]).toQuery()
2✔
153
      );
154
    await this.prismaService
2✔
155
      .txClient()
2✔
156
      .$executeRawUnsafe(
2✔
157
        this.knex.raw(`GRANT SELECT ON ALL TABLES IN SCHEMA ?? TO ??`, [baseId, roleName]).toQuery()
2✔
158
      );
159
    await this.prismaService
2✔
160
      .txClient()
2✔
161
      .$executeRawUnsafe(
2✔
162
        this.knex
2✔
163
          .raw(`ALTER DEFAULT PRIVILEGES IN SCHEMA ?? GRANT SELECT ON TABLES TO ??`, [
2✔
164
            baseId,
2✔
165
            roleName,
2✔
166
          ])
2✔
167
          .toQuery()
2✔
168
      );
169
  }
2✔
170

171
  async dropReadOnlyRole(baseId: string) {
87✔
172
    const roleName = this.getReadOnlyRoleName(baseId);
×
173
    await this.prismaService
×
174
      .txClient()
×
175
      .$executeRawUnsafe(
×
176
        this.knex.raw(`REVOKE USAGE ON SCHEMA ?? FROM ??`, [baseId, roleName]).toQuery()
×
177
      );
178
    await this.prismaService
×
179
      .txClient()
×
180
      .$executeRawUnsafe(
×
181
        this.knex
×
182
          .raw(`REVOKE SELECT ON ALL TABLES IN SCHEMA ?? FROM ??`, [baseId, roleName])
×
183
          .toQuery()
×
184
      );
185
    await this.prismaService
×
186
      .txClient()
×
187
      .$executeRawUnsafe(
×
188
        this.knex
×
189
          .raw(`ALTER DEFAULT PRIVILEGES IN SCHEMA ?? REVOKE ALL ON TABLES FROM ??`, [
×
190
            baseId,
×
191
            roleName,
×
192
          ])
×
193
          .toQuery()
×
194
      );
195
    await this.prismaService
×
196
      .txClient()
×
197
      .$executeRawUnsafe(this.knex.raw(`DROP ROLE IF EXISTS ??`, [roleName]).toQuery());
×
198
  }
×
199

200
  async grantReadOnlyRole(baseId: string) {
87✔
201
    const roleName = this.getReadOnlyRoleName(baseId);
×
202
    await this.prismaService
×
203
      .txClient()
×
204
      .$executeRawUnsafe(
×
205
        this.knex.raw(`GRANT USAGE ON SCHEMA ?? TO ??`, [baseId, roleName]).toQuery()
×
206
      );
207
    await this.prismaService
×
208
      .txClient()
×
209
      .$executeRawUnsafe(
×
210
        this.knex.raw(`GRANT SELECT ON ALL TABLES IN SCHEMA ?? TO ??`, [baseId, roleName]).toQuery()
×
211
      );
212
    await this.prismaService
×
213
      .txClient()
×
214
      .$executeRawUnsafe(
×
215
        this.knex
×
216
          .raw(`ALTER DEFAULT PRIVILEGES IN SCHEMA ?? GRANT SELECT ON TABLES TO ??`, [
×
217
            baseId,
×
218
            roleName,
×
219
          ])
×
220
          .toQuery()
×
221
      );
222
  }
×
223

224
  private async roleExits(role: string): Promise<boolean> {
87✔
225
    const roleExists = await this.prismaService.$queryRaw<
176✔
226
      { count: bigint }[]
227
    >`SELECT count(*) FROM pg_roles WHERE rolname=${role}`;
176✔
228
    return Boolean(roleExists[0].count);
176✔
229
  }
176✔
230

231
  private async roleCheckAndCreate(baseId: string) {
87✔
232
    if (this.driver !== DriverClient.Pg) {
2!
233
      return;
×
234
    }
×
235
    const roleName = this.getReadOnlyRoleName(baseId);
2✔
236
    if (!(await this.roleExits(roleName))) {
2✔
237
      await this.createReadOnlyRole(baseId);
2✔
238
    }
2✔
239
  }
2✔
240

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

246
  private async resetRole(prisma: Prisma.TransactionClient) {
87✔
247
    await prisma.$executeRawUnsafe(this.knex.raw(`RESET ROLE`).toQuery());
2✔
248
  }
1✔
249

250
  private async readonlyExecuteSql(sql: string) {
87✔
251
    return this.db?.$queryRawUnsafe(sql);
2✔
252
  }
2✔
253

254
  /**
87✔
255
   * check sql is safe
256
   * 1. role operations validation
257
   * 2. parse sql to valid table names
258
   * 3. read only role check table access
259
   */
87✔
260
  private async safeCheckSql(
87✔
261
    baseId: string,
2✔
262
    sql: string,
2✔
263
    opts?: { projectionTableDbNames?: string[]; projectionTableIds?: string[] }
2✔
264
  ) {
2✔
265
    const { projectionTableDbNames = [] } = opts ?? {};
2✔
266
    // 1. role operations keywords validation, only pg support
2✔
267
    if (this.driver == DriverClient.Pg) {
2✔
268
      validateRoleOperations(sql);
2✔
269
    }
2✔
270
    let tableNames = projectionTableDbNames;
2✔
271
    if (!projectionTableDbNames.length) {
2✔
272
      const tables = await this.prismaService.tableMeta.findMany({
1✔
273
        where: {
1✔
274
          baseId,
1✔
275
        },
1✔
276
        select: {
1✔
277
          dbTableName: true,
1✔
278
        },
1✔
279
      });
1✔
280
      tableNames = tables.map((table) => table.dbTableName);
1✔
281
    }
1✔
282
    // 2. parse sql to valid table names
2✔
283
    checkTableAccess(sql, {
2✔
284
      tableNames,
2✔
285
      database: this.driver,
2✔
286
    });
2✔
287
    // 3. read only role check table access, only pg and pg version > 14 support
2✔
288
    await this.readonlyExecuteSql(sql);
2✔
289
  }
2✔
290

291
  async executeQuerySql<T = unknown>(
87✔
292
    baseId: string,
2✔
293
    sql: string,
2✔
294
    opts?: {
2✔
295
      projectionTableDbNames?: string[];
296
      projectionTableIds?: string[];
297
    }
2✔
298
  ) {
2✔
299
    await this.safeCheckSql(baseId, sql, opts);
2✔
300
    await this.roleCheckAndCreate(baseId);
2✔
301
    return this.prismaService.$tx(async (prisma) => {
2✔
302
      try {
2✔
303
        await this.setRole(prisma, baseId);
2✔
304
        return await prisma.$queryRawUnsafe<T>(sql);
2✔
305
        // eslint-disable-next-line @typescript-eslint/no-explicit-any
2✔
306
      } catch (error: any) {
2✔
307
        throw new BadRequestException(error?.meta?.message || error?.message);
1!
308
      } finally {
2!
309
        await this.resetRole(prisma).catch((error) => {
2✔
310
          console.log('resetRole error', error);
1✔
311
        });
1✔
312
      }
2✔
313
    });
2✔
314
  }
2✔
315
}
87✔
STATUS · Troubleshooting · Open an Issue · Sales · Support · CAREERS · ENTERPRISE · START FREE · SCHEDULE DEMO
ANNOUNCEMENTS · TWITTER · TOS & SLA · Supported CI Services · What's a CI service? · Automated Testing

© 2026 Coveralls, Inc