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

[aws-samples]

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

[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 #57?

[goyalya]

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