How to Create User, Database and Grant Access in PostgreSQL

After successfully installing the PostgreSQL database, the next step is to manage users’ permissions for the stored databases, which involves creating new users and granting them access to specific databases while restricting their access to others.

In this post, we will discuss how to enter the Postgres shell and create a new user account with password protection.

Connecting to PostgreSQL Server

To enter the Postgres shell, we need to first move from the standard user account to the Postgres account. We can do this by running the following command:

$ sudo -i -u postgres

To determine which user we are working with, use the whoami command, and we may see output similar to the following:

$ whoami
Login to Postgres User
Login to Postgres User

After checking the username using the whoami command, we can run the psql command to get access to the PostgreSQL server. Alternatively, we can use the command “psql -U postgres -W” to enter the Postgres shell.

$ psql -U postgres -W
psql -V

Once inside, we will see a prompt that looks as follows:

Connect to Postgres Shell
Connect to Postgres Shell

Create a Postgres User

Now that we have entered the postgres shell, we can create a new user account called geeksveda by executing the following command:

CREATE USER geeksveda WITH PASSWORD 'geeks123';

The above command creates a new user account named geeksveda with the password ‘geeks123‘.

Create a NewPostgres User
Create a NewPostgres User

Next, we will create a database and grant geeksveda access to it.

Grant Permissions to Postgres User

To grant privileges to the user, we can use the GRANT command followed by the privileges we want to grant and the user we want to grant them to.

Create PostgreSQL Database

First, let’s create a database called geeksveda_db with the following command:

CREATE DATABASE geeksveda_db;

After creating a database named geeksveda_db, we can grant access to the user geeksveda by executing the following command:


This command grants geeksveda all permissions in the database, including SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCES, TRIGGER, CREATE, TEMPORARY, EXECUTE, and USAGE.

If we want to grant only one permission, such as SELECT, we can use the command:

GRANT SELECT ON DATABASE geeksveda_db TO geeksveda;
Grant All Privileges to Users in PostgreSQL
Grant All Privileges to Users in PostgreSQL

In PostgreSQL, privileges are used to determine which users or roles have access to specific objects such as tables, views, sequences, functions, and databases.

Various types of privileges can be granted to users or roles in PostgreSQL, including:

  • SELECT – allows the user to read data from a table or view.
  • INSERT – allows the user to insert new rows into a table.
  • UPDATE – allows the user to modify existing rows in a table.
  • DELETE – allows the user to delete rows from a table.
  • TRUNCATE – allows the user to delete all rows from a table.
  • REFERENCES – allows the user to create a foreign key constraint that references a column in another table.
  • TRIGGER – allows the user to create triggers on a table.
  • CREATE – allows the user to create new objects, such as tables, views, or functions.
  • CONNECT – allows the user to connect to a database.
  • TEMPORARY – allows the user to create temporary objects that are automatically dropped at the end of a session.
  • EXECUTE – allows the user to execute functions and stored procedures.
  • USAGE – allows the user to use a schema or a sequence.

It is important to be careful when granting privileges, as giving too many privileges to a user or role can lead to security issues. It is recommended to only grant the minimum necessary privileges for a user to perform their required tasks.

Update User Password in PostgreSQL

To update a user in PostgreSQL, you can use the ALTER USER command as shown.

ALTER USER geeksveda WITH PASSWORD 'new_password';

This will update the password for the user with the specified username (geeksveda).

Update Username in PostgreSQL

To change a user’s name in PostgreSQL, you can use the ALTER USER with RENAME TO statement. Here’s an example of how to change a user’s name from ‘oldname‘ to ‘newname‘:

ALTER USER geeksveda RENAME TO geeksveda1;

Delete User in PostgreSQL

To delete a user in PostgreSQL, you can use the DROP USER command as shown.

DROP USER geeksveda1;

This will delete the user with the specified username (geeksveda1) from the database.

Note that this action is permanent and cannot be undone, so use it with caution.

Overall, these commands are simple and straightforward to use. However, it’s essential to take care when performing any user management tasks, as incorrect changes or deletions can have a significant impact on your database and applications.

Use Comments in SQL Statements in PostgreSQL

We use comments to put notes or explanations about the large complex queries or functions that we have created so that we do not forget the reason for writing the SQL commands that we put in that specific form.

Therefore, if we want to review or modify them after a while, that will be very easy because we will read the comments until we understand how to install commands quickly and then modify what we want.

Putting comments does not affect the orders placed in the query, and an unlimited number of comments can be placed.

  • Comments, in general, can be placed in most types of databases, but not in all of them. For example, Access databases do not allow comments.
  • Another note is that comments are not placed in the same way in all types of databases. For example, there are three options for placing comments in MySQL databases, while in PostgreSQL databases there are only two options.

Add Comments in PostgreSQL

There are two ways to add comments in PostgreSQL:

Single-line comments, which start with two consecutive dashes (--). Everything that follows on that line will be considered a comment and ignored by the system.

For example:

CREATE DATABASE geeksveda_db; -- This query creates a database with name geeksveda_db

Multi-line comments can be used for longer comments that span across several lines. They start with the /* symbol and end with the */ symbol.

For example:

Create a user with the name geeksveda
create a database with name geeksveda_db
Assigning permissions to the user geeksveda

Comments can be added to most objects in PostgreSQL, including tables, columns, views, functions, and triggers.


In this post, we discussed how to create a new user, assign permissions to the user, and use comments to add notes and explanations in PostgreSQL databases. By following these steps, you can easily manage users’ permissions and optimize your PostgreSQL database for better performance.

If you read this far, tweet to the author to show them you care. Tweet a thanks
Mead Naji is a freelance developer with extensive experience in web development and Linux system administration. Since January 2022, Mead has been working on developing an e-commerce solution with a new payment system like Stripe, as well as doing one-on-one teaching sessions.

Each tutorial at GeeksVeda is created by a team of experienced writers so that it meets our high-quality standards.

Join the GeeksVeda Weekly Newsletter (More Than 5,467 Programmers Have Subscribed)
Was this article helpful? Please add a comment to show your appreciation and support.

Got Something to Say? Join the Discussion...