Create PostgreSQL users, roles and databases

Every application that talks to PostgreSQL should have its own login role and its own database. That keeps one app's data away from another and means a leaked password only exposes one database. This guide shows how to create both, with the command-line helpers and with SQL, and how to grant exactly the access the app needs.

Before you start

• A Linux VPS with PostgreSQL installed and running. If you still need it, see PostgreSQL basics.
• SSH access as root or a sudo user.
• The examples use the role appuser and the database appdb. Replace them with your own names.

In PostgreSQL, users and groups are both roles. A "user" is simply a role with the LOGIN attribute. Out of the box there is one superuser role called postgres, and on Linux you reach it through the postgres system account.

Option A: the createuser and createdb helpers

PostgreSQL ships two small shell wrappers. Run them as the postgres system user:

sudo -u postgres createuser --pwprompt appuser
sudo -u postgres createdb --owner=appuser appdb

--pwprompt asks for the new role's password twice, so it never appears in your shell history. Choose your own strong password. --owner makes appuser the owner of the new database, which gives it full control over that database.

That is enough for a typical single-app setup. Skip to Check it works if that is all you need.

Option B: SQL in psql

SQL gives you more control. Open psql as the superuser:

sudo -u postgres psql

Create the login role. The password below is a placeholder; use your own:

CREATE ROLE appuser WITH LOGIN PASSWORD 'REPLACE_WITH_YOUR_OWN_PASSWORD';

CREATE USER appuser... does the same thing, because CREATE USER is CREATE ROLE with LOGIN switched on.

Now create the database and hand it to the role:

CREATE DATABASE appdb OWNER appuser;

Leave psql with \q.

Grant privileges

As the owner, appuser can already connect to appdb and create tables in its public schema. On PostgreSQL 15 and later, the public schema is owned by the database owner and ordinary roles can no longer create objects in it by default. That is why making the app role the database owner is the simplest setup.

If you need a second role with limited rights, such as a read-only reporting account, grant access in layers. Create the role first, then connect to the target database as the superuser:

sudo -u postgres psql -c "CREATE ROLE reporter WITH LOGIN;"
sudo -u postgres psql -c "\password reporter"
sudo -u postgres psql -d appdb

Then grant, from the outside in:

GRANT CONNECT ON DATABASE appdb TO reporter;
GRANT USAGE ON SCHEMA public TO reporter;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporter;

GRANT... ON ALL TABLES only covers tables that exist now. To include tables the app creates later, set default privileges for the role that creates them:

ALTER DEFAULT PRIVILEGES FOR ROLE appuser IN SCHEMA public
  GRANT SELECT ON TABLES TO reporter;

For a role that also writes data, grant SELECT, INSERT, UPDATE, DELETE instead of SELECT, and add USAGE, SELECT on sequences so serial and identity columns work:

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO reporter;

Change ownership and remove roles

To move an existing database to another owner:

ALTER DATABASE appdb OWNER TO appuser;

This changes the database itself, not the tables inside it. To move every object one role owns in the current database to another role, connect to that database and run:

REASSIGN OWNED BY olduser TO appuser;

To delete a role, first reassign or drop what it owns in each database it uses, then drop it:

DROP OWNED BY olduser;
DROP ROLE olduser;

Check it works

List roles and databases from psql:

\du
\l

appuser should appear in \du, and appdb should show appuser as its owner in \l. Then test a real login over TCP, the way most applications connect:

psql -h 127.0.0.1 -U appuser -d appdb -c "SELECT current_user, current_database();"

Enter the password when asked. You should see appuser and appdb.

Troubleshooting

• "Peer authentication failed for user appuser": you connected through the local socket without -h, and pg_hba.conf maps socket logins to the Linux user. Add -h 127.0.0.1 to use password login.
• "password authentication failed": the password is wrong or was never set. Reset it with \password appuser as the postgres user.
• "permission denied for schema public": the role is not the database owner and has no CREATE or USAGE on the schema. Make it the owner, or grant the schema rights shown above.
• "permission denied for table": grants on existing tables were not given, or the table was created after your grant. Rerun the GRANT and set default privileges.
• "role does not exist": role names are case-sensitive when quoted. Use lowercase names without quotes to avoid surprises.

Next steps

• Learn the psql shortcuts used above: psql cheat sheet.
• Protect the new database: Back up and restore PostgreSQL with pg_dump.