Обсуждение: TODO item pg_hba.conf
Hi, I read the discussion thread once again and unless I am absolutely and totally on the wrong track this is what I understood from the general plan to be. The current pg_hba.conf provides the famous the host based mechanism to connect to a database. In order to add the discussed functionality we want to hold the CONNECT permission information inside a table in the database (something like pg_connect). The parser has to be changed in order to understand the new grant and revoke and of course the appropriate backend commands have to be developed to store/check/remove the new privilege. The SQL command could be something like this: REVOKE CONNECT ON DATABASE foo FROM PUBLIC; GRANT CONNECT ON DATABASE foo TO user1, user2, user3; There are some other important details but I will discuss them later. Would it be correct to state that: only the authentication is checked (username and password) when connecting to the server and not the any kind of privilege to access a database. Please see postmaster.c:2753 Which brings us to the real work to be done as suggested by Tom in postinit.c:143 ReverifyMyDatabase(const char *name). Please advice. Gevik.
"Gevik Babakhani" <pgdev@xs4all.nl> writes:
> Would it be correct to state that: only the authentication
> is checked (username and password) when connecting to the
> server and not the any kind of privilege to access a database.
Well, that would be the typical usage, ie, people relying on CONNECT
privilege probably wouldn't put any database-specific conditions into
pg_hba.conf. But we'd not take out any functionality that's there now.
I'm not sure if you realize it, but this should be an extremely small
patch. In particular, if you think you need to change the parser then
you are already off on the wrong track. The parser doesn't know
anything about specific privilege types (as of 8.1 anyway). It'd be
worth your while to study how the existing privileges on databases
are handled, eg, exactly what places know about the TEMP privilege.
regards, tom lane
On Thu, 2006-04-20 at 14:14 -0400, Tom Lane wrote: > "Gevik Babakhani" <pgdev@xs4all.nl> writes: > > Would it be correct to state that: only the authentication > > is checked (username and password) when connecting to the > > server and not the any kind of privilege to access a database. > > Well, that would be the typical usage, ie, people relying on CONNECT > privilege probably wouldn't put any database-specific conditions into > pg_hba.conf. But we'd not take out any functionality that's there now. > Of course. > I'm not sure if you realize it, but this should be an extremely small > patch. In particular, if you think you need to change the parser then > you are already off on the wrong track. The parser doesn't know > anything about specific privilege types (as of 8.1 anyway). It'd be > worth your while to study how the existing privileges on databases > are handled, eg, exactly what places know about the TEMP privilege. To study the existing privileges is the first thing on my list. Because I am new to this, it is sometimes difficult to know what is already there, and what is possible or not. Your advice in GOLD. Thank you :)
Gevik Babakhani wrote: > > I'm not sure if you realize it, but this should be an extremely small > > patch. In particular, if you think you need to change the parser then > > you are already off on the wrong track. The parser doesn't know > > anything about specific privilege types (as of 8.1 anyway). It'd be > > worth your while to study how the existing privileges on databases > > are handled, eg, exactly what places know about the TEMP privilege. > > To study the existing privileges is the first thing on my list. Because > I am new to this, it is sometimes difficult to know what is already > there, and what is possible or not. Your advice in GOLD. Thank you :) This is how a GRANT/REVOKE works: 1. the command is parsed by the parser (parser/gram.y) 2. a node is created, type GrantStmt 3. the node is picked up by the traffic cop (tcop/utility.c) 4. It's passed to ExecuteGrantStmt (commands/aclchk.c) 5. It's converted to internal form and passed to ExecGrant_Database Notice the handling of "WITH GRANT OPTION", and observe that the test is "reversed" on REVOKE, which seems awfully unintuitive. It should be easy to make this code understand a new privilege type. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Cool :) Thank you :) On Thu, 2006-04-20 at 15:05 -0400, Alvaro Herrera wrote: > Gevik Babakhani wrote: > > > > I'm not sure if you realize it, but this should be an extremely small > > > patch. In particular, if you think you need to change the parser then > > > you are already off on the wrong track. The parser doesn't know > > > anything about specific privilege types (as of 8.1 anyway). It'd be > > > worth your while to study how the existing privileges on databases > > > are handled, eg, exactly what places know about the TEMP privilege. > > > > To study the existing privileges is the first thing on my list. Because > > I am new to this, it is sometimes difficult to know what is already > > there, and what is possible or not. Your advice in GOLD. Thank you :) > > This is how a GRANT/REVOKE works: > > 1. the command is parsed by the parser (parser/gram.y) > 2. a node is created, type GrantStmt > 3. the node is picked up by the traffic cop (tcop/utility.c) > 4. It's passed to ExecuteGrantStmt (commands/aclchk.c) > 5. It's converted to internal form and passed to ExecGrant_Database > > Notice the handling of "WITH GRANT OPTION", and observe that the test is > "reversed" on REVOKE, which seems awfully unintuitive. > > It should be easy to make this code understand a new privilege type. >
Alvaro Herrera <alvherre@commandprompt.com> writes:
> It should be easy to make this code understand a new privilege type.
Another point worth making: most of the actual patch will probably
consist of teaching the ACL datatype code about another possible
bit-value in ACL masks. A lot of the generic GRANT/REVOKE code
just mashes privilege bit-masks and doesn't much care what the
individual bit meanings are.
regards, tom lane
Added to TODO:
o %Allow per-database permissions to be set via GRANT
Allow database connection checks based on GRANT rules in addition to the existing access checks in
pg_hba.conf.
and remove:
o %Allow pg_hba.conf settings to be controlled via SQL
This would add a function to load the SQL table from pg_hba.conf, and one to writes its contents to the
flatfile. The table should have a line number that is a float so rows can be inserted between existing
rows,e.g. row 2.5 goes between row 2 and row 3.
---------------------------------------------------------------------------
Gevik Babakhani wrote:
> Hi,
>
> I read the discussion thread once again and unless I am absolutely
> and totally on the wrong track this is what I understood from the
> general plan to be. The current pg_hba.conf provides the famous
> the host based mechanism to connect to a database.
> In order to add the discussed functionality we want to hold
> the CONNECT permission information inside a table in
> the database (something like pg_connect).
>
> The parser has to be changed in order to understand the new grant
> and revoke and of course the appropriate backend commands have to
> be developed to store/check/remove the new privilege.
>
> The SQL command could be something like this:
>
> REVOKE CONNECT ON DATABASE foo FROM PUBLIC;
> GRANT CONNECT ON DATABASE foo TO user1, user2, user3;
>
> There are some other important details but I will discuss them later.
>
> Would it be correct to state that: only the authentication
> is checked (username and password) when connecting to the
> server and not the any kind of privilege to access a database.
> Please see postmaster.c:2753 Which brings us to the real
> work to be done as suggested by Tom
> in postinit.c:143 ReverifyMyDatabase(const char *name).
>
> Please advice.
> Gevik.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
-- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +