New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Favorites - Mass edit - Import and Export #598

Closed
sparkacus opened this Issue Oct 18, 2018 · 19 comments

Comments

Projects
None yet
2 participants
@sparkacus
Copy link

sparkacus commented Oct 18, 2018

Would it be possible to export favourites into an editable format that can be redistributed to others?

We purchased the team licence but it's very difficult to keep everyones favourites in sync. We have 20+ favourites on each machine and if a change is required it can get complicated very quickly.

What I would like to do is be able to export my list of favourites into a single file, mass edit and then pass that onto a colleague who can then import (ideally overwriting their existing setup)

We would need to include all fields, including the SSH connection settings. I could mass edit the file after to remove unwanted options (or excluding options could be a user option upon export)

Also the ability to mass highlight favourites for deletion :)

@jakob

This comment has been minimized.

Copy link
Owner

jakob commented Oct 18, 2018

You can use the "Export Favorite" command to export favorites into files. The exported files are plist files, so you can edit them with plutil and a text editor.

If you want to mass-edit favorites, you could also edit the SQLite database where they are stored directly. (It's in Library/Containers/at.eggerapps.Postico/Data/Library/Application Support/Postico/Favorites.db) Make sure to do that only when Postico isn't running, though!

Postico also supports connection URLs which is another way to exchange connection details: https://eggerapps.at/postico/docs/v1.4.2/favorite-window.html

But all that isn't super convenient, and we're working on a better Favorite system. I'd love to get some feedback from people who actually need this. Would it be possible that I ask you a couple of questions privately via email? My email is jakob@eggerapps.at

@sparkacus

This comment has been minimized.

Copy link

sparkacus commented Oct 19, 2018

Hey Jakob, thank you very much for responding so quickly.

I wasn't aware of the plutil utility. Seems to work well, thank you:

What I am very interested in is the connection URLs as I could possibly automate our entire workflow using this.

We use Vault to dynamically provide auth - https://www.vaultproject.io/docs/secrets/databases/postgresql.html

Using the connection URL I can wrap this in a script that can pull in the required auth and launch Postico... Awesome...but, we use an SSH key and I'm not sure if that connection parameter is supported. Without the ability to pass in the location to the SSH key I can't really do anything with this. Any ideas?

@jakob

This comment has been minimized.

Copy link
Owner

jakob commented Oct 23, 2018

Does each user have their own SSH key locally on their machine, or do you want to include the SSH key in the URL?

@sparkacus

This comment has been minimized.

Copy link

sparkacus commented Oct 24, 2018

Yes, each user has their own unique SSH key, which may have a different file path.

If the URL were to accept a full path to the SSH key, that would be great.

@sparkacus

This comment has been minimized.

Copy link

sparkacus commented Oct 24, 2018

I've run into an issue.

Because the database username is dynamic, each call to the connection URL results in a new Postico favourite.
Is there a way stop this? e.g. maybe we could also pass in a favourite Nickname, which will overwrite that specific favourite

@jakob

This comment has been minimized.

Copy link
Owner

jakob commented Oct 24, 2018

There's a hidden preference that can disable the automatic favorite generation:

defaults write at.eggerapps.Postico CreateFavoritesForURLs 0

But I guess it would be nicer if that was configurable via an URL parameter (eg. ?create_favorite=no)

@sparkacus

This comment has been minimized.

Copy link

sparkacus commented Oct 24, 2018

Awesome - That does the trick, although I can't connect without the sshkey :)

It certainly would be useful if that were a parameter, otherwise I'd have to run that command on multiple machines, which isn't practical.

@jakob

This comment has been minimized.

Copy link
Owner

jakob commented Oct 24, 2018

The downside is that without a favorite, queries aren't autosaved, there is no query history, and some settings (like used filters) are not stored.

@sparkacus

This comment has been minimized.

Copy link

sparkacus commented Oct 24, 2018

Oh I see. We rely on those features.

@jakob

This comment has been minimized.

Copy link
Owner

jakob commented Oct 25, 2018

@sparkacus I've made some improvements to URL handling. The following arguments are now supported:

  • nickname (any string)
    You can now provide a nickname. If a favorite with the nickname already exists, Postico will update the favorite before connecting.
  • uuid (128bit UUID, hex digits separated by hyphens, eg. 68753A44-4D6F-1226-9C60-0050E4C00067)
    If you want to allow the user to customize the favorite nickname, you can provide a favorite UUID instead of the nickname
  • create_favorite (true or false)
    This overrides the CreateFavoritesForURLs user default. Pass true if you want to create a favorite if it does not exist, or 0 to connect without a favorite if none exists
  • connect_favorite (true or false, default is true)
    Pass false if you do not want Postico to connect to the favorite. Postico will then only update the favorite (if necessary) and show it in the Favorites window. Use this if the user needs to provide additional auth details (eg. a password or a SSH private key)

The big change is that you can now just use partial URLs, and Postico will automatically update favorites with details you provide in the URL. So for example, you can use this command:

open 'postico://user:password@?nickname=Staging'

Then Postico will look for a favorite named "Staging", update user & password, leave all other details as they were, and connect.

Regarding SSH Private keys: I've considered adding a ssh_private_key_path parameter, but at the moment this won't work. Postico is sandboxed and can only access files that the user selected in an open dialog. I have to think about this. But maybe you don't need it anymore, since Postico now allows updating favorites.

You can download a new build with those features here:
postico-3686.zip

PS: you can use open -a /path/to/app 'postico://xxx' if you have multiple copies of Postico installed and want to open a specific one.

Please let me know if any of this helps!

@sparkacus

This comment has been minimized.

Copy link

sparkacus commented Oct 25, 2018

Hi Jakob,

That looks brilliant. Liking the connect_favorite option.

Concerning ssh_private_key_path, ah that's a shame. Having the ability to update favs certainly does reduce a lot of issues. We'll just have to manually add the sshkey to each fav for now, which will now be a one time only task.

I'll try to make some time tomorrow to test and I'll provide you with feedback.

Thanks for all your work.

@sparkacus

This comment has been minimized.

Copy link

sparkacus commented Oct 30, 2018

Hi Jakob,

So far so good.

connect_favorite: Seems to be false by default. I have to pass in this param and set to true to auto-connect

I don't know if this is a good idea, but putting it out there. Is it possible to create a template favourite that contains the SSH host, username, pass, key etc that other favourites can consume? e.g. each favourite can specify it's own SSH connection details or select a template

If the above is possible, that will negate the need for me set environment variables on each machine so that my script can gather the relevant SSH details.

I hope this makes sense.

@jakob

This comment has been minimized.

Copy link
Owner

jakob commented Oct 30, 2018

Thanks a lot for testing!

connect_favorite should be true by default. That was an oversight.

I like the idea of a "template" favorite, but I still need to think about that a bit. It would probably only work when creating new favorites.

@jakob

This comment has been minimized.

Copy link
Owner

jakob commented Nov 28, 2018

Here's a new build of the favorites improvements branch. I've set connect_favorite back to true by default. I've also fixed an issue when passwords contain special characters (like / or %).

Postico 3937.zip

@jakob

This comment has been minimized.

Copy link
Owner

jakob commented Dec 12, 2018

Hi @sparkacus, I've continued to work on this. I've now implemented a ssh_private_key_path parameter, where you can provide a path to the SSH private key. If Postico does not have access to the path because of Sandbox restrictions, it shows an Open Dialog asking the user to grant permission. If the user clicks "Grant", Postico can use the private key. (this is only necessary the first time you use a private key)

Here's a new build of Postico: Postico 4011.zip

I've also written extensive documentation for the feature. Here are the interesting bits about the supported query string parameters in connection URLs:

  • nickname
    A nick name for the connection. Can be used when creating a new favorite, or when updating an existing favorite.
  • uuid
    A UUID in standard format for the favorite. If none is provided, a random UUID will be generated. This is useful if you want to update the favorite later, but want to allow changing the nickname manually. Example: e6ad04c5-9ebb-4e5c-95d0-9c634f373c83
  • dbname
    The database name to connect to.
  • host
    The host name of the dataase server.
  • user
    The user name for the dataase server
  • password
    The password for the dataase server
  • port
    The port number that the database is running on. Default is 5432
  • ssh_enabled
    Can be 'true' or 'false'. Wheter to connect via SSH tunnel or not.
  • ssh_host
    The host name of the SSH server.
  • ssh_port
    The port of the SSH server. Default is 22
  • ssh_user
    The username for authenticating with the SSH server.
  • ssh_password
    The password for the SSH server.
  • ssh_private_key_path
    Provide the path to a private key file for authenticating to an SSH server.
  • create_favorite
    Can be 'true' or 'false'. Default 'true'. Whether to automatically create a favorite when no matching favorite was found. If you set this to 'false', Postico will connect to the URL without creating a favorite. This avoids cluttering the Favorites window if you use many different connections, but it also disables some features that rely on favorites, like query history.
  • connect_favorite
    Can be 'true' or 'false'. Default 'true'. When this is true, Postico immediately connects to the favorite corresponding to the URL. When it is false, Postico only shows the favorite in the favorite window, but does not connect.
@sparkacus

This comment has been minimized.

Copy link

sparkacus commented Dec 12, 2018

Hi @jakob - I didn't get a chance to test your previous update but I'll try my best to test later this week. I Really appreciate your effort in all of this and I do like the SSH private key work around.

@jakob

This comment has been minimized.

Copy link
Owner

jakob commented Dec 18, 2018

The changes are now released in Postico 1.5

Docs can be found here:
https://eggerapps.at/postico/docs/v1.5/postico-url-scheme.html

I'm closing this issue now; if you have any more feedback regarding this, feel free to reopen this issue, or create a new one!

@jakob jakob closed this Dec 18, 2018

@sparkacus

This comment has been minimized.

Copy link

sparkacus commented Jan 3, 2019

@jakob Thank you very much for your hard work on this. The updates you've applied work really well for us so far.

Not sure if this should be a new issue or not, but are you able to expose the option to disable keychain? When creating a fav, keychain is automatically selected and our users may forget to uncheck.

Would it also be possible to expose the Color option? This would be very useful as I can automatically highlight production databases as red for example.

@jakob

This comment has been minimized.

Copy link
Owner

jakob commented Jan 17, 2019

@sparkacus I created a follow-up issue

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment