Home » Programming » Databases » PostgreSQL: Create A Database and User With Permissions

PostgreSQL: Create A Database and User With Permissions

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 DjangoDrupal, 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.

Photo of author
I'm Brad, and I'm nearing 20 years of experience with Linux. I've worked in just about every IT role there is before taking the leap into software development. Currently, I'm building desktop and web-based solutions with NodeJS and PHP hosted on Linux infrastructure. Visit my blog or find me on Twitter to see what I'm up to.

Leave a Comment