Setting up PostgreSQL Server in Docker Container on Ubuntu: A Step-by-Step Guide

Introduction

Setting up a PostgreSQL server within a Docker container may seem daunting, but it’s an efficient way to ensure your database environment is portable, consistent, and easily replicable. Welcome to our step-by-step guide designed for Ubuntu users, from novices to seasoned system administrators. Whether you’re looking to streamline your development process, deploy applications faster, or simply learn about containerization, you’re in the right place.

By following this guide, you’ll learn how to deploy a PostgreSQL server using Docker, configure it for secure remote access, and manage logs effectively. We’ll walk you through creating a dedicated user, thus minimizing risks and promoting good security practices right from the start.

Prerequisites

Before we dive in, ensure you have Docker and Docker Compose installed on your Ubuntu machine. If you’re unsure how to do that, check out the official Docker documentation for installation instructions.

Step 1: Define Your Docker-Compose Configuration

We will use docker-compose to manage our PostgreSQL container. The configuration below defines the setup for our PostgreSQL server.

version: '3.8'

services:
  postgres:
    container_name: postgres_container
    image: postgres:16
    environment:
      POSTGRES_USER: ${POSTGRES_USER:-postgres}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-defaultpassword}
      PGDATA: /var/lib/postgresql/data
    volumes:
      - ./data:/var/lib/postgresql/data
      - ./logs:/var/log/postgresql
      - ./postgresql.conf:/etc/postgresql/postgresql.conf
      - ./pg_hba.conf:/etc/postgresql/pg_hba.conf
    ports:
      - "5432:5432"
    restart: unless-stopped
    command: ["postgres", "-c", "config_file=/etc/postgresql/postgresql.conf",
               "-c", "hba_file=/etc/postgresql/pg_hba.conf",]
volumes:
  data:
    driver: local
  logs:
    driver: local

Explanation:

• version: Specifies the version of the Compose file format.
• container_name: Assign a custom name for easier identification.
• image: The PostgreSQL image version to use.
• environment: Defines environment variables for the container.
• volumes: Maps directories between the host and container for data persistence and log storage.
• ports: Exposes PostgreSQL default port to the host.
• restart: Ensures the container restarts unless manually stopped.
• command: Instructs PostgreSQL to use our custom configuration file.

Save this as docker-compose.yml in your project directory.

Explanation of the key configurations

environment:
      POSTGRES_USER: ${POSTGRES_USER:-postgres}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-defaultpassword}
      PGDATA: /var/lib/postgresql/data

environment: Sets environment variables inside the container. POSTGRES_USER and POSTGRES_PASSWORD are credentials for the default PostgreSQL user. If not provided, they default to postgres and defaultpassword. PGDATA sets the location where the PostgreSQL data files will reside inside the container.

volumes:
      - ./data:/var/lib/postgresql/data
      - ./logs:/var/log/postgresql
      - ./postgresql.conf:/etc/postgresql/postgresql.conf
      - ./pg_hba.conf:/etc/postgresql/pg_hba.conf

volumes: These lines mount directories from the host to the container. It creates a persistent storage for data (./data), a place for logs (./logs), and mounts custom configuration files (postgresql.conf and pg_hba.conf) from the host to the appropriate locations in the container.

command: ["postgres", "-c", "config_file=/etc/postgresql/postgresql.conf"]

command: Overrides the default command run by the container. It starts PostgreSQL with a specific configuration file.

tep 2: Configure PostgreSQL

Now let’s set up the postgresql.conf and pg_hba.conf files.

PostgreSQL Configuration (postgresql.conf)

logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_statement = 'ddl'
log_min_messages = 'info'
log_min_error_statement = 'info'
log_min_duration_statement = 0
log_line_prefix = '%m [%p]: [%l-1] user=%u,db=%d '  # Time, process ID, line number, username, and database name.
listen_addresses = '*'

This configuration enables logging and specifies the log directory and file format.

Client Authentication Configuration (pg_hba.conf)

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             0.0.0.0/0               md5

This file configures which hosts are allowed to connect, which databases they can access, and which authentication method is required.

The Intricacies of PostgreSQL Configuration

The postgresql.conf file contains settings that control the behavior of the PostgreSQL server.

logging_collector = on

logging_collector: This enables the collection of log files, which are essential for diagnosing issues and auditing access.

log_directory = '/var/log/postgresql'

log_directory: Specifies the directory where log files will be stored. It is set to the directory we mounted in our Docker volume.

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

log_filename: Defines the naming pattern for log files, which includes the date and time, making it easier to navigate through historical data.

log_statement = 'ddl'

log_statement: Determines which SQL statements are logged. ddl logs all data definition commands like CREATE, ALTER, and DROP.

