PostgreSQL | Role (User) ROLE Creation | Set Role Password Expiration (ALTER ROLE)
For the password used to authenticate a role connected to PostgreSQL, you can set password expiration both when creating the role and after the role has been created by changing its attributes. This page explains how to set password expiration for a role and how to make password authentication available again for a role whose password has been disabled.
Set Password Expiration
To set password expiration when creating a role, run the following.
CREATE ROLE name
WITH LOGIN PASSWORD 'password' VALID UNTIL 'timestamp'
A role created with the LOGIN and PASSWORD 'password' attributes can be used as a connection role for PostgreSQL. By setting VALID UNTIL 'timestamp', you can set the expiration of the password being used to timestamp.
To set only the password expiration attribute for an already created role, run the following.
ALTER ROLE name WITH VALID UNTIL 'timestamp'
This sets the current password expiration to timestamp.
Specify the valid period in a form such as '2020-10-31 20:33:10'. The password is disabled from the specified date and time. If only a date is specified, such as '2020-10-31', it is treated as '2020-10-31 00:00:00'.
–
Now try it in practice. Set the expiration date for the currently created mykuma role to '2020-10-10 06:00:00'.
devkuma=# alter role mykuma with valid until '2020-10-10 06:00:00';
ALTER ROLE
devkuma=#
Password expiration has been set for the mykuma role.
After setting password expiration, use the psql meta-command \du to display attributes of the mykuma role. You can confirm that password expiration is set.
devkuma=# \du mykuma
롤 목록
롤 이름 | 속성 | 소속 그룹:
---------+-------------------------------------------+------------
mykuma | 비밀번호 만료기한: 2020-10-10 06:00:00+09 | {}
devkuma=#
If a role with password expiration set is already connected to PostgreSQL before the password expires, that connection can continue to be used even after the expiration date and time. However, after disconnecting, password authentication fails.
If you try to connect while the password is disabled, “psql: 오류: 서버 접속 실패: 치명적오류: 사용자 “mykuma"의 password 인증을 실패했습니다” is displayed and the connection fails.
C:\Users\kimkc>psql -U mykuma -d postgres
mykuma 사용자의 암호:
psql: 오류: 서버 접속 실패: 치명적오류: 사용자 "mykuma"의 password 인증을 실패했습니다
C:\Users\kimkc>
Cancel Password Expiration
If a password has expired and been disabled, setting a new password alone is not enough for authentication to succeed. To allow the role to authenticate with a password again, cancel the password expiration setting by using the ALTER ROLE command.
Now try it in practice. To cancel the password expiration setting for the mykuma role, whose password currently cannot be used, either specify a date and time later than the current time again, or set 'infinity', which means no expiration.
postgres=# alter role mykuma with valid until 'infinity';
ALTER ROLE
postgres=#
The password expiration setting for the mykuma role has been canceled. More precisely, the password expiration has been set to no expiration.
Use the psql meta-command \du again to display attributes of the mykuma role. You can confirm that password expiration is set to infinity.
postgres=# \du mykuma
롤 목록
롤 이름 | 속성 | 소속 그룹:
---------+-----------------------------+------------
mykuma | 비밀번호 만료기한: infinity | {}
postgres=#
After changing the valid period setting, connect to PostgreSQL with that role. Password authentication succeeds and the connection can be established without a problem.
C:\Users\kimkc>psql -U mykuma -d postgres
mykuma 사용자의 암호:
psql (12.2)
도움말을 보려면 "help"를 입력하십시오.
postgres=>
–
This page explained how to set password expiration for a role and how to configure a role with a disabled password so that password authentication can be used again.