PostgreSQL: Creating Users and Databases

This is a very, very quick blog post regarding how to create a user and database in PostgreSQL through the command line prompt.

To do this you just need to run two commands:

createuser -h localhost -U postgres testuser -W -S -D -R -P

createdb -h localhost -U postgres -O testuser -W  test_db

These commands assume you have password protected the local postgres user (if not, you can drop the -W trigger).

A quick overview of the createuser command:

• -U tells the command which user to perform the command with (NOT the user to create)
• -S tells us that the new user is NOT have superuser privileges (as opposed to the lower case -s, which do have superuser privileges)
• -D tells us that the new user is NOT have create database privileges (as opposed to the lower case -d, which do have superuser privileges)
• -R tells us that the new user is NOT have create roles privileges (as opposed to the lower case -r, which do have superuser privileges)
• -P prompts the command to immediately prompt for a password for the new user.
• -W will prompt the password for the user that will run the command (i.e. the username passed with the -U trigger)
• -h tells the commands which host to connect to.

The createdb command only differs with the -O trigger, which tells the command which user will own the new database.

And that's it!

Other parts of PostgreSQL series include an installation guide for Ubuntu, useful functions and operators, a guide to improving Postgre performance, how to check for installed procedural languages, and an overview of some basic concepts and some more advanced ones.

You can also check out my notes on designing relational databases.