Thread: Regrading TODO item alerting pg_hba.conf from SQL

Regrading TODO item alerting pg_hba.conf from SQL

From
Gevik Babakhani
Date:
Folks,

I would like to start a discussion regarding the TODO item "%Allow
pg_hba.conf settings to be controlled via SQL"

In the past threads we have been talking about some of the options of
how this could be done. I would like to discuss the following items to
get more clarity.

1. What do we think about removing the pg_hba.conf functionality keeping
the connection information in a table. This would mean no more
pg_hba.conf and when something goes wrong with the table, pg has to be
started in standalone mode to correct the errors. 

I understand some would disagree with the option above regarding the
security. Reading the past threads I have to reconsider my thoughts.
I do not thing it would make it less secure to implement the option
above. But of course this is very much debatable.

1.1 For the backward compatibility reasons we could auto import the
exiting pg_hba.conf file once and then discard/rename/delete it.

2. What do we think about the SQL command to be. Would it be like the
following or another syntax. 

GRANT CONNECTION [LOCAL | HOST | HOSTSSL | HOSTNOSSL ] ON [ ALL | mydatabase1 ]TO [ ALL | user1,user2,user3 ]FROM
127.0.0.1/32METHOD[ TRUST | REJECT | MD5 ...... ]
 

3. Could someone clarify the design decisions regarding pg_hba.conf
file? Why was it done the why it is today? (Tom? Bruce?)


As suggested in the DEV_FAQ I would like to gather as much information
as possible before doing anything.

Regards,
Gevik.






Re: Regrading TODO item alerting pg_hba.conf from SQL

From
Martijn van Oosterhout
Date:
On Sun, Apr 16, 2006 at 01:08:36PM +0200, Gevik Babakhani wrote:
> Folks,
>
> I would like to start a discussion regarding the TODO item "%Allow
> pg_hba.conf settings to be controlled via SQL"

<snip>

> 1. What do we think about removing the pg_hba.conf functionality keeping
> the connection information in a table. This would mean no more
> pg_hba.conf and when something goes wrong with the table, pg has to be
> started in standalone mode to correct the errors.

Why does it have to be one or the other? While in-database is nice,
having it as a seperate file makes central administration easier
because you can just distribute files. You can also easily compare two
sites to see how the rules differ.

You also have to think about sites that perform maintainence. Currently
they just replace the file and SIGHUP. When the maintainence is
complete, put the file back and SIGHUP again. This is a feature hard to
replicate with in database stuff.

> 2. What do we think about the SQL command to be. Would it be like the
> following or another syntax.
>
> GRANT
>     CONNECTION [LOCAL | HOST | HOSTSSL | HOSTNOSSL ]
>     ON [ ALL | mydatabase1 ]
>     TO [ ALL | user1,user2,user3 ]
>     FROM 127.0.0.1/32
>     METHOD [ TRUST | REJECT | MD5 ...... ]

Apart from the complaint that this makes no attempt to take care of the
fact that entires in pg_hba.conf are order sensetive. Where is that
found in this syntax? What about pg_ident.conf?

> 3. Could someone clarify the design decisions regarding pg_hba.conf
> file? Why was it done the why it is today? (Tom? Bruce?)

Not sure if there was a design. It was created at some point and
evolved.

Now, to just suggest something I've been thinking of. Maybe a way of
thinking about it is similar to firewall chains in linux. You keep
pg_hba.conf but allow it to refer to a new auth type "chain blah". Then
you layer your above grant syntax into those chains. This allow people
to switch between different auth methods quickly by switching files,
while allowing people who want to do everything in the database can do
so too.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Re: Regrading TODO item alerting pg_hba.conf from SQL

