Обсуждение: Help with privilege or pg_hba.conf
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.
On Sat, Jul 19, 2003 at 11:31:29PM +0200, 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? Don't forget that when Postgres is going through pg_hba.conf to authenticate a connection, it uses the first line that matches *all* the connection parameters - type, user, db, ip address (for remote connections). So in your case, access by user "cool" to databases other than "cool" is matched by the line "local all all trust", which permits access. You need to specifically disallow connections by user "cool" to other databases, by putting a line like "local all cool reject" before the "local all all trust" line, and similarly for remote connections. Richard
--- "Arcadius A." <ahouans@sh.cvut.cz> 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. Would this work? 1. Move all of the lines where user = 'all' to the bottom. 2. Below lines where database = 'cool', but above user='all' lines, explicitly reject cool's access to all databases: # TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD # put database = 'cool' lines here host all cool 127.0.0.1 255.255.255.255 reject host all cool x.x.x.x 255.255.255.224 reject host all cool 192.168.0.0 255.255.255.0 reject # put user = 'all' lines here Best of luck, Andrew Gould
Hello! "Arcadius A." <ahouans@sh.cvut.cz> wrote in message news:bfcd9k$bk6$1@main.gmane.org... > 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. As suggested by Richard and Andrew , I have placed "local all cool reject" and "host all cool 0.0.0.0 0.0.0.0 reject" before any line having user = 'all' and now, <psql> cool=> \c mytestdb FATAL: No pg_hba.conf entry for host localhost, user cool, database mytestdb Previous connection kept cool=> </psql> Now, it seems to be working like I wanted. So again, thanks so much to both of you for the help! Arcadius Ahouansou.