[lug] postgres

[Hugh Brown]

On Thu, 2003-05-08 at 16:59, Jason W. Strnad wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> 
>  From the O'Reilly Practical PostgreSQL:
> 
> "Though any user may connect to a database, if they wish access to 
> objects within that database they must have those privileges explicitly 
> granted to them."
> 
>  From my (admittedly limited) use of PostgreSQL I have understood this 
> statement to be correct.  You can protect parts of a DB from users, but 
> any user who can connect to the DB server, can connect to any DB.
> 
> If I have this wrong please correct me.
> 

This has changed somewhat.  With the version of postgres shipped with
rh9 (7.3.2-3 according to the rpm), you can reject connections to
certain db's while allowing them to others.

so as a user I can connect to template1 but when I try to connect to
specialdb, I get a FATAL and can't connect.

I got around my dilemma by adding these lines after all the specialdb
lines:

host specialdb developeruser ip mask reject
local specialdb developeruser  reject

it works in my limited case where all developers hit versions of
postgres on their local machine and there is a default user with the
ability to create databases as needed.

The perms are set up in the database, but I was just experimenting with
what could be done at the connection.

Hugh




> - - -jasons
> 
> On Thursday, May 8, 2003, at 09:36  AM, Hugh Brown wrote:
> 
> > I am playing around with the version of postgres from rh9.  they added 
> > a
> > new column to the pg_hba.conf file that allows you to specify
> > users/groups that are allowed to connect.
> >
> > My question is this.  I have a machine that will have multiple 
> > databases
> > within the instance of postgres.  new ones will be created to do
> > development, etc.
> >
> > I have a particular database that I only want members of a certain 
> > group
> > to be able to access, but be able to allow anyone else to access any
> > other database.  I can't seem to find a way to do this.
> >
> > so far I have
> >
> > host specialdb +specialgroup   ip netmask md5
> > host specialdb +specialgroup   127.0.0.1 255.255.255.255 md5
> > local specialdb +specialgroup    md5
> > host    all         all         127.0.0.1         255.255.255.255   md5
> > local   all         all                                             md5
> >
> >
> > which keeps anyone not on the local machine off, but any user on the
> > local machine can get to the specialdb regardless of their membership 
> > in
> > specialgroup.
> >
> > what I'd really like is a directive like:
> >
> > host specialdb !+specialgroup   reject
> >
> > but there doesn't seem to be one.  Anyone know how to be exclusive
> > rather than inclusive (which is how the documentation says to do it)?
> >
> > Hugh
> >
> >
> > _______________________________________________
> > Web Page:  http://lug.boulder.co.us
> > Mailing List: http://lists.lug.boulder.co.us/mailman/listinfo/lug
> > Join us on IRC: lug.boulder.co.us port=6667 channel=#colug
> >
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.1 (Darwin)
> 
> iD8DBQE+usUj0Z7j/IawA+ERAvPpAJ0S8Y+QfXBmfmvsDkYFqLj8rcNoEgCfaidS
> kRu/b0wprVXtnMTE8Y2mcso=
> =q19G
> -----END PGP SIGNATURE-----
> 
> _______________________________________________
> Web Page:  http://lug.boulder.co.us
> Mailing List: http://lists.lug.boulder.co.us/mailman/listinfo/lug
> Join us on IRC: lug.boulder.co.us port=6667 channel=#colug