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 role 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 an 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: The ability to create users.
  • INHERIT / NOINHERIT: The ability to inherits 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 verions 8.1 and older

To create a superuser:

CREATE USER username SUPERUSER;

If you need to specify the password:

CREATE USER username SUPERUSER WITH 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