In order to connect to a PostgreSQL database, you must have a PostgreSQL user. A user in PostgreSQL has a username and a password to connect to the PostgreSQL server. Additionally, you can grant him various permissions to carry out his work. It is also possible to form a user group. Every member of the group will inherit all permissions. We will discover how to create users in PostgreSQL and give them varying levels of permission in this post.
Table of Contents
Introduction
PostgreSQL has a single concept of role for both database users and groups of users. You need to create a role, whether you are creating a single user or a group of users.
There are mainly two types of roles.
- Login Roles: These roles are used to login to the PostgreSQL server.
- Group Roles: A group role is a group of users that inherit group permissions.
Permissions are defined at the database level, and a role is defined at the server level. A single role can have different permissions on different databases. For example, a single role can have permission to insert data into database 1 but not into database 2.
PostgreSQL Role Commands
There are three main commands to handle roles in PostgreSQL.
- CREATE ROLE
- ALTER ROLE
- DROP ROLE
By default, a super user role “postgres” is created during the installation of the PostgreSQL server. You can check all the roles on a server by using the \du command.
The default role “postgres” has three main permissions.
- superuser: A superuser has all the permissions to do anything on the server.
- Create role: This permission is used to create a role.
- Create DB: The user can create a new database on the PostgreSQL server.
Create Role Command
The CREATE ROLE command is used to create a new user for the PostgreSQL server. Below is the syntax.
CREATE ROLE role_name [WITH OPTIONS]You can provide multiple options separated by spaces. Below are the most commonly used options supported by the CREATE ROLE command.
| Name | Description |
| SUPERUSER | NOSUPERUSER | User is a superuser or not. |
| CREATEDB | NOCREATEDB | user can create new database or not |
| CREATEROLE | NOCREATEROLE | user can create roles or not |
| LOGIN | NOLOGIN | user can login |
| CONNECTION LIMIT number | Simultaneous connections allowed by the user |
| VALID UNTIL ‘timestamp’ | Role expiry date |
| IN ROLE role_name | Used to add new user to group role |
| Password ‘pwd_secure_text’ | Password for new role |
Create Role Example
Below is an example of a new role.
CREATE ROLE kapil WITH LOGIN PASSWORD 'xxx';In the above example, I have created a new role named “kapil” with login and password options. The password option must be provided in single quotes (‘). If you create a role without the LOGIN option, then that user cannot login to the PostgreSQL server.
Valid Until Example
By default, a role is created without an expiration date. If you want to disable a role after a particular time, you can use the VALID UNTIL clause. The valid until clause allows you to create a role with an expiration date. After that expiration date, the role will not work. Below is an example.
CREATE ROLE george WITH LOGIN PASSWORD 'xxx' VALID UNITL '2024-03-31 23:59:59';In the above example, I have created a new role named “george” with a valid until option. VALID UNTIL accepts date time in a timestamp format. The above user can login only up to March 31, 2024.
Check the new roles using the \du command.
Create Role Superuser
To give superuser role to a new user, use the following create role command:.
CREATE ROLE kapil WITH SUPERUSER;
Create Role If Not Exists
In some of the automation scripts, we create new roles. If the role name already exists on the server, then PostgreSQL will give an error. This error can stop the entire script’s execution. To stop that, PostgreSQL provides an if exists clause to check first if a role does not exist, then only create a new role.
We need to use the DO statement to write a small script to check whether user exists in the pg_user table or not. Below is an example.
do
$$
BEGIN
IF EXISTS (SELECT * FROM pg_user WHERE usename='mynewrole') THEN
RAISE NOTICE 'Role already exists';
ELSE
CREATE ROLE mynewrole WITH LOGIN Password 'test@123';
END IF;
END
$$
;
ALTER ROLE
ALTER ROLE is used to change the role permissions of an existing role. Below is the syntax of the ALTER ROLE command.
ALTER ROLE role_name [WITH] option [...]The role name can be anything from below:
- Existing Role Name
- CURRENT_ROLE | CURRENT_USER
- SESSION_USER
The CURRENT_ROLE or CURRENT_USER is used to alter the current login user permissions.
SESSION_USER is used to change the permissions of the current session user.
Alter Role Password Example
Below is an example of changing the current login user password.
ALTER ROLE CURRENT_USER PASSWORD 'newpwd@123';
Alter Role Superuser Example
To make an existing role superuser, use the SUPERUSER option in the ALTER ROLE command.
ALTER ROLE kapil WITH SUPERUSER;
Alter Role CREATEDB Example
The CREATEDB option allows a role/user to create a new database in PostgreSQL. Below is an example of giving CREATEDB permission using the ALTER ROLE command in PostgreSQL.
DROP ROLE
To drop a role, use the DROP ROLE command in PostgreSQL. Below is an example:
DROP ROLE kapil;
Summary
PostgreSQL allows you to create users by using the CREATE ROLE command. You can set various permissions along with the CREATE ROLE command, like superuser, create DB, VALID UNTIL, etc. You can modify an existing role using the ALTER ROLE command and drop an existing role using the DROP ROLE command.
Related Posts: