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;
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.
- 2022-03-24: Added with owner creation