Install PostgreSQL on Ubuntu: Database, User, Access

curl -sSL https://cp.lv/scripts/ustanovit-postgresql.sh | sudo bash

wget -qO- https://cp.lv/scripts/ustanovit-postgresql.sh | sudo bash

#!/usr/bin/env bash
# Install PostgreSQL, create a database and a user
set -e
export DEBIAN_FRONTEND=noninteractive

DB_NAME=app_db
DB_USER=app_user
DB_PASS='StrongPassword123!'

# 1. Install PostgreSQL and the contrib extensions
sudo apt-get update
sudo apt-get install -y postgresql postgresql-contrib
sudo systemctl enable --now postgresql

# 2. Create the role (user) and the database
sudo -u postgres psql << EOF
CREATE ROLE ${DB_USER} WITH LOGIN PASSWORD '${DB_PASS}';
CREATE DATABASE ${DB_NAME} OWNER ${DB_USER};
GRANT ALL PRIVILEGES ON DATABASE ${DB_NAME} TO ${DB_USER};
EOF

# 3. Grants on the public schema (required from PostgreSQL 15 on)
sudo -u postgres psql -d ${DB_NAME} -c "GRANT ALL ON SCHEMA public TO ${DB_USER};"

echo "Done: database ${DB_NAME} and user ${DB_USER} created."

PostgreSQL is a powerful relational database with JSON support, full-text search and strict data integrity. Below is the installation on Ubuntu 22.04/24.04, creating a database and a user, and configuring access.

Step 1. Installation

PostgreSQL is in the standard repository:

sudo apt update
sudo apt install postgresql postgresql-contrib -y
sudo systemctl enable --now postgresql

Check the version and the status:

psql --version
sudo systemctl status postgresql

The postgresql-contrib package adds useful extensions (pg_stat_statements and uuid-ossp, for example).

Step 2. Role and database

PostgreSQL creates the system user postgres. Open the console as that user:

sudo -u postgres psql

Create a user (role) and a database for the application:

CREATE ROLE app_user WITH LOGIN PASSWORD 'StrongPassword123!';
CREATE DATABASE app_db OWNER app_user;
GRANT ALL PRIVILEGES ON DATABASE app_db TO app_user;
\q

In PostgreSQL 15 and newer, the default rights on the public schema are restricted. If the application cannot create tables, grant the schema rights explicitly:

sudo -u postgres psql -d app_db

GRANT ALL ON SCHEMA public TO app_user;
\q

Step 3. Verify the login

Connect locally as the new user:

psql -h 127.0.0.1 -U app_user -d app_db -W

Enter the password. If the app_db=> prompt appears, access works.

Step 4. Configure remote access

By default PostgreSQL listens on localhost only. To accept external connections (from a backend on another server, for example), edit two files. Find their paths:

sudo -u postgres psql -c 'SHOW config_file;'
sudo -u postgres psql -c 'SHOW hba_file;'

In postgresql.conf, set the listen addresses. Open the file in an editor (nano, vim or mcedit):

sudo nano "$(sudo -u postgres psql -tAc 'SHOW config_file;')"

The setting line:

listen_addresses = '10.0.0.5, localhost'

Or append it with a single command — copy it and paste it into the console (the heredoc appends the line to the end of the file):

sudo tee -a "$(sudo -u postgres psql -tAc 'SHOW config_file;')" > /dev/null << 'EOF'
listen_addresses = '10.0.0.5, localhost'
EOF

In pg_hba.conf, add a rule for the trusted subnet with password encryption. Open the file in an editor:

sudo nano "$(sudo -u postgres psql -tAc 'SHOW hba_file;')"

The rule:

# TYPE  DATABASE  USER      ADDRESS         METHOD
host    app_db    app_user  10.0.0.0/24     scram-sha-256

Or add the rule with a single command (the quotes around 'EOF' keep the text exactly as written):

sudo tee -a "$(sudo -u postgres psql -tAc 'SHOW hba_file;')" > /dev/null << 'EOF'
host    app_db    app_user  10.0.0.0/24     scram-sha-256
EOF

Never expose the database to 0.0.0.0/0 — only to specific IP addresses or a private network. Apply the changes:

sudo systemctl restart postgresql

Open the port for the trusted source only:

sudo ufw allow from 10.0.0.10 to any port 5432

Useful psql commands

FAQ

What is the difference between a role and a user in PostgreSQL? They are the same thing: a user is a role with the LOGIN attribute. CREATE USER is a synonym for CREATE ROLE ... WITH LOGIN.

Why does the application say “permission denied for schema public”? Since PostgreSQL 15 the rights on the public schema are restricted. Grant them explicitly: GRANT ALL ON SCHEMA public TO app_user.

How do I set a password for the postgres user? Run sudo -u postgres psql and execute ALTER USER postgres PASSWORD 'new_password';.

How do I check whether the database listens on an external port? sudo ss -tlnp | grep 5432 shows the listen addresses. If it is only 127.0.0.1, edit listen_addresses.

PostgreSQL is sensitive to disk speed and to the memory available for caching. For databases, take an NVMe plan: see VPS hosting and the dedicated database VPS.