Re: FINAL: Multi-User PostgreSQL usage SECURITY

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.
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.

Please let me know if anyone knows of a way to do this.

Thanks in advance;
--
Mike

"Mike Rogers" <temp6453(at)hotmail(dot)com> writes:
> I have tried chaning pg_hba.conf to add the database field to the user,
> but that doesn't seem to help at all.

Make the database field "sameuser", and then the line only allows
connection to one's own database. You will need an escape hatch to
allow you as superuser to get into everyone's db (else you can't run
pg_dumpall). The escape hatch should be a second line in pg_hba.conf
with a tighter authorization method (eg, a secondary password file
with an entry only for you).

regards, tom lane

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.

""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.

Is there any way I can make any user that _LOGS IN_ as postgres (using crypt
passwords) from localhost or through sockets, access to _ALL_ databases.
Currently I have all of my lines set as sameuser. I have tried using ident
but it doesn't work, as the user that tries to connect varries from my
username, to that of the web server, etc.

Anyway, is there anyway to force a user to be able to access ALL
databases on the server as opposed to just the sameuser without using IDENT?

Cheers;
--
Mike

----- Original Message -----
From: "Kevin McFadden" <jkm(at)patriot(dot)net>
To: <pgsql-admin(at)postgresql(dot)org>
Sent: Friday, September 14, 2001 5:22 PM
Subject: Re: [ADMIN] 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>