Обсуждение: Regrading TODO item alerting pg_hba.conf from SQL
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.
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.
... >> 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
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.
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
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 :)
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.
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
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
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 >
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
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.