PageOutline

Hypatia Client Applications

With the update of the school database to PostgreSQL, remote client access has changed. The old tools have been updated to work with PostgreSQL, however there are new connection possibilities.

Command Line

You can use the terminal SQL interface provided by PostgreSQL, psql to work with the database. The command psql -h infdb.inf.ed.ac.uk infdb will connect with the Informatics Database.

Programmatic

You can use the standard libPq libraries from within C.

You can connect from Perl using DBI as follows.

#!perl
use DBI;
use Env qw(USER);

my $dbserver = "infdb.inf.ed.ac.uk";
my $port = 5432;

$ENV{'PGDATESTYLE'} = 'SQL, DMY';

my $infdbh = DBI->connect("dbi:Pg:dbname=infdb;host=$dbserver;port=$port",
                                   $USER,
                                   { PrintError => 1,
                                     RaiseError => 1}) ||
      die $DBI::errstr;

You can connect from Python as follows.

Unattended Connections

It is possible to run scripts that make an unattended connection to the database on remote clients. To do this you need to create a Kerberos principal for the connection and use a keytab to get authentication. At the server end a mapping must be setup from the principal to a valid database user with sufficient permissions. Below is an example.

First some LCFG configuration on the client to create a principal and keytab. The principal in this case will be wibbleupdate/HOSTNAME.inf.ed.ac.uk@INF.ED.AC.UK.

!kerberos.keys                        mADD(wibbleupdate)
kerberos.keytab_wibbleupdate          /etc/wibbleupdate.keytab
kerberos.keytabuid_wibbleupdate       root
kerberos.keytabgid_wibbleupdate       sysman

Before you run the script get authentication.

#!sh
export KRB5CCNAME=/tmp/wibbleupdate.cc
/usr/kerberos/bin/kinit -t /etc/wibbleupdate.keytab -k wibbleupdate/HOSTNAME.inf.ed.ac.uk

Alternatively and more cleanly embed this into the script itself, here is a Perl example.

#!perl
use Authen::Krb5;

my $principal = "wibbleupdate/HOSTNAME.inf.ed.ac.uk";
my $keytab = "/etc/wibbleupdate.keytab";

eval("Authen::Krb5::init_context(); Authen::Krb5::init_ets();");

my $client = Authen::Krb5::parse_name($principal)
  or die Authen::Krb5::error()." while parsing client principal\n";

my $server = Authen::Krb5::parse_name('krbtgt/'.$client->realm)
  or die Authen::Krb5::error()." while parsing server principal\n";

my $cc = Authen::Krb5::cc_resolve("MEMORY:wibbleupdate")
  or die Authen::Krb5::error()." while resolving ccache\n";

$cc->initialize($client)
  or die Authen::Krb5::error()." while initializing ccache\n";

my $kt=Authen::Krb5::kt_resolve($keytab)
  or die Authen::Krb5::error()." while resolving keytab\n";

Authen::Krb5::get_in_tkt_with_keytab($client,$server,$kt,$cc)
  or die Authen::Krb5::error()." while getting ticket\n";

$ENV{'KRB5CCNAME'}="MEMORY:wibbleupdate";

Then connect as you would normally like above, except replace the connection user with the database user that the principal for authentication will be mapped to, for example, if the database user for wibbleupdate is wibble then you would make a connection in Perl as below.

#!perl
my $infdbh = DBI->connect("dbi:Pg:dbname=infdb;host=$dbserver;port=$port",
                                   "wibble",
                                   { PrintError => 1,
                                     RaiseError => 1}) ||
      die $DBI::errstr;

Some server configuration is required to setup the mapping (in the future this might be supported by an LCFG spanning map and is subject to change). Below IP is replaced by the IP of the remote client - this restricts the unattended authentication connection to be from that client only, this of course could be less restrictive but generally sensible assuming the client script is always going to be run from one machine.

#!c
pgluser.users_user              mADD(wibble)
pgluser.users_interim           mADD(wibble) /* or other applicable group membership */

!postgresql.hostssl             mSUBST(uinf,uwib uinf)
postgresql.ssldatabase_uwib     infdb
postgresql.ssluser_uwib         wibble
postgresql.ssladdress_uwib      /* IP address */
postgresql.sslnetmask_uwib      255.255.0.0
postgresql.sslauthtype_uwib     krb5
postgresql.sslauthopt_uwib      map=<%postgresql.idmap_uwib%>

!postgresql.ident               mADD(uwib)
postgresql.idmap_uwib           wibble
postgresql.sysname_uwib         wibprinc/hostname.inf.ed.ac.uk
postgresql.pgname_uwib          wibble

-- TimColles - 27 Nov 2018

Topic revision: r1 - 27 Nov 2018 - 14:23:08 - TimColles
 
This site is powered by the TWiki collaboration platformCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback
This Wiki uses Cookies