Optionally use pg_roles instead of pg_authid.

[lsowen]

pg_roles is a "public" version of pg_authid, which allows use in more restricted environments (such as RDS). The downside is that password comparison doesn't work, as pg_roles only returns a dummy ******** password, so passwords update logic will be fired for each account on every run.

Added --alternate-attributes-table to activate the use of pg_role, though the default is --no-alternate-attributes-table so that the existing behavior of using pg_authid is preserved

[coveralls]

Pull Request Test Coverage Report for Build 181

• 0 of 0 changed or added relevant lines in 0 files are covered.
• No unchanged relevant lines lost coverage.
• Overall coverage decreased (-0.3%) to 96.944%

---

Totals
Change from base Build 179:	-0.3%
Covered Lines:	1269
Relevant Lines:	1309

---

💛 - Coveralls

[lsowen]

This allows the use of pgbedrock in evironments where the "superuser" account is more limited than would typically be expected (eg AWS RDS, Google Cloud SQL).

Partially addresses #12.

[zcmarine]

Hey @lsowen, is there anything in version() or elsewhere in Postgres that would indicate you're on RDS? Rather than adding a new keyword and lots of changes, I'd love to simply modify this line once PR #40 is merged.

[lsowen]

@zcmarine at least version() doesn't return anything unique: PostgreSQL 10.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit There is a rds_superuser that is likely fairly unique to RDS instances (similar to https://github.com/Squarespace/pgbedrock/pull/40/files#diff-4b27be23591885e3c2ee92ac1d152e3bR212 in #40)

One downside of the "automatic" handling of #40 is that it will (possibly unexpectedly) operate differently in different environments (eg one way in a local dev database with full privileges and a different way in prod on RDS/Google Cloud SQL).

[tkh]

Hey guys; any chance of this one coming back to life? I thought I had solved my pg role woes when I found this tonight only to smack straight into this issue.

[SamEBae]

^^ was wondering the same as @tkh I'm using AWS RDS. this tool looks perfect but needs this PR to be merged

[elliotwestlake]

Any chance of bringing this back from the dead? Could really use this!

[idris]

@jfhbrook any chance of this happening? A lot of RDS folks (including me!) looking to use pgbedrock

[JessieAMorris]

RDS does have the rdsadmin user. @zcmarine @zmarine We could do a query like:

SELECT count(*) > 0 AS in_rds FROM pg_catalog.pg_user WHERE usename = 'rdsadmin';

to determine if we're in RDS.

I'll look at the way that #40 works and see if I can apply something similar here.

[JessieAMorris]

Ah, checking it looks like #40 was never merged. So how do you want to approach this at this point? Getting RDS support would be a huge win for the company I'm at. It sounds like Google Cloud users would benefit as well.

What can I do to make this happen?

[hrpomrx]

I like the spirit of this PR, it allows one to generate a spec on an RDS but it doesn't seem to work with configure, which complains about pg_authid even with --alternate-attributes-table specified. That said, I would love to see this (and a Redshift solution) developed further.

[slocke716]

What exactly are the issues other than password setting? I'd be happy to grab this and make alterations to get it merged. So far I've gathered: password reset doesn't work (this could be a known caveat since many like myself use a different tool for managing users) configure doesn't work (probably just need to add the changes that the original author added to that section) it requires a flag for the functionality which shouldn't be a blocker

Please indicate what else needs to be done to get this in a mergeable state and I will pick this up.

Context: Managing grants in postgres stinks and there is no better tool than this as far as I can tell