Creating a PostgreSQL Superuser: Quick Linux Setup with psql

When you need an admin-shaped login right after PostgreSQL is installed on Ubuntu, this is the smallest honest path: open psql as the postgres OS user, run CREATE USER ... SUPERUSER, prove it with \du, then log in once yourself. Superuser is a chainsaw—reach for it during bootstrap, not for every app.

On the same hosts where I later touch UFW, I still keep paste-ready nginx apt and UFW sequencing beside database tabs so ufw status verbose rhythm matches Redis and MongoDB work—not because nginx is required for Postgres, but because the firewall choreography should never be one-off muscle memory.

⏱️ Estimated time: 5–10 minutes

Before you start

• PostgreSQL is installed and the service is running (active in systemctl status).
• You can open a root or sudo-capable shell on the server.
• You are fine granting SUPERUSER only to a human operator account—not a web app.

Open psql as the postgres system user

Log into the interactive shell as the default database superuser identity:

sudo -u postgres psql

That uses the Unix postgres account so peer authentication can admit you without juggling database passwords yet—exactly why sudo postgres psql shows up in every baseline guide.

Create the superuser role

Inside psql, replace placeholders with your real admin name and a strong secret:

CREATE USER username WITH SUPERUSER PASSWORD 'password';

• CREATE USER: creates a login role (internally similar to CREATE ROLE ... LOGIN).
• WITH SUPERUSER: full admin privileges—use sparingly.
• PASSWORD: sets how this role authenticates when pg_hba.conf routes you to password auth.

List roles and confirm the Superuser flag

Still in psql, list roles:

\du

You should see username with the Superuser attribute. If you need more detail (connection limits, member-of), \du+ prints a wider row.

Test login with the new role

Exit the shell:

\q

Connect as the new login to prove passwords and pg_hba.conf cooperate for your path:

psql -U username -W

-W forces a password prompt even when .pgpass might skip it—handy while you are proving the story by hand.

Superuser hygiene and passwords

• Rotate admin secrets on a schedule that matches your threat model; document who holds the break-glass accounts.
• Audit role sprawl monthly—stale superusers are how incidents get louder than they need to be.
• Network policy still matters: strong passwords do not replace subnet controls. When you graduate past localhost tests, walk the PostgreSQL remote access checklist instead of opening TCP 5432 with optimism.
• Pair host firewalls with habit: whenever I script ufw allow / reload / status verbose next to a database tier, I still paste from the single-page nginx and UFW snippet so ordering matches every other Ubuntu service note on this site—not cargo-culting nginx, but keeping the firewall dance identical.

When authentication or networking still breaks

• Authentication errors: re-run \du, confirm spelling, and read pg_hba.conf for whether you are hitting local, host, or hostssl rows—your error log usually names the mismatch before Google does.
• Service down: sudo systemctl status postgresql plus journalctl -u postgresql catch the boring failures.
• Network path: ss -lntp | grep 5432 proves listening; firewalls and cloud security groups still need explicit allows. The nginx-focused command slab is my fast copy deck when Ubuntu firewall edits land next to database work, same as in MySQL user setup.

Frequently asked questions

Should new PostgreSQL accounts default to SUPERUSER?

No. SUPERUSER bypasses normal permission checks and should stay rare. Application roles usually need narrower grants. Use a superuser only for break-glass admins or bootstrap, then prefer least-privilege roles for apps.

What does sudo -u postgres psql actually do?

It launches psql as the Unix postgres user, the default local identity trusted through peer authentication. That shell can run CREATE USER and other DDL before you ever type a database password.

How do I list PostgreSQL roles and spot the superuser flag?

Inside psql run \du (or \du+) to print roles and attributes. Your new login should show Superuser in the list after CREATE USER WITH SUPERUSER.

Why does psql reject my password right after CREATE USER?

Confirm the password, verify the role with \du, and read pg_hba.conf for your connection type. Host versus local entries and md5 versus scram-sha-256 mismatches usually surface here before networking.

Where do pg_hba.conf and firewalls fit after I create a user?

A role is identity only. pg_hba.conf still controls who may authenticate and how; host firewalls and cloud security groups still gate TCP 5432. When you intentionally widen access, follow how to enable PostgreSQL remote connections safely instead of trusting passwords alone.