column level privileges
(try 2)
Here is an updated patch that applies to HEAD.
I will update the wiki.
(What is the maximum attachment size that -patches will accept?)
cheers
andrew
I wrote:
Show quoted text
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.
-------------
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
already 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.
Attachments:
pg_colpriv_version_0.4-20080421.patch.gzapplication/x-gzip; name=pg_colpriv_version_0.4-20080421.patch.gzDownload
���H pg_colpriv_version_0.4-20080421.patch �<is�F���_��VlP�xS�%U1�p"S�rv�q� �I" %+����{���R�'��Ld����>��zc����}���t��m+�\�������<��l�������0������������7O�q�)����=V+Uw�O��d�vl��;Kc������B��Z���*�}V�;�4�5�S��������i~���?8�����|\Y�!L��� �5!p���m� ?G���=�����_�7�E���QK;�8���W���+g|T�[��b����n�O/j��q��5��;{��b�u�rf��r��,�9!��G3���������dn�D��Y�8�c�V���O J�������K'�e>�
�v���P�&�r��<�*3dK��`q��z� �1��"�N&9�8<-��8��x`���]3����������t���R�x���,�#jU�P�Qg��h��Z������4���{;���k<B����9|e�+�
�P��s���=��F�Z�7 �+� 3�3�A�b���'�W\Nx�'�b|�y�"t��s��p��Q_�[�!�������}b���a��W�~�����T�wf�Tz}��3��]��o�g��0�}<��=���gh��,\����Q}��.Z�������:!�CE�H�0�����2����,�{�,��?��2�A*��K�����r�;�����n;A ��Kw��d����<CDA:�@��N�*�S�������_w� �a�Ua}������y`�J����ha�tHCp�G�?���>!������6� ����
��X��2��O����Y�0��-������!e
&r��
"�d7�5Y�_r���@G
6�d��x �6/��1���Kk
��=�s�d!^� f%�pK�5�����`��./���"�Y Bg��V�c�w�����s��0b��8���e���t���
�yh��;�m�{)w�Ws?���)R�����yPI|�A��V�I�vTV2�%F^l�RX��KHoY���������u$�brV�"1�u����n;�;���!Q��:�/Yn����-���aV%�1"FT8�"��L���.���d�Bc"�0��L���APL��VV��A~�~0N%|t�
X{�@:��
a��2�A
'r"H*V����.���,�`�p'��;(�
����P ��iy�V����ZM��4��|�b�%�A��������xN������Z��N�F� P��CE�k)* ���P�@R��l��r��X����Mb�Vh����/:�R��^b'��12�"P#��7�-K�&�h�V�he��w��h{�"8C���%�I6^���zS3��@����l���Nc��6�� ` ;�])��]�F��\+��h�������!;� �xRiAq��x�pF[;�To��.�:!;g��r��Ek<�����r!9�5k���.�"6������)%��5=��,�Ge���(�=R�]�h{��f�W����r�i{?����b����]
J�"J����4���X��.����$(:�0kf�p����W�@�Y5��vr�Je0�����<�r��2+�.�&s�l6[U�[dU{u�g��+F7��2�?����P��d��+:c�� 2Kh C<Smf�<[�_%04O,��<X�\��H�������q9���=������Z���4�������H���J���&e���i�c��_�U�8u�,.g����!�L���!�����<*9�V��>�D���
M���zTD�@@X?AK1_L��qL��2G�0���!�"OiK� j=�����_�=j�=�V=��_���������y����7���A�lT���g6R�$��A@T���cu�q���4����t���}��P,X,� �p�J�������F���3^Z�mK�W@�j~:�3?�� >� S
�[f�M\��e(�X�M�U����Rl�=���I����Yh2�Z�l����sv�6�)����J����*!�Y%�8��p��g���Xl�="|H�Y`T G�/���7S�A4��Q��
hh�r��?/v�OI���O������b���s'p�����?De�f���{e�OU�������/!������
%��N��?��C�\?��-��� ���Z��<��'U��}�$KjL����C�Q�����\�`���� �D�6�� u�EX|B*���s���T9i�7B���D���.9_���p-� )�m:[��:9W�����M:F����������k�����vj�9��G�wt�5� �(�J{Q��K�����n�}s(�X!H�������\���2e�m����$����r�<9������������ly����m��HI��C�X���-|��
�L���|�����?w�q�6�����_�9\��������j�T@�t�C�@�=sD�|MaBlki�<z@QP(�(��q�a����A4Q�)jQ��4�Um��{�/���
O�`j<VFV-����`���<Y6��f��|l��e�&��v�|��U���Hh�C:�����>��5�fsw����WP��Be` -�H��Q�2���c��>���~�;���Gp�\w���W\g�V^{�������c:��y���:{c%��{������������MU����h��Z���:S��]�j
�A�{@�W�(@�m.;)v�vk�n�
n��.��"��$5K���}�����i���Y��{�CX������3��������s�c�*��sUR�����b�I��O�uf����S!u� B�X !����� �e,x���[�C��G������ZI��y���UJ$����u����
9=��U��Z�����7E�['p�!Rq�WW����.��-��g�%��
��V^���f*��;��E5�� 5���a�rXO�8�F�o����7D��.�����n���-��e��a9����lKH�r�/���u>p�*����1��o ��(#����j�>�C+�r���~3�9��o=t�T4����t�d-�
sO�5�/��J�������
�cuO�DX����j!8mHy
`�5���uS��-l���j��~��NG�!�/��C�,��|���Vx��i�������Q����M��Bi���C����9kax0
W���ilT*f���_V�f��ge���TbL8�A�t��-��� �:��.�p-w���
��]+��ay�,8�b�6�N��XP9 %�?����
�E�e.�
��P��v�e�r�g�_!�����L;�0�2�x%�����6�FR�j���:���t@
O�,{f��uM���������B������;��wo���q�v��'#�z,f1����K��q P�_��!@�l���`�B��Z�>7�2d����T������yT��� �'E��Fy\�~#v�z'��wyv�h��c�3�?���r1�f0t��\�K$��v(I�vT�6,�fA �W�m��@��QGUw+����D�~�(�O�p�b�`t����i�)�� U�Yi�A�p�Z:V�d�� 4~���Bg�V\�� �b����(��>����i���Y`B��y�9&��9�CD�J�t
bZ�ZBf�>�~�41X�ZAv a��\C�����[HD��H�a�M&!%����9f]��&&�W�$�8�G>��������P0x� F�i��vF�����n�s*CMQ�`�<�[��n����n7����&L��rK���\���r(jL�W$M���p�F�n2�� Gb ��U��J���@_���}��V]K���}s/�@#��%q�%�Wr.x{(��>*�P�r`����P�
��T&��z�������/��j��";'I����b������M�
�|_y�I���Wh��� ���g���Es�G4t��wB�d�0:��3 -q,�K�`��
�"�����E���Tn���#4�@u��W�]0���� �x���o_���+�c�H!���d������:/������3�H+���B��2Z.�It�9ZL� l�6>�N06�����/�Hou�� �z�n�<�@,K(.$�t��
�I�+g����P/
=}��� ��p`D�%s���b��B��� �^7x�����p��M�bz5�����c����*��Wl���
8�=����Q=k�����Bb=Ms�$!}-��xE8�����$�����{`�`�9p�v�I4�IUao)m\�xc9�h�/�� ��R�m�� R����I����J�������[���K._���9����4I�<��PwRP�')< ��Ar�r�8aF�/?�K5~�fb�dW~Gz�22���{6���,���w����,2SQ�t�h������ .Uw�!ZN���{6T��1���[�_B�Xd���p{�#6#�Q�LZ#w��Le���0����H �M��!K���8��,�n��k%K�&���y�hR�����e{(�������
���'�9��4��MQI�l�{�4�>>�t�=6�b�l��HX��e�0��F�<S���i���|�������3���!wAqFW��Pf\��(��,B��N����,6v�7�>Rt��V�5{��F�N{�����%q��kF�r�N!7k��
h�c��8F�:����poZ��G����$�����p�)i��i����,���R��(���/��a���G"�����ua�2a[ �29��BI�@#��@4V!5.Z~i�{��k�[+U�2����'C!��i��
��K�����oH�{��fKc_Q�+��!�O%}P���g�|�=]a���O��B"���U�� M� <Fh��%���A��L���>���8�V*����L�/ju~�~�$;T�/�C����,]�Nw9����>�};m�A@�g��s�1�/� s��c���3�v��D���"�����g��$ ����9���<��T����O�{f���F���s�8^3��Ms���%?��x-��aX���ri�%}@}2����M� gx�f���������d}}Ig�N\6�����������6��4M���d���x5e��8=R���Ly]��vX�=0�y�\� ����_�t�5NW%�R��2d*������Q�!����T��!�Q,�<��K�#/����vM� IK��D#�SX&S-)�^s�����B�z�?��C���m6W�l���ZWK/���/��*j�ZC�Y����"j}�CeX�qW��LK/T��4���������2��+�����pb1 8_U`��1��L��-~B@'%��� ���Q�}�8�%�6n5 ���9�1���V��D��_D��Nt�U��|����Gv�?��N����r74�
���O�>��B���(��]=������9� �=
����T�)K�l��6t,%�0X�F����S#��V�2pcw��k�5����g1�����}��r�����d�U������Hy��*�R��t'�7�����S-��d8�����*P&CU�wW������������%{��IG���\�����rvm��C5\��?U{��LSsM��n6��Kp�]s���Q>�SL��K�x���~���Mu+�[sR�L��&�L�F;�*�~�~B�*�� } �>�M��}s�^��������t�J����{u��s���!Uy����k<p�QT�Tp������n�m��{ogt�������|�|��SW���w��+�[Z�v�*��H�F~>����3v
s7�k3�����B�SUE")a�S"