Thread: 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.
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@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
> 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.
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@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Wed, Aug 21, 2002 at 22:05:49 -0400, Bruce Momjian <pgman@candle.pha.pa.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.