CODEWITHSUNDEEP
sqldatabasepostgresqlaltersuperuser
avi
avi

Reputation: 1886

PostgreSQL - How to change superuser to nosuperuser?

I have a role:

CREATE ROLE x LOGIN
  ENCRYPTED PASSWORD '....'
  SUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

This role is already created.

I want to modify it to:

  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

Change the SUPERUSER to NOSUPERUSER ;

Is it possible to do it without doing Drop role ?

Upvotes: 9

Views: 6756

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51629

https://www.postgresql.org/docs/current/static/sql-alterrole.html

ALTER ROLE role_specification [ WITH ] option [ ... ]

where option can be:

  SUPERUSER | NOSUPERUSER

t=# create user su superuser;
CREATE ROLE
t=# \du+ su
                  List of roles
 Role name | Attributes | Member of | Description
-----------+------------+-----------+-------------
 su        | Superuser  | {}        |

t=# alter user su nosuperuser;
ALTER ROLE
t=# \du+ su
                  List of roles
 Role name | Attributes | Member of | Description
-----------+------------+-----------+-------------
 su        |            | {}        |

or with optional WITH:

t=# alter user su with nosuperuser;
ALTER ROLE

Upvotes: 11

Related Questions