You may need to create superuser in PostgreSQL for database management or website administration. In this article, we will look at how to create user with superuser privileges in PostgreSQL.
How To Create User With Superuser Privileges in PostgreSQL
Here are the steps to create user with superuser privileges in PostgreSQL. There are two different ways to create superuser, depending on your PostgreSQL version
PostgreSQL 8.1 and above
PostgreSQL 8.1 onwards uses the concept of ROLE instead of a USER. A role can be a user or a group depending on your configuration. So we will create a ROLE with superuser privileges.
Log into PostgreSQL and run the following command to create a new role with superuser privileges. Replace test_role as per your requirement.
CREATE ROLE test_role LOGIN SUPERUSER;A database role has all permissions, except the permission to login. So you need to grant it to the new role.
Also read : How to copy data from one table to another
If you also want to assign a log in password, modify the above command to the following. Replace test_password with your choice of password.
CREATE ROLE rolename LOGIN SUPERUSER PASSWORD 'test_password';You can also create a non-super user ROLE and then make it superuser as shown below. Replace test_role with your choice of role.
CREATE ROLE test_role LOGIN;ALTER ROLE test_role WITH SUPERUSER;
Here is the command to change superuser into a regular role.
ALTER ROLE test_role WITH NOSUPERUSER;Also read : How to list all users in PostgreSQL
PostgreSQL 8.1 and below
Here is the command to create user with superuser privileges as per the PostgreSQL version lower than 8.1. Replace test_user as your requirement.
CREATE USER test_user SUPERUSER;If you want you superuser to have a login password, modify the above command as shown below.
CREATE USER username SUPERUSER WITH PASSWORD 'test_password';That’s it. As you can see it is quite easy to create a superuser in PostgreSQL.
Need a good reporting tool for PostgreSQL? Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.
