logo Windmill Hub
Login
All items

Postgres with TLS / SSL

example on how to run a sql against a database that needs a ca certificate

Script postgresql Verified

by sindre svendby964 ยท 5/15/2023

Use in Windmill

The script

Submitted by hugo989 Bun
Verified 4 days ago
All edits
Permalink
1 
import { Client } from "pg";
2
3 
type Postgresql = {
4 
  host: string;
5 
  port: number;
6 
  user: string;
7 
  dbname: string;
8 
  sslmode: string;
9 
  password: string;
10 
};
11
12 
type Cacertificate = {
13 
  certificate: string;
14 
};
15 
export async function main(
16 
  dbConfig: Postgresql,
17 
  cacertificate: Cacertificate,
18 
  sql: string = "SELECT 1 as id",
19 
) {
20 
  const client = new Client({
21 
    host: dbConfig.host,
22 
    port: dbConfig.port,
23 
    user: dbConfig.user,
24 
    database: dbConfig.dbname,
25 
    password: dbConfig.password,
26 
    ssl: {
27 
      rejectUnauthorized: true,
28 
      ca: cacertificate.certificate,
29 
    },
30 
  });
31 
  await client.connect();
32
33 
  const res = await client.query(sql);
34
35 
  await client.end();
36
37 
  return res.rows;
38 
}
39
Other submissions
  • Submitted by sindre svendby964 Deno
    Created 1119 days ago
    All edits
    Permalink
    1 
    import { Client } from "https://deno.land/x/[email protected]/mod.ts";
    2 
    import * as wmill from "https://deno.land/x/[email protected]/mod.ts";
    3
    4 
    //PG parameterized statement. No SQL injection is possible.
    5 
    export async function main(
    6 
      dbConfig: wmill.Resource<"postgresql">,
    7 
      tlsSettings: wmill.Resource<"postgres-tls">,
    8 
      sql: string,
    9 
    ) {
    10 
      // small hack since we want to store the caCertificate as a secret in winmill.
    11 
      dbConfig.tls = tlsSettings;
    12 
      dbConfig.tls.caCertificates = [dbConfig.tls.caCertificate];
    13
    14 
      const client = new Client(dbConfig);
    15 
      await client.connect();
    16
    17 
      const res = await client.queryObject(
    18 
        sql,
    19 
      );
    20
    21 
      await client.end();
    22
    23 
      return res.rows;
    24 
    }
    25
  • Submitted by sindre svendby964 Deno
    Created 1119 days ago
    All edits
    Permalink
    1 
    import { Client } from "https://deno.land/x/[email protected]/mod.ts";
    2 
    import { Resource } from "https://deno.land/x/[email protected]/mod.ts";
    3
    4 
    export async function main(
    5 
      dbConfig: Resource<"postgresql">,
    6 
      cacertificate: Resource<"postgresql-cacertificate">,
    7 
      sql: string = "SELECT 1 as id",
    8 
    ) {
    9
    10 
      dbConfig.tls = {
    11 
          enabled: true,
    12 
          enforce: true,
    13 
          caCertificates: [cacertificate.certificate]
    14 
      }
    15 
      console.log(dbConfig.tls)
    16 
      const client = new Client(dbConfig);
    17 
      await client.connect();
    18
    19 
      const res = await client.queryObject(
    20 
        sql,
    21 
      );
    22
    23 
      await client.end();
    24
    25 
      return res.rows;
    26 
    }
  • Submitted by sindre svendby964 Deno
    Created 396 days ago
    All edits
    Permalink
    1 
    import { Client } from "https://deno.land/x/[email protected]/mod.ts";
    2
    3 
    type Postgresql = {
    4 
      host: string;
    5 
      port: number;
    6 
      user: string;
    7 
      dbname: string;
    8 
      sslmode: string;
    9 
      password: string;
    10 
    };
    11
    12 
    type Cacertificate = {
    13 
      certificate: string;
    14 
    };
    15 
    export async function main(
    16 
      dbConfig: Postgresql,
    17 
      cacertificate: Cacertificate,
    18 
      sql: string = "SELECT 1 as id",
    19 
    ) {
    20 
      dbConfig.tls = {
    21 
        enabled: true,
    22 
        enforce: true,
    23 
        caCertificates: [cacertificate.certificate],
    24 
      };
    25 
      const client = new Client(dbConfig);
    26 
      await client.connect();
    27
    28 
      const res = await client.queryObject(sql);
    29
    30 
      await client.end();
    31
    32 
      return res.rows;
    33 
    }
    34