Re: [PATCHES] Users/Groups -> Roles

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > I think one big issue is that we don't have a 'usage' database check
> > beyond pg_hba and so any user could get the schema definitions for any
> > database, which kind of sucks.
>
> Not unless he can connect to it.

That's controlled by pg_hba.conf though, isn't it? The idea being that
you'd like to give some people the ability to create users/roles, but to
limit the databases those created users/roles could connect to by, say,
requiring they have 'usage' or 'connect' permissions to that database,
which could be set by the database owner; without the database owner
having write permissions to the pg_hba.conf.

The scenario is one of an ISP who wants to give people Postgres access
but doesn't want to have to manage all the users. So, the ISP creates
a database, an 'admin' role for a given customer and gives 'createrole'
permissions to that admin role. The admin role can then create new
roles but can only give them access to connect to their database (since
that's the only one the admin either 'owns' or has 'create', etc,
privileges on). I *think* (perhaps I'm wrong..) that the only thing we
lack to make this work is a permissions check on the connect to a given
database which can be managed by a user of the database (ie: not
pg_hba.conf).

Thinking about this a bit more I guess this would probably involve
basically moving pg_hba.conf into the database catalogs and then having
pg_hba.conf generated similar to how pg_authid is generated. That's
probably too much to do for 8.1 then, I had been hoping there was a way
to do it which would be a smaller change than that.

Thanks,

Stephen