From
Tino Wildenhain
Date:
...
>> 2. What do we think about the SQL command to be. Would it be like the
>> following or another syntax. 
>>
>> GRANT 
>>     CONNECTION [LOCAL | HOST | HOSTSSL | HOSTNOSSL ] 
>>     ON [ ALL | mydatabase1 ]
>>     TO [ ALL | user1,user2,user3 ]
>>     FROM 127.0.0.1/32
>>     METHOD [ TRUST | REJECT | MD5 ...... ]
> 
> Apart from the complaint that this makes no attempt to take care of the
> fact that entires in pg_hba.conf are order sensetive. Where is that
> found in this syntax? What about pg_ident.conf?

there is actually no proof of the current order depency is really
a good idea. Other access lists work without that constraint.

>> 3. Could someone clarify the design decisions regarding pg_hba.conf
>> file? Why was it done the why it is today? (Tom? Bruce?)
> 
> Not sure if there was a design. It was created at some point and
> evolved.

Maybe now we can do a real design? No need to continue on the wrong
path (if it is wrong).

> Now, to just suggest something I've been thinking of. Maybe a way of
> thinking about it is similar to firewall chains in linux. You keep
> pg_hba.conf but allow it to refer to a new auth type "chain blah". Then

not that "chains" are the only and the best solution to firewall rules
out there :-)

> you layer your above grant syntax into those chains. This allow people
> to switch between different auth methods quickly by switching files,
> while allowing people who want to do everything in the database can do
> so too.

Even with in database rules only you can do the switches - you remove
all entries, keeping your current connection and then bring them
back when you are ready. Just a matter of some SQL commands in a script.

Kind regards
Tino


Re: Regrading TODO item alerting pg_hba.conf from SQL

From
Martijn van Oosterhout
Date:
On Sun, Apr 16, 2006 at 03:37:42PM +0200, Tino Wildenhain wrote:
> > Apart from the complaint that this makes no attempt to take care of the
> > fact that entires in pg_hba.conf are order sensetive. Where is that
> > found in this syntax? What about pg_ident.conf?
>
> there is actually no proof of the current order depency is really
> a good idea. Other access lists work without that constraint.

For something that may not be a good idea, it's awfully popular.
Firewall chains, tcpwrappers (hosts.allow), SSH config to name but a
few. I think you need to demonstrate that an order independant solution
is at least as flexble. Even network routing tables, being a common
case where order doesn't matter, get layered into tables with an order
to deal with complex routing setups.

I don't think you can get away from having an order because you have
several attributes all equally important (connection type
(local/remote), source (ip), ssl(yes/no), database name, user name).
How do you decide which to use if multiple match?

> > you layer your above grant syntax into those chains. This allow people
> > to switch between different auth methods quickly by switching files,
> > while allowing people who want to do everything in the database can do
> > so too.
>
> Even with in database rules only you can do the switches - you remove
> all entries, keeping your current connection and then bring them
> back when you are ready. Just a matter of some SQL commands in a script.

But I think you need to keep the concept of "commenting out". Disabling
a rule without removing it. And you havn't dealt with the central
administration aspect (farm out config files).

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Re: Regrading TODO item alerting pg_hba.conf from SQL

From
Tom Lane
Date:
Martijn van Oosterhout <kleptog@svana.org> writes:
>> there is actually no proof of the current order depency is really
>> a good idea. Other access lists work without that constraint.

> For something that may not be a good idea, it's awfully popular.

Didn't we have this entire discussion a month ago?

