Re: FINAL: Multi-User PostgreSQL usage SECURITY

""Mike Rogers"" <temp6453(at)hotmail(dot)com> wrote in message news:OE658VnurbAMeMfHUye00004188(at)hotmail(dot)com(dot)(dot)(dot)
> 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.