Create user and database in postgres

1 minute read

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.

UPDATES:

  • 2022-03-24: Added with owner creation