sqldatabasepostgresqlaltersuperuser

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 ?

Solution

  • 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