Role Ownership Issues in the RDS PostgreSQL Multi-User Rotation Lambda

[jweilhammer]

Hello,

My team has been working to implement our slightly custom RDS password rotation lambda based on the multi user lambda here: https://github.com/aws-samples/aws-secrets-manager-rotation-lambdas/blob/master/SecretsManagerRDSPostgreSQLRotationMultiUser/lambda_function.py

We are granting the master user role permissions to our secondary users, and also in between the users (as is done in this repo) and have run into several issues around object ownership (completely breaking) when actually using them with several micro-services.

As I understand this function, it is expecting the multi-user role permissions to look something like this:

master {perms to create objects}
user1 {}
user2 {member of user1}

I think that there is a major flaw in the rotation function as it is setup now. And the issue goes like this:

Assuming user2 is a member of the user1 Role:

1. The service is connecting with user2
2. User2 is used to create a new schema, table, or any object.
3. That new table is now owned by the user2 role
4. When the secrets rotate and the service connects with user1, it cannot access the object created by user2. It has permission denied errors because that Table/object is owned solely by user2
5. Service is now in a breaking state because of the password rotation

We've implemented several fixes around this where we reassign ownership during rotation back to the master user so it can be accessed by both user1 and user2 since they are members of the master role. (I can make a PR to this repo if this is indeed an issue), but this case seems so simple that I'm surprised no-one else is running into issues and wanted to confirm here as well.

Can someone confirm that this is an issue? It basically makes the rotation function unusable as is without heavy customization and/or custom SQL event triggers to reassign ownership. Is there an easy way to fix this, or something that we're missing?

[jweilhammer]

Here are the full replication steps with an RDS PostgreSQL DB and psql:

1. Logged in as master with psql:

-- Create two users for rotations 
CREATE ROLE user1 WITH PASSWORD 'password' LOGIN;
CREATE ROLE user2 WITH PASSWORD 'password' LOGIN;

-- Allow these to create objects in the DB (ours is already setup with master)
GRANT master TO user1;

-- Grant role membership like lambda does
GRANT user1 TO user2;

2. Logged in as user2:

-- Create schema and table as user2
CREATE SCHEMA test_schema;
CREATE TABLE test_schema.test_table ( column1 TEXT );
SELECT * FROM test_schema.test_table;
/****************
 column1 
---------
(0 rows)
****************/


-- View schemas (notice owner)
\dn+

/****************
                                                    List of schemas
                         Name                         |   Owner    |    Access privileges     |      Description       
------------------------------------------------------+------------+--------------------------+------------------------
 public                                               | master     | master=UC/master        +| standard public schema
                                                      |            | =UC/master               | 
 test_schema                                          | user2      |                          | 
(2 rows)
****************/


-- View tables (notice owner)
\dt *.*

/****************
                                          List of relations
                        Schema                        |          Name           | Type  |   Owner    
------------------------------------------------------+-------------------------+-------+------------
 test_schema                                          | test_table              | table | user2
****************/

3. Logged in as user1 (simulating rotation):

SELECT * FROM test_schema.test_table;
/****************
ERROR:  permission denied for schema test_schema
LINE 1: SELECT * FROM test_schema.test_table;
****************/

[whygoyal]

Thank you for opening this issue - we are looking into it.

[jweilhammer]

Sorry, I had talked this out with AWS support a while ago.

The issue stems from the two users not being read-only. If they have write permissions, the rotation lambda runs into this issue

This issue can likely be closed out, and maybe the rotation documentation updated to specify the users are supposed to be read-only

[nidazehra1108]

We have been facing this exact same issue with our service users(who are not read-only) and have opened up a case with AWS support as well. They mentioned adding it as a feature request. For now, as a workaround, we are forcefully setting the role of the current user on application startup. Any chance of reopening this PR that was closed https://github.com/aws-samples/aws-secrets-manager-rotation-lambdas/pull/57?

[goyalya]

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