Fix https://forums.aws.amazon.com/thread.jspa?threadID=322708

[thekevinbrown]

Issue #, if available: #38

Description of changes: Preserves grants on each rotation.

By submitting this pull request, I confirm that you can use, modify, copy, and redistribute this contribution, under the terms of your choice.

[followben]

@madsid - this would seem sensible. Do you mind reviewing and/ or merging?

[FloWi]

Hi! Thanks for looking into this issue. We got this error when we tried to run your suggested solution. I don't know enough about Postgres to evaluate how properly fix it, but just wanted to let you know.

ERROR:  role "username_clone" is a member of role "username"
SQL state: 0LP01

[FloWi]

Ok, I dug a little deeper and found a possible solution. So, in case the _clone user is active (and it potentially created object in the database) you could execute this command to reassign ownership of all of _clone's object to the normal user

REASSIGN OWNED BY username_clone TO username

We had to execute this statement as the _clone user (since it is the owner of the objects). I don't know why the master user was not allowed to execute that statement. We got this exception

ERROR:  permission denied to reassign objects
SQL state: 42501

[FloWi]

I created another PR that could solve our problem. Would you mind checking out if this works for you? I haven't tested the whole solution yet (only executed the sql-statement on our rds instance).

[goyalya]

We have addressed this issue with updating the documentation on when to use single or Multi User rotation strategy.

[thekevinbrown]

@goyalya, did you address the actual issue, which is that while using the multi user strategy the following can happen?

1. Initial user is created
2. Rotation occurs, second user is created and used
3. Privileges are granted to the second user by the application
4. Rotation occurs

Expected: Privileges are copied from the second user when rotating Actual: Operations fail because any new privileges granted during after the creation of the second user are not copied during rotation.

[followben]

Holy smokes this still isn't fixed? (says someone currently implementing multi-user rotation via the CDK at another site). Where's my reopen button?

Hello @thekevinbrown btw :)

[goyalya]

@thekevinbrown : The issue is in the miss-application of multi-user rotation. In the case of a fixed application, table creation and permissions should be managed by the application. That application needs to take into account muti-user rotation and if additional permissions are granted to the regular user, they need to be granted to the clone user in which case the application should manage the permissions.

In the case of add-hoc/interactive users, single user rotation should be used (this was the documentation change). These users do not need a high availability rotation strategy because sessions are not terminated during password rotation.

[thekevinbrown]

It seems like it'd be pretty easy to copy permissions when a rotation happens so that the application doesn't have to depend on the (undocumented as far as I know) pattern you use for the cloned username and understand the implementation details of multi-user rotation.

Is there a reason this isn't a good idea and it's better to make the application developer understand all of this?

[joebaro]

The multi-user rotation strategy _clone user is documented. The developer is going to have to understand the rotation strategy choices based on their application needs and design. It may be enough for some applications to just use a connection pool manager with the Secrets Manager JDBC wrapper and the single user rotation strategy, or it may not be sufficient depending on the application.

This is one of many permission problems an application can introduce and trying to manage permission changes as part of rotation is taking the wrong path.

[thekevinbrown]

Ok, in our application when we grant permissions in migrations we grant them to both user and user_clone, so if that's the recommended approach, then I agree, we're done here.

Thanks for the info!