Managing users in PostgreSQL is a crucial aspect of database administration. Whether you’re setting up a new database or maintaining an existing one, knowing how to create and delete users efficiently is essential. In this tutorial, we’ll walk you through the entire process step-by-step, ensuring that even if you’re new to PostgreSQL, you’ll find it easy to follow.

Prerequisites

Before we begin, ensure you have:

  • PostgreSQL installed on your system
  • Access to a PostgreSQL database as a superuser or a user with sufficient privileges
  • Basic knowledge of SQL commands and PostgreSQL

Step 1 – Connecting to PostgreSQL

To manage users, you first need to connect to your PostgreSQL database. Use the psql command-line tool for this purpose.

sudo -u postgres psql

This command connects you to the PostgreSQL server using the default postgres user. If you have set up a different superuser, replace postgres with that username.

psql (13.3)
Type "help" for help.

postgres=#

You’re now connected to the PostgreSQL database and ready to create a new user.

Step 2 – Creating a New User

Creating a new user in PostgreSQL is straightforward. Use the CREATE USER command followed by the username and any optional parameters like password and privileges.

CREATE USER new_user WITH PASSWORD 'password123';

Output:

CREATE ROLE

The command above creates a new user named new_user with the password password123. By default, this user has no privileges beyond connecting to the database.

Step 3 – Granting Privileges to the User

To make the new user more useful, you’ll likely want to grant some privileges. For instance, you might want the user to have the ability to create databases.

ALTER USER new_user CREATEDB;

Output:

ALTER ROLE

The ALTER USER command above grants the CREATEDB privilege to new_user, allowing them to create new databases.

Step 4 – Deleting a User

If you no longer need a user, you can remove them using the DROP USER command. Be careful with this operation as it cannot be undone.

DROP USER new_user;

Output.

DROP ROLE

The command above deletes the user new_user from the PostgreSQL database.

Conclusion

In this article, we covered the essential steps to create and delete a PostgreSQL user. You learned how to connect to your PostgreSQL database, create a new user, grant privileges, and finally, delete the user when they are no longer needed. These tasks are fundamental for PostgreSQL database administration and ensure that you can manage user access and permissions effectively. Try to create a user on PostgreSQL hosted on a dedicated server hosting from Atlantic.Net!