|
|
|
The school maintains two servers for teaching and research. Staff and students can be granted access to either server as appropriate. Generally there is no limit on research database use, but where consumption of resources becomes detrimental to other users of the server we may request that you source dedicated server hardware.
If you are an undergraduate student, and have been granted a postgresql database as part of a course you are taking, your account and database will be made available in time for your coursework, and removed when it ends. If you wish you retain your account longer than this, please contact support.
For most students and teaching users this is entirely automatic. You will receive an account if you are taking any courses which requires one. Once you drop this course, your access will be suspended and, ultimately, any stored data removed (unless you request otherwise). You can check if you have an account by following the instructions below. If you do not have an account but feel you are entitled, please get in touch with support noting any course or supervisor details you think are relevant.
If you are a member of staff and would like an account, you may request one using the support form. Please state whether the account is to be used in teaching or research, and if it is to be shared with anybody.
From any DICE machine (except certain research group servers), simply type the following:
psql -h pgteach
or
psql -h pgresearch
You will be connected to your database on the database server. If you have been given access to a named database (for example a shared group database) then you may access it by specifying the name of the database at the shell:
psql -h pgresearch databasename
If you are not on a DICE machine, the only recommended connection method is to use SSH to connect into DICE, then to use psql from within.
While attempting to connect to your database you may receive the following error:
psql: krb5_sendauth: Ticket expired
This is most likely because you have been logged in for a long time, or perhaps left your computer locked overnight. The solution is simply to issue the following command at a terminal:
renc
You will be prompted for your DICE password.
A similar error can be found if you clear out the contents of /tmp on your machine:
psql: pg_krb5_init: krb5_cc_get_principal: No credentials cache found
This can be avoided by not deleting files named /tmp/krb5cc_*, and is solved by the renc procedure described above.
If you have any other problems, feel free to contact support. Copy or write the error message into the form if possible.
Your regular database access is granted using Kerberos; this means that, while using PSQL, you will not need to enter your password to access databases. Non-interactive Kerberos authentication is not supported for this service. For this reason you also have the ability to set a password for scripting purposes.
If you have not set a password, your account will have have none. This denies all password attempts. To create a password, follow the instructions for resetting your password below.
Sorry, but at present, SSL is only supported over interactive psql sessions. You will have to use a regular, unsecured connection, unless you are on a known DICE web server.
Exceptions have been made for a number of web servers, including homepages.inf, so that both SSL and non-SSL connections will work.
You can force PHP not to use SSL with by adding "sslmode=disable" to your list of pg_connect options, i.e.:
<?php
$dbconn = pg_connect ("host=$server dbname=$db user=$username password=$password sslmode=disable")
>
(Thanks to neilb for the tip)
If you are using the interactive psql client in a script, setting the $PGSSLMODE environment variable to
'disable' before running psql will have a similar effect.
You can use the SQL GRANT and REVOKE commands to give access to other users. You must do this on each table to which you want to grant (or revoke) access. You can check who has permission to access your tables using the postgresql \z and \dp commands.
PostgreSQL provides documentation on all of the above: a good starting point might be the GRANT command.
An example:
myuser=> \z
Access privileges
Schema | Name | Type | Access privileges
--------+-------------+-------+-------------------
public | foo | table |
myuser=> GRANT INSERT, DELETE, UPDATE, SELECT ON foo TO another;
GRANT
myuser=> \z
Access privileges
Schema | Name | Type | Access privileges
--------+-------------+-------+-------------------------
public | foo | table | another=arwd/myuser
Note that in this example myuser has implicit access to the database: it's the owner.
This shouldn't happen, but massive DELETE FROM ... statements can take ages due to a quirk of the combination of PostgreSQL version and file system used on DICE servers.
It will help you (and please Support) greatly if you empty very large tables in a more efficient way:
myuser=> TRUNCATE TABLE tablename
But it would be wise to read the postgresql documentation on the TRUNCATE command and to please make sure you have a copy of any important data before deciding to TRUNCATE it.
Note your password is your responsibility, and should be secret. Don't write it down.
Most users can change their own passwords in postgres. (note the quotes in the commands below; they are important)
pgteach/pgresearch from any desktop DICE machine. You will not be required to enter a password.myuser=> ALTER ROLE username WITH password 'new password here';
Alternatively, if you have no Kerberos access to your account, and no password, support can reset the password on your behalf. Be sure to change it (using the above) as soon as you have received the new password.
Your password is not stored in accessible form anywere, but you don't need to know your password to change it. Simply reset it as above.
Please visit the Informatics support form. Ensure you have read all relevant FAQs before continuing.
|
Please contact us with any
comments or corrections.
Unless explicitly stated otherwise, all material is copyright The University of Edinburgh |
|
