How to Create a PostgreSQL Database, Add a User, and Assign Permissions in Ubuntu

Creating a PostgreSQL database and configuring user access is a foundational task for any web application stack or data management system. In this blog post, we'll walk you through the steps to install PostgreSQL on Ubuntu, create a database and a user, and grant necessary permissions.

Step 1: Install PostgreSQL on Ubuntu

Before we begin, ensure PostgreSQL is installed on your Ubuntu system. You can install PostgreSQL by running the following commands:

Step 2: Access the PostgreSQL Account

PostgreSQL creates a user account called postgres during installation. You need to switch to this account to manage databases and users:

Step 3: Use the PostgreSQL Prompt

After switching to the postgres account, access the PostgreSQL prompt with:

Step 4: Create a New Database

At the PostgreSQL prompt, use the CREATE DATABASE statement to create your database:

Replace mydatabase with the desired name for your database.

Step 5: Create a User

Now it's time to create a user who will have access to the database. Execute the following command:

Don't forget to substitute myuser with your chosen username, and mypassword with a secure password.

Step 6: Grant Permissions

Assign appropriate permissions to your user with the GRANT statement:

You can tailor the privileges to suit your needs. For example, you might want to grant only specific permissions like SELECT, INSERT, UPDATE, or DELETE.

Step 7: Exit the PostgreSQL Prompt

Exit the PostgreSQL prompt by typing:

Then return to your regular user account by typing exit or pressing Ctrl+D.

Step 8: Connecting to the Database