PostgreSQL 16: Setup, Roles, and Remote Connections

Unlike MySQL, PostgreSQL keeps role privileges and database privileges separate. This is where most people get stuck: letting a role connect to a database does not mean it can create tables in it. This guide creates an application role from scratch and grants only what it needs.

First connection and the superuser#

When you create the service, the initial user and password are set through environment variables; you can see them under 'Startup Variables' on the service detail page. Make the first connection with that user, then create a separate role for your app -- running your app as superuser turns a single SQL injection bug into the loss of the whole cluster.

# Panelde yazan IP ve portu kullanin
psql "postgresql://postgres:[email protected]:5432/postgres"

# Baglanti kuruldu mu?
postgres=# SELECT version();

A dedicated role and database for the app#

-- 1) Rolu olustur (LOGIN olmadan baglanamaz)
CREATE ROLE uygulama WITH LOGIN PASSWORD 'guclu-bir-parola';

-- 2) Veritabanini bu rolun sahipligiyle olustur
CREATE DATABASE uygulama_db OWNER uygulama;

-- 3) Herkese acik public semasindan yazma yetkisini al
\c uygulama_db
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- 4) Yalnizca uygulama rolune ver
GRANT ALL ON SCHEMA public TO uygulama;

Keeping remote access safe#

• Instead of exposing the port to the world, allow only your application server's IP.
• Keep the password in the service's environment variables, not in application code.
• Use different roles and passwords per environment; sharing one password means a leak in staging is a leak in production.
• For read-only consumers, create a separate role limited to SELECT.

Taking backups with pg_dump#

# Ozel bicim (-Fc): sikistirilmis ve secici geri yuklemeye uygun
pg_dump -Fc \
  -h 192.0.2.10 -p 5432 -U uygulama \
  -d uygulama_db \
  -f uygulama_db_$(date +%F).dump