Postgres Database in a Docker Container
Tags: devops, database, programming
Published: 2023-01-15 02:33:43

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
```
| Parameters  | Description  |
|---|---|
| <sub>name</sub>  | The name of your container   |
| <sub>-e POSTGRES_PASSWORD='mypassword' </sub>  | This is the password of the admin user 'postgres'.  |
| <sub>-p &lt;local port&gt;:&lt;container port&gt;</sub>  | Port mapping between container and local machine  |
| <sub>-d</sub>  | Run the process in the background  |
| <sub>postgres[:&lt;version&gt;</sub>  | The container image to download from dockerhub. Add :&lt;version&gt; to specify the version of the container |

<h4>Create the Database</h4>
Creating the database is as simple as running a <i>createdb</i> command. This command is part of the postgres client library <i>libpq</i>.
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
```
<ul>
  <li>-h &lt;hostname&gt;
  <blockquote>This specifies the hostname of the database server. Since this is a docker container, it is default to be the localhost.</blockquote></li>
  <li>-U postgres
    <blockquote>The default postgres admin user.</blockquote></li>
  <li>&lt;database name&gt;
    <blockquote>The name of the database to be created, here it's 'mydatabase'</blockquote></li>
</ul>

<h4>Create Database Users</h4>
<p>You don't use the default user to connect to the database regularly, specially in a Production Environment. 
  You need to create users. The <i>psql</i> command is part of the postgres client library 'libpq'.
</p>
```
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;"
```
<ul>
  <li>-h &lt;hostname&gt;
  <blockquote>This specifies the hostname of the database server. Since this is a docker container, it is default to be the localhost.</blockquote></li>
  <li>-U postgres
    <blockquote>The default postgres admin user.</blockquote></li>
  <li>-d &lt;databae name&gt;
    <blockquote>The database you want to connect to.</blockquote></li>
  <li>-c &lt;sql statements&gt;
    <blockquote>The sql statement you want to run. In this case the <i>create user</i> statement.</blockquote></li>
</ul>

<h4>Assign Permission</h4>
<p>Creating user is not enough. You will not be able to use the user you created without giving them permission to access
  the database.
</p>
```
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;"
```

<h3>Full Script</h3>
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;"
```