Re: Per-Database Roles

On Fri, May 25, 2012 at 11:12 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Fri, May 25, 2012 at 10:34:54PM -0400, Stephen Frost wrote:
>> * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
>> > On Thu, May 24, 2012 at 6:21 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> > > Yes, pre-1996.  I think the fact that authentication/user names appear
>> > > in pg_hba.conf really locked the user name idea into global objects, and
>> > > we have never really been able to make a dent in that.
>> >
>> > Eh?  Why would the presence of usernames in pg_hba.conf mean that they
>> > have to be global objects?
>>
>> I havn't had a chance (yet) to look, but perhaps the current code
>> attempts to validate the role before figuring out what database is being
>> requested?  We'd have to essentially invert that, of course, for this..
>> One thing I was wondering about is if we're going to have an issue
>> supporting things like "tell me what databases exist" (psql -l), which
>> connect to the 'postgres' by default, for local-only roles.  I'm not
>> sure that I actually care, to be honest, but it's something to consider.
>> I don't think we should require users to create every local role also in
>> postgres, nor do I feel that we should allow connections to postgres by
>> any role, nor do I want to break tools which use 'postgres' to basically
>> get access to shared catalogs- but I don't see an immediate or easy
>> solution..
>
> Yes.  In a simple case, you have a username, you want to validate it
> against LDAP or kerberos --- how do you partition the external
> authentication tool based on database name?  Seems like an obvious
> problem to me.

Well, when people try to connect to database "it", you set up
pg_hba.conf to point them at the Kerberos server, but when they try to
connect to database "sales", you just use MD5 for that. Or whatever
your site policy happens to be. I'm not seeing the problem;
pg_hba.conf already allows different authentication methods for
different databases.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company