PostgreSQL: upgrade a user to be a superuser?


In this article, you will learn how to upgrade an existing user to superuser, the condition is that you cannot delete the existing user.

PostgreSQL gives the facility to upgrade the existing user, we can use the following statement to upgrade the existing user to superuser.

Syntax:

ALTER USER <UserName> WITH SUPERUSER;


How to upgrade a user to superuser

For example: Suppose we have a user "admin" which doesn't have the permission as a superuser, and I want to upgrade this user as like superuser. See the following statement.


ALTER USER admin WITH SUPERUSER;


Execute the above query it will return the following result successfully.


ALTER ROLE

Query returned successfully in 185 msec.


Run the following command, and you can see all privileges and other details of your users.


postgres-# \du admin


Result:


Here you can see the list of roles attributes, now admin is upgraded to a superuser.

You can see here it is superuser but don't have much more privileges, I want to give the "CreateDB" permission to user "admin". Now, what you will do. See the following statement.

Give the privileges to the user.


ALTER USER admin WITH SUPERUSER CREATEDB;


After execute the above query will run the following command and see the result. You can see the Create DB permission is added now.


postgres-# \du admin;


Result:

Like the above query, if you want to remove the privileges of CreateDB, you should run the following query.


ALTER USER admin WITH SUPERUSER NOCREATEDB;


Note: NOCREATEDB is using to remove the permission to CreateDB.
To see the privileges of user 'admin' run the following command and you can see CreateDB permission for the use 'admin' has been removed.


postgres-# \du admin;


Result:


See the following syntax where you can understand what type permissions are available in Postgres and how to add and remove the privileges for a user.


ALTER USER role_specification [ WITH ] option [ ... ]
where option can be:
      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    | VALID UNTIL 'timestamp'


  

No comments:

Post a Comment

Please do not enter any spam link in the comment box.

NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...