Review vault role / user creation

[Ardiea]

Description/Context

4:04 PM sar  Yeah think as long as we don't have the grant definitions, then we should be good as those are the statements that are causing the error
4:06 PM tmacey Looking at it more, it seems like the static role definition is for when you have already defined the role and just want to rotate credentials. There's no parameter for defining the creation statements to allow for specifying the grants.
4:21 PM sar Can it be broken down into multiple roles?
4:22 PM tmacey Yeah, we would need to have one role for admins, one for app users, and one for readonly
4:23 PM sar We kinda have that though. I was thinking more in terms of the app role or whatever role that's throwing the error. Can that be broken down further
4:25 PM tmacey So, the issue is that the app user explicitly grants permissions every time it gets created. We need to move those grants to the role definition, and then the user creation permissions are just "grant <role> to <user>;"
4:28 PM sar But if the problem is the overloaded grants, then wouldn't the error be thrown when trying to create that role with all the grant statements
4:32 PM tmacey The problem is that the grants are getting run too many times (I think)
4:32 PM Also, I think the revoke statements aren't all working right, which is leaving lots of excess user objects laying around
4:34 PM Basically, we need to do a thorough and holistic review of our Postgres permissioning. At this point it's mostly a cobbled together set of "I got it to work?" fixes that have accrued over time.
4:34 PM sar I'm not sure about that, cause i think when i ran into this, I tried running a script that would run the grants sequentially and that seemed to have worked, but u can't do that in the vault creation statement
4:34 PM tmacey I think you can.
4:34 PM If you look at the UI, it accepts a list of statements
4:34 PM We just have it all as a single blob because of historical reasons.
4:35 PM I think we can turn our "one big string" into an array
4:35 PM sar Oh great, then maybe that would be the way to solve it. But yeah I agree, we need to look over the entire approach

https://github.com/mitodl/ol-infrastructure/blob/main/src/ol_infrastructure/lib/vault.py#L19-L98

Plan/Design