Re: DB Access Restrictions

I'm finalizing the setup to be used to host customer DBs for a domain
hosting service, and I'd like to make sure I've got the access controls
straight:

In pg_hba.conf, I've seen and managed to figure out *most* of how access
to the various DBs can be controlled. I'll be using the "password"
authentication, most likely with either sameuser or all:
-> db of "sameuser" *REQUIRES* that the connecting user have the same
name as the database they're trying to connect to - for ANYONE
using this access method
-> db of "all" lets the access control slip down a level to whether a
user has provided the proper password.

Is there any way to simply specify a list of users for each db? I
haven't been able to figure out if that's possible or not with
"password" authentication. (ident is useless; all DB access except
limited administrative control on my part will be via PHP across the
local UNIX socket- and ident will return "apache" if it returns anything
useful at all.)

Platform (at least initially) will be RedHat7.0/Postgres7.0; but it
will be moving to RH7.3/Postgres7.2 "Real Soon Now". (ie, as soon as
the new server is put together.)

-kgd
--
Money is overrated.

> If your network is not secure, I recommend MD5. In fact, we recommend
> MD5 with encrypted_passwords enabled in postgresql.conf in almost all
> cases. Encrypted passwords will be the default in 7.3.

psql seems to work flawlessly with MD5 passwords, which is no great
surprise.

The JDBC client doesn't seem to work. I had to go back to "password."

Is there something the client has to do with JDBC to make MD5 passwords
work? The particular client I'm talking about is DB Visualizer.

--
Tim Ellis
Senior Database Architect
Gamet, Inc.

Kris Deugau wrote:
> I'm finalizing the setup to be used to host customer DBs for a domain
> hosting service, and I'd like to make sure I've got the access controls
> straight:
>
> In pg_hba.conf, I've seen and managed to figure out *most* of how access
> to the various DBs can be controlled. I'll be using the "password"

If your network is not secure, I recommend MD5. In fact, we recommend
MD5 with encrypted_passwords enabled in postgresql.conf in almost all
cases. Encrypted passwords will be the default in 7.3.

> authentication, most likely with either sameuser or all:
> -> db of "sameuser" *REQUIRES* that the connecting user have the same
> name as the database they're trying to connect to - for ANYONE
> using this access method
> -> db of "all" lets the access control slip down a level to whether a
> user has provided the proper password.
>
> Is there any way to simply specify a list of users for each db? I

In 7.3, due out in a few months, there is a USER column where you can
list users or specify a filename containing usernames.

> haven't been able to figure out if that's possible or not with
> "password" authentication. (ident is useless; all DB access except
> limited administrative control on my part will be via PHP across the
> local UNIX socket- and ident will return "apache" if it returns anything
> useful at all.)

In 7.2.X and earlier, the only way is to specify a secondary password
file, and list user names in there. You don't actually need the
passwords in the file, just the usernames, but again, that only works
with 'password', I think.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

The jdbc that comes with 7.2.X should work fine, as well as the jdbc on
the CVS server. In fact, some one reported a problem with the jdbc MD5
code recently, but I know others were using it fine.

JDBC folks, is MD5 working, and is that MD5 fix recently posted in the
current CVS of jdbc?

---------------------------------------------------------------------------

Tim Ellis wrote:
> > If your network is not secure, I recommend MD5. In fact, we recommend
> > MD5 with encrypted_passwords enabled in postgresql.conf in almost all
> > cases. Encrypted passwords will be the default in 7.3.
>
> psql seems to work flawlessly with MD5 passwords, which is no great
> surprise.
>
> The JDBC client doesn't seem to work. I had to go back to "password."
>
> Is there something the client has to do with JDBC to make MD5 passwords
> work? The particular client I'm talking about is DB Visualizer.
>
> --
> Tim Ellis
> Senior Database Architect
> Gamet, Inc.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

The development version on the website has the mentioned fix. The
problem that the fix fixes happens when using certain character sets
like multibyte character sets.

thanks,
--Barry

Bruce Momjian wrote:

>The jdbc that comes with 7.2.X should work fine, as well as the jdbc on
>the CVS server. In fact, some one reported a problem with the jdbc MD5
>code recently, but I know others were using it fine.
>
>JDBC folks, is MD5 working, and is that MD5 fix recently posted in the
>current CVS of jdbc?
>
>---------------------------------------------------------------------------
>
>Tim Ellis wrote:
>
>
>>>If your network is not secure, I recommend MD5. In fact, we recommend
>>>MD5 with encrypted_passwords enabled in postgresql.conf in almost all
>>>cases. Encrypted passwords will be the default in 7.3.
>>>
>>>
>>psql seems to work flawlessly with MD5 passwords, which is no great
>>surprise.
>>
>>The JDBC client doesn't seem to work. I had to go back to "password."
>>
>>Is there something the client has to do with JDBC to make MD5 passwords
>>work? The particular client I'm talking about is DB Visualizer.
>>
>>--
>>Tim Ellis
>>Senior Database Architect
>>Gamet, Inc.
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 4: Don't 'kill -9' the postmaster
>>
>>
>>
>
>
>

On Wed, Aug 21, 2002 at 22:05:49 -0400,
Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
>
> In 7.3, due out in a few months, there is a USER column where you can
> list users or specify a filename containing usernames.

Another key thing about 7.3 is that that match for access now includes
the username, so you can have multiple access methods for different users
on the same DB. I am currently using the following in a CVS version of 7.3:
# TYPE DATABASE USER IP_ADDRESS MASK AUTH_TYPE

local all postgres ident postgres
local area,book,template1 bruno ident sameuser
local area,book nobody ident nobody
local sameuser all ident sameuser

The ident file has the following in it:
# MAP IDENT PGUSERNAME
postgres root postgres
postgres bruno postgres
postgres postgres postgres
nobody bruno nobody
nobody nobody nobody

The net result of this is that the postgres account can use any database.
bruno can use area, book, bruno or template1 (the last one is needed to
create new databases). nobody (the web server) can access area, book
and nobody (if it existed). Other users can access a db matching their
username.
The postgres user can be used by bruno, root or postgres. And the nobody
user can be used by bruno or nobody. Other users are stuck using their
normal username to connect to postgres.
I will probably play around with this setup some more, but it does illustrate
a way to have a bunch of users with databases matching their usernames, but
also have other databases and some users that can access more than just
their own db.