Hello! Today we are going to talk about how to create admin user in PostgreSQL.

In most cases, i don’t like to use postgres user as administrator. Because the version you use might have a security vulnerability and postgres user is the first user attackers going to scan.

As addition, you can disable ssh conncetion to postgres user and root user to increase level of security. Securing a PostgreSQL database is topic of another article.

How to Create Admin User in PostgreSQL

As i described in my existing article how to create roles in PostgreSQL are considered as roles, users and groups. You can also take a look to PostgreSQL documentation about roles.

Let’s take a look to how to create admin user in PostgreSQL.

What is a Superuser

A superuser’s permissions are not checked. It is a real superuser, no security control can stop it.

Creating a superuser is risky, superusers can not be stopped by any security control and it can manipulate all settings and data in the database.

Only a superuser can create another superuser.

How to Create a Superuser

We can create a superuser very easy:

CREATE ROLE SUPERHUMAN SUPERUSER LOGIN;

A superuser is the super admin user of PostgreSQL.

You can also give it a password to login:

ALTER ROLE SUPERHUMAN PASSWORD '123456';

When you login to psql using SUPERHUMAN you can do anything:

postgres@a5dd0e71635a:~$ psql -U superhuman -d postgres
psql (16.1 (Debian 16.1-1.pgdg120+1))
Type "help" for help.

postgres=# CREATE DATABASE SUPERDB;
CREATE DATABASE
postgres=# DROP DATABASE SUPERDB;
DROP DATABASE

As you can see superhuman can create a database and drop a database.

Notes

More users is more headache.

Powerful users are much more.

Consider having less users with granular permissions appropriate to development requirements.

Do not grant unnecessary people to manage configuration, replication, data, structure etc.

To Sum Up

In this article i covered creating an admin user in postgresql topic.

Feel free to share your thoughts and ask any questions in the comments section below. Your engagement and questions are highly valued, and I look forward to connecting with you and providing insightful responses. Let’s keep the conversation going!

Hi! I’m an IT Specialist

I want to hear from you! I am Working with enterprises for 10+ years to improve their infrastructure and efficiency.

Get in touch with me.

Get in touch