Thread: few more securiry questions :-)
Thanks! But this answers gave rise to some new
questions.
1.I think it will be good pgSQL to accept the
"GRANT ALL ON * TO SOMEUSER;" and
"REVOKE ALL ON * FROM SOMEUSER;" so are there
any plans in implementing this? Does anybody knows?
2.Why the authentication system is not integrated in
the system databases of pgSQL,like in MySQL for
example?
3.Are there any plans in making security system more
flexible?
regards:
Rumen
--- Peter Eisentraut <peter_e@gmx.net> wrote:
> R D writes:
>
> > 1. How can I forbid to some users to create
> tables
> > in some databases which they can acccess.
>
> You can't. Working on that ...
>
> > 2. How can I GRANT/REJECT some privileges on all
> > objects in a database TO/FROM some users, since i
> > can't type "GRANT ALL ON * TO SOMEUSER;" in PgSQL.
> Is
> > there any functional analog?
>
> You can't internally. You can read the list of all
> tables from the
> pg_class system catalog and have your application
> issue the command GRANT
> x ON table1, table2, table3, ... TO y.
>
> > 3. How can I reject to some users connections to
> > some databases from any host using password
> > authentication?
>
> You can create a separate password file for the
> databases and only list
> the users you want in that password file. The syntax
> for this is
> `... password filename' in pg_hba.conf. See also the
> pg_passwd command for
> making password files.
>
> > 4. Why this pg_hba.conf does not alow uses from
> > 192.168.200.X to connect to the databases with
> message
> > telling that there was no entry for 192.168.200.x
> in
> > pg_hba.conf?
> > # pg_hba.conf
> >
> > local all trust
> > host all 0.0.0.0 0.0.0.0
> reject
> > host all 192.168.200.0 255.255.255.0
> password
>
> Because a mask of 0.0.0.0 matches every host, so the
> reject kicks in. The
> logic here is that
>
> ({host entry} XOR {actual host}) AND {mask entry}
>
> must be 0 for a record to match.
>
>
> --
> Peter Eisentraut Sernanders v�g
> 10:115
> peter_e@gmx.net 75262 Uppsala
> http://yi.org/peter-e/ Sweden
>
__________________________________________________
Do You Yahoo!?
Send instant messages & get email alerts with Yahoo! Messenger.
http://im.yahoo.com/
R D writes: > 1.I think it will be good pgSQL to accept the > "GRANT ALL ON * TO SOMEUSER;" and > "REVOKE ALL ON * FROM SOMEUSER;" so are there > any plans in implementing this? Does anybody knows? I think so too. We'll take it under advisement. > 2.Why the authentication system is not integrated in > the system databases of pgSQL,like in MySQL for > example? Because there's a bootstrapping problem: You would have to connect to the database to change the authentication settings, but in order to connect you have to be authenticated. > 3.Are there any plans in making security system more > flexible? Sure. I'm planning some work on that very soon. Any suggestions you might want to contribute? -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
> > 2.Why the authentication system is not integrated in > > the system databases of pgSQL,like in MySQL for > > example? > > Because there's a bootstrapping problem: You would have to connect to the > database to change the authentication settings, but in order to connect > you have to be authenticated. That's not to hard to fix. You just have initdb create system catalogs with a the required settings. -- Chris Albertson calbertson@logicon.com Voice: 626-351-0089 x17 Logicon, Pasadena California Fax: 626-351-0699
Hi, > > 2.Why the authentication system is not integrated > in > > the system databases of pgSQL,like in MySQL for > > example? > > Because there's a bootstrapping problem: You would > have to connect to the > database to change the authentication settings, but > in order to connect > you have to be authenticated. > I Can't pealize the problem, First you have to use the authentication settings made before to connect and modify them and they will take effect on the next connection. For example when you connect to UNIX machine AAA via telnet from host BBB and modify hostst.deny and hosts.allow of AAA in order to deny connnections from BBB. In this case all connections from BBB will not be terminated but new ones will not be accepted. > > 3.Are there any plans in making security system > more > > flexible? > > Sure. I'm planning some work on that very soon. Any > suggestions you might > want to contribute? > Here are some points of interest for me. 1. To allow or deny to user to create tables views and etc. This could be done with CREATE privilege.I know there is a work on this. 2. To allow or deny to users to grant the privileges they have to others. For example: "GRANT ALL ON mytable TO someuser WITH GRANT OPTION;" 3. Is there any way to allow or deny executing the procedures? I think pgSQL needs something like: "GRANT EXECUTE ON someprocedure to SOMEUSER; 4. To allow or deny some users to connect to some databases just by modyfying the system database using SQL queries.(This is connected with the integration of the authentication system in the pgSQL system databases).according to me the method used now is somewhat clumsy and old-fasioned. Best regards: Rumen __________________________________________________ Do You Yahoo!? Send instant messages & get email alerts with Yahoo! Messenger. http://im.yahoo.com/
Chris Albertson writes: > > > 2.Why the authentication system is not integrated in > > > the system databases of pgSQL,like in MySQL for > > > example? > > > > Because there's a bootstrapping problem: You would have to connect to the > > database to change the authentication settings, but in order to connect > > you have to be authenticated. > > That's not to hard to fix. You just have initdb create system catalogs > with a the required settings. But when you accidentally hose things up then you have no way to get in a repair things. It also looks like a race condition waiting to happen. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
--- Peter Eisentraut <peter_e@gmx.net> wrote: > Chris Albertson writes: > > > > > 2.Why the authentication system is not > integrated in > > > > the system databases of pgSQL,like in MySQL > for > > > > example? > > > > > > Because there's a bootstrapping problem: You > would have to connect to the > > > database to change the authentication settings, > but in order to connect > > > you have to be authenticated. > > > > That's not to hard to fix. You just have initdb > create system catalogs > > with a the required settings. > > But when you accidentally hose things up then you > have no way to get in a > repair things. It also looks like a race condition > waiting to happen. > OK! Bit I think there could be a solution for this. You can inplement some option in the postmaster to trust to some user from localhost, for example: "postmaster --trust postgres &" this should give you a root acess to dbs from user postgres ;-) and there should no be problem to repair the things (if you know what you are doing ofcourse :-)). regards, Rumen __________________________________________________ Do You Yahoo!? Yahoo! Photos -- now, 100 FREE prints! http://photos.yahoo.com
R D writes: > You can inplement some option in the postmaster to > trust to some user from localhost, for example: > "postmaster --trust postgres &" But the problem is that there's no way to know by what user a given connection is actually initiated -- unless you use some authentication mechanism. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden