Quick Start with Postgres¶
Compiled guide for quick start with Postgres
Installation¶
The best way to start working with Postgres is to launch Docker container from official Postgres image. Such approach allows you to launch up databases for any Postgres version with the possibility to run them in parallel. Great benefit of such approach is that everything is done with a single command.
Note: I'm on Fedora 31 linux distribution which has podman as substitution for docker. Nevertheless all the commands are identical to Docker ones.
For example, to spin up container with latest 12th version just do
It will download image from public Docker hub and spin up postgres on port 5432 with default user postgres and password postgres
$ podman exec pg12 psql postgres postgres -c "select version()"
version
---------------------------------------------------------------------------------------
PostgreSQL 12.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 9.2.0) 9.2.0, 64-bit
If you will need to test you application against different postgres versions the easiest way to do this, again, will be by using docker containers. In order to spin up for example 11.6 postgres instance in parallel execute the same command again but specify different port and version
$ podman run -d --name pg11 -p 4444:5432 -e POSTGRES_PASSWORD=postgres postgres:11.6-alpine
$ podman ps
CONTAINER ID IMAGE COMMAND PORTS NAMES
b4af06006851 docker.io/library/postgres:11.6-alpine postgres 0.0.0.0:4444->5432/tcp pg11
85fdc770d03d docker.io/library/postgres:alpine postgres 0.0.0.0:5432->5432/tcp pg12
$ podman exec pg11 psql postgres postgres -c "select version()"
version
---------------------------------------------------------------------------------------
PostgreSQL 11.6 on x86_64-pc-linux-musl, compiled by gcc (Alpine 9.2.0) 9.2.0, 64-bit
Connecting to Database¶
To connect to the database start psql from within container
Postgres default installation is a database cluster with default postgres database created. Two additional template0 and template1 databases used as (surprise) template for a new databases that will be created. There is also postgres superuser account is created. All such metadata information can be viewed via psql backslash commands
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
One magic \? backslash command lists all built-in psql commands. These commands cover almost all possible cases for day-to-day work with database. You literally don't need any GUI application unless you perform some data analysis with many complex ad-hoc queries. In such cases excel like data grid would be more preferrable.
Another oftenly needed feature is the possibility to execute shell command without exiting psql. You can do this via \! command
Database setup¶
In order to create database you should execute CREATE DATABASE <dbname> command. It will create a copy of template1 built-in database mentioned above and assign to it provided by user <dbname>.
postgres=# create database dev;
CREATE DATABASE
postgres=# \l dev
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------+----------+----------+------------+------------+-------------------
dev | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
(1 row)
If you don't remember exact syntax of command psql has built-in help for all supported sql commands. Just type in \h to get list of all commands for which help is available. To view help for specific command execute \h <command>
postgres=# \h create database
Command: CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
[ [ WITH ] [ OWNER [=] user_name ]
...
Common pattern for database user access is to have application owner and user account(s). Owner account is used to create all objects within database. Subsequently it has grants for objects creation and deletion. User account has limited set of privileges on these objects issued via grants. The most common access patterns for application user accounts are read/write or readonly. It's also considered a good practice to issue grants not directly to the user accounts but through the roles.
Postgres database logically groups objects into schemas. If schema name is not defined during object creation then first schema in the search_path for a current user will be used. By default search_path is set to "$user", public value. First "$user" element of this list means schema with the same name as current user. If such schema doesn't exist then public schema will be used.
By default no schema is created during users creation. And thus all objects created by such user will be created in public schema.
Public schema also might be used as default target for different extensions. Hence it's better to create separate schema for each application. Usually application owner account is made as owner of such schema.
Application skeleton¶
Let's implement such approach in the created above dev database for an application schema named app with owner app account and two users account: app_user and app_readonly. Grants for user accounts will be issued through the app_rw and app_ro roles.
In order to create schema in some database we must be connected to it
postgres=# \c dev postgres
You are now connected to database "dev" as user "postgres".
-- input prompt now shows that we are connected to database "dev"
-- ending # says that we are connected to the database under superuser account
dev=#
Being connected to dev database as superuser postgres account create app users and provide basic connection grant on database
create user app;
create schema app authorization app;
create user app_user;
create user app_readonly;
create role app_rw;
create role app_ro;
grant app_rw to app_user;
grant app_ro to app_readonly;
grant connect, create on database dev to app;
grant connect on database dev to app_user, app_readonly;
In order to access object stored in some schema role must have usage grant on corresponding schema.
To resolve object names in app schema without specifying its name explicitly user's search path must include this schema.
grant usage on schema app to app_user, app_readonly;
alter user app_user set search_path = app, public;
alter user app_readonly set search_path = app, public;
Connect under just created app account, create table and give corresponding grants for roles
dev=# \c - app
You are now connected to database "dev" as user "app".
-- input prompt has been changed and ends with ">" symbol
-- this tells us that we are connected to the database under regular non-superuser account
dev=> create table t(id int, n varchar);
CREATE TABLE
dev=> grant select, insert, update, delete on table t to app_rw;
GRANT
dev=> grant select on table t to app_ro;
GRANT
Connect under app user and check that we can add some data into the table
dev=> \c - app_user
You are now connected to database "dev" as user "app_user".
dev=> insert into t(id, n) values (1, 'asdf'), (2, 'qwer');
INSERT 0 2
dev=> select * from t;
id | n
----+------
1 | asdf
2 | qwer
(2 rows)
Connect under app readonly user and try read and write operations. As expected, we cannot change data in the table under readonly account
dev=> \c - app_readonly
You are now connected to database "dev" as user "app_readonly".
dev=> select * from t;
id | n
----+------
1 | asdf
2 | qwer
(2 rows)
dev=> delete from t where id = 2;
ERROR: permission denied for table t
You might notice that database connection and schema usage grants were issued to the user accounts directly.
While DML grants on table t were granted through roles.
This is done intentionally. System level grants don't tied to specific app.
They are global ones and define user profile within database.
Also they are issued usually in a small amounts.
Instead, DML grants are application specific. There might be a lot of such grants withing single applicaiton.
And within single application there might be many different users.
By combining similar app level grants into roles and granting these roles to user accounts we obtain manageable approach for handling any access pattern for given app.