PostgreSQL info

PostgreSQL overview

PostgreSQL (Postgres) is a relational database server (made spatially aware through the PostGIS extension) used by TAU as a data storage, analysis, and ETL tool.

Some terms used within this documentation:

Term	Description	Example
user	Your postgres username	`org_proj`
project role	A role that is used to facilitate mutual object ownership	`proj_dev`

Connecting to PostgreSQL

When required, you can be given access to TAU’s PostgreSQL server. A user will be created for you and you may become a member of one or more project roles.

In addition to your username, password, and desired database the following connection information can be used to connect to the server:

Host	`tau-rds.tmr.qld.gov.au`
Port	`5432`

Database development protocols

There are certain protocols which must be followed when undertaking database development work to ensure collaborators can work together effectively.

Object ownership

Object ownership must be managed carefully to ensure that all users collaborating on a project can do everything they need to do. Ownership and it’s associated privileges are not grantable, but they can be inherited by being a member of the role that owns the object. For example, if proj_dev owns this_table and org_proj is a member of proj_dev then org_proj inherits ownership of this_table by proxy. But this_table must be owned by proj_dev (as opposed to being owned by org_proj) otherwise other members of proj_dev won’t be able to interact with it fully.

To ensure this happens, users must always take one of these two paths when interacting with the server:

After establishing a connection to the server as your user, use the SET ROLE SQL command to assume the proj_dev role, so that by default all database admin activities (such as creating and altering tables) are performed as the project role (more info), or
After creating objects under your user then as soon as practical (or ideally as part of the same script), you must use the ALTER ... OWNER TO proj_dev command to hand ownership over to the project role (more info in relation to altering tables noting that there are separate ALTER commands for other database objects too).