Postgres Database in a Docker Container

You want to install a Database in your machine for development purposes, correct? I would advise not to but instead install Docker and create a Dockerise Database, Postgres for example.

Why? Simple, you can spin off multiple instances of a database and you can easily drop the containers once you are done with it.

PostgreSQL is an open-source, object-relational database with strong capabilities. It allows for more sophisticated data types and object inheritance, but this increased functionality also increases complexity. It uses a single storage engine that guarantees ACID compliance.

docker run --name mydb -e POSTGRES_PASSWORD='mypassword' -d -p 5432:5432 postgres

Create the Database

Creating the database is as simple as running a createdb command. This command is part of the postgres client library libpq.
Make sure that an environment variable 'PGPASSWORD' has been set to have the value of the password
defined in the docker container command above.

export PGPASSWORD=mypassword
createdb -h localhost -U postgres mydatabase

• -h <hostname>

  This specifies the hostname of the database server. Since this is a docker container, it is default to be the localhost.

• -U postgres

  The default postgres admin user.

• <database name>

  The name of the database to be created, here it's 'mydatabase'

Create Database Users

You don't use the default user to connect to the database regularly, specially in a Production Environment. You need to create users. The psql command is part of the postgres client library 'libpq'.

psql -h localhost -U postgres -d mydatabase -c "create user db_admin superuser;"
psql -h localhost -U postgres -d mydatabase -c "create user write;"
psql -h localhost -U postgres -d mydatabase -c "create user readonly;"

• -h <hostname>

  This specifies the hostname of the database server. Since this is a docker container, it is default to be the localhost.

• -U postgres

  The default postgres admin user.

• -d <databae name>

  The database you want to connect to.

• -c <sql statements>

  The sql statement you want to run. In this case the create user statement.

Assign Permission

Creating user is not enough. You will not be able to use the user you created without giving them permission to access the database.

psql -h localhost -U postgres -d mydatabase -c "GRANT ALL PRIVILEGES ON DATABASE mydatabase TO write;"
psql -h localhost -U postgres -d mydatabase -c "GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly;"

Full Script

I've compiled all of the above commands into one script below.

  docker run --name mydb -e POSTGRES_PASSWORD='mypassword' -d -p 5432:5432 postgres    
  # add in `:<version>` to install a specific version of postgres (e.g. :11)
  brew install libpq # install postgres client libraries if havent done so

  # PGPASWORD env variable is used by psql command to authenticate a user with the -U option
  export PGPASSWORD=mypassword

  # Create a database based on your project
  createdb -h localhost -U postgres mydatabase

  # Create users based on your project
  psql -h localhost -U postgres -d mydatabase -c "create user admin superuser;"
  psql -h localhost -U postgres -d mydatabase -c "create user write;"
  psql -h localhost -U postgres -d mydatabase -c "create user readonly;"

  # Grant users
  psql -h localhost -U postgres -d mydatabase -c "GRANT ALL PRIVILEGES ON DATABASE mydatabase TO write;"
  psql -h localhost -U postgres -d mydatabase -c "GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly;"