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.