log_min_messages = 'info'

log_min_messages: Sets the minimum level of messages to log. info ensures we log all informational messages, which is useful for troubleshooting.

log_min_error_statement = 'info'

log_min_error_statement: Similar to log_min_messages, but for error statements. It ensures that

all error information is captured in the logs.

log_min_duration_statement = 0

log_min_duration_statement: All SQL statements that run are logged along with their duration, set to 0 to log every statement regardless of duration.

log_line_prefix = '%m [%p]: [%l-1] user=%u,db=%d '

log_line_prefix: This is a format string that is output at the beginning of each log line, %m for time, %p for process ID, %l for line number, %u for user, and %d for database.

listen_addresses = '*'

listen_addresses: Configures the server to listen on all available IP addresses, which is necessary for accepting connections from the internet.

Network Security with pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             0.0.0.0/0               md5

This line in the pg_hba.conf file determines who can connect, to which database, from where, and which authentication method is used. md5 requires a password for connections.

Postgres in docker on ubuntu

Step 2: Creating and Managing PostgreSQL Roles and Permissions

Next, let’s establish a secure user role and its permissions. It’s a best practice to avoid using the superuser role for application connections because of the extensive privileges it carries. Instead, we create a specific role with more limited permissions for everyday use.

Creating a Database

To create a new database:

CREATE DATABASE bettertrack;

This command creates a new database named bettertrack. The name should be meaningful to your environment or project.

Switching Databases

To switch to your new database, use the command:

\c bettertrack;

The \c command followed by the database name changes the current database context to bettertrack.

Creating a Role for the Environment

Now, create a role with login permissions and a password:

CREATE ROLE stage LOGIN PASSWORD 'strongpassword';

Here’s the breakdown:

• CREATE ROLE: This statement is used to create a new role in PostgreSQL.
• stage: This is the name of the role. Ideally, it should be relevant to the environment it’s meant for, like stage, dev, or prod.
• LOGIN: This clause allows the role to log in, which is essential for a user that needs to connect to the database.
• PASSWORD: Specifies the password for the role. It’s crucial to use a strong, ideally generated, password here for security reasons.

Granting Permissions to the Role

Once the role is created, grant it the necessary permissions on the database:

GRANT CONNECT ON DATABASE bettertrack TO stage;
GRANT USAGE ON SCHEMA public TO stage;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO stage;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO stage;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO stage;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO stage;
GRANT CREATE ON SCHEMA public TO stage;

Here is what each command accomplishes:

• GRANT CONNECT: Allows the stage role to connect to the bettertrack database.
• GRANT USAGE ON SCHEMA public: Permits the stage role to access the public schema, which is the default schema for most operations.
• GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES: Gives the stage role permission to read, create, modify, and delete records in all tables within the public schema.
• ALTER DEFAULT PRIVILEGES: Ensures that any future tables created within the public schema will automatically grant the specified privileges to the stage role.
• GRANT USAGE ON ALL SEQUENCES: Allows the stage role to use all sequences. Sequences are often used for generating unique identifiers for table rows.
• GRANT CREATE ON SCHEMA public: Gives the stage role the ability to create new tables or other objects within the public schema.

Connecting to the Database Using the New User

Finally, to test the connection to the bettertrack database using the new stage role:

psql -h localhost -U stage -d bettertrack -W

You will be prompted to enter the password for the stage user. Ensure that this is done on a secure machine, as the password will grant access to the database.

Securing Your Setup

Security should be at the forefront when configuring any database accessible from the internet. Make sure to:

• Keep your PostgreSQL and Docker software up to date with security patches.
• Use strong, unique passwords for all database roles.
• Regularly review your pg_hba.conf file to ensure that only intended clients can connect.
• Monitor your logs to detect unauthorized access attempts.

Practical Examples: Real-World Applications of PostgreSQL within Docker

Development and Testing Environments: Developers often require isolated environments to test new features or updates without affecting the main codebase. Docker containers with PostgreSQL can be spun up quickly, offering a consistent database environment that closely mirrors production. For instance, a developer working on a new application feature that requires database changes can use Docker to create a temporary, isolated test database to experiment without risking the stability of the production database.

In Summary: Deployment Workflow: PostgreSQL on Docker in Ubuntu

By now, you have not only set up PostgreSQL on Docker, but you have also learned the ins and outs of each configuration step. You’ve taken the extra step to secure your database connections and ensure that your logs keep you informed of the database’s health and access patterns.

Feel empowered to explore further configurations that align with your project needs, and remember, every step you take towards mastering containerization and database management is a stride towards a more robust and scalable architecture.

Keep exploring, keep learning, and may your data always be well-structured and your connections secure!