Generating .tableplusconnection files

[bbeckford]

trafficstars

I'm using TablePlus (build 136) on windows.

I'm interested in generating .tableplusconnection config files so that I can quickly make configs for developers on my team.

I appreciate this might be a proprietary format but I thought I'd ask on the off-chance that it might be possible to share both the underlying file format and the encryption method so that I can generate them myself with code.

Thanks!

[tamvm]

I am migrating my db connections, there's an option to export exisiting connections to .tableplusconnection but there's no way to import this file, any suggestion ?

[huyphams]

It's right above the "export connections"

[phantomwhale]

Was also looking to script up creating DB connections from a common repository, so I can help users quickly populate their TablePlus connections list - but doesn't look like there is an easy way to do this?

[zoispag]

@huyphams we are also interested in creating DB connections, for example using an ansible inventory to have a list of database urls, and using cli command to retrieve the password.

[tomquist]

We're also interested in this.

[bd1308]

I'm very interested in this, having the ability to generate these profiles, or having any knowledge of the encryption algo will help me script up generating these for various team-members.

[bd1308]

I'm not sure if this is very helpful at all, but local storage for the connections are stored in ~/Library/Application Support/com.tinyapp.TablePlus/Data/Connections.plist. I tested duplicating the element and renaming it and changing some of the info, and the application. The password seems to be stored somewhere else (keychain?), blanking out the ID guid (empty) seems to allow saving a password specific to that entity.

[louisabraham]

I would also love to generate .tableplusconnection files!

[louisabraham]

Hey there!

I used otool -tvV on the macOS binary and by looking for strings related to encryption (eg "encrypt" or "password"), I found references to https://github.com/RNCryptor/RNCryptor

When I generated a few files, they all started with 03 01, which is consistent with the RNCryptor file format

I found a Python implementation (just pip install rncryptor).

Here is how to read .tableplusconnection files:

import json

import rncryptor

file = "path to your file.tableplusconnection"
password = 'your password'

with open(file, "br") as f:
    enc = f.read()
cryptor = rncryptor.RNCryptor()
data = cryptor.decrypt(enc, password)
json_data = json.loads(data)

print(json_data)

Encryption works the same with cryptor.encrypt.

The json is a list of dicts looking like this:

    {
        "ShowSystemSchemas": 0,
        "ShowSequenceSection": 0,
        "DatabasePasswordMode": 0,
        "PreloadAllSchemaItems": 0,
        "ShowRecentlySection": 0,
        "isUseSocket": 0,
        "ConnectionName": "......",
        "ServerUser": "",
        "ItemGroups": [],
        "Enviroment": "local",
        "ServerPassword": "",
        "ShowUserDefineSection": 0,
        "HideFunctionSection": 0,
        "DatabaseKeyPassword": "",
        "StartupCommands": "",
        "DatabaseSocket": "",
        "DatabaseUser": "......",
        "DatabasePort": "443",
        "isOverSSH": 0,
        "LimitQueryRowsReturned": 0,
        "RecentlySchema": [
            "......"
        ],
        "DriverVersion": 0,
        "DatabaseUserRole": "",
        "StartupBashScript": "",
        "tLSMode": 2,
        "DatabaseType": "",
        "OtherOptions": [],
        "RecentUsedBackupDriverName": "",
        "statusColor": "#686B6F",
        "DatabaseWarehouse": "",
        "ServerPasswordMode": 0,
        "SectionStates": {
            "llm.Functions": "collapsed",
            "llm.Tables": "expanded"
        },
        "DatabaseHost": "......",
        "DatabasePassword": "......",
        "DatabaseName": "......",
        "RecentUsedBackupOptions": [],
        "SafeModeLevel": 0,
        "TlsKeyPaths": [
            "......",
            "......",
            "......",
        ],
        "RecentlyOpened": [
            "......",
            "......",
            "......",
            "......",
            "......"
        ],
        "ServerAddress": "127.0.0.1",
        "RecentUsedBackupGzip": 0,
        "AdvancedSafeModeLevel": 0,
        "LimitRowsReturned": 0,
        "Favorites": {},
        "Driver": "PostgreSQL",
        "Authenticator": "",
        "DatabaseEncoding": "",
        "ServerPort": "22",
        "DatabasePath": "",
        "isUsePrivateKey": 0,
        "RecentUsedRestoreOptions": [],
        "TlsKeyName": "......"
    },

[bbeckford]

Great find @louisabraham, thanks!

I'll try and generate one myself (I'll be working in PHP), I'll share my findings.

[bd1308]

@louisabraham huge favor, can you create one of these and try to import it into TablePlus? I wrote out a thing to generate these, but anything I encrypt with RNCryptor as above silently fails to import (unless there's a logfile somewhere other than stdout/stderr when you run Tableplus from a terminal). I can't tell what it's erroring on, but I am unable to decrypt a working file, encrypt the underlying JSON and import it. Were you able to get the create tableplusconnection file to successfully import?

[louisabraham]

import json

import rncryptor

file = "/Users/louis/Downloads/tableplus_connection_dump_21_august_2024.tableplusconnection"
password = 'qwerty'

with open(file, "br") as f:
    enc = f.read()
cryptor = rncryptor.RNCryptor()
data = cryptor.decrypt(enc, password)
json_data = json.loads(data)

data = cryptor.encrypt(json.dumps(json_data), password)
with open(file, "bw") as f:
    f.write(data)

I was able to import this file into TablePlus. Also, RNCryptor is deterministic so if I just rencrypt data it will be exactly the same. Instead, json.dumps added some whitespaces and newlines.

[louisabraham]

@bd1308 Maybe share your code? Without any secret.

I would suggest to start from an exported file, then just modify it.

[bd1308]

@louisabraham I started again with an export and it's working now, thank you!

[electr0sheep]

@louisabraham is goated. On the other hand, it's pretty dumb we have to resort to this to manipulate the json file. I had a ton of connections I needed to edit, and editing the json saved me a ton of time. The encryption should be optional imo.