Re: HIPPA (was Re: Anyone know ...)

On 3/10/07, Kenneth Downs <ken(at)secdat(dot)com> wrote:
>
> Alvaro Herrera wrote:
>
> David Legault escribió:
>
> That's basically what I've done with my past questions on the ROLE system in
> place. Since roles are global, I wanted it fine grained to the DB level so I
> had to append DB_ in front of each role name and by using current_database()
> inside my functions, I could hide that from the exterior.
>
> Hmm, there used to be a facility to restrict users to specific
> databases, enabled by db_user_namespace (not by default).
>
>
I tried this, but couldn't achieve what I wanted with it, it's also marked
as "temp stuff could change in the future".

It seems to still work on 8.2 ...
>
>
> there is also the 'samegroup' facility in pg_hba.conf. We create a group
> named after each database, and a person cannot get into a database unless
> they are in that group.
>

I append the name of the DB on front of the roles and it works flawlessly,
you can't have 2 DB with the same name in the cluster. It's also transparent
to the application since the functions to interact with the roles + other
tables use current_database() which gets the name of the DB to which the
groups are assigned.

All databases are REVOKED from public as all functions. When a user is
created he is granted connect on the database to which he belongs. So even
if someone gets the credentials to db1_user and attempts to connect to db2
with it it will fail.

David