Ability to "lock down" settings (especially for internet-facing features)

[coussens]

What

I was wondering if it would be possible allow an admin to "lock down"/"block" DBCode settings/functionality, especially for features that can result in information leaving the premises (like AI, history Sync, etc).

Why (problem/use case)

I have a teams subscription, and I'd like to set up pre-configured environments for colleagues (many of whom are not developers or data scientists, and don't have a lot of prior exposure to IDEs). My concern isn't malicious intent, but accidental enabling of functionality that would conflict with company policies. I imagine this is probably a common concern for organizations that handle sensitive data!

[mikeburgh]

I have been working towards this for a while, it sort of falls into two parts..

Part 1.. I just released 1.30.1 which should let you do what you need, you can now create roles, or use the pre built ones and assign them to team members to restrict feature use… the docs on the features you can restrict and the roles are here: https://dbcode.io/docs/accounts/team-roles If there are other features you want to restrict let me know and I can plug them in rather quickly.. I tried to cover the big ones for taking data out/ai usage.

Like you mentioned, this is not so much about security/malicious intent, more about policy.. as with this roles functionality someone can log out of the team account and then use the export feature, or copy feature if they had them previously disabled. They would need to login to another account to use AI or sharing.. but idea is the same.. since the team account does not control/gate the connection they can connect to it.

Part 2 is the bigger lift that this is tied into (and the recent work on the Library section).. I am moving towards having the team admin/owner accounts be able to create a connection and share it to roles or individual members.. the big issue is the authentication details within that connection. I don't want to store it server side in anyway that can be decrypted (same approach as history sync/sharing etc) so need to work through the key exchange and making that seamless for team members but secure at the same time..

Once that's figured out, then the team account would own the connection and the feature restrictions would become closer to a security boundary (but not totally, see next point).. since they sign out of the extension they loose access to the connection.

It's not totally secure though when it comes to settings like data copy/data export, since VSCode is still a webpage, they can open up the dev tools and copy the html content from the grid to get the data, and a few other places.

Is connection sharing of interest to you in the team environment you are running ? Still a little ways off, but great to chat about needs and uses cases while I work through it.

[coussens]

Thanks for this! Re connection sharing: for our use case, we wouldn't want anything leaving our cloud infrastructure at all, so while it could certainly be helpful functionality, we would only use it if we could import/export the connection files locally (or on S3, etc), and not using a third-party cloud service.

[mikeburgh]

Yeah, I don't want to really store the connections details on my side either, as it's a risk/complication I would prefer to avoid.

Thinking through your S3 comment.. do your users have aws accounts ? one idea could be to let you select a backend for storing the config's.. so S3, Github, Google Cloud etc, and then the users in your team could authentication against that backend via the extension which would be where the stored configs are (extension would manage all of this using the cli tools or node sdk's)..

In the case of cloud providers we could use their secret managers to store the auth (if it was not already setup to use IAM on the database).

This keeps all the information in your existing cloud solutions and on local devices (no new third party service), and access using your existing infrastructure if all your users are setup in AWS as well..

The backends could also include some of the popular vault products as well, as I believe a lot of those now also can store configs, so it's just a matter of having pluggable backends for storing shared configs between teams..

[coussens]

Yes, in our use case, the instance on which the user is using DBCode would have an IAM policy that could allow access to an S3 bucket.

If db schemas (corresponding to these connections) could also be stored on S3, that would be amazing, too! Our users need to be able to connect to a number of large Athena (i.e. Glue) databases which can be quite slow to load, so having a centralized cache location with all the schema data (which could be copied to the DBCode instances) would really speed up schema loading.

[coussens]

Also @mikeburgh -- since the ability to "lock down" functionality is dependent on users having a "team" license installed, I was wondering whether it would be possible to introduce a setting (disabled by default of course, since this likely isn't of interest for most users) that blocks use of DBCode unless a license is provided? Something simple like a flag in a settings file that I could set before onboarding a team member to a dev environment.

The reason why I ask is because I've noticed it's pretty easy for me to miss whether i'm logged into DBCode or not, so I just wanted to avoid the potential for users on my team to accidentally work "off-license" in violation of policy.

[mikeburgh]

I just shipped the lock down env as part of #1055.

Storing the cache in s3 is an interesting option.. it makes sense since the connection config is there.. and last write wins is fine since that will always be the most current… will keep that on the table as I work on the functionality for config sharing.

In the meantime, if you edit the Athena connection in DBCode, go to the bottom in the advanced section, and expand the introspection section, make sure the progressive loading option is ticked, and the background one is un ticked.

I am slowly rolling the progressive loading out to all drivers (about 10 left) and once that's done it will become the default option, and eventually not even an option, just the way it works.

It should solve the slow loading problem, as it will only fetch schema information as you need it (browsing the tree, opening a table, referencing one in a query for auto complete etc).. making initial connections a lot faster in large db's and not harming the UX too much (brief pause on initial request, and much longer cache times, stored per individual object).. you can enable it to fetch in the background, which is sort of the trade off between the two.. it batches them up into groups and background the process to not block the ui or the connection.