Re: FINAL: Multi-User PostgreSQL usage SECURITY

On Fri, 7 Sep 2001, Mike Rogers wrote:

>     So this issue was raised quite some time ago by many many people and
> seems to contantly be asked by new PostgreSQL users.  I never seem to find
> any real answers for it.
>
>     I am running a multi-user system and wish to have 10 user accounts with
> 10 different corresponding databases.  I do not want user 'a' to be able to
> access user 'b's database-  Only their own 'a' database.  It really
> shouldn't be this difficult.  I realize that I can revoke access to all
> users on the 'a' tables, but then user B can still create tables within user
> A's database.
>     There has to be an easy solution.  As a hosting solutions provider for a
> small number of clients, I have always steered in the direction of MySQL for
> this feature, but I am seeing some demand for PostgreSQL.  I do not have the
> resources to run each user with their own copy of PostgreSQL.
>
>     I have tried chaning pg_hba.conf to add the database field to the user,
> but that doesn't seem to help at all.
>
> Any thoughts?  If it makes a difference, i can make the databases the same
> name as the username if I must.

IIRC, with dbnames same as user names you can use something like:
host sameuser <ip> <addressmask> password
to make the connections only to their own.

Otherwise, I think you can use separate external password files for the
different databases on different lines of the conf file...
#   password:   Authentication is done by matching a password supplied
#               in clear by the host. If AUTH_ARGUMENT is specified then
#               the password is compared with the user's entry in that
#               file (in the $PGDATA directory).  These per-host password
#               files can be maintained with the pg_passwd(1) utility.
#               If no AUTH_ARGUMENT appears then the password is compared
#               with the user's entry in the pg_shadow table.