PostgreSQL | Role (User) ROLE Creation | Change Role Attributes and Name (ALTER ROLE)

Tags:

• PostgreSQL
• Database

You can change attributes of a created role by using the ALTER ROLE command. Role attributes include whether the role is a superuser and whether it has database creation and role creation privileges. This page explains how to change role attributes.

Change Role Attributes

To change role attributes, use the ALTER ROLE command. The syntax is as follows.

ALTER ROLE role_name [ WITH ] option [ ... ]

option:
      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'

This changes the attributes of the target role (role_name). Set values for the attributes you want to change. Attributes that are not set remain unchanged. Instead of specifying a role name, you can also specify CURRENT_USER, which represents the current user, or SESSION_USER, which represents the current session user.

Items that can be set with the CREATE ROLE command when creating a role can be changed with the ALTER ROLE command. However, settings used when treating roles as groups must be changed with separate commands.

A superuser can change attributes of all roles. A role with CREATEROLE privilege can change attributes of roles other than superusers and replication roles. Other roles can change their own passwords.

–

Now try it in practice. Connect to PostgreSQL as the superuser postgres and change attributes of the created superkuma role. The current attributes of superkuma are as follows.

devkuma=# \du superkuma
                    롤 목록
  롤 이름  |         속성         | 소속 그룹:
-----------+----------------------+------------
 superkuma | 롤 만들기, DB 만들기 | {}


devkuma=#

The CREATEDB and CREATEROLE privileges are set.

Now remove the CREATEROLE privilege and set CONNECTION LIMIT to 3. Run the following.

devkuma=# alter role superkuma with nocreaterole connection limit 3;
ALTER ROLE
devkuma=#

The attributes of the superkuma role have been changed. Check the attributes of superkuma again.

devkuma=# \du superkuma
              롤 목록
  롤 이름  |   속성    | 소속 그룹:
-----------+-----------+------------
 superkuma | DB 만들기+| {}
           | 3개 연결  |


devkuma=#

You can confirm that the CREATEROLE privilege has been removed and the maximum number of concurrent connections has been set to 3.

Let a Regular Role Change Its Own Connection Password

Next, try changing the password of a regular role that is not a superuser and does not have the CREATEROLE privilege.

The role whose password will be changed is kuma. From the command prompt, connect to the database as kuma. The connection database is postgres.

C:\Users\kimkc>psql -U kuma -d postgres
kuma 사용자의 암호:
psql (12.2)
도움말을 보려면 "help"를 입력하십시오.

postgres=>

Use the ALTER ROLE command to change the password. This time, instead of specifying the role name, use SESSION_USER, which represents the role used to start the current connection. Run the following.

postgres=> alter role session_user with password 'mybear';
ALTER ROLE
postgres=>

The connection password for kuma, the role used when the current connection was started, has been changed to the new value.

Attempt to Change Attributes Without Permission

Changing attributes other than the password can only be done by a superuser or a role with the CREATEROLE privilege. Check what happens when a role tries to change attributes without permission.

Connect to the database as the regular role kuma. The connection database is postgres.

C:\Users\kimkc>psql -U kuma -d postgres
kuma 사용자의 암호:
psql (12.2)
도움말을 보려면 "help"를 입력하십시오.

postgres=>

While connected as kuma, try changing attributes of the kuma role. Run the following.

postgres=> alter role kuma with createrole;
오류:  권한 없음
postgres=>

The attempt displays “오류: 권한 없음”. As shown here, changing role attributes requires a role with the necessary privileges.

–

This page explained how to change attributes of a created role by using the ALTER ROLE command.