I don't think there would be any objection to adding a database-level
CONNECT privilege that's checked inside the database, *after* the
existing pg_hba.conf mechanism.  That requires no new concepts: we
already have databases and privilege bits for them.  If the default is
to grant CONNECT to PUBLIC then the behavior is backward-compatible, and
people can use the privilege, pg_hba.conf, or a combination to control
access.  (Might be best to call it USAGE so we don't need to create a
new reserved word, but that's a minor detail.)

Eliminating pg_hba.conf altogether is a much harder sell, because you'd
have to prove that you're not giving up any functionality, and quite
frankly I don't think you can prove that.  (Arguing that people don't
need the functionality you can't provide is not going to carry the day.)
In any case it would force a lot of relearning on DBAs, and there will
be push-back just because of that.  I'm also not pleased with adding a
bunch of concepts that are not even part of the SQL world (eg, SSL,
Unix-domain connections) into GRANT.
        regards, tom lane


Re: Regrading TODO item alerting pg_hba.conf from SQL

From
Gevik Babakhani
Date:
On Sun, 2006-04-16 at 11:48 -0400, Tom Lane wrote:

> I don't think there would be any objection to adding a database-level
> CONNECT privilege that's checked inside the database, *after* the
> existing pg_hba.conf mechanism.  That requires no new concepts: we
> already have databases and privilege bits for them.  If the default is
> to grant CONNECT to PUBLIC then the behavior is backward-compatible, and
> people can use the privilege, pg_hba.conf, or a combination to control
> access.  (Might be best to call it USAGE so we don't need to create a
> new reserved word, but that's a minor detail.)

Tom, could you please provide more insight of how you see this taking
shape. I am sure your vote counts heavy on this. How would you suggest
the SQL syntax be like for example.

> Eliminating pg_hba.conf altogether is a much harder sell, because you'd
> have to prove that you're not giving up any functionality, and quite
> frankly I don't think you can prove that.  (Arguing that people don't
> need the functionality you can't provide is not going to carry the day.)
> In any case it would force a lot of relearning on DBAs, and there will
> be push-back just because of that.  I'm also not pleased with adding a
> bunch of concepts that are not even part of the SQL world (eg, SSL,
> Unix-domain connections) into GRANT.
> 

Of course, there are many legitimate reasons why the existing
pg_hba.conf should be left alone. There is no arguing in that. I just
wanted to make sure where the sirs stand :)



Re: Regrading TODO item alerting pg_hba.conf from SQL

From
Martijn van Oosterhout
Date:
On Sun, Apr 16, 2006 at 08:34:10PM +0200, Gevik Babakhani wrote:
> On Sun, 2006-04-16 at 11:48 -0400, Tom Lane wrote:
>
> > I don't think there would be any objection to adding a database-level
> > CONNECT privilege that's checked inside the database, *after* the
> > existing pg_hba.conf mechanism.  That requires no new concepts: we
> > already have databases and privilege bits for them.  If the default is
> > to grant CONNECT to PUBLIC then the behavior is backward-compatible, and
> > people can use the privilege, pg_hba.conf, or a combination to control
> > access.  (Might be best to call it USAGE so we don't need to create a
> > new reserved word, but that's a minor detail.)
>
> Tom, could you please provide more insight of how you see this taking
> shape. I am sure your vote counts heavy on this. How would you suggest
> the SQL syntax be like for example.

I think we went over this last thread. The basic idea would be:

REVOKE CONNECT ON DATABASE foo FROM PUBLIC;
GRANT CONNECT ON DATABASE foo TO user1, user2, user3;

The default would be to grant CONNECT to public to retain backwards
compatability. So you revoke that then GRANT the priveledge to the
users and/or roles who can connect.

For simple systems then you could have a short pg_hba.conf to limit the
IP addresses users can connect on, and the DB stores what databases
they have access to...
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Re: Regrading TODO item alerting pg_hba.conf from SQL

From
Tom Lane
Date:
Gevik Babakhani <pgdev@xs4all.nl> writes:
> On Sun, 2006-04-16 at 11:48 -0400, Tom Lane wrote:
>> I don't think there would be any objection to adding a database-level
>> CONNECT privilege that's checked inside the database, *after* the
>> existing pg_hba.conf mechanism.

> Tom, could you please provide more insight of how you see this taking
> shape.

It doesn't seem particularly complicated: inside the connection-startup
transaction done by InitPostgres, you could check to make sure the
selected user has the CONNECT privilege on the selected database.
[ looks at code... ]  Actually ReverifyMyDatabase is the right place,
since it already has its hands on the pg_database row.  You don't want
this to cost an extra pg_database search during startup.

If you use the normal definition of privilege checking, superusers
would always pass the test, which seems fine to me.  (Without that,
you'd need some special exception for standalone mode, to provide
a recovery path from DBA mistakes like revoking connect privilege
from everyone on all databases.  autovacuum needs to be immune
from the check too.)

> How would you suggest the SQL syntax be like for example.

Just another privilege name in the existing GRANT/REVOKE ON DATABASE
syntax.
        regards, tom lane


Re: Regrading TODO item alerting pg_hba.conf from SQL

From
Tom Lane
Date:
Martijn van Oosterhout <kleptog@svana.org> writes:
> For simple systems then you could have a short pg_hba.conf to limit the
> IP addresses users can connect on, and the DB stores what databases
> they have access to...

Right, you'd still have a pg_hba.conf, but it would hopefully be short
and sweet, not doing much more than listing which addresses you want
to allow connections from and what the authentication mechanisms ought
to be.
        regards, tom lane


Re: Regrading TODO item alerting pg_hba.conf from SQL

From
Gevik Babakhani
Date:
Thank you very much :) :)

