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 Dockerize Database, Postgres for example.

Postgres

PostgreSQL is a powerful, open source object-relational database. It offers more complex data types and allows objects to inherit properties, but it also makes working with PostgreSQL more complex. PostgreSQL has a single, ACID-compliant storage engine.

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

• --name "mydb"

  This is the name of your container.

• -e POSTGRES_PASSWORD='mypassword'

  This is the password of the admin user 'postgres'.

• -p <system port>:<target port>

  Map the container's port 5432 (left of :) to the systems port 5432 (right of :)

• -d

  Start the containerized app in the background

• postgres[:<version>]

  The container to download and start. Add :<version> to specify the version of the container.

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.

Click Here to Collapse and copy to Clipboard -->

  
  docker run --name mydb -e POSTGRES_PASSWORD='mypassword' -d -p 5432:5432 postgres    
  # add in `:` 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;"