This guide will help you to create a user and database in PostgreSQL server. Also assign the permissions on created database to newly created user. You can also use the same SQL statements for existing databases and users.

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 dbname;
CREATE USER username WITH PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE dbname TO username;

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

PostgreSQL: Creating a User, Database, and Assign Permissions Database Administration and Security PostgreSQL psql
PostgreSQL: Creating a User, Database, and Assign Permissions

Step-by-Step 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 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 username WITH PASSWORD 'password';
  • Replace username 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 dbname;
  • Replace dbname with the name you want for the database.

Step 3: Assigning Permissions

Permissions are like rules that decide what a user can do with a database. To give a user access to a database:

Run the following command:


GRANT ALL PRIVILEGES ON DATABASE dbname TO username;
  • Replace dbname with the name of your database.
  • Replace username with the name of your user.

Summary

Creating a user, a 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 ALL PRIVILEGES.

, ,
Show Comments