Create user and database in postgres

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