Databases_2019 Page History


Web servers and Databases for Teaching

Architectural overview

  • For teaching use, the School provides an Apache-httpd web service which accessed via a pound proxy to allow for future expansion.

  • The School provides an SQL database server. This is implemented with MariaDB-Galera and the Maxscale front-end proxy in order to provided a reliable, replicated service. The service entry point is cs-db.ncl.ac.uk. Product documentation is available online.

WebDbCluster

Changes in Summer 2019

  • Prior to summer 2019, a single server homepages.cs.ncl.ac.uk was used both for the personal web pages and MySQL databases. This meant that the database server as seen by these web pages was localhost. This will need to be changed to refer to the separate clustered database server cs-db.ncl.ac.uk. This separation is in line with current best practice. The name homepages.cs.ncl.ac.uk will be retained for external use. One or more back-end Apache web servers will be fed from this.

  • The outdated mysql driver should not be used for new work and will be replaced with PDO_mysql or mysqli.

Web pages

Personal Web pages are created automatically for members of staff and all students taking a Computing module or registered for a Computing degree. The URL for this is:

http://homepages.cs.ncl.ac.uk/*name*

where *name* is replaced with the user's registered Newcastle e-mail name in lower case. These pages will be removed soon after you cease to be a student or member of staff in Computing, so should not be used for long-lived pages referred to in published papers. There is a separate arrangement for Team projects.

homepages.cs is an alias for our reverse proxy which is accessible both on and off-campus. It directs web traffic to a back-end server cs-homepages01.ncl.ac.uk. This back-end will allow updates to be made with an SFTP client such as Filezilla or WinSCP. Alternately, open a folder view of \\cs-homepages01.ncl.ac.uk in the Windows File Explorer.

Updates using FileZilla

FileZilla

  • Filezilla is installed on university-managed Windows 10 systems. It is only usable on-campus as there is no convenient way to manage a tunnelled connection from off campus. Note the use of a full URI including sftp://.

Updates using WinSCP

WinSCP

session adv_auth_gss

  • WinSCP is installed on university-managed Windows 10 systems. It is the most flexible windows-based update mechanism allowing simple connections, secure tunnelling from off-campus and optionally reusing the existing campus authentication of a windows session by selecting GSS in the advanced authentication dialog.

Using WinSCP through a secure tunnel.

  • A different combination of advanced connection options will allow a public/private key-pair to be used for login working end-to-end from an off-campus host if needed. Start by generating a key.

keygen

  • Probably on a managed Windows 10 PC, run PuTTYgen and generate a new public/private key pair. For security reasons, you should use either an RSA key of a minimum of 2048 bits or an ED25519 key.
  • Set a strong passphrase that you do not use elsewhere.
  • Click the button to save the private key, either within your H: drive or on a memory stick.
  • Right-click in the upper dialog box (here starting ssh-rsa) and select-all, then copy this text.

keys_config

  • Use a text editor to paste this onto a single line in the authorized_keys in both your Linux home directory ${HOME}/.ssh/authorized_keys and your web file space (which can me seen on cs-linux.ncl.ac.uk as /homenfs/homepages.cs/<e.mail-name>/.ssh.authorized_keys. Although this latter file sits within your web space, the server is configured not to serve this file on the web and, unlike the private key, its contents need not be secure provided it is not writeable by others.
  • Enable SSH Agent Forwarding by creating or updating the file ${HOME}/.ssh/config containing the lines Host cs-homepages01.ncl.ac.uk ForwardAgent yes

agent

  • Launch the agent program PuTTYAgent and right-click on it's task bar icon to add the newly generated key. Enter the passphrase when requested.

adv_auth adv_tunnel

  • Open a new connection as before and set the advanced connection options to use this key. When connecting, you are likely to be prompted to accept the key fingerprint visible in the PuTTYAgent key display windows, but will not be prompted again for your password or passphrase. On a personal machine, you only need to accept the new key once. On a managed machine, the key will need to be accepted each time you open the connection.

Using Windows File Explorer

Explorer

  • Working on-campus, File Explorer should be directed to \\cs-homepages01.ncl.ac.uk\homes. Direct CIFS access is not available off-campus.

Using a Linux file share

Linux

  • When signed on to the Linux compute-cluster, cs-linux.ncl.ac.uk, the personal home-space can be found mounted under /nfshome/cs-homepages/<email.name>

PHP

PHP is supported. Please note the following:

  • PHP scripts are granted restricted access only to the user's personal space and selected system directories.
  • The PHP include path is set to search relative to the root on each user's personal space, so that PHP include statements such as: require_once("inc/connect.php"); can be used without needing varying levels of ../..
  • The flag register-globals is false which means that variables declared in web forms are not automatically made into global variables in your PHP script. This may differ from some outdated tutorials for PHP.

SQL Databases

Graphical User Interface

The recommended graphical front-end is DBeaver. This has a built in mechanism for opening a secure tunnel when performing database administration tasks from off-campus and can connect to various different database servers, including MySQL. The previously recommended front end MySQL Workbench cannot connect to our current MySQL server unless an outdated version is used.

Using DBeaver internally

On managed windows 10 machines in the Urban Sciences Building, DBeaver is already installed (start typing its name in the start menu to locate it). The first time this is used, it will download a small driver package into your personal home space. A screen-dump of the main dialog boxes involved in setting up a new connection is as shown. Beware that if you save the database password locally, this is obfuscated in a very simple manner - it is not securely encrypted.

DBeaver

Off-campus access

To use a personal copy of DBeaver from elsewhere, the Advanced network settings can be used to connect via a tunnel.

DBeaverTunnel

Note in particular:

  1. Use the Host cs-linux.ncl.ac.uk and your campus login name.
  2. If using a key pair, an rsa key will be needed. DBeaver does not support elliptic curve keys.
  3. Do not save the password or passphrase. It is not securely encrypted, nor is it possible to use a restricted key to mitigate this.

Command Line Tools

The command line interfaces to SQL are:

mysql:: available on School Linux and Windows systems. It is run like this:

mysql -h db.cs.ncl.ac.uk -u <db-user> -p

and you will be prompted for your database password.

Connector libraries


Last edited by Chris Ritson