Thread: Login limitation?
Hi All, I'd like to know if there is a method to let a user login into only predefined databases? I know that I could edit pg_hba.conf to achive this, but - there will be many databases on the server with the same structure but with different data - there will be "local admins" who must be able to create new "local users" (of their own db) - it is a win32 client application... so editing pg_hba.conf is not too easy... So I'd need an administrative method (command?) which is capable to define (in the server level) a set of databases (0, 1 or more) for every user which she can login and prevent her from logging in to any other databases. This data should be modified via SQL statements like GRANT. I tried to REVOKE all priviges from a user on a db, but the user still able to login. Another question is that she can't do anything. Any ideas? Thx -- Csaba Együd -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 2005.11.25.
On Sun, Nov 27, 2005 at 12:32:06PM +0100, Egy?d Csaba wrote: > Hi All, > > I'd like to know if there is a method to let a user login into only > predefined databases? I know that I could edit pg_hba.conf to achive this, > but > - there will be many databases on the server with the same structure but > with different data > - there will be "local admins" who must be able to create new "local users" > (of their own db) > - it is a win32 client application... so editing pg_hba.conf is not too > easy... > > So I'd need an administrative method (command?) which is capable to define > (in the server level) a set of databases (0, 1 or more) for every user which > she can login and prevent her from logging in to any other databases. This > data should be modified via SQL statements like GRANT. > > I tried to REVOKE all priviges from a user on a db, but the user still able > to login. Another question is that she can't do anything. > > Any ideas? I think you're basically stuck with pg_hba.conf. There's been some functions added to 8.1 that make it possible to do some more administrative stuff with config files via SQL, but I'm not sure if they'd cover this case. I can see where this could be a problem for people providing hosting; if enough other users request this functionality it might make it onto the TODO list. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Hi Jim, so IIUC, I will have to schedule a program on the server which reads all user information and if a new user is added it modifies the pg_hba.conf and reloads the server? thanks, -- csaba -----Original Message----- From: Jim C. Nasby [mailto:jnasby@pervasive.com] Sent: Tuesday, November 29, 2005 12:46 AM To: Egy?d Csaba Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Login limitation? On Sun, Nov 27, 2005 at 12:32:06PM +0100, Egy?d Csaba wrote: > Hi All, > > I'd like to know if there is a method to let a user login into only > predefined databases? I know that I could edit pg_hba.conf to achive > this, but > - there will be many databases on the server with the same structure > but with different data > - there will be "local admins" who must be able to create new "local users" > (of their own db) > - it is a win32 client application... so editing pg_hba.conf is not > too easy... > > So I'd need an administrative method (command?) which is capable to > define (in the server level) a set of databases (0, 1 or more) for > every user which she can login and prevent her from logging in to any > other databases. This data should be modified via SQL statements like GRANT. > > I tried to REVOKE all priviges from a user on a db, but the user still > able to login. Another question is that she can't do anything. > > Any ideas? I think you're basically stuck with pg_hba.conf. There's been some functions added to 8.1 that make it possible to do some more administrative stuff with config files via SQL, but I'm not sure if they'd cover this case. I can see where this could be a problem for people providing hosting; if enough other users request this functionality it might make it onto the TODO list. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.9/185 - Release Date: 2005.11.28.
Yes, that's probably true. Depending on your pain you could also fund development of a feature that would make this easier to deal with. On Tue, Nov 29, 2005 at 09:43:16AM +0100, Egy?d Csaba wrote: > Hi Jim, > so IIUC, I will have to schedule a program on the server which reads all > user information and if a new user is added it modifies the pg_hba.conf and > reloads the server? > > thanks, > -- csaba > > -----Original Message----- > From: Jim C. Nasby [mailto:jnasby@pervasive.com] > Sent: Tuesday, November 29, 2005 12:46 AM > To: Egy?d Csaba > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Login limitation? > > On Sun, Nov 27, 2005 at 12:32:06PM +0100, Egy?d Csaba wrote: > > Hi All, > > > > I'd like to know if there is a method to let a user login into only > > predefined databases? I know that I could edit pg_hba.conf to achive > > this, but > > - there will be many databases on the server with the same structure > > but with different data > > - there will be "local admins" who must be able to create new "local > users" > > (of their own db) > > - it is a win32 client application... so editing pg_hba.conf is not > > too easy... > > > > So I'd need an administrative method (command?) which is capable to > > define (in the server level) a set of databases (0, 1 or more) for > > every user which she can login and prevent her from logging in to any > > other databases. This data should be modified via SQL statements like > GRANT. > > > > I tried to REVOKE all priviges from a user on a db, but the user still > > able to login. Another question is that she can't do anything. > > > > Any ideas? > > I think you're basically stuck with pg_hba.conf. There's been some functions > added to 8.1 that make it possible to do some more administrative stuff with > config files via SQL, but I'm not sure if they'd cover this case. > > I can see where this could be a problem for people providing hosting; if > enough other users request this functionality it might make it onto the TODO > list. > -- > Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com > Pervasive Software http://pervasive.com work: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > > -- > No virus found in this outgoing message. > Checked by AVG Free Edition. > Version: 7.1.362 / Virus Database: 267.13.9/185 - Release Date: 2005.11.28. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
I see. Thank you very much. -- csaba -----Original Message----- From: Jim C. Nasby [mailto:jnasby@pervasive.com] Sent: Tuesday, November 29, 2005 10:48 PM To: Egy?d Csaba Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Login limitation? Yes, that's probably true. Depending on your pain you could also fund development of a feature that would make this easier to deal with. On Tue, Nov 29, 2005 at 09:43:16AM +0100, Egy?d Csaba wrote: > Hi Jim, > so IIUC, I will have to schedule a program on the server which reads > all user information and if a new user is added it modifies the > pg_hba.conf and reloads the server? > > thanks, > -- csaba > > -----Original Message----- > From: Jim C. Nasby [mailto:jnasby@pervasive.com] > Sent: Tuesday, November 29, 2005 12:46 AM > To: Egy?d Csaba > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Login limitation? > > On Sun, Nov 27, 2005 at 12:32:06PM +0100, Egy?d Csaba wrote: > > Hi All, > > > > I'd like to know if there is a method to let a user login into only > > predefined databases? I know that I could edit pg_hba.conf to achive > > this, but > > - there will be many databases on the server with the same structure > > but with different data > > - there will be "local admins" who must be able to create new "local > users" > > (of their own db) > > - it is a win32 client application... so editing pg_hba.conf is not > > too easy... > > > > So I'd need an administrative method (command?) which is capable to > > define (in the server level) a set of databases (0, 1 or more) for > > every user which she can login and prevent her from logging in to > > any other databases. This data should be modified via SQL statements > > like > GRANT. > > > > I tried to REVOKE all priviges from a user on a db, but the user > > still able to login. Another question is that she can't do anything. > > > > Any ideas? > > I think you're basically stuck with pg_hba.conf. There's been some > functions added to 8.1 that make it possible to do some more > administrative stuff with config files via SQL, but I'm not sure if they'd cover this case. > > I can see where this could be a problem for people providing hosting; > if enough other users request this functionality it might make it onto > the TODO list. > -- > Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com > Pervasive Software http://pervasive.com work: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > > -- > No virus found in this outgoing message. > Checked by AVG Free Edition. > Version: 7.1.362 / Virus Database: 267.13.9/185 - Release Date: 2005.11.28. > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.10/188 - Release Date: 2005.11.29.