PostgreSQL – Create superusers If You Don’t Have Them

During the initial phase of our application development, we need superusers for our database, especially when we are starting with greenfield projects and no prior databases exist. Most of the time, we use this type of user to run DDL statements against our database. We may use it as part of some migration tool operation or for manual updates. However, it is not good to run an application using superuser credentials, even during development. We should have separate database users for applications and migration tools instead. This post shows how to create superusers in PostgreSQL.

PostgreSQL Default Superuser

Depending on a given installation of PostgreSQL, the default superuser may not be readily available, but it is called the postgres user. For this post, we are using PostgreSQL 10.5. Before we proceed to create superusers in PostgreSQL, let us briefly talk about users and roles.

PostgreSQL Users are Roles are Users

PostgreSQL represents users as roles. Some roles can log in, called login roles, and others contain other roles.  Meanwhile, there are roles that both can log in and hold different roles. For this post, we’ll just briefly touch on creating login roles and superuser roles in PostgreSQL.

PostgreSQL Create Users and Groups

In the past, PostgreSQL uses the following terms – user and groups. However, in recent versions, those are no longer in use. For backward compatibility, CREATE USER and CREATE GROUP still work in the current version, but shun them and use CREATE ROLE instead.

Create Login Roles

To create login roles, follow the example below.

The VALID line is optional and specifies when the role should expire and lose its privileges; the default is infinity, which means the role never expires. Moreover, the CREATEDB modifier grants database creation rights to the new role.

Create PostgreSQL Superuser Roles

First and foremost, we can create another superuser role if the current role (account or user) is also a superuser, e.g., the initial postgres role.

For more information, please visit https://www.postgresql.org/docs/10/static/sql-createrole.html.