Thread: few more securiry questions :-)

few more securiry questions :-)

From
R D
Date:
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/

Re: few more securiry questions :-)

From
Peter Eisentraut
Date:
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


Re: Re: few more securiry questions :-)

From
Chris Albertson
Date:
> > 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

Re: Re: few more securiry questions :-)

From
R D
Date:
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/

Re: Re: few more securiry questions :-)

From
Peter Eisentraut
Date:
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



Re: Re: few more securiry questions :-)

From
R D
Date:
--- 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

Re: Re: few more securiry questions :-)

From
Peter Eisentraut
Date:
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