Skip to main content

Postgres

This page provides instructions for configuring PostgreSQL

Prerequisites​

Homebrew package manager installed on your system

Installation​

Uninstall existing PostgreSQL (if applicable)​

brew uninstall postgresql

Install PostgreSQL​

brew install postgresql

Set environment variables​

Once done, execute below commands depending on the shell type.

To determine shell type execute

ps -p $$ -o comm
For bash
echo '# Postgres' >> ~/.bash_profile
echo 'export POSTGRES_HOME="$BREW_HOME/opt/postgresql@14"' >> ~/.bash_profile
echo 'export POSTGRES_DATA="$BREW_HOME/var/postgresql@14"' >> ~/.bash_profile
echo 'export PATH="$POSTGRES_HOME/bin:$PATH"' >> ~/.bash_profile
For zsh
echo '# Postgres' >> ~/.zshrc
echo 'export POSTGRES_HOME="$BREW_HOME/opt/postgresql@14"' >> ~/.zshrc
echo 'export POSTGRES_DATA="$BREW_HOME/var/postgresql@14"' >> ~/.zshrc
echo 'export PATH="$POSTGRES_HOME/bin:$PATH"' >> ~/.zshrc

Start PostgreSQL​

brew services start postgresql

Verification​

PostgreSQL version​

psql --version

Viewing Configuration​

psql postgres -c 'SHOW config_file;'

The configuration file is typically located at /opt/homebrew/var/postgresql@14/pg_hba.conf

Users​

Connect to the PostgreSQL server​

As admin
psql postgres

Set the default password for the postgres user​

ALTER USER postgres PASSWORD 'password';

Then you have to use below command to connect with psql

psql -d databasename -U username
psql -d postgres -U xsc

Verify the current user​

SELECT current_user;

Roles​

Create additional role​

CREATE ROLE username WITH LOGIN PASSWORD 'password';

Security​

Enabling SSL​

1. Changing Directory​

Change to POSTGRES_DATA (/opt/homebrew/var/postgresql@14) directory

cd $POSTGRES_DATA

2. Generate Certificates​

In below command -subj "/CN=dbhost.yourdomain.com" argument is optional for local setup.

openssl req -new -x509 -days 365 -nodes -text -out server.crt \
  -keyout server.key -subj "/CN=dbhost.yourdomain.com"

this will create two files server.key & server.crt

3. Restrict The Permissions​

So only root user can read and write the file.

chmod og-rwx server.key

4. Update the config file​

open postgresql.conf

open POSTGRES_DATA/postgresql.conf

and edit existing ssl config.

# - SSL -

ssl = on

5. Restart Postgres​

brew services restart postgresql
# OR
brew services restart postgresql@14

pg_hba.conf​

The pg_hba.conf file is a crucial configuration file in PostgreSQL that controls how the server authenticates users.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     password
# IPv4 local connections:
host    all             all             127.0.0.1/32            password
# IPv6 local connections:
host    all             all             ::1/128                 password
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     password
host    replication     all             127.0.0.1/32            password
host    replication     all             ::1/128                 password

Breakdown​

  1. Local Connections
    • local all all all password: Allows any user to connect locally (using a Unix domain socket) to any database using password authentication.
  2. IPv4 Local Connections
    • host all all 127.0.0.1/32 password: Allows any user to connect to any database from the local host (IPv4) using password authentication.
  3. IPv6 Local Connections
    • host all all ::1/128 password: Allows any user to connect to any database from the local host (IPv6) using password authentication.
  4. Replication Connections
    • local replication all all password: Allows a user with the replication privilege to connect locally (using a Unix domain socket) to any database using password authentication.
    • host replication all 127.0.0.1/32 password: Allows a user with the replication privilege to connect to any database from the local host (IPv4) using password authentication.
    • host replication all ::1/128 password: Allows a user with the replication privilege to connect to any database from the local host (IPv6) using password authentication.

Additional Notes​

  • all in the Database and User fields means the rule applies to all databases and users, respectively.
  • 127.0.0.1/32 and ::1/128 are CIDR notations for the IPv4 and IPv6 loopback addresses, respectively, restricting connections to the local host.
  • password as the authentication method requires users to provide a correct password to connect.
  • Avoid using trust: The trust method allows connections without any authentication, which is generally insecure.

User Management​

  • Least privilege principle
  • Role-based access control (RBAC)
  • Disable unnecessary users

Network Security​

  • Firewall rules
  • SSL/TLS encryption
  • Consider using SSH tunneling to add an extra layer of security for remote connections

Database​

CREATE DATABASE mydatabase_local;
CREATE DATABASE mydatabase_staging;
CREATE DATABASE mydatabase_production;
CREATE DATABASE mydatabase_testing;

Extension​

Create a PostgreSQL extension

CREATE EXTENSION IF NOT EXISTS citext;

citext is a PostgreSQL data type that is case-insensitive and accent-insensitive. This means that it treats strings as equivalent regardless of their case or the presence of accents.

Connection​

Connecting to a Specific Database

psql --host=localhost --dbname=mydatabase_local --username=my_username
psql -d database -U username