CREATE USER <user> WITH PASSWORD '<password>' SUPERUSER;
\du
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
admin | Superuser, Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
psql -h localhost -U admin
psql (13.20)
Type "help" for help.
admin=#
- create application database with specific user
CREATE USER <app_user> WITH ENCRYPTED PASSWORD '<app_password>';
CREATE DATABASE <app_db> OWNER <app_user>;
GRANT ALL PRIVILEGES ON DATABASE <app_db> TO <app_user>;
pg_dump -h localhost -U admin -d <app_db> -f /path/to/file/<app_db>.sql
psql -h localhost -U admin
drop database <app_db>;
\q
psql -h localhost -U admin -d <app_db> < /path/to/file/<app_db>.sql