You can see which versions are available with something like:

 # this is an example - your versions might differ
 $ ls /usr/lib/postgresql/
 16

 $ psql --version
psql (PostgreSQL) 16.9 (Ubuntu 16.9-1.pgdg24.04+1)

The other PostgreSQL tools are always available in the versioned subdirectory of /usr/lib/postgresql:

 $ ls /usr/lib/postgresql/16/bin/
clusterdb   dropuser    pg_archivecleanup  pg_config       pg_dumpall      [...]

Clients are generally now compatible with servers of different versions. If you have difficulty using tools across versions get in touch with support and we can add non-default versions to your PC.

PostgreSQL accounts

How do I get a PostgreSQL account / database?

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 to one, 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 for teaching or research, and if it is to be shared with anybody.

How long can I keep my account?

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 to retain your account for longer than this, please contact support.

How do I access my database?

We recommend that you use Kerberos to connect to any PostgreSQL server. This is the most secure and mechanism and is usually also the simplest, as it doesn't require a password once you've authenticated. Your Kerberos ticket can also be used to SSH and connect to other Informatics services. On DICE machines, you'll already have a Kerberos ticket. On your own computers, you may wish to Set up Kerberos yourself.

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. On self-managed machines over Informatics VPN you might need to add

.inf.ed.ac.uk

to the server name.

If you have been given access to a named database (for example a shared group database) on a specific server then you may access it by specifying the name of the database and hostname of the server, at the shell:

psql -h <servername[.inf.ed.ac.uk]> <databasename>

If you are not on a DICE machine, you can either

1. use SSH to connect to a DICE machine such as remote or student.ssh, and then use psql, or
2. (if permitted!) use Informatics OpenVPN to connect directly to the server. Please ask support if you need more information about this.

I tried typing psql, but it's asking for a password

There are three likely reasons:

• Your Kerberos credentials might have expired (see 'errors', below)
• You might be using an older client that doesn't support GSS Encryption -- we recommend PostgreSQL 12 or newer.
• You might be on a server where password support is always required (if this is a research server, someone might have requested this!)

I tried typing psql, but got an error

Here are a few common cases:

1. 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.

2. 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.

3. If you're attempting to use your scripting password (see below) and receive an error relating to GSSAPI it's likely that you've not disabled SSL. See Why is my script's SSL connection breaking? below.

Special Requests

Can I have my own server?

Possibly, though it might be quite resource-limited unless you can fund hardware. It might be better just to request a number of user accounts and databases to be added to the managed servers which are large, fast and backed up regularly. You can even run the PostgreSQL server software on your own DICE desktop simply by running

postmaster

, but this comes with limitations. The support we can provide is limited.

Can I install my own extensions?

No, but we will be able to install some extensions on your behalf. Generally we should be able to add any of the standard "contrib" extensions on a per-account basis. We might also grant permission to install some third-party extensions where we're confident that they will not be detrimental to the server. Extensions won't typically be installed for students unless needed for a course or project.

Can I use PostGIS?

Yes, PostGIS is a good example of a well-supported third-party extension. We will install this on request.

Are there any catches with third-party extensions?

The continued operation and compatibility of third-party extensions can't be guaranteed across major database upgrades (these occur less than once per academic year on average). If we encounter problems with third-party extensions on a shared server we will let you know so you can make alternative arrangements.

Using your own code with PostgreSQL

How do I use my database with scripts, web applications or the clusters?

Your regular database access is authenticated using Kerberos; this means that, while using PSQL, you will not need to enter your password to access databases. For convenience however we also permit access with a password, for scripted access where it is not possible or convenient to use stored Kerberos credentials.

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. Cluster users: before using postgresql with your distributed application please verify your expected usage and contact support if you think your code will need to make excessive (>20) concurrent connections or manipulate large (Gigabytes of) data.

Why is my script's SSL / GSS Enc connection breaking?

On most of our services GSS Encryption is reserved for Kerberos-authenticated clients, and SSL is available for noninteractive users. So, if you are connecting with your password, you might need to force a specific connection security measure depending on the client libraries used.

Exceptions have been made for a number of servers, including the Teaching Cluster, Groups Web and Homepages, so that you will be able to connect without any special measures.

If you are still getting errors when attempting to connect with a password, however, you can force your client / code to use SSL rather than GSS by adding the parameter "gssencmode=disable" (and on older servers also "sslmode=disable") to your list of connection options.

For example:

• If you are using the interactive psql client in a script, setting the $PGGSSENCMODE (and occasionally $PGSSLMODE and) environment variables to 'disable' before running psql will have a similar effect.
• If you are using PHP:
  
  <?php
$dbconn = pg_connect ("host=$server dbname=$db user=$username password=$password sslmode=disable gssencmode=disable")
>


These parameters environment variables are amongst the standard connection parameters defined in libpq, the PostgreSQL client library on which most clients are based. You'll find that this trick also works for the likes of popular python PostgreSQL module psycopg2, for example.

I'm using a shared database. Can I give access to another user?

You can do this using the SQL GRANT and REVOKE commands to give access to other users, either on an individual table basis or en masse using the "ALL IN SCHEMA" clause.

PostgreSQL provides documentation on all of the above: a good starting point might be the GRANT command.

Some examples:

-- Check who has permission to access your tables using 
-- the postgresql \z and \dp commands.

myuser=> \z
                Access privileges
 Schema |    Name     | Type  | Access privileges
--------+-------------+-------+-------------------
 public | foo         | table |
 public | bar         | table |

-- Remember that in this example myuser has implicit access 
--  to the database: it's the owner.

myuser=> GRANT INSERT, DELETE, UPDATE, SELECT ON foo TO another;
GRANT
myuser=> GRANT SELECT ON foo TO another;
GRANT

myuser=> \z
                   Access privileges
 Schema |    Name     | Type  |    Access privileges
--------+-------------+-------+-------------------------
 public | foo         | table | another=arwd/myuser
 public | bar         | table | another=r/myuser

-- Using the schema noted above (default in postgresql is 'public')
-- it is simple to revoke privileges from all tables, if you wish:

myuser=> REVOKE ALL ON ALL TABLES IN SCHEMA public FROM another;
REVOKE

How can I create my own schema? psql says, "permission denied".

The default permissions on your database do not permit the creation of a schema.

 myuser=> CREATE SCHEMA myschema;
 ERROR: permission denied for database mydb.

However, they do allow you to give yourself permission!

Execute the following at the psql shell:

 myuser=> GRANT CREATE ON DATABASE mydb TO myuser;
 GRANT

 myuser=> CREATE SCHEMA myschema;
 CREATE

 myuser=> \dn
      List of schemas
      Name    |  Owner
 -------------+----------
  myschema    | myuser
  public      | postgres
 (2 rows)

I'm being asked for a password: how can I set or change it?

Most users can set their passwords, and change them any time, using the psql client on a DICE machine. (note the quotes in the commands below; they are important)

• Log in to your account on pgteach/pgresearch/ilpdb from any desktop DICE machine. You will not be required to enter a password from DICE:
• Now change your main account password (please DO NOT use your DICE password):
  

  myuser=> ALTER ROLE username WITH password 'new password here';

I've forgotten my password, and lost the post-it note. How can I reset it?

Your password is not stored in accessible form anywhere, but you don't need to know your password to change it. Simply reset it as above.

I'm still stuck. How can I get help?

Please visit the Informatics support form. Ensure you have read all relevant FAQs before continuing.