Re: FINAL: Multi-User PostgreSQL usage SECURITY

""Mike Rogers"" <temp6453@hotmail.com> wrote in message news:OE658VnurbAMeMfHUye00004188@hotmail.com...
> Greets all;
>     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.

Hi Mike,

I've just spent the last  hour trying to figure this out for myself.
Coming from Oracle, user management in PG is quite a mess, but when
they add the schema stuff in it should be easier.

I'm not really sure why no one has posted a concise answer in the past
(half of those 60 minutes were mostly spent searching groups.google),
so I hope this does what everyone has been hoping for.

What I tried to accomplish was to deny users the ability to enter
other databases and to also deny them access to template1 (why should
general non-admin users have access to this in the first place?)  It
should also allow the postgres user access to all (for backups,
etc...)

------------------------------------------------------
pg_hba.conf:

local   all                                     reject
local   sameuser                                password
host    sameuser  127.0.0.1   255.255.255.255   password
host    all       127.0.0.1   255.255.255.255   ident postgres

pg_ident.conf:

#MAP       IDENT    POSTGRES USERNAME
postgres    postgres  postgres
-------------------------------------------------------

- The first line restricts all local access.  It may not be necessary,
but it shouldn't hurt.
- The second line allows userA to connect to DB userA.
- The third line provides tcp/ip access.
- The fourth line provides the backdoor Tom Lane has mentioned.  It
also allows access to template1 for postgres.  It requires that identd
is running check inetd.conf or xinetd.d/identd.  MS users may be SOL.)
 It also requires you to set PGHOST=localhost in your environment.
(You can't do identd checking with local, unfortunately.)

Kevin

PS Users were created with the CREATEDB option which was removed once
the database was created.