Postgres create user with expired password

Question

How to create user if i want to force user to change password for the first time login to the database?

When i using code like

CREATE USER myUser WITH PASSWORD 'mypass' VALID UNTIL current_date

ERROR:  syntax error at or near "current_date"
LINE 1: ...REATE USER myUser WITH PASSWORD 'mypass' VALID UNTIL current_da...

The value for VALID UNTIL can't be an expression, it must be a constant (e.g. valid until '2021-10-26'. But that won't "force the user to change the password" - it will simply reject any login attempt by the user if the account is expired — user330315
– user330315, Commented Oct 26, 2021 at 8:08

jjanes · Accepted Answer · 2021-10-26 16:07:28Z

2

To do that, PostgreSQL would have to implement some restricted login capability which lets a person log in with an expired password, but then not let them do anything other than change the password. But it doesn't implement that. Once a password is expired, you need to get the superuser to reset it, or maybe login with some passwordless method (like locally from the database machine if configured with peer or trust) to reset it.

If you want advanced features like self-serve password resets, you should use a dedicated system like kerberos, Active Directory, etc. and hook PostgreSQL up to use them.

answered Oct 26, 2021 at 16:07

jjanes

45k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

We'll See Over a year ago

Thanks for your answer! It's a pity because you can do that in MySQL and I find it very handy: ALTER USER 'we_llsee'@'%' PASSWORD EXPIRE;

Collectives™ on Stack Overflow

Postgres create user with expired password

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related