How to Create a Postgres User (2 Methods) – Hostbillo

Introduction

Are you looking to set up a new database using Postgres and wondering how to create a new user? In this blog post, we will be discussing two methods for creating a Postgres user. 

Postgres is a powerful open-source relational database management system that has gained popularity among developers and organizations. However, creating a new user can be an essential but challenging task for some users.

Whether you are a beginner or an experienced user, we have got you covered with different methods. By the end of this post, you will have a clear understanding of different methods to create a Postgres user easily. So, without any further delay, let’s dive in and grab all the details! 

System Requirements

Before you move on to learn how to create a postgres user, you must meet the following prerequisites –

• Installation of Postgres  
• Accessibility to the terminal with sudo privileges.
• Accessibility to the postgres user.

How to Create a New User in PostgreSQL?

There are two methods to create a new user in PostgreSQL, and both cases require access to the postgres user.

Create a New User Using createuser Client Utility

The createuser utility serves as a command-line tool. It is used to create a user in PostgreSQL. This tool comes pre-installed with PostgreSQL. It can be employed to construct new users with different attributes and permissions.

Here are the steps to create a new user with the usage of the createuser utility:

1. Open a command-line interface to your PostgreSQL server.

2. Run the createuser command with the desired options to create a new user. Here’s an example:

createuser --interactive --pwprompt

This command will prompt you to enter a new username and password for the user. You can also identify other options to configure the user’s attributes as well as permissions. For example, you can employ the –createdb option to enable the user to create new databases. Or, you can use –no-superuser option to prevent the user from having superuser privileges.

3. Once you have entered all the needed information, the createuser utility will create a new user in PostgreSQL with the defined attributes and permissions.

This method is particularly reasonable for more experienced users who prefer working with command-line tools.

Create a Postgres User Using PSQL Shell

Here are the steps to create a new user employing the PSQL shell in the terminal:

1. Open the terminal on your machine.

2. Log in to your PostgreSQL server by typing the following command:

psql -U username -d database_name

Replace username with the username of a user with adequate privileges to create new users, and replace database_name with the name of the database where you want to create the new user.

3. Once you are logged in, run the following SQL command to create a new user:

CREATE USER username WITH PASSWORD 'password';

Replace the username with the desired username for the new user, and replace the password with the desired password.

4. Optionally, you can specify additional attributes and permissions for the user by including additional options in the CREATE USER command. For instance, you can employ the CREATEDB option to allow the user to create new databases, or the NOLOGIN option to prevent the user from logging in to the database.

5. After you have entered all the required information, press Enter to implement the CREATE USER command. The PSQL shell will construct a new user in PostgreSQL with the specified attributes and permissions.

List all Users in PostgreSQL

Listing all users in PostgreSQL signifies getting a complete list of all the users that have been constructed in a PostgreSQL database cluster. It is an essential task for database administrators and developers as it enables them to keep track of all the users that have access to the database cluster. This information can be employed to ensure that the database is secure and that the users have the required permissions to perform their tasks. In addition to this, listing all users in PostgreSQL can also be useful for troubleshooting as well as debugging purposes. 

Here are the steps to list all users in PostgreSQL:

1. Open a command-line interface to your PostgreSQL server.

2. Connect to the PostgreSQL server utilizing the psql command-line tool. You can do this by executing the following command:

psql -U postgres

Replace postgres with the name of a user who has permission to access the PostgreSQL database.

3. As soon as you have connected to the PostgreSQL server, you can employ the \du command to list all users:

\du

4. The output will display a list of all users in the current PostgreSQL database cluster. Each user is listed with their username, privileges, and whether they are a superuser or not.

Create a Superuser in PostgreSQL

To create a Superuser in PostgreSQL, follow the below steps:

1. Firstly, log in to your PostgreSQL database as a user with administrative privileges. You can do this utilizing the psql command-line tool.

2. Type the following command to create a new user and assign them the role of a Superuser:

CREATE USER <username> WITH SUPERUSER PASSWORD '<password>';

Replace <username> and <password> with the desired username and password for the Superuser.

3. Optionally, you can grant additional permissions to the Superuser employing the GRANT command. For instance, to allow the Superuser to create databases, employ the following command:

GRANT CREATEDB TO <username>;

4. Once you have created the Superuser, you can log in to the database using the Superuser account by running the following command:

psql -U <username> -h <hostname> -d <database>

Replace <username>, <hostname>, and <database> with the apt values for your database.

5. You must note that while granting Superuser privileges to a user, they will have full access to all database objects and can potentially cause damage if utilized improperly. So, it is typically recommended to limit Superuser privileges to a small number of trusted users.

PostgreSQL Updating USER

For updating a user in PostgreSQL, you can make use of the ALTER USER command. Here are the steps: 

1. Log in to your PostgreSQL database as a user with administrative privileges.

2. Type the following command to update the user’s password:

ALTER USER <username> WITH PASSWORD '<new_password>';

Replace <username> with the username of the user you wish to update, and <new_password> with the new password you want to set for the user.

3. If you desire to modify the user’s name, you can employ the following command:

ALTER USER <old_username> RENAME TO <new_username>;

Replace <old_username> with the current username of the user you want to update, and <new_username> with the new username you want to set.

4. You can also grant or revoke privileges from the user by utilizing the ALTER USER command. For instance, to grant the user the ability to create databases, you can use the following command:

ALTER USER <username> CREATEDB;

5. As soon as you have made the essential changes to the user, you can exit the PostgreSQL prompt by typing \q.

Also Read: How to SSH into a Docker Container and Run Commands?

Summing Up

After thoroughly reviewing this guide, you must have successfully learned how to create a Postgres user using different methods. We hope that this post has been helpful to you and has given you a clear insight into how to create a user in PostgreSQL. Creating a new user is a critical task when working with a database, and it is paramount to do it correctly. A user with inappropriate permissions or attributes can cause security risks and compromise the database’s integrity.

In addition to creating users, you have also understood how to list all users in PostgreSQL. This is an essential task that helps you keep track of who has access to your database as well as their associated permissions. If you have any questions or comments, please feel free to reach out to Hostbillo’s experts who provide prompt support.