pgcrypto extension

[tonyxiao]

pgcrypto is one of the most used postgres extension out there. it seems that there has been some attempt to support it in the past but the code has never been merged. Are there plans to support pgcrypto?

I even see a pgcrypto.tar.gz file in the npm package itself, but no reference to it alas.

[tonyxiao]

Fwiw i tried to use the pgcrypto file like so

/* eslint-disable arrow-body-style */
import {PGlite} from '@electric-sql/pglite'
import type {
  Extension,
  ExtensionSetupResult,
  PGliteInterface,
} from '@electric-sql/pglite'

const baseUrl = await import.meta.resolve?.('@electric-sql/pglite')

console.log('baseUrl', baseUrl)

const setup = async (_pg: PGliteInterface, _emscriptenOpts: any) => {
  return {
    bundlePath: new URL('pgcrypto.tar.gz', baseUrl),
  } satisfies ExtensionSetupResult
}

const pgcrypto = {
  name: 'pgcrypto',
  setup,
} satisfies Extension

const pglite = new PGlite({
  extensions: {
    pgcrypto,
  },
})
await pglite.query('CREATE EXTENSION IF NOT EXISTS pgcrypto')
const res = await pglite.query('SELECT 1')
console.log(res.rows)

but got the following error at runtime

1 | import{b as ae,e as s,f as h,g as f,h as p,i as oe,j as y}from"./chunk-BTBUZ646.js";var hn={};ae(hn,{ABSTIME:()=>Et,ACLITEM:()=>Vt,BIT:()=>Wt,BOOL:()=>be,BPCHAR:()=>_e,BYTEA:()=>ge,CHAR:()=>gt,CID:()=>St,CIDR:()=>Tt,CIRCLE:()=>Ut,DATE:()=>He,FLOAT4:()=>je,FLOAT8:()=>Qe,GTSVECTOR:()=>rn,INET:()=>kt,INT2:()=>ve,INT4:()=>Ge,INT8:()=>we,INTERVAL:()=>vt,JSON:()=>Ae,JSONB:()=>Ye,MACADDR:()=>Ot,MACADDR8:()=>Nt,MONEY:()=>Lt,NUMERIC:()=>Qt,OID:()=>We,PATH:()=>Mt,PG_DEPENDENCIES:()=>en,PG_LSN:()=>Xt,PG_NDISTINCT:()=>Zt,PG_NODE_TREE:()=>Bt,POLYGON:()=>Rt,REFCURSOR:()=>_t,REGCLASS:()=>Yt,REGCONFIG:()=>sn,REGDICTIONARY:()=>an,REGNAMESPACE:()=>on,REGOPER:()=>Ht,REGOPERATOR:()=>qt,REGPROC:()=>wt,REGPROCEDURE:()=>zt,REGROLE:()=>un,REGTYPE:()=>$t,RELTIME:()=>Ct,SMGR:()=>It,TEXT:()=>F,TID:()=>At,TIME:()=>Ft,TIMESTAMP:()=>qe,TIMESTAMPTZ:()=>xe,TIMETZ:()=>Gt,TINTERVAL:()=>Pt,TSQUERY:()=>nn,TSVECTOR:()=>tn,TXID_SNAPSHOT:()=>Jt,UUID:()=>Kt,VARBIT:()=>jt,VARCHAR:()=>ze,XID:()=>xt,XML:()=>Dt,arrayParser:()=>yn,arraySerializer:()=>Ke | ... truncated

error: could not load library "/tmp/pglite/lib/postgresql/pgcrypto": Could not load dynamic lib: /tmp/pglite/lib/postgresql/pgcrypto
Error: bad export type for 'EVP_bf_cbc': undefined
     length: 241,
   severity: "ERROR",
     detail: undefined,
       hint: undefined,
   position: undefined,
 internalPosition: undefined,
 internalQuery: undefined,
      where: undefined,
     schema: undefined,
      table: undefined,
   dataType: undefined,
 constraint: undefined,
       file: "dfmgr.c",
    routine: "internal_load_library",
       code: "58P01"

      at new E (/Users/tony/Code/openintegrations/openint/node_modules/.pnpm/@[email protected]/node_modules/@electric-sql/pglite/dist/chunk-EADU5A67.js:1:1)

Bun v1.2.1 (macOS arm64)

[pmp-p]

i don't think the wasm openssl build we are using has support for blowfish cypher. It only has support for hash types required by python wasm build and has not been tested outside that.

most likely autotools/configure is assuming too much of openssl is present at build time.

[charleslowell-valstro]

I've tried to "fake" it with a javascript only extension since the only function that I'm using is gen_random_uuid()

import { randomUUID } from "node:crypto";

const pgcrypto: Extension = {
  name: "pgcrypto",
  setup() {
    return Promise.resolve({
      namespaceObj: {
        gen_random_uuid() {
          return Promise.resolve(randomUUID());
        }
      }
    });
  }
}

But It fails to create the extension

error: extension "pgcrypto" is not available

Is there a reason that I can't just do it that way?

[zhaungueigyea]

I've tried to "fake" it with a javascript only extension since the only function that I'm using is gen_random_uuid()

import { randomUUID } from "node:crypto";

const pgcrypto: Extension = { name: "pgcrypto", setup() { return Promise.resolve({ namespaceObj: { gen_random_uuid() { return Promise.resolve(randomUUID()); } } }); } } But It fails to create the extension

error: extension "pgcrypto" is not available

Is there a reason that I can't just do it that way?

Guess better to use external uuid or crypto related functions at the moment...

[charleslowell-valstro]

@zhaungueigyea I think that's what I'm trying to do: use the nodejs uuid generator.

[zhaungueigyea]

@zhaungueigyea I think that's what I'm trying to do: use the nodejs uuid generator.

Ye and also forget about other pgcrypto functions at the moment, also, actually web crypto API is way much better

[ragrag]

@charleslowell-valstro any luck with the js land alternative?

[charleslowell-valstro]

@ragrag Sadly no.

[pmp-p]

the pgcrypto.tar.gz pkg from 0.3.6 and after should support all ssl expected features.

pgcrypto.tar.gz

please test and report any problem

[brendanator]

@pmp-p How can I use this tar file? I want to use the pgp_* functions. Are they available?