DB Connections + Public Notebooks?

Community Help
1

Is there any possibility of making notebooks that use a db connection public? I assume the reason this isn’t allowed has to do with security, but if the connection credentials only allow for a read and not write then is there still a security vulnerability?

If this isn’t a possibility is there anyway of sharing notebooks that use a db connection internally within a team?

2

Your assumptions are correct in that it’s a large liability (not really in the legal sense, but maybe legally too) to allow a public-facing database connection since it’d be quite difficult to ensure everything is locked down. We do advise best practices and even added a warning if you provide credentials with write access, but there could also easily be sensitive data available in the same database.

That being said, we’d love to be able to do this! Haha. Most recently, we were working with the Splitgraph team to make sure people could connect to their datasets using Observable database clients, and that would be a great use case of public db connections. We’ve been trying to figure out how to do it responsibly.

But team-shared database connections (and Secrets) are already a team feature. Everyone on the team can use the team-configured connections (we use them every day on the Observable team).

P.S. Thanks for the SF bay area basemap! I recently used it while exploring purpleair data!

2 Likes
3

Chris, if you want to learn more about the team-shared database connections, please refer to this notebook

1 Like
4

Thanks @Cobus!

5

Thanks @visnup, sounds like teams are the way to go. I will keep an eye out for public db connections.

Glad the SF Bay Area basemap is getting some use too!

6

Hi @visnup firstly, thanks for creating this awesome feature!

If I rephrase this question a bit: DB Connections + Downloaded Notebooks

Use case: I download the private notebook with the DB connection and want to use it in an off-network web app (or maybe just from the downloaded package in a httpserver), it’s basically an interface to the backend db, quickly prototyped in observable but needs to function without a notebook, would it be possible to configure the downloaded version to work with the database proxy? Thanks for taking a look at this.

1 Like
7

Hi @a10k
Good question. Unfortunately we don’t think there is a simple answer to your question. The DatabaseClient code will not run in the downloaded notebook. It requires the configuration data in our application servers which are note exposed. This will not be straightforward, but what you will probably have to do is:

  1. Install a self-hosted database proxy, and use a blank shared secret, and configure it to connect to your database.

  2. In your downloaded code, you will need to look for the cells with your queries and replace those queries with fetches from your database proxy.

Sorry that there is no simple answer for you at this time.

2 Likes
8

Thanks @Cobus! Got it, thanks for sharing the workaround! I’ll try that.

9

Please let us know how it goes…

10

Hi again,

What you suggested mostly worked for me i.e. using fetch, but the proxy code required few changes too:

  1. The shared secret from observable was a base64 json that contained the secret as a property along with origin etc., So I had to base 64 encode a dummy json in my ~/.observablehq
  2. I had to comment out some of the authorization code in server.js and also set the header res.setHeader(“Access-Control-Allow-Origin”, req.headers.origin);
  3. then the fetch returned data :smiley: 
fetch("http://127.0.0.1:2899/query", {
  body: "{\"sql\":\"SELECT * FROM test;\"}",
  method: "POST"
}).then(d => d.json())
  1. I think I now need to write a small wrapper using the sqljs example replacing the query with fetch and name in DatabaseClient to use in the downloaded notebooks!

Thanks again for pointing me in the right direction!

3 Likes
11

Fantastic! Thanks for sharing your experience. Please feel free to send the changes you had to make to server.js to me at cobus@o…hq.com, so we can look into making that part of the self-hosted proxy.

1 Like
12

Glad you made some progress!

Now that you’ve got it working, I’d recommend trying to layer back in some of the security.

You can keep an origin check by explicitly checking it against a known good list or maybe simply ‘localhost’ depending on your case? I’m unsure what the download case will send as an origin, but you can check it at that same spot and set the header accordingly. Something like:

const origins = new Set(['localhost:8080', 'internal.name.test:3000']);
if (origins.has(req.headers.origin))
  res.setHeader('Access-Control-Allow-Origin', req.headers.origin);

This will protect against any random website you visit testing localhost for anything that responds and running things against it. Without the origin check, your browser + self-hosted proxy will happily send it data.

And, secret-wise, I’d also recommend generating your own random value and using that as an additional check via an Authorization header. So replacing database-proxy/server.js at main · observablehq/database-proxy · GitHub with something like:

if (authorization !== 'randomsecretvalue')
  throw unauthorized('Invalid authorization');

and pass the value via fetch:

fetch('http://127.0.0.1:2899/query', {
  headers: { authorization: 'token randomsecretvalue' },
  body: ...
});

This will protect slightly further against data exfiltration. It’s definitely not perfect since that secret isn’t stored very well, but it’s at least another layer.

Like @Cobus said, let us know how it goes and if you have ideas on how to make the database-proxy side simpler for this case.

2 Likes
13

Thanks @visnup! I was tinkering with something along the same lines, but being not sure if I want to fiddle with the original proxy that works perfectly for all the use cases, except this one notebook, I created a simple standalone nodejs script for the downloaded version, and also made a notebook+runkit demo for explaining the poc here: Remote Database Proxy / Alok Pepakayala / Observable for anyone who is trying something along these lines, I linked this post there as well. Thanks again for all your help!

1 Like