Add Features for renaming users in the postgresql_user module

[azdfzshffg]

SUMMARY
Currently, the community.postgresql.postgresql_user module does not support renaming a user. This feature would allow renaming a PostgreSQL user directly within the module instead of requiring a separate SQL query.

ISSUE TYPE
Feature Idea
COMPONENT NAME
community.postgresql.postgresql_user

ADDITIONAL INFORMATION
This feature would simplify user management by allowing a direct rename operation without needing to execute a raw SQL query (ALTER ROLE old_name RENAME TO new_name;).
It would be useful for maintaining consistency in PostgreSQL role management, especially in automated deployments.

Example of expected usage:

- name: Rename PostgreSQL user
  community.postgresql.postgresql_user:
    name: "old_user"
    new_name: "new_user"
    login_db: "postgres"
    login_user: "admin"
    login_password: "admin_password"
    login_port: 5432

This would improve usability and reduce the need for raw SQL queries in playbooks.😊

Thanks

[hunleyd]

@azdfzshffg thanks for the suggestion! is this something you'd like to tackle?

[azdfzshffg]

I don’t have the dev skills to contribute directly 😞, so I’m hoping someone in the community can take this on. It’d be a great improvement for managing PostgreSQL users with Ansible! 🙏

[andreasscherbaum]

Imho this does not fit into how automation is used.

Where does the information about renaming a role from one name into another name come from. And what happens upon re-running the playbook - is the original role created every time, then renamed? What if the new role already exists?

Either your state demands that a role exists, or that it does not exist.

[gspanos]

I also tried formulating a safe/idempotent scenario in my head but ran into many problems. This seems like an ad-hoc/manual operation that once has taken effect then you'll probably need to revise your relevant playbooks (to account for the user renaming and any old references to it)

[azdfzshffg]

Hi, and thanks a lot for your feedback! 😊

I’m having a bit of trouble understanding the concerns here.

From what I see, this "rename" logic is already handled in other modules, like postgresql_db, where it's explained here:

Ansible Documentation - postgresql_db module

rename is used to rename the database name to target.  

If the database name exists, it will be renamed to target.  

If the database name does not exist and the target database exists, the module will report that nothing has changed.  

If both databases exist as well as when they have the same value, an error will be raised.  

Wouldn't it make sense to apply the same logic here?

It also feels like there's a missing feature compared to postgresql_db. What do you think? 😊

[andreasscherbaum]

I'd rather say that having this in the postgresql_dbmodule is also not good. Same concerns as laid our here apply to the other module as well.

[gspanos]

@azdfzshffg that was my first reaction when I thought of tackling this feature, but as @andreasscherbaum brought up, I think this is a "footgun" :D

Playbooks should be safe/idempotent and they don't play well with ad-hoc/once-off operations like these. I'd argue also that it doesn't make sense for postgresql_db as well. By the time it will take you to write a playbook with a task like that you could have run this ALTER ... RENAME TO operation in a fraction of the time. But if you cannot replay that playbook... why writing it in the first place?

[Andersson007]

@azdfzshffg @andreasscherbaum @gspanos , great discussion here, thanks!

Those rename things were added w/o good discussion - there was almost no active community around the collection at that time. I even added this rename thing myself to a couple of modules, which was imo a mistake because of the idempotency issue the folks mentioned.
Moreover, renaming is achievable with only two safe tasks:

1. postgresql_info (or postgresql_query) - get the user, register a variable
2. postgresql_query - if the user is present based on the variable ^, run alter ... rename

Or if you're sure it exists, just run postgresql_query
What do you think?

[andreasscherbaum]

Don't forget to start a transaction, keep it open and also write lock the tables. Otherwise another CI pipeline might attempt to do the same task in parallel.

[Andersson007]

Closing the issue then (though if anybody disagree, feel free to raise your concerns).
@azdfzshffg thanks for bringing this up! I think we should go further and deprecate this rename feature from other modules. I'll raise an issue.
@azdfzshffg @andreasscherbaum @gspanos @hunleyd thanks for your input!