Support pass-thru auth for Amazon RDS

[cgamache]

So, RDS holds the actual PostgreSQL root user hostage, and gives you a user role that can do /most/ things that the PostgreSQL root user can. It cannot access pg_shadow, though.

If I want to use auth_user for pass-thru user authentication, I need to be able to write a query that can test a username/password combo.

So first, is there something I'm missing in RDS that provides that capability? Would it kill Amazon to create a function that could do that? I guess so.

Second, there IS a de facto method to test whether a username and password combination is good. Just try to connect to the database. This would be an alternative to logging-in as a one-trick pg_bouncer user with SECURITY DEFINER perms and running a query to check for username/password validity-- understanding this would be at the expense of potentially creating a ton of short-lived connections...

To mitigate that problem, you could hold the hashes of successful combinations of username/password in memory, check against that first, and throw them out every n seconds/minutes/hours to allow them to be refreshed. That's no worse than having a userfile.txt.

[gplv2]

I assume that on RDS (haven't tested) you cannot access pg_authid either ? aka : select * from pg_authid;

[joekohlsdorf]

@gplv2 Thats right, you cannot access it.

Trying to connect is the only option.

[gplv2]

Hi Joe,

I have an aversion against using RDS. I've had tons of issues with it's so-called HA , I've had databases of 10GB where the failover to the backup node took hours, The concept is fine but their implementation is(was) flawed and limiting. I prefer setting up my own HA which will failover in mere milliseconds using haproxy/repmgr/pgbouncer combo. Also, resizing RDS took ages. admittedly, this was on mysql back then since they didn't support pgsql yet. I sure hope they improved this.

[gplv2]

Check this https://tickets.puppetlabs.com/browse/MODULES-5068

There seems to be a workaround in there using pg_user , assuming in RDS you have access to that view

[petere]

One of the problems with the idea of verifying the supplied password by connecting through to a server is that it doesn't work with SCRAM. PgBouncer doesn't support that yet, but that's where things should be heading.

[diranged]

I'm curious about this issue as well - as its a critical path for us to be able to use PGBouncer with RDS. Something I'm trying to understand is why this is necessary at all.

Can someone explain why pgbouncer needs to own the auth to begin with? Why not just let PGBouncer try to auth to the backend database with the user-supplied user:password combo. If it works, then store the hash in memory as good. Why bother with the whole user_file or user_query thing at all?

[gplv2]

It's by design because PG authenticates sessions early. See here https://www.2ndquadrant.com/en/blog/pg-phriday-securing-pgbouncer/

[petere]

Can someone explain why pgbouncer needs to own the auth to begin with? Why not just let PGBouncer try to auth to the backend database with the user-supplied user:password combo.

Then anyone could connect to pgbouncer and use up all the connection slots before having to authenticate. That would be quite mad.

[diranged]

@petere hey.. just coming back to this.. if we want this option, we should be able to use it.. its our option to decide whether or not we want to risk it.. but right now the management of pgbouncer + rds is incredibly painful. i would like to bring this topic back up and see if we can get an optional feature built in where we acn do pass_through auth..

[petere]

I understand, but I'm not going to be the one building it. Well-crafted design proposals and/or patches are welcome.

[thekev]

I have not found a way to get the md5 from any RDS view. I think the only way this can work is to connect as the user. There would need to be guard rails, and I don't have a proposal or a PR yet. Mostly wanted to add to the discussion.

In addition, AWS is already solving the pooling problem for us with RDS Proxy. More bespoke use cases for pgbouncer are unlikely to be a high priority for them.

[jfirebaugh]

Then anyone could connect to pgbouncer and use up all the connection slots before having to authenticate.

Couldn't pgbouncer eagerly connect the first time a given user:password combination is tried, and cache the result? I assume that's essentially what auth_query/auth_user-based authentication does already.

[petere]

As I mentioned above, this whole idea doesn't work with SCRAM. So maybe you then wouldn't use SCRAM, but that on the whole seems kind of backwards in terms of security.