PostgreSQL calls users roles. A role with the LOGIN attribute is what other databases call a user. Here’s how to create one for your application, give it a database, and get permissions right — including the PostgreSQL 15 change that breaks many older tutorials.

(Haven’t installed PostgreSQL yet? See installing PostgreSQL on a VPS.)

Connect as the superuser

On Debian and Ubuntu, the postgres Linux user maps to the postgres superuser via peer authentication:

sudo -u postgres psql

You’re now at the postgres=# prompt.

The quick way: one user, one database, owned by that user

CREATE ROLE shop_app WITH LOGIN PASSWORD 'a-long-random-password';
CREATE DATABASE shop OWNER shop_app;

Because shop_app owns the database, it can create schemas and tables in it, and everything works for typical web frameworks (Django, Rails, Laravel, Prisma) that run their own migrations.

From the shell, without entering psql:

sudo -u postgres createuser --pwprompt shop_app
sudo -u postgres createdb --owner=shop_app shop

Test the login:

psql -h 127.0.0.1 -U shop_app -d shop

Use -h 127.0.0.1 to force a TCP connection with password authentication; without it, psql uses the socket and peer authentication, which fails because there’s no shop_app Linux user.

PostgreSQL 15+: the public schema change

Before PostgreSQL 15, every user could create tables in the public schema of any database they could connect to. Since 15, only the database owner can. If you create a database owned by postgres and grant a user access, migrations fail with:

ERROR: permission denied for schema public

Fixes:

  • Make the app user the owner of the database (as above) — simplest.
  • Or grant it explicitly:
\c shop
GRANT USAGE, CREATE ON SCHEMA public TO shop_app;

Granting privileges to additional users

Say you want a read-only reporting user:

CREATE ROLE report WITH LOGIN PASSWORD '…';
\c shop
GRANT CONNECT ON DATABASE shop TO report;
GRANT USAGE ON SCHEMA public TO report;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO report;
-- tables created in the future by shop_app:
ALTER DEFAULT PRIVILEGES FOR ROLE shop_app IN SCHEMA public GRANT SELECT ON TABLES TO report;

PostgreSQL privileges are layered: database (CONNECT) → schema (USAGE) → tables (SELECT, INSERT, …). Missing any layer means “permission denied”. And GRANT … ON ALL TABLES only covers tables that exist now — ALTER DEFAULT PRIVILEGES covers future ones.

PostgreSQL 14+ also has predefined roles that make this simpler:

GRANT pg_read_all_data TO report;     -- read everything in every database

Group roles

Roles can contain other roles, which keeps permissions manageable:

CREATE ROLE readonly NOLOGIN;
GRANT CONNECT ON DATABASE shop TO readonly;
\c shop
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

CREATE ROLE alice WITH LOGIN PASSWORD '…' IN ROLE readonly;

Role attributes

CREATE ROLE dba WITH LOGIN PASSWORD '…' SUPERUSER;     -- full power, use sparingly
CREATE ROLE deployer WITH LOGIN PASSWORD '…' CREATEDB;  -- may create databases
ALTER ROLE shop_app CONNECTION LIMIT 50;
ALTER ROLE temp_user VALID UNTIL '2026-12-31';

Inspect roles and permissions

In psql:

\du                 -- list roles and attributes
\l                  -- list databases and owners
\dn+                -- schemas and their privileges
\dp                 -- table privileges in the current database
\conninfo           -- who and where you're connected as

Change a password or drop a role

ALTER ROLE shop_app WITH PASSWORD 'new-password';

Or interactively, without the password ending up in logs: \password shop_app.

To drop a role that owns objects, reassign or drop them first:

\c shop
REASSIGN OWNED BY olduser TO shop_app;
DROP OWNED BY olduser;
DROP ROLE olduser;

(REASSIGN/DROP OWNED act on the current database — repeat in each database where the role owns objects.)

Password authentication: pg_hba.conf

Who can log in, from where and how is controlled by pg_hba.conf (on Debian/Ubuntu: /etc/postgresql/<version>/main/pg_hba.conf). The defaults allow password login (scram-sha-256) from 127.0.0.1 and ::1, and peer login over the socket. Remote connections need extra lines — see PostgreSQL remote connections.

Got “Peer authentication failed for user”? You’re connecting via the socket as a role with no matching Linux user; add -h 127.0.0.1. Details in fixing peer authentication errors.

Security checklist

  • One login role per application, owning only its database.
  • No application connects as postgres.
  • Passwords generated randomly; password_encryption = scram-sha-256 (the default since PostgreSQL 14).
  • Revoke CONNECT from PUBLIC on sensitive databases: REVOKE CONNECT ON DATABASE shop FROM PUBLIC;

Next: back up what you’ve created with pg_dump and pg_restore. PostgreSQL runs well on any KVM VPS with SSD storage and enough RAM for its shared buffers.

  • PostgreSQL
  • Databases
  • Linux