## Re: column level privileges

From: Andrew Dunstan sanjay sharma PostgreSQL-development Re: column level privileges 2008-04-03 00:34:27 47F42613.8000001@dunslane.net (view raw or flat) 2008-04-01 22:40:24 from Andrew Dunstan  2008-04-02 01:48:34 from sanjay sharma   2008-04-02 02:02:30 from Andrew Dunstan    2008-04-03 00:21:42 from sanjay sharma     2008-04-03 00:34:27 from Andrew Dunstan  2008-04-05 05:02:55 from "Jaime Casanova"   2008-04-05 13:32:16 from Andrew Dunstan pgsql-hackers
```Postgres does not backport features, so you would need to retrofit the
patch to 8.3 yourself, or pay / persuade somebody else to do that for
you. That should not be too hard, as it was in fact developed late in
the 8.3 cycle.

Before you jump on it as suiting your needs, read carefully. In
particular, take note of the fact that it is SQL92 privileges, which
specifically do NOT include SELECT restrictions.

cheers

andrew

sanjay sharma wrote:
> It would be great help to me, and I am sure for many other people too
> who are working with security solutions, if this feature is released
> as patch before 8.4 release.
>
> Sanjay Sharma
>
> > Date: Tue, 1 Apr 2008 22:02:30 -0400
> > From: andrew(at)dunslane(dot)net
> > To: sanksh(at)hotmail(dot)com
> > CC: pgsql-hackers(at)postgresql(dot)org
> > Subject: Re: [HACKERS] column level privileges
> >
> >
> >
> > The earliest will be 8.4, which is many many months away.
> >
> > It should be possible to produce a patch for 8.3 if you're interested.
> >
> > cheers
> >
> > andrew
> >
> > sanjay sharma wrote:
> > > Hello Andrew,
> > >
> > > When do you expect this patch to go in production and available for
> > > public use? I would keep an eye for its release.
> > >
> > > Sanjay Sharma
> > >
> > > > Date: Tue, 1 Apr 2008 18:40:24 -0400
> > > > From: andrew(at)dunslane(dot)net
> > > > To: pgsql-hackers(at)postgresql(dot)org
> > > > Subject: [HACKERS] column level privileges
> > > >
> > > >
> > > > Apologies if this gets duplicated - original seems to have been
> dropped
> > > > due to patch size - this time I am sending it gzipped.
> > > >
> > > > cheers
> > > >
> > > > andrew
> > > >
> > > > -------- Original Message --------
> > > > Subject: column level privileges
> > > > Date: Tue, 01 Apr 2008 08:32:25 -0400
> > > > From: Andrew Dunstan <andrew(at)dunslane(dot)net>
> > > > To: Patches (PostgreSQL) <pgsql-patches(at)postgresql(dot)org>
> > > >
> > > >
> > > >
> > > > This patch by Golden Lui was his work for the last Google SoC. I
> was
> > > his
> > > > mentor for the project. I have just realised that he didn't send his
> > > > final patch to the list.
> > > >
> > > > I guess it's too late for the current commit-fest, but it really
> needs
> > > > to go on a patch queue (my memory on this was jogged by Tom's recent
> > > > mention of \$Subject).
> > > >
> > > > I'm going to see how much bitrot there is and see what changes are
> > > > necessary to get it to apply.
> > > >
> > > > cheers
> > > >
> > > > andrew
> > > >
> > > >
> > > > -------------
> > > > Here is a README for the whole patch.
> > > >
> > > > According to the SQL92 standard, there are four levels in the
> privilege
> > > > hierarchy, i.e. database, tablespace, table, and column. Most
> > > commercial
> > > > DBMSs support all the levels, but column-level privilege is hitherto
> > > > unaddressed in the PostgreSQL, and this patch try to implement it.
> > > >
> > > > What this patch have done:
> > > > 1. The execution of GRANT/REVOKE for column privileges. Now only
> > > > INSERT/UPDATE/REFERENCES privileges are supported, as SQL92
> specified.
> > > > SELECT privilege is now not supported. This part includes:
> > > > 1.1 Add a column named 'attrel' in pg_attribute catalog to store
> > > > column privileges. Now all column privileges are stored, no matter
> > > > whether they could be implied from table-level privilege.
> > > > 1.2 Parser for the new kind of GRANT/REVOKE commands.
> > > > 1.3 Execution of GRANT/REVOKE for column privileges. Corresponding
> > > > column privileges will be added/removed automatically if no
> column is
> > > > specified, as SQL standard specified.
> > > > 2. Column-level privilege check.
> > > > Now for UPDATE/INSERT/REFERENCES privilege, privilege check will be
> > > > done ONLY on column level. Table-level privilege check was done
> in the
> > > > function InitPlan. Now in this patch, these three kind of
> privilege are
> > > > checked during the parse phase.
> > > > 2.1 For UPDATE/INSERT commands. Privilege check is done in the
> > > > function transformUpdateStmt/transformInsertStmt.
> > > > 2.2 For REFERENCES, privilege check is done in the function
> > > > ATAddForeignKeyConstraint. This function will be called whenever a
> > > > foreign key constraint is added, like create table, alter table,
> etc.
> > > > 2.3 For COPY command, INSERT privilege is check in the function
> > > > DoCopy. SELECT command is checked in DoCopy too.
> > > > 3. While adding a new column to a table using ALTER TABLE
> command, set
> > > > appropriate privilege for the new column according to privilege
> > > > granted on the table.
> > > > 4. Allow pg_dump and pg_dumpall to dump in/out column privileges.
> > > > 5. Add a column named objsubid in pg_shdepend catalog to record ACL
> > > > dependencies between column and roles.
> > > > 6. modify the grammar of ECPG to support column level privileges.
> > > > 7. change psql's \z (\dp) command to support listing column
> privileges
> > > > for tables and views. If \z(\dp) is run with a pattern, column
> > > > privileges are listed after table level privileges.
> > > > 8. Regression test for column-level privileges. I changed both
> > > > privileges.sql and expected/privileges.out, so regression check
> is now
> > > > all passed.
> > > >
> > > > Best wishes
> > > > Dong
> > > > --
> > > > Guodong Liu
> > > > Database Lab, School of EECS, Peking University
> > > > Room 314, Building 42, Peking University, Beijing, 100871, China
> > > >
> > > >
> > >
> > >
> > >
> ------------------------------------------------------------------------
> > > Exclusive Marriage Proposals! Find UR life partner at Shaadi.com Try
> > > it! <http://ss1.richmedia.in/recurl.asp?pid=430>
> >
> > --
> > Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-hackers
>
>
> ------------------------------------------------------------------------
{"extraction_info": {"found_math": false, "script_math_tex": 0, "script_math_asciimath": 0, "math_annotations": 0, "math_alttext": 0, "mathml": 0, "mathjax_tag": 0, "mathjax_inline_tex": 0, "mathjax_display_tex": 0, "mathjax_asciimath": 0, "img_math": 0, "codecogs_latex": 0, "wp_latex": 0, "mimetex.cgi": 0, "/images/math/codecogs": 0, "mathtex.cgi": 0, "katex": 0, "math-container": 0, "wp-katex-eq": 0, "align": 0, "equation": 0, "x-ck12": 0, "texerror": 0, "math_score": 0.9432905316352844, "perplexity": 9730.947487633382}, "config": {"markdown_headings": true, "markdown_code": true, "boilerplate_config": {"ratio_threshold": 0.18, "absolute_threshold": 10, "end_threshold": 15, "enable": true}, "remove_buttons": true, "remove_image_figures": true, "remove_link_clusters": true, "table_config": {"min_rows": 2, "min_cols": 3, "format": "plain"}, "remove_chinese": true, "remove_edit_buttons": true, "extract_latex": true}, "warc_path": "s3://commoncrawl/crawl-data/CC-MAIN-2014-42/segments/1414119647629.9/warc/CC-MAIN-20141024030047-00251-ip-10-16-133-185.ec2.internal.warc.gz"}