Re: Database permissions

Tommi Mäkitalo wrote:

>Am Freitag, 7. Dezember 2001 17:02 schrieb Dado Feigenblatt:
>...
>
>>Have you looked at pg_hba.conf at all?
>>There you can setup which user, from which machine, conect to which
>>database.
>>
>
>Ah! That's (almost) what I've missed. I have looked at pg_hba.conf, but not
>not that much. I thought pg_hba.conf does authentication but no authorisation.
>
>The way to create users for db1 and other users for db2 is to create these
>users in the database and set up pg_hba.conf to look up some maps and put
>these users in these maps, right?
>
That's one way.
I'm not very experienced with pg_hba.conf myself.
My application will run on an intranet. It is still under development so
I'm not very concerned about security right now.
I have just a few users on the DB, the developers, and the user that the
web server will use to conect.
I had just IP authentication. Recently I had to add ident authorization
and a map so that my backup/restore script could login as different
users when restoring different databases. That method is used only for
localhost connections.

>I feel, that authorisation should be done at the engine, not outside (I
>consider pg_hba.conf as outside).
>
The process that carries the authentication *is* the engine.
Whether it gets the information from the database or from a flat file is
a different story.
You wouldn't like to screw up your permissions and find yourself locked
outside of the database.
With a flat file you can still edit the file and regain access.
I don't know if that's the reason the developers decided on the external
file or if there's something else.

>What about usergroups? It would be much easier to put the users to groups and
>assign connect-authorisation to these groups.
>
I don't know if there's a way of configuring pg_hba.conf or writing maps
in such a way that you'd had that functionality.
The database offers groups internally, but that has nothing to do with
the pg_hba.conf authorization.

>And what about performance and administration with many users? The maps,
>pg_hba.conf references are flat files. They are slow to parse and hard to
>administer. That's what we have a database for. Is there any chance to put
>connect authorisation to the database?
>
I'm not sure about reading and parsing a flat file being so much slower
than accesing the database.
There's an overhead for every new connection to the database, and either
way there would be a disk access.
The advantages of databases is all the other functionality it offers
over flat files.
For data sets that are very small and simple you're better off using
flat files, sed, grep, awk, PERL,
or whatever text manipulation tool you're more confortable with.
Any guru would like to comment on this?

Anyway, I think postgreSQL, as most applications offers a very simple
implementation of user/group authentication/authorization.
I forsee I'll have to develop my own RBAC (Role Based Access Control),
which will live in the database and might be what you're looking for.
For me that's still is a multi headed beast. But one thing I know (I
think). The connecting process (Apache) will use the RBAC to decide what
data it can serve, but the process itself will always connect as the
same user. So I can keep my pg_hba.conf very simple and the entire logic
will be in my own application. I would welcome comments on this too.

I hope I was able to help, but I would look for a second opinion, after
all my experience in these areas is very limited :)

--
Dado Feigenblatt Wild Brain, Inc.
Technical Director (415) 216-2053
dado(at)wildbrain(dot)com San Francisco, CA.