Thread: users & groups
Hi there, i have few questions in users and droups in pgSQL that i can't check in the next few days and this is inportant in my choice of database: 1. If I'm using different password files for each database can I grant or revoke privileges to this users without creating the same users in pg_user table? I mean is that enough to create the user only in password file and to operate with that user,I think I can't ALTER USER, but what about GRANT ,REVOKE? 2. How can I add a user in more than one GROUP? How can I add an existing user to a new group? How can I remove a user from some group? What if the users exist only in password file and not in pg_user table(if it's possible :-))? Thanx in advance! Rumen __________________________________________________ Do You Yahoo!? Send instant messages with Yahoo! Messenger. http://im.yahoo.com/
R D writes: > 1. If I'm using different password files for each > database can I grant or revoke privileges to this > users without creating the same users in pg_user > table? No, you always have to use CREATE USER. Btw., I think we're pondering removing the secondary password file feature sometime; would you care to explain why you think you need it? > 2. How can I add a user in more than one GROUP? ALTER GROUP foo ADD USER bar; ALTER GROUP foo2 ADD USER bar; > How can I add an existing user to a new group? CREATE GROUP foo WITH USER bar1, bar2; > How can I remove a user from some group? ALTER GROUP foo DROP USER bar; RTFM :) -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
I think all that works in 7.0, not 6.5 ? (after RTFM a few months ago) Nicolas Huillard -----Message d'origine----- De: Peter Eisentraut [SMTP:peter_e@gmx.net] Date: dimanche 18 juin 2000 16:17 A: R D Cc: pgsql-admin@postgresql.org Objet: Re: users & groups R D writes: > 1. If I'm using different password files for each > database can I grant or revoke privileges to this > users without creating the same users in pg_user > table? No, you always have to use CREATE USER. Btw., I think we're pondering removing the secondary password file feature sometime; would you care to explain why you think you need it? > 2. How can I add a user in more than one GROUP? ALTER GROUP foo ADD USER bar; ALTER GROUP foo2 ADD USER bar; > How can I add an existing user to a new group? CREATE GROUP foo WITH USER bar1, bar2; > How can I remove a user from some group? ALTER GROUP foo DROP USER bar; RTFM :) -- Peter Eisentraut Sernanders vag 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
--- Peter Eisentraut <peter_e@gmx.net> wrote: > R D writes: > > > 1. If I'm using different password files for > each > > database can I grant or revoke privileges to this > > users without creating the same users in pg_user > > table? > > No, you always have to use CREATE USER. Btw., I > think we're pondering > removing the secondary password file feature > sometime; would you care to > explain why you think you need it? > Hi,I need a secondary passwd files because I do not know another way to manage user access to DBs. But i think user managenent is very clumsy using this method especially if you have over 100 users and over 20 DBs. If there was a pg_shadow in every DB replacing external passwd file, and this authentication method was to be used just by adding something like this in pg_hba.conf file: host all X.X.X.X Y.Y.Y.Y password database or host all X.X.X.X Y.Y.Y.Y crypt database and everything else to be done internaly , it would be very very nice. Or maybe it would be better and more reasonable to use a table in every database with the list of users who can access the database and their privileges on it. I know a lot of people using another DB systems just because of the lack of some features in the security system of PostgreSQL. Here is the list of things they mentioned (I'm on the same opinion): 1. The topic of the above discussion. 2. GRANT/REVOKE creating tables and procedures (and maybe execution on procedures). 3. GRANT/REVOKE privileges on all objects simultaneously just with one query. I think it should appear in the nearest future. Maybe in PostgreSQL 7.1 :-). regards Rumen __________________________________________________ Do You Yahoo!? Send instant messages with Yahoo! Messenger. http://im.yahoo.com/
On Mon, 19 Jun 2000, Nicolas Huillard wrote: > I think all that works in 7.0, not 6.5 ? Correct. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
On Mon, 19 Jun 2000, R D wrote: > Hi,I need a secondary passwd files because I do not > know another way to manage user access to DBs. That's what I thought. > If there was a pg_shadow in every DB replacing external passwd file, Hmm, that would be in semi-violation of SQL, since users are supposed to be global. We've been thinking about making per-database *passwords* for each user, but I don't think this buys a lot. > Or maybe it would be better and more reasonable to use a table in > every database with the list of users who can access the database and > their privileges on it. That could be a problem. Assume you delete all users from that table. How do you get back in and add any? But something like this is clearly needed, it just can't be in the database -- probably pg_hba.conf. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
--- Peter Eisentraut <e99re41@DoCS.UU.SE> wrote: > On Mon, 19 Jun 2000, R D wrote: > > > Hi,I need a secondary passwd files because I do > not > > know another way to manage user access to DBs. > > That's what I thought. > > > If there was a pg_shadow in every DB replacing > external passwd file, > > Hmm, that would be in semi-violation of SQL, since > users are supposed to > be global. We've been thinking about making > per-database *passwords* for > each user, but I don't think this buys a lot. > > > Or maybe it would be better and more reasonable to > use a table in > > every database with the list of users who can > access the database and > > their privileges on it. > > That could be a problem. Assume you delete all users > from that table. How > do you get back in and add any? But something like > this is clearly needed, > it just can't be in the database -- probably > pg_hba.conf. > Ok, But why it should be outside the DBs?!? I think that all user information should be stored comletely inside or completely outside. Any other way of doing this is not reasnonable(put politely). All this information could be stored in some system table like users ;). It's stupid to create user two times, once in database, once in pg_hba.conf or whereever else. What do you think? Am I right? Rumen p.s. Btw what will happen if U delete all users from pg_user? :) __________________________________________________ Do You Yahoo!? Send instant messages with Yahoo! Messenger. http://im.yahoo.com/
peter_e@gmx.net said: > I think we're pondering removing the secondary password file feature > sometime; The current system allows a particular user (in my case http) to connect to the database olny from the unix domain socket and no where else. What ever system is going to replace the secondary passwd file should allow this type of affair. -- +-----------------+------------------------------------------+ | _ ^ _ | Dr. Rodney McDuff | | |\ /|\ /| | Manager, Software Intrastructure, ITS | | \ | / | The University of Queensland | | \ | / | St. Lucia, Brisbane | | \|/ | Queensland, Australia. 4072. | |<-------+------->| TELEPHONE: +61 7 3365 8220 | | /|\ | FACSIMILE: +61 7 3365 7539 | | / | \ | EMAIL: mcduff@its.uq.edu.au | | / | \ | | | |/ \|/ \| | Ex ignorantia ad sapientiam | | - v - | Ex luce ad tenebras | +-----------------+------------------------------------------+