PostgreSQL: Create User and Database

April 30, 2014 Add a comment

cheat-sheet articles are about code snippets that I need every once in a while, and which I constantly forget about.

(Taken from PostgreSQL add or create a user account and grant permission for database.)

user@host:~$ psql -U postgres

postgres=# CREATE USER tom WITH PASSWORD 'myPassword';
postgres=# CREATE DATABASE jerry;
postgres=# GRANT ALL PRIVILEGES ON DATABASE jerry to tom;

Or, with multi-line input directly from the command line:

user@host:~$ cat <<EOF | psql -U postgres
CREATE USER tom WITH PASSWORD 'myPassword';
CREATE DATABASE jerry;
GRANT ALL PRIVILEGES ON DATABASE jerry to tom;
EOF

You can specify a different collation than the default collation by using template0 as template database:

user@host:~$ cat <<EOF | psql -U postgres
CREATE DATABASE jerry TEMPLATE = template0 LC_COLLATE = 'de_AT.UTF-8';
EOF