Since I always google this. Here now a couple of methods.
Method 1 - create and grant all privileges
CREATE DATABASE yourdbname;
CREATE USER youruser WITH PASSWORD 'yourpass';
GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser;
# for PG 15, you need to do more now (https://stackoverflow.com/a/75876944)
\c yourdbname adminuser
GRANT ALL ON SCHEMA public TO youruser;
The owner of the database will not be “youruser”. This seems to be just fine in 99% of all cases. If you need database ownership for “myuser”, read on.
Method 2 - create with a certain owner
CREATE USER myuser WITH PASSWORD 'mypassword';
GRANT myuser TO your_current_admin_user;
CREATE DATABASE mydatabase WITH OWNER myuser ;
DISCLAIMER: I have no idea what the 2nd line does and whether this is a good idea. It just worked.
Note on encryption
Note: Sometimes you read CREATE USER ... WITH ENCRYPTED .... Starting with Postgres 10 the ENCRYPTED keyword has no longer any significance:
The password is always stored encrypted in the system catalogs. The ENCRYPTED keyword has no effect, but is accepted for backwards compatibility. (v10)
Whereas the docs of Postgres 9.6 state:
These key words control whether the password is stored encrypted in the system catalogs.
UPDATES:
- 2022-03-24: Added with owner creation
- 2023-05-30: postgres 15 update