Connection to ISPyB not authorized via fragalysis-stack account/key SSH tunnel

[Waztom]

SSHing via fragalysis-stack account/public key (see #1316) works, but authorization denied for accessing the ISPyB DB. The fragalysis-stack is a User account and will need to check in with James Hall re what restrictions might be in place. @alanbchristie to add sensible comments/add to summary.

[mwinokan]

Superseded by #1329 and #1316 ?

[Waztom]

@mwinokan suggest we leave this in its own ticket, as it's a chunk of work - as are the others #1329 and #1316 => both conceivably completed with this one sorted.

@alanbchristie James and Karl think this is an authorisation issue. Could you please confirm either the config file the stack is using to access the iSPyB API or the ENV variables used (Karl said it would be either one of these two options)? I suspect it's the ENV variable option - need to pass on these details to Karl to check if the functional account can access.

[alanbchristie]

At the moment the code that handles connection to the DB is in the Python module api/remote_ispyb_connector.py in the Fragalysis Backend repository https://github.com/xchem/fragalysis-backend/ (on the staging or production branches).

There are two steps: -

1. Establishing an SSH tunnel (using the Python package sshtunnel) using username/password or private key file
2. Connection to the MySQL DB (using the Python package pymysql)

The following excerpts illustrate the actual method calls made for each step: -

SSH tunnel with username/password (it's what we use at the moment)

We use a "well known" username and password atm, but we can also use a private key file, where: -

• ssh_host is ssh.diamond.ac.uk

self.server = sshtunnel.SSHTunnelForwarder(
        (ssh_host),
        ssh_username=ssh_user,
        ssh_password=ssh_pass,
        remote_bind_address=(db_host, db_port),
    )

MySQL connection

Once a tunnel has been established, the following method is called, with a suitable MySQL username and password, to create a DB connection...

self.conn = pymysql.connect(
    user=db_user,
    password=db_pass,
    host='127.0.0.1',
    port=self.server.local_bind_port,
    database=db_name,
)

[Waztom]

@alanbchristie could you please ping me in Slack what the ISPYB ENV variables defined in the settings.py file here are?