Skip to main content

Connect to your Platform.sh Database with an IDE (MySQL,PHPStorm)

Comments

3 comments

  • Michael Brauner

    Ok, nice to know. That works.

    BUT since PHPStorm has a tunnel feature itself - it would be nice to know,
    how to configure this:

    Is it possible to share it for us?

    1
  • Dan Morrison

    Did you try that? it was a bit more complicated, but I found it could be made to work.

    For this, you now need to know two extra bits of knowledge, plus an sneaky SSH gotcha work-around.

    Define a tunnel

    To set up the tunnel, you need to know or retrieve your normal ssh credentials, and provide them to the “Use SSH Tunnel” setup.
    Discover that from your web console, or by going
    platform ssh --pipe

    EG:
    43g727syo3xr4-main-ba6vxei--app@ssh.au.platform.sh

    In Data Sources and Dirvers > [your data source] > SSH/SSL > Use SSH Tunnel > [Create new or edit] > “SSH Configurations” : And enter the Host and Username there, In this context, 43g727syo3xr4-main-ba6vxei--app is your username

    This will define what the tunnel destination is. The rest of the ‘tunneling’ flags will be taken care of by the IDE.

    Authentication is tricky.

    Gotcha: on that panel, “Authentication type” cannot be “Password”, and it seems that “Open SSH config and authentication agent” would be good, but it didn’t work for me at first, even with ssh-agent running.

    As the Platform.sh security layer provides a temporary session key for SSH authentication, not simple key-pair, we (can/should/must?) use that. :grimacing:

    SO:
    Find the location of your temporary session key. This gets written into your home directory when you first successfully authenticate with the CLI for each session. I found it by opening a ssh session with verbose options, and seeing which of my keys the server actually accepted during the handshake.

    ssh -vv $(platform ssh --pipe) exit 2>&1 | grep "using private key"
    
    debug2: sign_and_send_pubkey: using private key "/home/dman/.platformsh/.session/sess-cli-default/ssh/id_ed25519" for certificate
    

    So, you need to tell the IDE to use that key for auth when opening your tunnel.

    • Choose Authentication type: “Key Pair”
    • Enter that path as the “Private key file”
    • [Test connection]

    Save and step back to the “General” tab of your data source definition.

    Now you have a tunnel. Still need to connect to the DB on the other side.

    • Find your remote db connection details
    platform environment:relationships
    
    database:
        -
            username: user
            port: 3306
            host: database.internal
            path: main
            password: ''
            url: 'mysql://user:@database.internal:3306/main'
    

    ^ these credentials are usually the same for everyone who started with one of the templates, but may be different if you have modified your own DB relationships.

    Enter the Host, User, Password (blank), and database as appropriate.
    [Test Connection]


    At this point, you SHOULD have access to the remote DB as a data source direct from the IDE. The wires are connected now.

    I found I had to refresh things and re-save a few times in the Database tab before the tables started showing up, but it did.

    So, a lot more painful to get configured. This is why the platform tunnel command is supposed to help.

    and a final big gotcha:.

    The temporary SSH certificate is temporary - it will expire on its own by design, and it will need to be refreshed regularly.

    If you use platform CLI, this will be detected and the cert will be refreshed for you automatically. This is good.
    If you use the above tunnel process, to bypass the platform CLI and have your IDE make the SSH calls directly, this will not renew automatically and the certificate will suddenly stop working after an hour. :frowning:

    You will still manually need to run platform login (or almost any platform command that needs ssh) every so often to keep the authentication for the connection fresh. There may be a way to avoid this by tweaking the tunnel keep-alive setting, but really … all this was way to much more work than the easy original answer …
    So I simply don’t want to recommend any of this process.
    Just letting you know, yes, it can be done. But it’s no fun.

    1
  • Benoit Baudoin

    Thank you Dan Morrison you saved my afternoon, your solution work perfectly for DBeaver.

    1

Please sign in to leave a comment.