This short guide will show you how to create a database, and a user that can access it, in PostgreSQL. This is useful if you are installing an app that provides its own table structures and data during installation.
Many applications support PostgreSQL, for example the popular frameworks Django, Drupal, and Ruby on Rails can all connect to PostgreSQL databases and store data in them.
These apps and frameworks usually supply either their own pre-defined tables, or their own tools for building your own table structure, leaving your only actual task in PostgreSQL to set up a user and database, and supply the credentials into the app so that it can do the rest.
How to Create Users in PostgreSQL
Creating users is done using the CREATE USER statement:
CREATE USER user_name WITH PASSWORD 'password';
How to Create Databases in PostgreSQL
Creating databases in PostgreSQL is done using the CREATE DATABASE statement:
CREATE DATABASE database_name;
How to Grant User Permissions in PostgreSQL
When creating a database, you can grant a user full permissions by using the OWNER statement:
CREATE DATABASE database_name OWNER user_name;
If the database already exists, and it is safe to change the owner (ie, no other user requires those permissions), you can update the owner of the database to grant full permissions:
ALTER DATABASE database_name OWNER TO user_name;
Another method is to grant all privileges for the database to the user, which can be done for multiple users:
GRANT ALL PRIVILEGES ON database_name to user_name;
You can also granularly manage permissions for users and roles – see our article here.