On Sun, 2006-04-16 at 17:08 -0400, Tom Lane wrote:
> Gevik Babakhani <pgdev@xs4all.nl> writes:
> > On Sun, 2006-04-16 at 11:48 -0400, Tom Lane wrote:
> >> I don't think there would be any objection to adding a database-level
> >> CONNECT privilege that's checked inside the database, *after* the
> >> existing pg_hba.conf mechanism.
> 
> > Tom, could you please provide more insight of how you see this taking
> > shape.
> 
> It doesn't seem particularly complicated: inside the connection-startup
> transaction done by InitPostgres, you could check to make sure the
> selected user has the CONNECT privilege on the selected database.
> [ looks at code... ]  Actually ReverifyMyDatabase is the right place,
> since it already has its hands on the pg_database row.  You don't want
> this to cost an extra pg_database search during startup.
> 
> If you use the normal definition of privilege checking, superusers
> would always pass the test, which seems fine to me.  (Without that,
> you'd need some special exception for standalone mode, to provide
> a recovery path from DBA mistakes like revoking connect privilege
> from everyone on all databases.  autovacuum needs to be immune
> from the check too.)
> 
> > How would you suggest the SQL syntax be like for example.
> 
> Just another privilege name in the existing GRANT/REVOKE ON DATABASE
> syntax.
> 
>             regards, tom lane
> 



Re: Regrading TODO item alerting pg_hba.conf from SQL

From
"Sander Steffann"
Date:
Hi,

Tom Lane writes:
> Martijn van Oosterhout <kleptog@svana.org> writes:
>> For simple systems then you could have a short pg_hba.conf to limit the
>> IP addresses users can connect on, and the DB stores what databases
>> they have access to...
>
> Right, you'd still have a pg_hba.conf, but it would hopefully be short
> and sweet, not doing much more than listing which addresses you want
> to allow connections from and what the authentication mechanisms ought
> to be.

From another message from Tom:
>> How would you suggest the SQL syntax be like for example.
>
> Just another privilege name in the existing GRANT/REVOKE ON DATABASE
> syntax.

Sounds like a good idea to me. Make pg_hba.conf simpler and administered by 
the admin, and give the database owner the tools to decide who gets to 
connect.

Nice!
Sander




Re: Regrading TODO item alerting pg_hba.conf from SQL

From
Gevik Babakhani
Date:
Hello again :)

> It doesn't seem particularly complicated: inside the connection-startup
> transaction done by InitPostgres, you could check to make sure the
> selected user has the CONNECT privilege on the selected database.
> [ looks at code... ]  Actually ReverifyMyDatabase is the right place,
> since it already has its hands on the pg_database row.  You don't want
> this to cost an extra pg_database search during startup.
> 

I looked at the code as suggested. (so far many questions but I guess
all in right time) 

Anyway... would it be correct to start any connection checking 
inside the if statement from line 186 "if (IsUnderPostmaster && !
IsAutoVacuumProcess())" in method ReverifyMyDatabase? Sorry for being
too trivial, but I want to be sure.

Regards,
Gevik.