PostgreSQL 18: Databases and roles

Create a database

Create a new database from the shell.

createdb -U postgres mydb

Drop a database

Delete a database and everything in it.

dropdb -U postgres mydb

Caution: This permanently deletes the database. Take a backup first.

Create a login role

Create a role that can sign in with a password. Use a real password.

CREATE ROLE app_user WITH LOGIN PASSWORD 'change-me';

Grant table privileges

Let a role read and write all tables in the public schema.

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;