[HACKERS] [HACHERS] privilege check: column level only?
Hello I'm one of the Google SoC's students for PostgreSQL. While reading sql92 standard, I found something like this: 11.36 General Rules 3) For every identified privilege descriptor whose action is SELECT, INSERT, UPDATE, or REFERENCES without a column name, privilege descriptors are also created for each column C in O for which A holds the corresponding privilege with grant op- tion. For each such column, a privilege descriptor is created that specifies the identical , the identical , object C, and grantor A. According to this, column privilege descriptors are created automatically while table privilege descriptor is created. Then, while checking privilege, can I JUST check column level privilege? Here is some examples. (1) CREATE TABLE t1 (c1 int, c2 int); GRANT SELECT ON t1 TO grantee; REVOKE SELECT ON t1 (c1) FROM grantee; Now grantee has privilege on t1(c2) but NOT on t1(c1). Although grantee has privilege on t1, he still has no privilege on t1(c1). So checking column privilege is enough. We don't need to check table privilege. (2) CREATE TABLE t1 (c1 int, c2 int); REVOKE SELECT ON t1 FROM grantee; GRANT SELECT ON t1(c2) TO grantee; Here, still, grantee has privilege on t1(c2) but NOT on t1(c1). (Is this right?) Although grantee has no privilege on t1, he can has privilege on t1(c1). Here, again, checking column privilege is enough. Table privilege is useful when you add columns to a table. Whether grantee has privilege on the new columns depends on whether he has privilege on the table. Any and all help and/or comment is appreciated. From sql standard, I found no information on how privilege check should be done. Thanks. Dong -- Guodong Liu Database Lab, School of EECS, Peking University Room 314, Building 42, Peking University, Beijing, 100871, China
Re: [HACKERS] Google SoC: column-level privilege subsystem
Dear August Thank you for your reply. On 4/25/07, August Zajonc <[EMAIL PROTECTED]> wrote: Golden Liu wrote: > 3. Before evaluating a SQL command, check column-level privilege. > This is done AFTER checking table-level privilege. As I mentioned > before, if table-level privilege is granted, it's not necessary to > check column-level privilege. Golden, this sounds good. I'm just a user. It sounds like table || column is the check, so table implies all of columns. ie, revoking a column permission does nothing unless TABLE permission is also revoked. It also might be nice to specify some of the failure / usage modes. ie, how does "SELECT * FROM Students" work if I don't have permission to a column. Return all values except for forbidden ones? How does "SELECT ForbiddenColumn FROM Students" work. For "SELECT * FROM Students", I think this will just raise an error. In PG, if you commit a command like "SELECT * FROM T1, T2" but do not have permission to T2, PG will raise an error. For column, we should do the same thing. "SELECT ForbiddenColumn FROM Students" will raise an error too. For INSERTS, they probably need to fail if you don't have permission to non-null columns. What about columns with default values? Are inserts permitted if you don't have permission to a column with default values? For INSERTS, privilege check will just do on columns specified. For table T with two columns, say C1 and C2, and C2 has a default value. If you just have INSERT permission on C1, this will be right: INSERT INTO T(C1) VALUES (V1) since you just specified C1. But this will raise an error: INSERT INTO T VALUES (V1, default) since you specified C2 which you do not have permission to insert into. Do you have a project page up somewhere? I wouldn't mind helping with some of the documentation for example. Good luck! - August Golden ---(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
[HACKERS] Google SoC: column-level privilege subsystem
Hi all,
I'm one of the Google SoC's students for PostgreSQL. My project is
to implement column-level privilege in PG. Here is a description of my
project. Any and all help and/or comment is appreciated.
Table-level privilege subsystem in PG is now used like this:
GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] tablename [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] tablename [, ...]
FROM { username | GROUP groupname | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
According to this and SQL92 standard, I'll define the grammar of
column-level privilege as follows:
GRANT { { SELECT | INSERT | UPDATE | REFERENCES }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] tablename[ '(' columnname [, ...] ')' ] [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | REFERENCES }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] tablename[ '(' columnname [, ...] ')' ] [, ...]
FROM { username | GROUP groupname | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
According to SQL92, if TABLE privilege is granted to a grantee,
he/she will also get the privilege of ALL columns added to the table.
That is to say, if you commit this command:
GRANT SELECT ON student TO Tom;
Then Tom can select all columns of the student table. This is also
true for UPDATE, INSERT and REFERENCES. So, if Tom can see the name
column of the student table but has no right to see other columns, a
superuser should commit some commands like these:
REVOKE SELECT ON student FROM Tom;
GRANT SELECT ON student(name) TO Tom;
Here is a plan of my project:
1. Modifying the parser for supporting column-level Grant/Revoke
grammar. The grammar is defined as before. This will change gram.y and
some relative data structures.
2. Add codes to record column-level privilege information as
meta-data in system catalog pg_attribute. This will add a column named
'attacl' in pg_attribute. The format of this column is just the same
as 'pg_class.relacl'.
3. Before evaluating a SQL command, check column-level privilege.
This is done AFTER checking table-level privilege. As I mentioned
before, if table-level privilege is granted, it's not necessary to
check column-level privilege.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Unique index: update error
On 9/18/06, Tom Lane <[EMAIL PROTECTED]> wrote: Jim Nasby <[EMAIL PROTECTED]> writes: > On Sep 14, 2006, at 9:16 PM, Golden Liu wrote: >> I try to solve this problem this way: >> First, update the table t but DON'T update the index. >> Next, find all the tuples updated by this command and insert them into >> the unique index. > I suspect that your change adds a non-trivial overhead, which means > we don't want it to be the normal case. There's a bigger problem: begin; update tab set col1 = ... where unique_key = ...; update tab set col2 = ... where unique_key = ...; commit; If the first update doesn't insert index entries into unique_key's index, then the second update won't find the tuples it needs to update (unless we hack the planner to not trust the index as valid ... and then it'd fall back on a seqscan, which is hardly acceptable anyway). The first update DOES insert index entries into unique_key's index. In fact, index entries will be inserted after each command, not each transaction. Is this right? Or should we insert index entries after each transaction? The scheme that I've thought about involves inserting index entries as usual, but instead of having the aminsert code error out immediately upon finding a duplicate, have it make an entry in a list of things that need to be rechecked before commit. This wins as long as potential conflicts are uncommon. Performance could suck if the list gets too large --- but we have more or less the same hazard now for foreign-key checks, and it mostly works well enough. (In fact, maybe the existing deferred trigger event list is the thing to use for the deferred conflict rechecks.) regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Unique index: update error
Suppose there are too tuples in a table t, named
id
---
1
2
and there is a unique index on id. Now we do an update on table t
update t set id=id+1
Since PG executes the update one tuple at a time, it updates tuple "1"
to "2" and insert it into the index. Before insert into the index, it
check whether the id is still "unique" or not. No, it's not, old tuple
"2" is still in the table. So an error is raised.
I try to solve this problem this way:
First, update the table t but DON'T update the index.
Next, find all the tuples updated by this command and insert them into
the unique index.
By doing so, the problem seemed to be solved. My modifications focus
on the function "ExecutePlan". Here is my patch for PG8.1.4.
Tuplestore is used to record all the tuples being updated by this
command.
Is there any problom with it? Thanks.
Index: backend/executor/execMain.c
== =
RCS file: /home/gdliu/cvsroot/postgresql/src/backend/executor/execMain .c,v
retrieving revision 1.1.1.1
diff -c -r1.1.1.1 execMain.c
*** backend/executor/execMain.c 5 Sep 2006 07:19:08 - 1.1.1.1
--- backend/executor/execMain.c 7 Sep 2006 08:28:34 -
***
*** 1068,1073
--- 1068,1092
long current_tuple_count;
TupleTableSlot *result;
+
+ MemoryContext holdCtidContext = NULL;
+ Tuplestorestate *holdCtidStore = NULL;
+ MemoryContext oldcxt = NULL;
+ if(operation == CMD_UPDATE) {
+holdCtidContext =
+ AllocSetContextCreate(CurrentMemoryContext,
+ "HoldUpdateCTIDContext",
+ ALLOCSET_DEFAULT_MINSIZE,
+ ALLOCSET_DEFAULT_INITSIZE,
+ ALLOCSET_DEFAULT_MAXSIZE);
+
+oldcxt = MemoryContextSwitchTo(holdCtidContext);
+holdCtidStore = tuplestore_begin_heap(false, false, work_mem);
+MemoryContextSwitchTo(oldcxt);
+ }
+
/*
* initialize local variables
*/
***
*** 1287,1293
--- 1306,1319
break;
case CMD_UPDATE:
ExecUpdate(slot, tupleid, estate);
+
+ oldcxt = MemoryContextSwitchTo(holdCtidContext);
+ slot->tts_tuple->t_data->t_ ctid = slot->tts_tuple->t_self;
+tuplestore_puttuple(holdCtidStore,slot->tts_tuple);
+ MemoryContextSwitchTo(oldcxt);
result = NULL;
break;
***
*** 1308,1313
--- 1334,1372
break;
}
+
+ //insert index
+ if(operation == CMD_UPDATE &&
+ estate->es_result_relation_info->ri_NumIndices > 0) {
+HeapTuple tuple = NULL;
+bool should_free = false;
+
+oldcxt = MemoryContextSwitchTo(holdCtidContext);
+
+tuplestore_rescan(holdCtidStore);
+for(;;) {
+ tuple = (HeapTuple)tuplestore_gettuple(holdCtidStore,true,&shoul d_free);
+ if(!tuple)
+ break;
+ tuple->t_self = tuple->t_data->t_ctid;
+ ItemPointerSetInvalid(&(tuple->t_data-> ;t_ctid));
+ if(!ItemPointerIsValid(&(tuple->t_self))) {
+ elog(ERROR, "Insert Index: ctid is invalid.");
+ }
+ ExecStoreTuple(tuple, slot, InvalidBuffer, false);
+ ExecInsertIndexTuples(slot, &(tuple->t_self), estate, false);
+ if(should_free)
+ pfree(tuple);
+}
+tuplestore_end(holdCtidStore);
+MemoryContextSwitchTo(holdCtidStore);
+holdCtidContext->methods->delete(holdCtidContext);
+ }
+ //*/
/*
* Process AFTER EACH STATEMENT triggers
*/
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] UPDATE/DELETE XXX WHERE CURRENT OF cursor_name
Updateable cursors are used as follows: begin; declare foo cursor for select * from bar for update; fetch foo; update bar set abc='def' where current of foo; fetch foo; delete from bar where current of foo; commit; PostgreSQL doesn't support this feature now ( 8.1.4). Will PGSQL support it recently? Does anyone work on this? thanks Golden 7.24 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
