How to Create User in PostgreSQL

PostgreSQL is a popular database used widely by businesses, websites & apps. Often PostgreSQL database administrators need to create & manage users. It is very important to understand what a PostgreSQL user is, what it can and cannot do, and how it is different from PostgreSQL role. This knowledge allows database admins to easily manage users, control user access and keep their data secure. In this article, we will learn how to create a PostgreSQL user.

What is a PostgreSQL User

A PostgreSQL user is a database account with a username and password, that can connect to the database. It is the basis for access control and data security. It is primarily used to authenticate and authorize a user to access one or more databases.

There are mainly 2 types of users in PostgreSQL – regular users and superuser. A superuser is an account with a lot more privileges and access compared to a regular user. A database administrator is a superuser. A regular user can be assigned various permissions to select, insert, modify, remove data. This is mostly done by a superuser.

But PostgreSQL also supports Roles, which can be confusing for many database developers. So let us look at the difference between PostgreSQL user and role.

PostgreSQL User vs Role

Before we look at steps to create new user, it is very important to understand the difference between user and role in PostgreSQL.

A PostgreSQL user is an entity that can log into PostgreSQL. Depending on the privileges granted to it, the user can perform various tasks such as access database, select rows, insert data and more.

A PostgreSQL role is a group of such user privileges. When a user is assigned a role then it gets all the privileges present in the role. It is useful to easily manage privileges of multiple users.

Please note, a user need not have a role, and a role cannot log into PostgreSQL.

How to Create User in PostgreSQL

Here are the steps to create PostgreSQL user. You can use PostgreSQL console or GUI tools like PgAdmin to create new users.

1. Log into PostgreSQL

First of all, log into PostgreSQL prompt as a superuser. Only a superuser has the privilege to create new users.

sudo -u postgres psql

2. Create New User

Run CREATE USER command as shown to create new user. Replace <username> and <password> with the username & password of your choice.

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

The above command will create a vanilla user without any special privileges.

Let us look at some specific use cases for user creation.

Sometimes database administrators need to create a superuser. Here is the command to create new user with superuser rights.

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

If you want to create a user with the privilege to create databases, then use CREATEDB command in your query as shown.

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

If you want to create a user with limited number of simultaneous connections, use this query. In this example, a user can only have 10 concurrent connections.

CREATE USER <username> WITH CONNECTION LIMIT 10 PASSWORD '<password>';

3. Grant Privileges

By default, a new user can only log into PostgreSQL and not do much else. It cannot even access a database. So, depending on your requirement, you need to assign privileges to this new user.

Let us look at some common use cases. Here is an example to grant all privileges to a database for new user. Replace <database_name> and <username> with the names of database and user respectively.

GRANT ALL PRIVILEGES ON DATABASE <database_name> to <username>;

Often database administrators need to allow new user to use public schema, the default schema to store databases. You can do this with the following query.

GRANT USAGE on SCHEMA public TO <username>;

Here is the query to grant read only privileges to new user. It is commonly required in business intelligence and reporting systems.

GRANT SELECT ON ALL TABLES IN SCHEMA public to <username>;

If you want to grant other privileges such as INSERT, UPDATE, etc. then add them in above query as shown.

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public to <username>;

4. Verify User

Once you have created the new user, you can verify it with \du command which lists all users in your system, along with their privileges.

\du

Best Practices

1. Strong Username & Password – These days there are so many bots and tools to guess passwords, not to mention data leaks. So it is best to use strong username and passwords that contain a combination of small and upper case letters, numbers and at least 1 special character. You can always change user passwords even after they are created.
2. Regularly Review User Permissions – You should only give as much user privilege as is required for a user. Don’t give extra permissions unnecessarily. They may be exploited. These permissions must be reviewed regularly to ensure they are in line with the role changes in your database.
3. Monitor User activity – Use tools like pg_stat_statements and pgAudit to periodically monitor user activity. Check if any user is running suspicious queries. Prevention is better than cure.

Conclusion

Proper user management is essential to manage databases. In this article, we have learnt how to create new PostgreSQL user. We learnt how a user is different from role, and also some of the best practices in user management. Using strong user credentials, providing user access only when needed and regularly reviewing user activity and permissions, will help database administrators manage users more effectively.

Also read:
How to List All Users in PostgreSQL
How to Alter Column from NULL to NOT NULL
How to Prevent SQL Injection Attack