Can't access proxy database

Community Help
1

Continuing the discussion from PostgreSQL "connection terminated" - quite frustrated :confused::

I’ve been stuck trying to get a proxy database set up for the past several days. If anyone has any tips, I’d really appreciate it.

  • My database is in a docker container inside a VPS.

  • I’m able to run the observable proxy server at http://MY_VPS_IP:2899

  • I’m able to connect to the database inside the VPS with psql postgresql://user:password@127.0.0.1:5432/my-db-name

  • If I go to http://MY_IP:2899 on the browser I see a 401 error in the proxy log (as I should)

  • when I run the “Test connection” from Observable, I don’t see any connection attempt in the proxy log, just this error “Connection terminated unexpectedly”:

image
image824×97 10.8 KB

1 Like
2

Have you tried switching to https (assuming your proxy has a valid certificate)?

Note the following part of the database-proxy documentation:

If you’re using Chrome or Edge, and running the database proxy on your local computer (at 127.0.0.1), you can connect to it directly with HTTP — there’s no need to set up a self-signed SSL certificate for the proxy.

If you’re using Firefox or Safari, or if you wish to run the database proxy on a different computer on your intranet, you can create a self-signed SSL certificate and configure the database proxy to use it in order to proxy over HTTPS. Be sure to “Require SSL/TLS” in the Observable configuration, and specify the --sslcert and --sslkey options when running the database proxy.

3

Thanks. I’m using Chrome and http.

4

Rereading your initial question, you have a remote VPS, correct? In that case there’s no way around https.

As far as I understand the proxy is meant to be run locally, in order to bridge access to networks that are otherwise inaccessible from the web.

1 Like
5

You’re right. I used the --sslcert and --sslkey settings (along with the ?sslmode=require in the connection string) and it works!

I was almost ready to give up because testing the connection in /settings/databases page kept giving me the same error as above, but when I tried it in the notebook it worked.

For the record, I’m running the proxy in a remote server.

2 Likes
6

For the record, although I’m able to get the connection to work and I receive initial data, as soon as I query more than a few dozen rows, I get an out of memory error in the server:

events.js:291
      throw er; // Unhandled 'error' event
      ^

error: out of memory
    at Connection.parseE (/usr/lib/node_modules/@observablehq/database-proxy/node_modules/pg/lib/connection.js:614:13)
    at Connection.parseMessage (/usr/lib/node_modules/@observablehq/database-proxy/node_modules/pg/lib/connection.js:413:19)
    at TLSSocket.<anonymous> (/usr/lib/node_modules/@observablehq/database-proxy/node_modules/pg/lib/connection.js:129:22)
    at TLSSocket.emit (events.js:314:20)
    at TLSSocket.EventEmitter.emit (domain.js:483:12)
    at addChunk (_stream_readable.js:298:12)
    at readableAddChunk (_stream_readable.js:273:9)
    at TLSSocket.Readable.push (_stream_readable.js:214:10)
    at TLSWrap.onStreamRead (internal/stream_base_commons.js:188:23)
Emitted 'error' event on Pool instance at:
    at Client.idleListener (/usr/lib/node_modules/@observablehq/database-proxy/node_modules/pg-pool/index.js:59:10)
    at Client.emit (events.js:314:20)
    at Client.EventEmitter.emit (domain.js:483:12)
    at connectedErrorHandler (/usr/lib/node_modules/@observablehq/database-proxy/node_modules/pg/lib/client.js:202:10)
    at Connection.connectedErrorMessageHandler (/usr/lib/node_modules/@observablehq/database-proxy/node_modules/pg/lib/client.js:209:7)
    at Connection.emit (events.js:314:20)
    at Connection.EventEmitter.emit (domain.js:483:12)
    at TLSSocket.<anonymous> (/usr/lib/node_modules/@observablehq/database-proxy/node_modules/pg/lib/connection.js:134:12)
    at TLSSocket.emit (events.js:314:20)
    at TLSSocket.EventEmitter.emit (domain.js:483:12) {
  length: 154,
  severity: 'ERROR',
  code: '54000',
  detail: 'Cannot enlarge string buffer containing 0 bytes by 1130312700 more bytes.',

Apparently Postgres is being asked to process more than 1GB and it’s erroring out. I’ve looked at @visnup 's database-proxy code and the only difference between it and what I do outside of observable (which works) is this call to QueryStream.

I typically just do this:

await client.query(sql)

I’m not sure if that’s the reason, though.

7

Do you have very wide tables with lots of columns coming back? The machine the hosted proxy is running on could have less memory allocated to it than your local machine… It’s interesting that locally you seem to be able to load up the entire dataset into memory. That QueryStream version is specifically to reduce memory pressure to avoid issues like this. :thinking:

Oh, this is using the self-hosted version. Hm actually, that error message is kinda suspicious, talking about going from 0 to something…

8

Just to double-check: what npm pg versions are you using when you do the await client.query(sql)? Much different from pg@7.11.0?