Reference

PostgreSQL: Background

This page is managed by StJohn Piano.

If you have any questions, comments, corrections, or suggestions - please contact StJohn Piano on Tela:
tela.app/id/stjohn_piano/7c51a6

Follow Tela Network on LinkedIn:
linkedin.com/company/tela-network

Join Tela Network and become a consultant:
telablog.com/how-to-use-tela-for-consulting

Postgres origin

PostgreSQL, also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley. In 1996, the project was renamed to PostgreSQL to reflect its support for SQL.

Connect to a Postgres server with psql

psql is a terminal-based front-end to PostgreSQL. It enables you to type in queries interactively, issue them to PostgreSQL, and see the query results. Alternatively, input can be from a file or from command line arguments. In addition, psql provides a number of meta-commands and various shell-like features to facilitate writing scripts and automating a wide variety of tasks.

Here's the general form of the psql connection command:

PG_HOST='localhost'
PG_PORT='5432'
PG_USER='postgres'
PG_PASSWORD='postgres_password'
PG_DATABASE='postgres'

PGPASSWORD=$PG_PASSWORD psql -h $PG_HOST -p $PG_PORT -d $PG_DATABASE -U $PG_USER

In my case, I'm using a test database cluster with these values:

PG_HOST='localhost'
PG_PORT='5432'
PG_USER='admin'
PG_PASSWORD=''
PG_DATABASE='postgres'

Of these, only the PG_DATABASE value is not default, and PG_DATABASE can also be the first non-option argument. So I can connect with this short form of the command:

psql postgres

Postgres database structure

When you run a PostgreSQL server, you are really running a database cluster.

A PostgreSQL database cluster contains one or more named databases.
Roles and a few other object types are shared across the entire cluster.
A client connection to the server can only access data in a single database at a time.
A database contains one or more named schemas.
A schema contains tables.
Schemas also contain other kinds of named objects, including data types, functions, and operators.
The same object name can be used in different schemas without conflict; for example, both schema1 and myschema can contain tables named mytable.
Unlike databases, schemas are not rigidly separated: a user can access objects in any of the schemas in the database they are connected to, if they have privileges to do so.
Schemas are analogous to directories at the operating system level, except that schemas cannot be nested.

Another way to think about schemas: A schema is essentially a namespace - it contains named objects (tables, data types, functions, and operators).

By default, new tables and objects are created in the initial schema, which is called "public" and does not have to be specified in the commands.

There are several reasons why one might want to use schemas:

To allow many users to use one database without interfering with each other.
To organize database objects into logical groups to make them more manageable.
Third-party applications can be put into separate schemas so they do not collide with the names of other objects.

Postgres role structure

PostgreSQL manages database access permissions using the concept of roles.

A role can be thought of as either a database user, or a group of database users, depending on how the role is set up. Roles can own database objects (for example, tables and functions) and can assign privileges on those objects to other roles to control who has access to which objects. Furthermore, it is possible to grant membership in a role to another role, thus allowing the member role to use privileges assigned to another role.

The concept of roles subsumes the concepts of “users” and “groups”.

The privileges to create tables, views, functions, operators, data types, etc are not properties of the role. They are properties of the relevant object (e.g. a schema, a table, etc).

Roles in a database cluster do not necessarily have the privilege to access every database in the cluster.

A role is permitted to change its own password. A database superuser can change the password for any role.

Postgres role deletion

A role's privileges and ownerships must be explicitly removed before the role can be dropped.

Note: If you drop the only superuser role, you can restart Postgres in single-user mode where you'll have superuser privileges again.

Postgres database deletion

A database can only be dropped by its owner or a superuser.

DROP DATABASE drops a database. It removes the catalog entries for the database and deletes the directory containing the data. It can only be executed by the database owner. It cannot be executed while you are connected to the target database.

Event triggers

Unlike regular triggers, which are attached to a single table and capture only DML events, event triggers are global to a particular database and are capable of capturing DDL events.

Examples of DML events: INSERT, DELETE, UPDATE.

Examples of DDL events: CREATE TABLE, DROP TABLE.

DML = Data Manipulation Language.

DDL = Data Definition Language.

The first type catches changes to the data. The second type catches changes to the data definition.