Postgres - How to allow user to change their password
Greetings,
On Postgres 13, I'm trying a Stored procedure, which users can use to change their password, as well as extend the due date.
But when the user executes it, it's failing with a "permission denied" error, because of the "Valid until" clause.
Can someone kindly help as to how to get this to work?
Thank you!
ASKER
Thank you!
I have added Security Definer to the code. But now, the check to see if the user id parameter value is the same as the login value, is failing.
How to still validate the login user value and current user are the same?
Attaching the modified code.
I like to add literals around my strings to make sure there are no spaces or anything else, so I added ':' before and after the %s.
RAISE NOTICE 'I_user_id,V_CURR_USER, :%:, :%:', UPPER(QUOTE_LITERAL(I_user_id)) ,UPPER(V_CURR_USER);ASKER
In the Raise notice, for the current_user, I see the masteraccount which is the super user.
But, the I_user_id is the user's individual id and so, they are not matching.
I think, the current_user is showing the superuser because of the Security Definer.
I wanted to know if there's a way to still do that validation and fail only if the i_user_id value is different from the current_user value.
ASKER
Thank you, as usual!
It would be very dangerous but you can set the procedure to run as the owner.
Look at SECURITY DEFINER:
https://www.postgresql.org/docs/current/sql-createfunction.html
The reason I say it is dangerous is you might be open to SQL injection. I say "might be" because I don't have an injection off the top of my head. Just suggesting it might be possible.