How to create users and databases in PostgreSQL

In PostgreSQL, users are called "roles". Here is how to create a role, a database and link them.

1. Enter the console

sudo -u postgres psql

2. Create a role with a password

CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password';

3. Create the database

CREATE DATABASE app_db OWNER app_user;

4. Grant privileges

GRANT ALL PRIVILEGES ON DATABASE app_db TO app_user;

5. Connect as the new user

psql -U app_user -d app_db -h 127.0.0.1

Tips

• A role can have login rights or not; use WITH LOGIN for application accounts.
• For fine table privileges, use GRANT SELECT, INSERT ... ON table TO app_user;.
• For remote access, see connect to a remote database.