Creating PostgreSQL User and Assign Permissions

Creating a user in PostgreSQL and assigning permissions might seem complicated, but it’s quite straightforward once you understand the basics. PostgreSQL is a powerful database management system used to handle data efficiently. To allow different people or applications to access the database, you need to create users and give them specific permissions. This process ensures that only authorized users can view or modify the data, keeping it secure and well-managed.

In this guide, we will walk you through the simple steps of creating a user in PostgreSQL, creating a database and assigning them the necessary permissions, making it easy even for beginners to follow.

Quick Instructions

Here are the quick instructions to create a user and database with assigning permissions in PostgreSQL server. Tested with PostgreSQL 16 on Ubuntu 24.04 LTS, these instructions will guide you through the process easily.

CREATE DATABASE my_database;
CREATE USER new_user WITH PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE my_database TO new_user;

Make sure to connect PostgreSQL command line interface using sudo -i -u postgres psql command. For more reference, see the screenshot:

Step-by-Step Detailed Instructions

PostgreSQL is a powerful database system. To use it, you need to create users, databases, and give permissions. This guide will show you how to do this step by step.

Step 1: Connect to PostgreSQL

The first step is to connect to PostgreSQL server. You can do it either graphical interfaces or open your PostgreSQL command line.

sudo -i -u postgres psql

Step 2: Creating a User

A user in PostgreSQL is like a person who can access the database. You can create one by running the following command:

CREATE USER new_user WITH PASSWORD 'password';

• Replace new_user with the name you want for the user.
• Replace password with a strong password for the user.

Step 3: Creating a Database

A database is a place where you store your data. To create a database, run the following command:

CREATE DATABASE my_database;

• Replace my_database with the name you want for the database.

Step 4: Assigning Permissions

Permissions are like rules that decide what a user can do with a database. You can either choose to assign all privileges to user on specified database or give limited access on specific tables.

Grant Full Access on Database

Generally we provide full access on database to smooth running any application. On development and staging environment, its fine to allow full privilege’s.

Use the following statement to allow full access on my_database to new_user

GRANT ALL ON DATABASE my_database TO new_user;

But in some cases, you may need to allow a user on specific tables. For example, you want to allow only products table access to an publicly accessible API endpoint.

Allow Database Connection

Next, you need to grant the new user connect to a specific database. Let’s assume you have a database named my_database. Use the GRANT command to give new_user access to my_database:

GRANT CONNECT ON DATABASE my_database TO new_user;

This will not allow to access table data to user. In order to allow access to data you need to allow table specific permissions.

Granting Table Permissions

After granting access to the database, you may want to give the user permissions on specific tables within the database. Here’s how you can do it:

1. Granting SELECT Permission (Read Only)

   To allow new_user to read data from a table named my_table:

   
   GRANT SELECT ON my_table TO new_user;
   
   

2. Granting INSERT Permission (Add Data)

   To allow new_user to insert data into my_table:

   
   GRANT INSERT ON my_table TO new_user;
   
   

3. Granting UPDATE Permission (Modify Data)

   To allow new_user to update existing data in my_table:

   
   GRANT UPDATE ON my_table TO new_user;
   
   

4. Granting DELETE Permission (Remove Data)

   To allow new_user to delete data from my_table:

   
   GRANT DELETE ON my_table TO new_user;
   
   

Granting All Permissions

If you want to grant all permissions (SELECT, INSERT, UPDATE, DELETE) on a table to new_user, you can use the following command:

GRANT ALL PRIVILEGES ON my_table TO new_user;

Step 5: Revoking Permissions

In case you need to revoke permissions from a user, you can use the REVOKE command. For example, to remove the INSERT permission from new_user on my_table:

REVOKE INSERT ON my_table FROM new_user;

Step 6: Deleting a User

If you ever need to delete a user, you can use the DROP USER command. For example, to delete new_user:

DROP USER new_user;

Summary

Creating a user, database, and assigning permissions in PostgreSQL is simple. You just need to run three commands. First, create a user with CREATE USER. Second, create a database with CREATE DATABASE. Finally, give the user access to the database with GRANT command. PostgreSQL allows you to choose what data a user can a user see, edit or delete from tables.

Hope this tutorial helped you to learn about creating users and assigning permissions in PostgreSQL server.