10264510916

Committed 06 Aug 2024 10:07AM UTC coverage: 81.985% (+64.3%) from 17.734%

Build # 10264510916

Build Type

push

github

Committed by

web-flow

Commit Message

chore: adjusting copy-paste success toast disappearance time and chec… (#797)

* chore: adjusting copy-paste success toast disappearance time and checking browser support

* fix: db-connection owner check

Run Details

4272 of 4471 branches covered (95.55%)

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

28270 of 34482 relevant lines covered (81.98%)

1219.93 hits per line

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

19.62

/apps/nestjs-backend/src/features/base/db-connection.service.ts

import {
  BadRequestException,
  Injectable,
  InternalServerErrorException,
  NotFoundException,
} from '@nestjs/common';
import { ConfigService } from '@nestjs/config';
import type { IDsn } from '@teable/core';
import { DriverClient, parseDsn } from '@teable/core';
import { PrismaService } from '@teable/db-main-prisma';
import type { IDbConnectionVo } from '@teable/openapi';
import { Knex } from 'knex';
import { nanoid } from 'nanoid';
import { InjectModel } from 'nest-knexjs';
import { ClsService } from 'nestjs-cls';
import { BaseConfig, type IBaseConfig } from '../../configs/base.config';
import { InjectDbProvider } from '../../db-provider/db.provider';
import { IDbProvider } from '../../db-provider/db.provider.interface';
import type { IClsStore } from '../../types/cls';

@Injectable()
export class DbConnectionService {
  constructor(
    private readonly prismaService: PrismaService,
    private readonly cls: ClsService<IClsStore>,
    private readonly configService: ConfigService,
    @InjectDbProvider() private readonly dbProvider: IDbProvider,
    @InjectModel('CUSTOM_KNEX') private readonly knex: Knex,
    @BaseConfig() private readonly baseConfig: IBaseConfig
  ) {}

  private getUrlFromDsn(dsn: IDsn): string {
    const { driver, host, port, db, user, pass, params } = dsn;
    if (driver !== DriverClient.Pg) {
      throw new Error('Unsupported database driver');
    }

    const paramString =
      Object.entries(params as Record<string, unknown>)
        .map(([key, value]) => `${key}=${value}`)
        .join('&') || '';

    return `postgresql://${user}:${pass}@${host}:${port}/${db}?${paramString}`;
  }

  async remove(baseId: string) {
    if (this.dbProvider.driver !== DriverClient.Pg) {
      throw new BadRequestException(`Unsupported database driver: ${this.dbProvider.driver}`);
    }

    const readOnlyRole = `read_only_role_${baseId}`;
    const schemaName = baseId;
    return this.prismaService.$tx(async (prisma) => {
      // Verify if the base exists and if the user is the owner
      await prisma.base
        .findFirstOrThrow({
          where: { id: baseId, deletedTime: null },
        })
        .catch(() => {
          throw new BadRequestException('Only the base owner can remove a db connection');
        });

      // Revoke permissions from the role for the schema
      await prisma.$executeRawUnsafe(
        this.knex.raw('REVOKE USAGE ON SCHEMA ?? FROM ??', [schemaName, readOnlyRole]).toQuery()
      );

      await prisma.$executeRawUnsafe(
        this.knex
          .raw(`ALTER DEFAULT PRIVILEGES IN SCHEMA ?? REVOKE ALL ON TABLES FROM ??`, [
            schemaName,
            readOnlyRole,
          ])
          .toQuery()
      );

      // Revoke permissions from the role for the tables in schema
      await prisma.$executeRawUnsafe(
        this.knex
          .raw('REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA ?? FROM ??', [
            schemaName,
            readOnlyRole,
          ])
          .toQuery()
      );

      // drop the role
      await prisma.$executeRawUnsafe(
        this.knex.raw('DROP ROLE IF EXISTS ??', [readOnlyRole]).toQuery()
      );

      await prisma.base.update({
        where: { id: baseId },
        data: { schemaPass: null },
      });
    });
  }

  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 getConnectionCount(role: string): Promise<number> {
    const roleExists = await this.prismaService.$queryRaw<
      { count: bigint }[]
    >`SELECT COUNT(*) FROM pg_stat_activity WHERE usename=${role}`;
    return Number(roleExists[0].count);
  }

  async retrieve(baseId: string): Promise<IDbConnectionVo | null> {
    if (this.dbProvider.driver !== DriverClient.Pg) {
      return null;
    }

    const readOnlyRole = `read_only_role_${baseId}`;
    const publicDatabaseProxy = this.baseConfig.publicDatabaseProxy;
    if (!publicDatabaseProxy) {
      throw new NotFoundException('PUBLIC_DATABASE_PROXY is not found in env');
    }

    const { hostname: dbHostProxy, port: dbPortProxy } = new URL(`https://${publicDatabaseProxy}`);

    // Check if the base exists and the user is the owner
    const base = await this.prismaService.base.findFirst({
      where: { id: baseId, deletedTime: null },
      select: { id: true, schemaPass: true },
    });

    if (!base?.schemaPass) {
      return null;
    }

    // Check if the read-only role already exists
    if (!(await this.roleExits(readOnlyRole))) {
      throw new InternalServerErrorException(`Role does not exist: ${readOnlyRole}`);
    }

    const currentConnections = await this.getConnectionCount(readOnlyRole);

    const databaseUrl = this.configService.getOrThrow<string>('PRISMA_DATABASE_URL');
    const { db } = parseDsn(databaseUrl);

    // Construct the DSN for the read-only role
    const dsn: IDbConnectionVo['dsn'] = {
      driver: DriverClient.Pg,
      host: dbHostProxy,
      port: Number(dbPortProxy),
      db: db,
      user: readOnlyRole,
      pass: base.schemaPass,
      params: {
        schema: baseId,
      },
    };

    // Get the URL from the DSN
    const url = this.getUrlFromDsn(dsn);

    return {
      dsn,
      connection: {
        max: this.baseConfig.defaultMaxBaseDBConnections,
        current: currentConnections,
      },
      url,
    };
  }

  /**
   * public a schema specify and readonly connection
   *
   * check role is empty, if not, throw badRequest
   *
   * create a readonly role
   *
   * limit role to only access the schema
   */
  async create(baseId: string) {
    if (this.dbProvider.driver === DriverClient.Pg) {
      const readOnlyRole = `read_only_role_${baseId}`;
      const schemaName = baseId;
      const password = nanoid();
      const publicDatabaseProxy = this.baseConfig.publicDatabaseProxy;
      if (!publicDatabaseProxy) {
        throw new NotFoundException('PUBLIC_DATABASE_PROXY is not found in env');
      }

      const { hostname: dbHostProxy, port: dbPortProxy } = new URL(
        `https://${publicDatabaseProxy}`
      );
      const databaseUrl = this.configService.getOrThrow<string>('PRISMA_DATABASE_URL');
      const { db } = parseDsn(databaseUrl);

      return this.prismaService.$tx(async (prisma) => {
        await prisma.base
          .findFirstOrThrow({
            where: { id: baseId, deletedTime: null },
          })
          .catch(() => {
            throw new BadRequestException('only base owner can public db connection');
          });

        await prisma.base.update({
          where: { id: baseId },
          data: { schemaPass: password },
        });

        // Create a read-only role
        await prisma.$executeRawUnsafe(
          this.knex
            .raw(
              `CREATE ROLE ?? WITH LOGIN PASSWORD ? NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE NOREPLICATION CONNECTION LIMIT ?`,
              [readOnlyRole, password, this.baseConfig.defaultMaxBaseDBConnections]
            )
            .toQuery()
        );

        await prisma.$executeRawUnsafe(
          this.knex.raw(`GRANT USAGE ON SCHEMA ?? TO ??`, [schemaName, readOnlyRole]).toQuery()
        );

        await prisma.$executeRawUnsafe(
          this.knex
            .raw(`GRANT SELECT ON ALL TABLES IN SCHEMA ?? TO ??`, [schemaName, readOnlyRole])
            .toQuery()
        );

        await prisma.$executeRawUnsafe(
          this.knex
            .raw(`ALTER DEFAULT PRIVILEGES IN SCHEMA ?? GRANT SELECT ON TABLES TO ??`, [
              schemaName,
              readOnlyRole,
            ])
            .toQuery()
        );

        const dsn: IDbConnectionVo['dsn'] = {
          driver: DriverClient.Pg,
          host: dbHostProxy,
          port: Number(dbPortProxy),
          db: db,
          user: readOnlyRole,
          pass: password,
          params: {
            schema: baseId,
          },
        };

        return {
          dsn,
          connection: {
            max: this.baseConfig.defaultMaxBaseDBConnections,
            current: 0,
          },
          url: this.getUrlFromDsn(dsn),
        };
      });
    }

    throw new BadRequestException(`Unsupported database driver: ${this.dbProvider.driver}`);
  }
}

1	import {	4✔
2	BadRequestException,	4✔
3	Injectable,	4✔
4	InternalServerErrorException,	4✔
5	NotFoundException,	4✔
6	} from '@nestjs/common';	4✔
7	import { ConfigService } from '@nestjs/config';	4✔
8	import type { IDsn } from '@teable/core';	4✔
9	import { DriverClient, parseDsn } from '@teable/core';	4✔
10	import { PrismaService } from '@teable/db-main-prisma';	4✔
11	import type { IDbConnectionVo } from '@teable/openapi';	4✔
12	import { Knex } from 'knex';	4✔
13	import { nanoid } from 'nanoid';	4✔
14	import { InjectModel } from 'nest-knexjs';	4✔
15	import { ClsService } from 'nestjs-cls';	4✔
16	import { BaseConfig, type IBaseConfig } from '../../configs/base.config';	4✔
17	import { InjectDbProvider } from '../../db-provider/db.provider';	4✔
18	import { IDbProvider } from '../../db-provider/db.provider.interface';	4✔
19	import type { IClsStore } from '../../types/cls';	4✔
20		4✔
21	@Injectable()	4✔
22	export class DbConnectionService {	4✔
23	constructor(	76✔
24	private readonly prismaService: PrismaService,	76✔
25	private readonly cls: ClsService<IClsStore>,	76✔
26	private readonly configService: ConfigService,	76✔
27	@InjectDbProvider() private readonly dbProvider: IDbProvider,	76✔
28	@InjectModel('CUSTOM_KNEX') private readonly knex: Knex,	76✔
29	@BaseConfig() private readonly baseConfig: IBaseConfig	76✔
30	) {}	76✔
31		76✔
32	private getUrlFromDsn(dsn: IDsn): string {	76✔
33	const { driver, host, port, db, user, pass, params } = dsn;	×
34	if (driver !== DriverClient.Pg) {	×
35	throw new Error('Unsupported database driver');	×
36	}	×
37		×
38	const paramString =	×
39	Object.entries(params as Record<string, unknown>)	×
40	.map(([key, value]) => `${key}=${value}`)	×
41	.join('&') \|\| '';	×
42		×
43	return `postgresql://${user}:${pass}@${host}:${port}/${db}?${paramString}`;	×
44	}	×
45		76✔
46	async remove(baseId: string) {	76✔
47	if (this.dbProvider.driver !== DriverClient.Pg) {	×
48	throw new BadRequestException(`Unsupported database driver: ${this.dbProvider.driver}`);	×
49	}	×
50		×
51	const readOnlyRole = `read_only_role_${baseId}`;	×
52	const schemaName = baseId;	×
53	return this.prismaService.$tx(async (prisma) => {	×
54	// Verify if the base exists and if the user is the owner	×
55	await prisma.base	×
56	.findFirstOrThrow({	×
NEW 57	where: { id: baseId, deletedTime: null },	×
58	})	×
59	.catch(() => {	×
60	throw new BadRequestException('Only the base owner can remove a db connection');	×
61	});	×
62		×
63	// Revoke permissions from the role for the schema	×
64	await prisma.$executeRawUnsafe(	×
65	this.knex.raw('REVOKE USAGE ON SCHEMA ?? FROM ??', [schemaName, readOnlyRole]).toQuery()	×
66	);	×
67		×
68	await prisma.$executeRawUnsafe(	×
69	this.knex	×
70	.raw(`ALTER DEFAULT PRIVILEGES IN SCHEMA ?? REVOKE ALL ON TABLES FROM ??`, [	×
71	schemaName,	×
72	readOnlyRole,	×
73	])	×
74	.toQuery()	×
75	);	×
76		×
77	// Revoke permissions from the role for the tables in schema	×
78	await prisma.$executeRawUnsafe(	×
79	this.knex	×
80	.raw('REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA ?? FROM ??', [	×
81	schemaName,	×
82	readOnlyRole,	×
83	])	×
84	.toQuery()	×
85	);	×
86		×
87	// drop the role	×
88	await prisma.$executeRawUnsafe(	×
89	this.knex.raw('DROP ROLE IF EXISTS ??', [readOnlyRole]).toQuery()	×
90	);	×
91		×
92	await prisma.base.update({	×
93	where: { id: baseId },	×
94	data: { schemaPass: null },	×
95	});	×
96	});	×
97	}	×
98		76✔
99	private async roleExits(role: string): Promise<boolean> {	76✔
100	const roleExists = await this.prismaService.$queryRaw<	×
101	{ count: bigint }[]	×
102	>`SELECT count(*) FROM pg_roles WHERE rolname=${role}`;	×
103	return Boolean(roleExists[0].count);	×
104	}	×
105		76✔
106	private async getConnectionCount(role: string): Promise<number> {	76✔
107	const roleExists = await this.prismaService.$queryRaw<	×
108	{ count: bigint }[]	×
109	>`SELECT COUNT(*) FROM pg_stat_activity WHERE usename=${role}`;	×
110	return Number(roleExists[0].count);	×
111	}	×
112		76✔
113	async retrieve(baseId: string): Promise<IDbConnectionVo \| null> {	76✔
114	if (this.dbProvider.driver !== DriverClient.Pg) {	×
115	return null;	×
116	}	×
117		×
118	const readOnlyRole = `read_only_role_${baseId}`;	×
119	const publicDatabaseProxy = this.baseConfig.publicDatabaseProxy;	×
120	if (!publicDatabaseProxy) {	×
121	throw new NotFoundException('PUBLIC_DATABASE_PROXY is not found in env');	×
122	}	×
123		×
124	const { hostname: dbHostProxy, port: dbPortProxy } = new URL(`https://${publicDatabaseProxy}`);	×
125		×
126	// Check if the base exists and the user is the owner	×
127	const base = await this.prismaService.base.findFirst({	×
128	where: { id: baseId, deletedTime: null },	×
129	select: { id: true, schemaPass: true },	×
130	});	×
131		×
132	if (!base?.schemaPass) {	×
133	return null;	×
134	}	×
135		×
136	// Check if the read-only role already exists	×
137	if (!(await this.roleExits(readOnlyRole))) {	×
138	throw new InternalServerErrorException(`Role does not exist: ${readOnlyRole}`);	×
139	}	×
140		×
141	const currentConnections = await this.getConnectionCount(readOnlyRole);	×
142		×
143	const databaseUrl = this.configService.getOrThrow<string>('PRISMA_DATABASE_URL');	×
144	const { db } = parseDsn(databaseUrl);	×
145		×
146	// Construct the DSN for the read-only role	×
147	const dsn: IDbConnectionVo['dsn'] = {	×
148	driver: DriverClient.Pg,	×
149	host: dbHostProxy,	×
150	port: Number(dbPortProxy),	×
151	db: db,	×
152	user: readOnlyRole,	×
153	pass: base.schemaPass,	×
154	params: {	×
155	schema: baseId,	×
156	},	×
157	};	×
158		×
159	// Get the URL from the DSN	×
160	const url = this.getUrlFromDsn(dsn);	×
161		×
162	return {	×
163	dsn,	×
164	connection: {	×
165	max: this.baseConfig.defaultMaxBaseDBConnections,	×
166	current: currentConnections,	×
167	},	×
168	url,	×
169	};	×
170	}	×
171		76✔
172	/**	76✔
173	* public a schema specify and readonly connection	76✔
174	*	76✔
175	* check role is empty, if not, throw badRequest	76✔
176	*	76✔
177	* create a readonly role	76✔
178	*	76✔
179	* limit role to only access the schema	76✔
180	*/	76✔
181	async create(baseId: string) {	76✔
182	if (this.dbProvider.driver === DriverClient.Pg) {	×
183	const readOnlyRole = `read_only_role_${baseId}`;	×
184	const schemaName = baseId;	×
185	const password = nanoid();	×
186	const publicDatabaseProxy = this.baseConfig.publicDatabaseProxy;	×
187	if (!publicDatabaseProxy) {	×
188	throw new NotFoundException('PUBLIC_DATABASE_PROXY is not found in env');	×
189	}	×
190		×
191	const { hostname: dbHostProxy, port: dbPortProxy } = new URL(	×
192	`https://${publicDatabaseProxy}`	×
193	);	×
194	const databaseUrl = this.configService.getOrThrow<string>('PRISMA_DATABASE_URL');	×
195	const { db } = parseDsn(databaseUrl);	×
196		×
197	return this.prismaService.$tx(async (prisma) => {	×
198	await prisma.base	×
199	.findFirstOrThrow({	×
NEW 200	where: { id: baseId, deletedTime: null },	×
201	})	×
202	.catch(() => {	×
203	throw new BadRequestException('only base owner can public db connection');	×
204	});	×
205		×
206	await prisma.base.update({	×
207	where: { id: baseId },	×
208	data: { schemaPass: password },	×
209	});	×
210		×
211	// Create a read-only role	×
212	await prisma.$executeRawUnsafe(	×
213	this.knex	×
214	.raw(	×
215	`CREATE ROLE ?? WITH LOGIN PASSWORD ? NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE NOREPLICATION CONNECTION LIMIT ?`,	×
216	[readOnlyRole, password, this.baseConfig.defaultMaxBaseDBConnections]	×
217	)	×
218	.toQuery()	×
219	);	×
220		×
221	await prisma.$executeRawUnsafe(	×
222	this.knex.raw(`GRANT USAGE ON SCHEMA ?? TO ??`, [schemaName, readOnlyRole]).toQuery()	×
223	);	×
224		×
225	await prisma.$executeRawUnsafe(	×
226	this.knex	×
227	.raw(`GRANT SELECT ON ALL TABLES IN SCHEMA ?? TO ??`, [schemaName, readOnlyRole])	×
228	.toQuery()	×
229	);	×
230		×
231	await prisma.$executeRawUnsafe(	×
232	this.knex	×
233	.raw(`ALTER DEFAULT PRIVILEGES IN SCHEMA ?? GRANT SELECT ON TABLES TO ??`, [	×
234	schemaName,	×
235	readOnlyRole,	×
236	])	×
237	.toQuery()	×
238	);	×
239		×
240	const dsn: IDbConnectionVo['dsn'] = {	×
241	driver: DriverClient.Pg,	×
242	host: dbHostProxy,	×
243	port: Number(dbPortProxy),	×
244	db: db,	×
245	user: readOnlyRole,	×
246	pass: password,	×
247	params: {	×
248	schema: baseId,	×
249	},	×
250	};	×
251		×
252	return {	×
253	dsn,	×
254	connection: {	×
255	max: this.baseConfig.defaultMaxBaseDBConnections,	×
256	current: 0,	×
257	},	×
258	url: this.getUrlFromDsn(dsn),	×
259	};	×
260	});	×
261	}	×
262		×
263	throw new BadRequestException(`Unsupported database driver: ${this.dbProvider.driver}`);	×
264	}	×
265	}	76✔

teableio / teable / 10264510916

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