Help with privilege or pg_hba.conf

Arcadius A. wrote:

Hello!
I have a Unix box running PostgreSQL 7.3.3.

So far, I have been the only one user of the DB server...and I didn't have
any problem using it with the JDBC driver.

Now, a friend of mine wants to use the DB too for doing web stuffs(PHP,
JSP).... so I need to create a new user on PostgreSQL. this has been done
easily.
Now, I don't want the new user (his username is "cool",and he has a
password ) to access other DBs on my server... I want him to access ONLY a
database named "cool" that he owns
(as user "cool", I have created the DB "cool"... and I want user "cool" to
access ONLY DB "cool" and do any operation on it).

How can this be done?

I've been trying to modify "pg_hba.conf" but with no luck .... as user
"cool", after I connect to the DB server via psql and I do "\c anotherdb",
user "cool" can still connect to "anotherdb".
my "pg_hba.conf" can be found at http://ahouans.sh.cvut.cz/pg_hba.txt

Thanks in advance.

Have a nice weekend!

Arcadius Ahouansou.

By creating a user "cool" you are allowing the user to connect to
postgres and connect to any database.
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.
In other words if you didn't make "cool" a superuser (which you
shouldn't of) or granted "cool" the right to create databases, "cool"
will be able to connect to databases but that's it. "cool" won't be
allowed to even select data or delete anything.
If you create a database and make "cool" the owner, "cool" can then do
whatever with only that database.
As long as you create normal users you have to "Grant Privileges" to any
new user to do anything, except connecting to databases.
Read up on, "creating users" and "Granting Privileges".
Or install the newest "pgaccess", it has a good gui for adding
users/groups, displaying rights to objects and granting privileges.

Hello,

I've been trying to modify "pg_hba.conf" but with no luck .... as user
"cool", after I connect to the DB server via psql and I do "\c anotherdb",
user "cool" can still connect to "anotherdb".
my "pg_hba.conf" can be found at http://ahouans.sh.cvut.cz/pg_hba.txt

I think, you should not say, that all are allowed to connect to the database
and cool had to type in a password, because if cool is not cool, he also can
have access to all other databases, because the other lines said: ALL

At the moment I can't give a solution, but hopefully another person, because
I don't know exactly, if my answer is right (never needed restrict the
access to only one database)

Daniel Seichter
--
postgreSQL on Netware - the red elephant
http://postgresql.dseichter.org