PostgreSQL Installation & Setup Guide (Debian)

1️⃣ Install Latest PostgreSQL (Official Repository)

Update system

apt update
apt install -y curl ca-certificates gnupg lsb-release

Add PostgreSQL official GPG key

curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor -o /usr/share/keyrings/postgresql.gpg

Add official repository

For Debian 12 (bookworm):

echo "deb [signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt bookworm-pgdg main" > /etc/apt/sources.list.d/pgdg.list

For Debian 11 (bullseye):

echo "deb [signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt bullseye-pgdg main" > /etc/apt/sources.list.d/pgdg.list

Update & Install newest PostgreSQL

apt update
apt install -y postgresql

Enable service

systemctl enable postgresql
systemctl start postgresql

2️⃣ Create PostgreSQL Superuser `root`

Switch to postgres system user and open psql:

su - postgres
psql -d postgres

Inside PostgreSQL:

CREATE ROLE root WITH LOGIN SUPERUSER PASSWORD 'YourStrongPassword';

Exit:

\q
exit

3️⃣ Create Database `powerfolder`

Open psql again:

su - postgres
psql -d postgres

Create database:

CREATE DATABASE powerfolder OWNER root;

4️⃣ Create Application User `powerfolderuser`

Still inside psql:

CREATE ROLE powerfolderuser WITH LOGIN PASSWORD 'powerfolderpass';

Grant privileges:

ALTER DATABASE powerfolder OWNER TO powerfolderuser;
\c powerfolder

GRANT ALL ON SCHEMA public TO powerfolderuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO powerfolderuser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO powerfolderuser;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON TABLES TO powerfolderuser;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON SEQUENCES TO powerfolderuser;

Exit:

\q
exit

5️⃣ Test Connection

Login as root

psql -U root -W -d powerfolder

Login as powerfolderuser

psql -U powerfolderuser -W -d powerfolder

6️⃣ (Optional) Set Default Database

To always connect to powerfolder:

vi ~/.bashrc

Add:

export PGDATABASE=powerfolder

Reload:

source ~/.bashrc

🔐 Change `peer` → `scram-sha-256` for local connections

Edit the file PostgreSQL reported:

vi /etc/postgresql/18/main/pg_hba.conf

Change any matching lines like:

local   all             all                                     peer
local   all             postgres                                 peer

To:

local   all             all                                     scram-sha-256
local   all             postgres                                 scram-sha-256

Restart:

systemctl restart postgresql