Alter user in Postgres and change the ownership of the user to amended user

All we need is an easy explanation of the problem, so here it is.

I am doing an audit and amending usernames in our postgres database (9.6) environment.

I can amend the user to a new username via the ALTER USER name RENAME TO new_name command however I want to also amend the ownership of the user on any objects they may have to the new renamed user.

If I rename the user I am then unable to make the ownership change as the old username would no longer exist.

Is there a way round this? or would it simply be easier to create a brand new username and move the ownership to the new username and then delete the old username.

Thanks

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The name of a user is just a string attribute in a system column, the true identity is the user’s object ID number, which doesn’t change.

So if user x owns a table, and you rename x to y, then it is still the same user, and it will still own the table. After the name change, the table will be owned by y.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply