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.