1

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

0

1 Answer 1

2

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.

2
  • thanks for the explanation. Just verified by amending a user that had objects they owned and it did indeed change to the renamed user. No way of getting round the "MD5 password cleared because of role rename"" is there? Meaning would have to reset the users password anyway?
    – rdbmsNoob
    Commented Jun 25, 2021 at 13:43
  • 1
    "Amend" is a strange choice of word for that. Anyway, the password needs to be cleared, because it would become invalid after the rename, as the user name is part of the hash that is stored in the database. Commented Jun 25, 2021 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.