How to create a superuser in PostgreSQL?

This guide will walk you through the steps to create a new superuser in PostgreSQL.

1. For the PostgreSQL versions 8.1 and newer

From the release 8.1, PostgreSQL uses the concept of roles to manage database access permissions.

  • A role can be a user or a group, depending on how you set up the role.
  • A user is a role with login privilege.
  • A role may be a member of other roles, which are known as groups.

So apart from the old way of creating a user, you can create a role instead.

Create a new role with superuser privilege

Note that you have to log in with a superuser, which has the CREATE ROLE privilege.

CREATE ROLE rolename LOGIN SUPERUSER;

CREATE USER is equivalent to CREATE ROLE except that CREATE USER assumes LOGIN by default, while CREATE ROLE does not.

A database superuser bypasses all permission checks, except the right to log in. A role is considered a database user when it has LOGIN privilege, so you have to grant the LOGIN privilege to the new role.

If you need a password for the new role:

CREATE ROLE rolename LOGIN SUPERUSER PASSWORD 'passwordstring';

Apart from the SUPERUSER and LOGIN, a role in PostgreSQL can have several different privileges:

  • CREATEDB / NOCREATEDB: The ability to create databases.
  • CREATEROLE / NOCREATEROLE: The ability to create roles.
  • CREATEUSER / NOCREATEUSER: An old spelling of SUPERUSER / NOSUPERUSER, removed in PostgreSQL 9.6.
  • INHERIT / NOINHERIT: The ability to inherit the privileges of roles it is a member of.

You can create a role then make it a superuser later

To create a new role:

CREATE ROLE rolename LOGIN;

Then make the new role a superuser:

ALTER ROLE rolename WITH SUPERUSER;

When you want to revoke the superuser status:

ALTER ROLE rolename WITH NOSUPERUSER;

Or you can drop that role:

DROP ROLE role_name;

To list out all existing roles and detect those with superuser privileges

SELECT rolname, rolsuper FROM pg_roles;

2. For the PostgreSQL versions prior to 8.1

To create a superuser:

CREATE USER username CREATEUSER;

If you need to specify the password:

CREATE USER username WITH CREATEUSER PASSWORD 'passwordstring';

*Need a good GUI tool for PostgreSQL? TablePlus is a modern, native tool with an elegant GUI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server, Redis and more.
*

Download TablePlus here. It's free anyway!

TablePlus GUI Tool PostgreSQL

← All posts