Renaming the database and service account in postgresql database

Renaming a Database

When you rename a database, the objects within the database retain their existing privileges, but any external references to the database by name will need to be updated. The ALTER DATABASE command can be used to rename the database:

ALTER DATABASE old_database_name RENAME TO new_database_name;

Renaming a Service Account

Renaming a service account (role/user) will not affect the privileges assigned to that role. However, any scripts, applications, or configurations that reference the old name will need to be updated. The ALTER ROLE command can be used to rename the service account:

ALTER ROLE old_role_name RENAME TO new_role_name;

Steps to Rename and Verify Privileges

1, Rename the Database:

ALTER DATABASE old_database_name RENAME TO new_database_name;

2, Rename the Service Account:

ALTER ROLE old_role_name RENAME TO new_role_name;

3, Verify Privileges:

After renaming the role, check the privileges to ensure they are intact.

\du+ new_role_name

Check the privileges on the database and its objects to ensure they are still properly assigned.

\l+ new_database_name

Important Considerations

References in Configurations and Scripts:

Any references to the old database name or old role name in application configurations, connection strings, or scripts will need to be updated.

Database Connections:

Ensure that all connections are updated to use the new database name and the new role name.

Backup and Testing:

It is always a good practice to take a backup of the database before making such changes.

Test the changes in a staging / validation environment if possible.

Summary

Renaming a database or a service account in PostgreSQL does not inherently affect the privileges, but it requires updates to references and connection strings. The steps to rename and verify the integrity of privileges are straightforward, but careful planning and validation are recommended.