Skip site navigation (1) Skip section navigation (2)

WIP: Column-level Privileges

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: WIP: Column-level Privileges
Date: 2008-09-01 06:38:55
Message-ID: 20080901063855.GJ16005@tamriel.snowman.net (view raw or flat)
Thread:
Lists: pgsql-hackers
Greetings,

  First, a quick digression into what I've gleaned from the spec in
  terms of implementation, from SQL2003:

	-- Table-level grants.  The spec basically says that a table-level
	-- grant is as if the same grant was done on all existing and future
	-- columns. (I looked at GRANT and ALTER ADD COLUMN definitions to
	-- discover that)
    GRANT SELECT, INSERT ON tab1 TO role1;

	-- Column-level grants.  These apply when accessing the column, and
	-- are independent of table-level grants.  If you have a
	-- column-level grant to a table, you can access that column even if
	-- you do not have table-level rights to same table.
    GRANT SELECT (col1, col2), INSERT (col1, col2) ON tab1 TO role2;

    -- Column-level revokes.  Mostly what you would expect, but be aware
	-- that table-level grants 'with admin option' apply when revoking
	-- column-level grants on that table.
    REVOKE SELECT (col1, col2), INSERT (col1, col2) ON tab1 FROM role2;

	-- Table-level revokes.  A table-level revoke of a given privilege
	-- applies to all columns as well.  So if you have INSERT on col1
	-- and I do a 'REVOKE INSERT ON tab1 FROM you;', your column-level
	-- permissions will also be removed.
    REVOKE SELECT, INSERT ON tab1 FROM role1;

  One of the implications from all of this is that we're going to have
  to keep column-level and table-level permissions seperate and
  distinct from each other.  An upshot from this is that it probably
  more closely matches what people expect from how we've handled things
  in the past.  Also, as long as we do table-level checks first, people
  who don't use column-level permissions shouldn't see much of
  performance hit.  It'll be a bit slower on failure cases because it'll
  do per-column checks for ACLs which aren't defined.  I'm on the fence
  as to if that's a big enough concern to warrant a flag in pg_class.

  Attached is the current state of the patch.  The grammer/parser
  changes have been tested and seem to work reasonably well so far.  The
  aclchk.c changes are probably broken at the moment.  I've been
  frustrated with implementing what the spec calls for and what it means
  for the code.  Specifically, I don't like the number of nested loops
  to get the per-privilege column lists into per-column ACL masks, and
  dealing with multiple relations at a time.  I also don't like the
  amount of what seems like mostly duplicated code between the
  table-level permissions handling and the column-level handling, but I
  might be a little pedantic there.  Working the column-level
  permissions into other parts of the code has also proven challenging
  since you need a (Oid,AttrNumber) combination to identify a column
  instead of just an Oid like everything expects.

  I've yet to even start in on the changes necessary to get the column
  information down to the executor, unfortunately.

  Comments welcome, apologies for it not being ready by 9/1.  I'm
  planning to continue working on it tomorrow, and throughout September
  as opportunity allows (ie: when Josh isn't keeping me busy).

  	Thanks,

		Stephen

Attachment: colprivs_wip.20080901.diff.gz
Description: application/octet-stream (13.3 KB)

Responses

pgsql-hackers by date

Next:From: Jaime CasanovaDate: 2008-09-01 06:50:48
Subject: Re: Extending grant insert on tables to sequences
Previous:From: Heikki LinnakangasDate: 2008-09-01 06:29:29
Subject: Re: [PATCHES] VACUUM Improvements - WIP Patch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group