/
Database Access for Non Technical Users

Database Access for Non Technical Users

For internal users with permission to see the ukrdc data but not systems level permission there is now a read only user to allow direct database access via connection manager. This is a modified version of the steps here:

https://renalregistry.atlassian.net/wiki/x/BoDXn?atlOrigin=eyJpIjoiNWY1NGU2YTkyMTUzNDk5YmFmMGQwNWJmMzYxNGQ2MjciLCJwIjoiYyJ9

User Steps - Create ssh keys

  1. Open terminal app from the windows start menu

  2. Copy and paste the following command into the terminal and hit enter ssh-keygen -t rsa

  3. Use enter to select the defaults that are suggested. This will generate a new set of ssh keys on your C: Drive

  4. Navigate to the folder: C:\Users\joe.bloggs\.ssh where your key is stored this will be named id_rsa.pub (Note that it’s the one microsoft incorrectly assumes is a publisher file) . This is needed for the next step.

  5. Email or otherwise send id_rsa.pub to a friendly member of systems to do the next step

System Admin Steps - Make keypass database and upload public key to server

  1. In this repository: https://github.com/renalreg/rr-key-manager there is a script called add_connection_console.py which takes a id_rsa.pub key and a json as inputs and generates a folder with a keypass database in it for the connection manager.

    1. The json should reflect the keypass database structure and should not contain all the users (for example the root user).

    2. You may need to update the path to look at the directory where the key and the json are being stored

    3. The tool will create a folder along the lines of: R:\Connection Manager\joe_bloggs

    4. The script will also attempt to upload the keys to the app server for this you will need to be able to .ssh locally to the location the keys are being uploaded to (e.g root@my-app-server….).

  2. Get Michael to lock down the permissions of the folder so it can only be accessed by the user

Connecting with Python

You need something like the following.

conn = PostgresConnection(app = "ukrdc_staging", tunnel = True, via_app = True) sessionmaker = conn.session()

and possibly

session.execute(text('SET search_path TO "$user", extract, public;')

Things to note:

  1. connection manager must be =<4.1.0

  2. Post db migration you need