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
    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. Jun 25, 2021 at 15:02

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .