column level privileges

Started by Andrew Dunstanover 17 years ago10 messages
#1Andrew Dunstan
andrew@dunslane.net
1 attachment(s)

(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
���Hpg_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���OJ�������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��qP�_��!@�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�ZAva��\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���ZWK/���/��*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"?>�@�L :��������?���M&���gy�_��_{�����,�Y�mNc�����y0�p.g��!
0k!�0f�}�[U}���f6��K�f���������r#d+������m�R/�z�����uH/91�Q"���f��2\8=Hj��MdL�q�.�VA���;�*0FLF�P	��HW�g�)��0*$sX�E���n�"h+�<�JX�s[����e��j�q��{��{F��[
����\|�S��r����W��;�oEe��1O�����,�h:Ox�.��<>�������-�k�Z��F	��`�v���Y�WH�Y��:5��dD1���,.�O�V���p���
���Z{,����y�����p���Q$�!���u�����F���F�E�-�Z~�i�y����jC+~����^�t�!�(�{�7��������hh�zz�"��/=>.*j����NE�r�/u/l�P]?U�54����G���������N���tv$�[�.�{���|��{�����5o`�%�Z�!�5[�NK�"���Ya����_{t�*1NO���V��s-2-� �O���,�z��\��@=-Zk0�a����)�u���pv6z��Q6�h<>�70O��: ��k�O"�_~��0����Bdd���?�'tn�]%{��KO�_��^���s���Z�	��E4��@��dr]]���WqB��0��0Y:�o���LxtJJ�"��4u���D���`��V��`�z4�3\W�j<���tr��He��s��!������n������H���x����'��M'7�x���1zUL�}�.������
�+�1}��4���7�F�^�V������TT8� �
>|?�e�<�M��T�F�����j[q���*kq��{���u��G��e2���W������?
��a�7��n4����x�X7�	��
f �Kf������$�q�of��p�
J�Jq���6x!Q�`2�6E@���&��:��+�K��k�������a��BWsn���<mbN���}�nW\�9���8�QT����R�����������FU�/.�Q�&���4/7���A�Z%�o��,�E�f�/*<�9�~�][���{�	Q��PU���������������~�3	��W����9��h_�fz����b��|w,�����6�TF�
�����W#Y�5������6R�!V{������t��$5S�7�����x1t4�DP�W�[%�w��L>)��Wk�d�[5�+�v��gV{H�d�&Si0��KXDr�B�$��pQ_�����%vkk$���<���,���ebM����Cb>����jy~XX�
G�~����d����X��1zS� ��\Y�+$��l���O�4���n[h�N&���Z=|[��(�������x���u
�F<"v_�E����<��~����o��
��5ly��R<���v���`z])2�R�"V!D��4�{.
����T���d�)]9�N�����6����SF��,U��OG�E��R$YW��=����g4,	8�
��!��G���@��r��z���A4�������z�|9�����nD���?�$����m�5
�X�"4AMa{��������j��0i��}��d$��U�myM/�"�]���D~Lki^�)�4|��S���xSaX���;���D���+AK�������&���l^�)Y�\�)��R(������A+�.5/����Mq�����Vj]�Q�V}���sJk�^��6p�-.{���Q>@�Yz"sv6�su�Q���_������z�<;�������J���������x���d����6����a���������?���N���k���.!�td�3w���>���7����j�{���_~��e��;�?E�����{�!�-�w�R*�����{t�B+p:�N �)�a���m�Q7
�9�����rs��.3��)^<��i��
�"^�z�h��`P�x,�q.%�aW���?��A�E$���f?���a��4�<��9�k��5��N���y-}���0�,a^.�u>��Q�W���������p�y�8���g]�����#�X��P3�ch�q���d�������$q��,}���yW���Yw]�d�K^�����6�.a��	�*��	s0�1�<��>;����4�"m)$%����\���,��S�h*P��5���`A�
����r���L3&I�<v����Wr��
AW����ZW�U���P����`a�$���yN1���	�('TQS������p"^G*h����'�<h �F��.il���]���C��� m��F����R0�0�J�t�
.��D� e�BS
��"0y��������}<*W�p&���"����4�h��b��fd[������~�.���A�6��@�T�
����
��]��l��@�/y��$)��1�H�'v�����"�l������9��S��qK�scp���������{HJg��J]�Xx G�����
����@6���zS���2"+'�����W�4����p�.#�4-]|g�<e�x??r6��f(YuFg�:#�^�_B�!p�V�9
�ts�X��3_� W�'c<��c)$L�-zd����3#���PD����==[�0�����T��/<�s�4g�y�=�WrF5Uh��b�@�a
P/��fO�����D�?4��v��J-`��"��h4��
�`�/	$H7��f����cT;�����R99�����|xM�[E�{���*��;
NM�R���28������kO�E'�IM��g�f+��.�>���R���RS	�m��a�:�I�Hy|��6��hN<�����\
F"���q
ad)����=y��G�I���������u�:�4�8�b�D�`���K��N_|�������SY��=�ff�6��3�X�U����o�<�e$��[><���h/��������;t�������%��XX��U��@3�9��5�[��%���������UBp��
���b������2FstML�T����x�����z�L��qI�h���]�"V^}�]rA���O��Ao����L|��S�V�Pyp4��EGY�+�l�A�}s�j���5}��0v���P�=�I���i���|���x��h�}��m�3L
sOy�nBeP�T+^���*���]�
M�w��C�U�:�~������uv�;��=�q��^h� >�n����y�l7�������g|0�\�	h���s;!����`�#-����
nq��VZwx����5���������5�d@�3���L��(�v�/��C�j���!/�]�[��a��;g5�}�P�!�],�R+"�������d���Y1GI��^���r�gD���F4��^������e��
���|�!��	��rw� mt<>�U�:f
LBb��_�-�L�,���������aza^�;s6����e:c�$���{���V#�Z��b7��A�u��*�Z��);�}s�"�m�X4F�K������"�.�F%�a��";�+0�c��QD�0��svU{L��F�k5u��'�uS��g�����{�v�\�3+v�y:]?�b�CLp�
]��+�1���'��V�a��.&��P�MOJc���V�P�c���]��+����k�����7�v�m��:u\�;�E~�a��wIV�?���-q����$���(��cO�{y'�v&��[�OBYM����J�R�	��p��1h�[�OU��7,���T�G>�3��I��rP3��-�<	�W�D��i��Of�k���@������v�����Z]�(J�R :�.�1���#%�kJw��`zc����q�:���@��X�����������d�p��������h������y����$��,^��p��?�s`T�7�g�o��R�\�J��a������\�TuA!YH~���6�\�����_�&����d4
2�32�]`l��;'/�xk�Y���-3�{�d�^�(�u����i._��I��KJ�B��1�)�P�?���
JAu������b3+_iEEOF��Ty2��d=^&H�=���5?uV����M�oY�3���\��l>��p��A�:u�j�����]?�f��3���MW����'N��]PD�?���o���J9�k�*k����������>s���9�+����y�����������1��`�m��2�����V�J��:��`���X�|3���%KX�c��ny�`������~�����J-����*�vO*������#��y��&�(����WD���0���h���Ys	���<����>����)�O������<�9����d�B�2�����_�t����N7IrD.�����|D�^�#�x��7A;����B��U�A*#��Y���E��
[�@�M��+��x8���c�U�Wv��F��uM����xC��$J�P�������i,D5]��{�}��=���B���?�wW�����E3���+?��-#�y�/���|2�7��I��5]���* {Fb���{���`?�x�d
�u+y�a>��%���#�v�H�����i����z{��1l��Y�!
����
�e��x�=�x(&���f�}�*�5�z��Lf�����c�(������M����
/�{������q�O`�9�[��n���%�W1��R����hZ�a0�J��H��L,��k*���r�`h��*��V��Gf��C���F�N#��:��X�������~�yq�qrD���P���� NP�8"���
m�����q���^i�n��@�R�������	��2���<:�B�X��o���o��	Z!�^+mI�yg3)���,5�s�^Y�����X0�KLj�Y��frn�:�F;�����[u�u�K#-.E2D�xbt�HG/�Md�E���T���0u:������-3F�G������f|�8H��������eD��"2���
����tF������<����F|/5��^�%��BZ�@3FO��G������.���!H���MC��M_�1;�/O���3�=6O` A}p5LEj0Z�5[th���u��`���6(�o����+D�P��?G���4�\��A��.�1����po|McM��V�1�Q��1zA������=���UK@a�'Yxu�)'�.���0�~�6
��e{@2'��^�x�>#v�her&r���P�E?�i
}���/�G��<
,�=\�C��{3ix��-��t�����+�������-_N�]*�!v�Co������	�B�TAw��
������s�������$��s`5�0*�������@dB���`�q?��:w���PN�e2�\��8�%������������z���t�9rP�\�	J�3�1�)=)9*�k�������KH�J�J�')GX�c����!�l����I���w��H���������8�c�A�GG@�0�p��
L_sX�����y!a$�*���oS�����(�������]���"�N��D�Yp�/ 1��:[�kF�U�(��V9O�Ks�$,G\�K��5C�V8��8�a���(�F��1E��]N���EUrM��������p����Vy�f�m�HU��J�z��#NnI����ql����WF�W���g]��m��SU�<�K���+��c>��"�<�:SM�J%��r�l�\w.�z
��k�7���|�Q�E�~�A���32�_�b6����������������9~��z�w����2<���X����E�3���.ICw�M(�}�y�
x��jv��w�(J|�P���3r��I�S��
�?�$��(��������ON:���o�)j��q/;`{|��{�	`@$��x�����V�x�g���L&�eH|�1�q��h*���\)-�Bo�!�24�����Ih5`�u���6����Z�-�K�f�#��rU���V�I~[���	��YaE� ��R��5����U#���}pa����]����*����H�5G]$���c��X����i$Y�5q|h��O��y����=8���{���]�{K�b&a��1��_`&��<Z����w��&S;Q���?Fk)~��.������cE?F��/����Xp�3�y}���yt��~<E������:y���L�|�������&�~���B���o��s����o����������,a�<	��=�~�\d=��wl�yB��$$.@9�.59T�<B��j)�?$^��
B���Mx�9�pt����|�X��"���������?���w��-������j�(��y%@S�K�0�QPI�h���V�.`�"���D�:��ij��3)�d�Q�0�z<�a����S@}�r��l��S�1�n�LF���*
��@
fe��O���f _`4�$C��oh�E�y����{gb�sA/���psU������GQ ������o��C�i��s���H%h���f�ob2n�:���c
#�8�(�������E�zD�=�|!�]��S�hw��S��j��f��c\��W�z|�(�-��R�[��u(U���)"
����`$
����7���B�UV=�Wa �����j�#$����������y�X��/����bx�������s�<��avP�uM������+���X��^h���E��
���<;!��7���>��q��[�����+5���3��f�m�d�p��������w�E@
l��:M���JZ����G��8
.5�MvF}�h��q1��?���r��_����r��fx�"�3Q�c�fAd�����l���w�IJ^� 0��0�^�d���L���MP�<J�bAh����	��Q="O"�Xw����H��vQia��M+�������������'H�H(G)����b
)[��T�~���U�����.���
�YL�T��'�ppV���&�O��8�\�k�E�b�s���n�����M�Yd�������2������������
<9����Bb_vp�1��]���x�Em>2M�s�N*9T'`&�i6��\��im�V��N6ITDI������AnF�6����
�C�hl��$�:�T!i�D��~�(��������*h�2���sx�+ew��7{��]�D�\/���jq�����_1��=���J�^�7�}�x���1�����d���'��M.r�;�GB����	�.�n�\��FhS��=#�s�����'���U�r��������v���#8|g��mY����)������N����km�r�������/�J�������W��Z�;������9�/�.o�|b��(2��Al���{�*<S������.�5��2�l����fHm�)��*`v��BuL�W~��G���c�^��p�y��Gw����BM���L[�e
s��M��4�������Uol6jz���4����^�7����St.F�
�Q�����V!�h^��4����Y:�#��J��n�^��Q������g�rw�m����E���x�m��L�����������+�@W��k2i���y��6D��,sfe�A:�>�1���Q��B��S2��y�?��q'-�v^�c���p������-���D���aL�P{"����������F���������ms�R(J�)?�V�����v�q~F�����w��<��8�����8�l�����43��@�|D��v�z)���G�9��i�~u����)�4G������&��|��zu}��=�k}��F�uF�S6�m�����|�Gum��2>��������T�M6�z6�/�r����rN�4���P9P{zf��'\<eHw��)�u�+�k�jz�(���~�7�<������T�7kV��J:���
�K�G�$��7���0jJXd�2~��������������iC�@�!����I{��DLw�c�������>�p8I���V�F����ms�|E�|�*�aM��q�5���$��7b��{��E�U�}B�{]�[�&��W�^���T��Q�]2\���<��Y>c��J4�W�5��5�sQ�`<��b/�6M.�=[�V� yz6���4��E�#n��by�=���kw�����QL�O�8��[��������6���1�7�s�6������L`�s��o���9�����^��_�o5�&����bN��x@���}X5��G	'y|:����_�u�|�P��V����0��\�;V�`D�/x���|���������A�C3�� h��A��l�����*u��_ ��_$�����tA����2�	t=S
�����J�$5���)�j��f����*��������Q�Q��tw��Rp���X��_o��.��9��v���*�*i?'�3��c�NnV����@�����G�`�����Q���+
t�y
�rB"�|���5Y���[�����)d�0!C�s�����Se�*���}������X~1��d��������J��v
N�m�����K~Or[��h0)MS{�n6�������m�����e^���?
���km���4R�V�����G|���/�7���C�X��qB�@����)?\��r57�
G")EP��Pj��M���v�N�4M�x���uH$B76��~��>S
�����.�h{�QM%)���ade0�]]_K ��X��L�2�C�a�B��F`z�%����\+T�+]��\w���;Tx;���=�v�D>�2��&Uf{_�r��6!hkA��@�O�D'_�?Z5/}���[n���x�.y�d�}H�z�?`�9<����'��~}HT��;���h�}Y�t	N9����Fp~H.'7�!#o=a�0�~�0�F_k�r<$�'^Bx�������a��{��A*��N�xjI�`x#+�u6�\�[a��.�;c�.�V2Y���G�k�3q0�����JJ����zS�x�7p�J���N���1@�c��Y��0�����v�B_�m����o��7�qO0Q��1�o�T�F\&S^��_�[(�����W��\��O�����y���.�b5�(�@�jn��1�c�
��e<�g�3�����F���F��
�0U�B`%��7��������EBU[��r���otP��1�RW�>
C6$=4B������kED��;8=�B"4��j��M��?������l%����~����K+�������|�fv5����?������}�o�����$�V����Z���F1R��<�e���)S~���?~[y5}���|}txr���{�;y�=~�<�~��/[������}��������NW������>�����p^N=|n#��w+�GP���GP@���{���_7&d)m�GWQqeA�Vi�xXRo�����|�}�M�sWI�s�����N��_���o�����H�b�H�7W����b��fsn�m����L#kr����A��������g��
�����|3��8Bw��~g��3��c�?�1z)�����(=�S�	0T������s�V�5��t��(�fG?v?]��9?������Jj���
]]Mf�O�����W�Uk5�'��:�x����F�]FS��A�o4�E���t�h�����!��U�	���c+���7�W%�D[�#&�E��z@�|y
�o+bV��5l���������b8���w�qD��"����g ��7B�� �T���cl�C��KE��~�2�%N��������V4����)�-U�!���0.�^[Q�d;$�A`v�J��{�F�fX�	�2�D�}�!�b����nn����sY�	������*�4&�!L�������X>C�3<���n'�
�7�����3��'���+����>�[5}����z���)�3y��9�<����b���S������**i�YZ�\�(n� {����<���ZxN�_\kV�Kgc�"��q.b��*�g�]E�OU����C/Wrl�`���'��|:���Y�H������k��J�SC����p}�]���J��?���x-]!
b���xn��I�/|��\(�>�bf+[S�9��9����j�	S���Ym�D��)3��'�o��;���o��G?^��M�i�b�P�3y7���B@Da9��$�w2�V%3��`��H�%�����u/xNc\��,�
/��M�y���1^�`
������t8��9@�X��� ��/����l��H�XC	�[6mO���������{�[��)Kv$W2��	G�jV���*F z��M��S��H��2:YMP�&�0a��H0�0�p��W�FX���'��������~��l5p�j�v:^����*�o�-[��C�dEkTu�"`b�f*�o������A�V�o/�bHE�HmF��?��M����R��<`���������w�s����)�����%�R��&%�ZgX�k��Q����,\#:
��d��;����ZE���*E�Y�����O��@��U���I��$k+��������&fw2�R�/E3&�Y�E���EV��!+=��aq|�B�����3P���Y!��j�e FE[�s���v7��G��:�rk�b��qC�����ck�����S]:�R��:����,�
M/h�vU� H��$��'.���C'���|�Z������|���e�l)�lY���XU����E�e`����	H�C��{WBq����:����7���a��jh�l$�]-��i6������Y�t].�&���*JG��YF"V�_L	j��O��T��v�iL-B����v��r�jI�k~��kA�.���@��5LE4E"l��	R{���\�&��������&�?���wpx�n{��m��oU��*����cx��uqU�*���@<�m/���3'��X�.*�H�@V_�[/���t��T>�3p��~g+����u9	p3�bT.�[^�����Ec��jmU��#5�����a���
Z�}kE�3�H;�E��H�H�`aq8��$�I���J���}ln��z�0@�ch����C=���	���/���@��%��o�d�����G���6�H��.�p�'��j]M��`�0A��(3���qt�_b�^�\��3�����fh�tp��o�1Vi4��)&�`(����������iv`�b�aF���i	��^���!A�G���'�o�����OZ4� ��7$T�������r�v�Le��g7�f�m��|L]`@W���#��([�(dY��8���	���=
��1N�S�X��������JE@!5�}��=8:	���o�v��R���*�5�MW�UQD��������z�:�����Y��?��Hi~y��`��|��3a�"�0?��,�(��0�&��+��I���R��*:�a��o,�<^AgGkd��n��6����&��^���Y�Vxg������v{.��>���W��'"��}��"[�k'��o����+/��x+�/Q�ne������� 	��5|Vk~�r�
EEI��\�G����c�oFY��k�T���[Rg�;\��D�q�~�sn����u������G]�.��3��z�,#P���}s��z�w����;h&1V�B�(uxgc4��;�;{nO2���q��d�lBT5���HV#�=!U����QU�2T�W]����5��{���c�t��fT4�
3�����[9<�����2k�7��������-W���mEG���K��,wct0?iNH�jo������9��G��<��}�)p�����'��,�Q�
�kww9��W����G�x/2��}o��N��
ft��<���]�Q����k�;ux��kM��%2Kd�X�j�n��m'GVC���vv �}	����������*uf!-Ok�`W���N�-eZ���m>����=���� TA��O$��\q��������<��vF�)~��������[*��R)������`���QR4��9`������
���W��k�x�*
��E��H��$��'F�������G�9�3�<�r���.���ui��@4o	���\S��v+w���%W�������y�����������D=�pi���3B;����t�{��]*7���qy��rb(��5
���2TO���0d��� )�'�a����s�e"����<�Sm������H1Z����(�<rt�������C�
�Joo'�T,��zSE1�I�{l�R��^�Nc����5�5;�(|g�yW��xz��5@��wk����5|Q�X���~�L��7d,hvB)�q���	&��� <d�%2.�1aO.'��c�M���nwgc�Q����� �&�i��z���	����ot�a���3�Y�aor���qg2�n��rK�a�5>���������
��P�es��r��vjdz	�2�1�T�����T���k�������j9�<[���s+���
[,�z{���!�k�As�n��0_B�������b�R���Y���1O����v�<\�I��������:��@=�md���*��\�h
"��U��X��
�������_��zK���n6=_JG�#x��Q�+(�����G�"��2��L����w����!��T�E�H�X��|���I��q����'�9j)�M�l�<��w
'd�3����e�C��7���s�|~����a���������������X����g�x�_{����sM�6W?��[����p�?�����-�)l~����i�S����vU���R�L�� �u�����~�D�>�����l���OL����@zM�[��(H���H
P1������P���- I1 gw�8a��4,0�b`�Ik\Bi.%N8�M|Z�@+��j/Fp$c�:��T4��L(~m(���g�����o�H.�K0�����h0K1��Q�12�������hu��4�[`�K���c�Zj�y�nN{)8��bl.���b������h���_�Y?�8�Q+"X�����P�Y�����
�j0KQ���1��4��_�(���Y�`F� 	[����Yd���:�������&��0���@��H�'��U���GyF3�0Kp����\F�4�|��^O'�5��AfT������������p�c�%$��
�`��'@+.VVN�,���YZ	Y���"g`%$�"�P\-�����&��8Z\q����i1hEe�"RBT-���ZdJ�E�y	���,#���E��"�-.���ed��������t�*.�.�TB�]�����b�
K���*,/��R2���h�R�����mS��h���5(���x���jC���YY���H�!j)�-��i�-*7�|(���v_�*�to�*3��/,(%��`�p�V�&���f�p��~�}r���������Nn�4�����I���r�+�D`�&���W�M2����	���J�V4K"�_Rq��|�����$���+I����<r�$��:����PV���T�J(k����_���H��!�X�V@������	�����\���%��2����b���[�m�����,��/v�@�]f|#�sH8~i8����j
��5����j
	�{j
	�{j
�����h�$�������@K���
|(uaY���{�G��CL*���TZ	��n%d�"#Y\�*��$Uh������\U\)����RV1K	[�@����,���WX�%�BL�� Vd�J�c��=�Z��t��C����j��z�V9�m�����r�S����aM�t���V_F�Fq�!H����U�.f��z���`U~_|,[Q��xl6�x�f�F�_4��4����T3����	B����m&^�L�]v�i{Z����S�b���	��PZ�����]��VY[�������e�t�yNW���_l��	jm(���X7.����c�<2&�����cr����� 	�m��NN�u������	N���� 
������m�n�*k�+@q�uPY�^H��tT�.�SV�D�x��v�>��?+�����:`l�yc���u>�
�����������>������G3R����������g�_/+��ZF�)���SbqQ���x�+!�WN�)6������HC�(��PT^������
�+#)WR`*���T^)���gtL�P[F�TN*��W8[��CYD��c�@Qe�R�#.���FuL���[&Q$4��.����*P�#����ul}�����j� �P
����:2�����J����8��[V����8��@��T�^����A��JI9�k�z-3����EN]Xj!c�.w�O�����)~� ��*rH��S��N�������B���Jj_8 L;k��������uYR���P���SR�(�k_\DSPk@7�;�[�e��g�t�3��-()���P�asL�����\C�L8KQ2��� 0-E�(�9�-��9�O'�����+��}�b��K���|#h\����-_iM�����@��xec����P66>���6��!��z�f���G8-o����Z`qQ��khE��J��EF��[`	i�z�j��/%�XX�-���[^)����ro!�PX�-��\df�����������qh�%�b\XP.���\\q���2�s��-%D��J��E��r"�"�frXF�^��LNo\N�^���~V�1��Gg���H�YE�s^7R��@yNDVQW�J5Q7�M\�*�uc^4V�d$����aC��1��dC����&<5��L}����!O��&�&��O4:���I�co(�$t���^1r��2.��=89b-��}%j�DM���4�������8!'1����n�d������b�BG���
4&�~3��$���h$�P\�C��F#;�s @qLi��4������t�]�Fa�x7
�"��6��+ic>��������s����l���]]j>���R����^�a������9h`����[J���19g��
��`��Y?��t2�z�+�@�[fU�Vp~=
�n�(J�������#k�0���.xRm��0����cAS`o�����0�0_5��(^FG�'��CE��	D6<������������#bSC�b0����������s4c�F�v����`�\�u,��������-g�jU���V?�/c���/(��5���?���*��K�����:���>�����?��Jz�,[9sGW�A����9ppl�&�����V��:�����;�������4_��z�Yo+��`��9U�$G�^Xo<��$!����4�H��"�V����f<�9�]nO/O:%�LJ)d>8Y�N{;��?��w(c�,�����2���~=�O�W��k>P��G"����|M;-|g�n�0����[����[cn��`8H�3�4:��������`c:gV�	E�Li���x�������;|��������wS?z���Q����}�wx��fK{�G�i�EO�?���tyQ��9[����'���,�\>yM����.�t��(�9��K8rN���C�B(IX���6�@��QR���1&1��?�iE(���O�J[2yK�e�xv\�a�OAS���H"�MT�g�M���7fo��N_w:��K��#L`���E���Y�4�0$��E�2�yEvK���[z
d�s)�bs��r�?����q�2����>3<�Ha4�� tr��9����H#S���g`�0\0�b����|�{����h���}�e����l�$���i���f���r�ht��+Li�B��L/.�cYu�V�YVf����=�77q����|j_���~�!a$za/W�@%���r����F��%Kn��'SL:$��3sN>Jp�����;�{�������J���t�O�������v��q�M�����)��p$��B�T��O3���)@|�=����6D[�O��v�M�"����?���i���~xp`���H�����j��T^
n���itG���M0s�dk�f��.	{�t
�y{���������#�U��aEm�?����Oa�^�?���0H���Qcg��#UxA�=U���E1�fM%��Z�4�k����F"N��� �	�������gy�h�?[���������������1��<�R��p�scs�����d���uzexh�����1^	�8��M�[��~XFc��#�s7���1����a9����z
�P�&(�=Lo*pc��Bw>o/���K�
�����{t���Fj�N���-�X��N�L"��Lz*B�VS,�?����X������������MY	��|��<���F=h������������U�`�W�[%��+_�������\��V��q���D���c��Fh��lxAK�o"��
�Bh�NfWb��[O�7�$]A���I�Z/l=V��x����:����&��|�G$���=���m��t2����@�7�����]NnM������5l��J$�]�	���V�g�&�S���k�S�nyA���:h�8J���Ae����59N�3m������6��!&������H��w3�\�|(=�<��~/'�~���JqL���gU�^�#��1�l��Bb�am��9������8�Z"/�zC����p����C�������3��:#dq-/�o_@�6�M��S*
SB^*#/�ks��F��5:F�C|��)C�o��3������G����'�!}�j��}�/(:�(of������H�D�Ui7�H@����^��x�O�����(
B%���u.O�Bn�sXKP.�7���kV�E����Z���`\v2%(U��T|K
S��}]#2��=\��|jGW��;�|��!���S=�w��E,J<�S����
����J��'�������������"[�Je�[��`����B<Wb���������,�A��W�}X�)ZY�������O&��e J�����FG���/5*s(o����X����V��j��a������s�]W�����=���&m�9��yf��������jwxnw��_�c�3�V�&[��$����k������a�b��V`��������:Nfi���`l��(���F�(�M��U��������
8�|�=����m��`)���^��J��D��7�
���u�mm�UT����{��d�C�_��v������1���DR5w���7��l%U`�j��������q@J�dr	Y0��)�����n��������4t�;���6��<�����so�����������Q���?'/lxa���-/��[Ltv��[n�u��F��2�����Ah-��������k@���k�a�ik���������l���Q>�����H����Oo����U����U}�~�>\��>[_X<���#+��0��k|�+���gAm0�qe��{�!����E�?#5�O��9M�.��H���/�������O��Ij��h�&��O���(�]d����K��m������]��������
�����&"T}��OE������!�uB���v������\��'���82����T����q���3S�Sd��������|���A����y���.��[����?c����\������h�~��><�;����iR@��D�������UJi�R��p������P(m�P��6CSu�@�M�*+��<��e��M��������u�;9=��M|tkq��@�:��il�Hs�=e�Q�g��x�����O+�1I7��b"�b���	C;vx),
L������o����$R}k��
@�\��P7�[�Ttv�i��1�d���0Z�u���o��������2���
e�����k\���yM��������JFZ�����I��a^�����uI~��[����4�b
V��c�Z��_[c������>{�������K��?&�?����n2�KQ��� "p--�����hb+�����}��V������9@
@r-�����y?F�!I`��v�/��AF"��C�W���!��L��dj��H������A�4�S^���8t}m�17qr'��������$xn��!
$�2v�o�������t�,LP/��)Na.e0�����
�g������{�C�.��#�016jO�����-�=s��5�S��l�'���qq������0���c�����)�|p�T����D������)�� ���h*�~47Bb(�������5Z�,UqX�O�~���>���@$
�x���&�����hUcX�T~���+Dl}�
��G�"������lz4�05����EM����� �����|������'{?u�����F�������_%|�C2���P+g���
%{������c��������[=��'�[t9��p�gu�
�x�
4�Mn�~���w@#4v���4?���+���+�1�)�p����g�
s�%h��X}kY�#E�����#H5���dZ��&�C�9h��Vuv��:}=mBk���v���?(���}z��d����`�e���.D�~�My�K�)����)�UY��\yl��m-����Y7�f����^zOqH���������������n'xQ��<7�A���Q�1��7�(t��ct����O}���@�����O��}
�<�8��jh�x^�8'pR0h���?VIid��gq>0�UlsU��xB��#��[A6J�-]���G���>���?Q�'���EW6
���UQ-=����4�i���������8���G3��ZZ�������:4q�Q��j8i�?7*������-~��ZAY@�W�*�;���;�6��/����kwI:�Y�($���q����E�w�T�V�����f���B��u������qi��K�������1��!�N���5�p�.�=��!jt�^�f�d5�-��R�@�g����a�zXO�9�.�1}��.���J�3���}��=~N~e�6���Yk@
��f������}��~����w���]t���qW_��w�|�����a)��]�����nB#/\7�0�7��YD��.��k;;@���
�2=��/��P���!�������k�8M�k){���6f9v�����v�s)���,�����:�]D��x�P���m������)�������_����g��:�>������K���� ���l������<�\�S�L�:�8�X��ya�0�
;�v�����������6}��z-�����=���Y��
#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Andrew Dunstan (#1)
Re: column level privileges

Andrew Dunstan <andrew@dunslane.net> writes:

[ column privileges patch ]

I looked over this patch with the hope of applying it, but soon despaired.
It needs a great deal more work than I am willing to put into it during
commitfest. There are two absolutely critical must-fix problems:

1. The syntax implemented by the patch for GRANT and REVOKE has nothing
to do with that specified by the standard. The patch does, eg,

GRANT INSERT ON TABLE foo (col1, col2) TO somebody

but unless I have lost my ability to read BNF, what the spec demands is

GRANT INSERT (col1, col2) ON TABLE foo TO somebody

Admittedly the patch's syntax is more logical (especially if you
consider the possibility of multiple tables) but I don't think we
can go against the spec. This problem invalidates the gram.y changes
and a fair amount of what was done in aclchk.c.

2. The checking of INSERT/UPDATE permissions has been moved to a
completely unacceptable time/place, namely during parse analysis instead
of at the beginning of execution. This is unusable for prepared
queries, for example, and it also fails to apply permission checking
properly for UPDATEs of inheritance trees (only the parent would get
checked). This seems not very simple to fix :-(. By the time the plan
gets to the executor it is not clear which columns were actually
specified as targets by the user and which were filled in as defaults by
the rewriter or planner. One possible solution is to add a flag field
to TargetEntry to carry the information forward.

Some other points that need to be considered:

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.

Well, SQL99 has per-column SELECT privilege, so we're already behind
the curve here. The main problem again is to figure out a reasonable
means for the executor to know which columns to check. TargetEntry
markings won't help here. I thought about adding a bitmap to each
RTE showing the attribute numbers explicitly referenced in the query,
but I'm unsure if that's a good solution or not.

I'd be willing to leave this as work to be done later, since 90% of
the patch is just concerned with the mechanics of storing per-column
privileges and doesn't care which ones they are exactly. But it
needs to be on the to-do list.

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.

What this actually means, but doesn't say, is that there's no
table-level representation of INSERT/UPDATE privilege any more at all.
I think this is a pretty fundamental design error. In the first place
it bloats pg_attribute with data that's entirely redundant for the
"typical" case where per-column privileges aren't used. In the
second place it slows privilege checking for the typical case, since
instead of one check for the relation you have to do one for each
attribute. There are some other problems too, like having to extend
pg_shdepend to include an objsubid column, and some other places where
the patch has to do awkward things because it's now lacking table-level
information about privilege checks.

What I think would be a more desirable solution is that the table ACL
still sets the table-level INSERT or UPDATE privilege bit as long as
you have any such privilege. In the normal case where no per-column
privilege has been granted, the per-column attacl fields all remain
NULL and that's all you need. As soon as any per-column GRANT or
REVOKE is issued against a table, expand out the per-column ACLs to
match the previous table-level state, and then apply the per-column
changes. I think you'd need an additional pg_class flag column,
say "relhascolacls", to denote whether this has been done --- then
privilege checking would know it only needs to look at the column
ACLs when this field is set.

With this scheme we don't need per-column entries in pg_shdepend,
we can just reference the table-level bits as before. REVOKE would have
the responsibility of getting rid of per-column entries, if any, as a
followup to revoking per-table entries during a DROP USER operation.

Something else that needs to be thought about is whether system columns
have privileges or not. The patch seems to be assuming "not" in some
places, but at least for SELECT it seems like this might be sensible.
Also, you can already do COPY TO the OID column if any, so even without
any future extensions it seems like we've got the issue in front of us.

One other mistake I noted was that the version checks added in pg_dump
and psql are ">= 80300", which of course is obsolete now.

I'm not sure where we go from here. Your GSOC student has disappeared,
right? Is anyone else willing to take up the patch and work on it?

regards, tom lane

#3Stephen Frost
sfrost@snowman.net
In reply to: Tom Lane (#2)
Re: column level privileges

Tom, et al,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

I'm not sure where we go from here. Your GSOC student has disappeared,
right? Is anyone else willing to take up the patch and work on it?

I'm willing to take it up and work on it. I'm very interested in having
column-level privileges in PG. I also have some experiance in the
gram.y and ACL areas already that should make things go quickly. If
anyone else is interested/has resources, please let me know.

Admittedly the patch's syntax is more logical (especially if you
consider the possibility of multiple tables) but I don't think we
can go against the spec. This problem invalidates the gram.y changes
and a fair amount of what was done in aclchk.c.

Agreed, we need to use the SQL spec syntax.

2. The checking of INSERT/UPDATE permissions has been moved to a
completely unacceptable time/place, namely during parse analysis instead
of at the beginning of execution. This is unusable for prepared
queries, for example, and it also fails to apply permission checking
properly for UPDATEs of inheritance trees (only the parent would get
checked). This seems not very simple to fix :-(. By the time the plan
gets to the executor it is not clear which columns were actually
specified as targets by the user and which were filled in as defaults by
the rewriter or planner. One possible solution is to add a flag field
to TargetEntry to carry the information forward.

I'll look into this, I liked the bitmap idea, personally.

Some other points that need to be considered:

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.

Well, SQL99 has per-column SELECT privilege, so we're already behind
the curve here. The main problem again is to figure out a reasonable
means for the executor to know which columns to check. TargetEntry
markings won't help here. I thought about adding a bitmap to each
RTE showing the attribute numbers explicitly referenced in the query,
but I'm unsure if that's a good solution or not.

I'd be willing to leave this as work to be done later, since 90% of
the patch is just concerned with the mechanics of storing per-column
privileges and doesn't care which ones they are exactly. But it
needs to be on the to-do list.

I think it would be quite unfortunate to not include per-column SELECT
privileges with the initial version. It has significant uses and would
really be a pretty obvious hole in our implementation.

What I think would be a more desirable solution is that the table ACL
still sets the table-level INSERT or UPDATE privilege bit as long as
you have any such privilege. In the normal case where no per-column
privilege has been granted, the per-column attacl fields all remain
NULL and that's all you need. As soon as any per-column GRANT or
REVOKE is issued against a table, expand out the per-column ACLs to
match the previous table-level state, and then apply the per-column
changes. I think you'd need an additional pg_class flag column,
say "relhascolacls", to denote whether this has been done --- then
privilege checking would know it only needs to look at the column
ACLs when this field is set.

I agree with this approach and feel it's alot cleaner as well as faster.
We definitely don't want to make permission checking take any more time
than it absolutely has to.

With this scheme we don't need per-column entries in pg_shdepend,
we can just reference the table-level bits as before. REVOKE would have
the responsibility of getting rid of per-column entries, if any, as a
followup to revoking per-table entries during a DROP USER operation.

Doesn't sound too bad.

Something else that needs to be thought about is whether system columns
have privileges or not. The patch seems to be assuming "not" in some
places, but at least for SELECT it seems like this might be sensible.
Also, you can already do COPY TO the OID column if any, so even without
any future extensions it seems like we've got the issue in front of us.

I certainly feel we should be able to have per-column privileges on
system columns, though we should only use them were appropriate, of
course.

One other mistake I noted was that the version checks added in pg_dump
and psql are ">= 80300", which of course is obsolete now.

That one's pretty easy to handle. :)

Thanks,

Stephen

#4Stephen Frost
sfrost@snowman.net
In reply to: Stephen Frost (#3)
Re: [PATCHES] column level privileges

Greetings,

This really probably should have gone to -hackers rather than just back
to -patches, so here it is. Comments welcome.

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

I'm not sure where we go from here. Your GSOC student has disappeared,
right? Is anyone else willing to take up the patch and work on it?

I'm willing to take it up and work on it. I'm very interested in having
column-level privileges in PG. I also have some experiance in the
gram.y and ACL areas already that should make things go quickly. If
anyone else is interested/has resources, please let me know.

Admittedly the patch's syntax is more logical (especially if you
consider the possibility of multiple tables) but I don't think we
can go against the spec. This problem invalidates the gram.y changes
and a fair amount of what was done in aclchk.c.

Agreed, we need to use the SQL spec syntax.

2. The checking of INSERT/UPDATE permissions has been moved to a
completely unacceptable time/place, namely during parse analysis instead
of at the beginning of execution. This is unusable for prepared
queries, for example, and it also fails to apply permission checking
properly for UPDATEs of inheritance trees (only the parent would get
checked). This seems not very simple to fix :-(. By the time the plan
gets to the executor it is not clear which columns were actually
specified as targets by the user and which were filled in as defaults by
the rewriter or planner. One possible solution is to add a flag field
to TargetEntry to carry the information forward.

I'll look into this, I liked the bitmap idea, personally.

Some other points that need to be considered:

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.

Well, SQL99 has per-column SELECT privilege, so we're already behind
the curve here. The main problem again is to figure out a reasonable
means for the executor to know which columns to check. TargetEntry
markings won't help here. I thought about adding a bitmap to each
RTE showing the attribute numbers explicitly referenced in the query,
but I'm unsure if that's a good solution or not.

I'd be willing to leave this as work to be done later, since 90% of
the patch is just concerned with the mechanics of storing per-column
privileges and doesn't care which ones they are exactly. But it
needs to be on the to-do list.

I think it would be quite unfortunate to not include per-column SELECT
privileges with the initial version. It has significant uses and would
really be a pretty obvious hole in our implementation.

What I think would be a more desirable solution is that the table ACL
still sets the table-level INSERT or UPDATE privilege bit as long as
you have any such privilege. In the normal case where no per-column
privilege has been granted, the per-column attacl fields all remain
NULL and that's all you need. As soon as any per-column GRANT or
REVOKE is issued against a table, expand out the per-column ACLs to
match the previous table-level state, and then apply the per-column
changes. I think you'd need an additional pg_class flag column,
say "relhascolacls", to denote whether this has been done --- then
privilege checking would know it only needs to look at the column
ACLs when this field is set.

I agree with this approach and feel it's alot cleaner as well as faster.
We definitely don't want to make permission checking take any more time
than it absolutely has to.

With this scheme we don't need per-column entries in pg_shdepend,
we can just reference the table-level bits as before. REVOKE would have
the responsibility of getting rid of per-column entries, if any, as a
followup to revoking per-table entries during a DROP USER operation.

Doesn't sound too bad.

Something else that needs to be thought about is whether system columns
have privileges or not. The patch seems to be assuming "not" in some
places, but at least for SELECT it seems like this might be sensible.
Also, you can already do COPY TO the OID column if any, so even without
any future extensions it seems like we've got the issue in front of us.

I certainly feel we should be able to have per-column privileges on
system columns, though we should only use them were appropriate, of
course.

One other mistake I noted was that the version checks added in pg_dump
and psql are ">= 80300", which of course is obsolete now.

That one's pretty easy to handle. :)

Thanks,

Stephen

#5Tom Lane
tgl@sss.pgh.pa.us
In reply to: Stephen Frost (#3)
Re: column level privileges

Stephen Frost <sfrost@snowman.net> writes:

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

I'm not sure where we go from here. Your GSOC student has disappeared,
right? Is anyone else willing to take up the patch and work on it?

I'm willing to take it up and work on it.

Excellent! As you say, you've seen that code before, so it should
go more quickly for you than most people.

One possible solution is to add a flag field
to TargetEntry to carry the information forward.

I'll look into this, I liked the bitmap idea, personally.

Yeah, I do too. What I am thinking now is that we need two bitmaps
per RTE: one showing the columns explicitly referenced (hence needing
SELECT permission) and one showing the columns assigned to (hence
needing INSERT or UPDATE as appropriate --- we will never have both
cases in one Query, so we don't need two bitmaps). It would be
fairly easy to build these in the parser, and to check them in
the executor ... the fun part would be keeping them up-to-date
while the rewriter and planner mash the query around ...

One other mistake I noted was that the version checks added in pg_dump
and psql are ">= 80300", which of course is obsolete now.

That one's pretty easy to handle. :)

Yeah, I just wanted to make sure it wasn't forgotten. It's the kind
of thing you'd not notice in testing unless you thought to try pg_dump
against old server versions (which is a good idea of course).

regards, tom lane

#6Tom Lane
tgl@sss.pgh.pa.us
In reply to: Stephen Frost (#4)
Re: [PATCHES] column level privileges

Stephen Frost <sfrost@snowman.net> writes:

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

What I think would be a more desirable solution is that the table ACL
still sets the table-level INSERT or UPDATE privilege bit as long as
you have any such privilege. ...

I agree with this approach and feel it's alot cleaner as well as faster.
We definitely don't want to make permission checking take any more time
than it absolutely has to.

It occurs to me that there's something else to be thought about here.
Given a table against which some per-column GRANTs/REVOKEs have been
issued, what is the proper privilege state for a newly added column?
I'm feeling too lazy right now to try to deconstruct what the spec
says about it, if it says anything. However, I'm pretty sure that the
patch-as-given doesn't behave very reasonably (or backwards compatibly)
on the point: it's going to end up with no privileges on the new column,
whereas our historical behavior is that the new column is accessible to
anyone with table-level privileges.

regards, tom lane

#7Andrew Dunstan
andrew@dunslane.net
In reply to: Tom Lane (#2)
Re: column level privileges

Tom Lane wrote:

I'm not sure where we go from here. Your GSOC student has disappeared,
right? Is anyone else willing to take up the patch and work on it?

No, he has not disappeared at all. He is going to work on fixing issues
and getting the work up to SQL99 level.

Your review should help enormously.

Stephen, perhaps you would like to work with him.

cheers

andrew

#8Peter Eisentraut
peter_e@gmx.net
In reply to: Tom Lane (#2)
Re: column level privileges

Am Mittwoch, 7. Mai 2008 schrieb Tom Lane:

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.

What this actually means, but doesn't say, is that there's no
table-level representation of INSERT/UPDATE privilege any more at all.
I think this is a pretty fundamental design error. �In the first place
it bloats pg_attribute with data that's entirely redundant for the
"typical" case where per-column privileges aren't used. �In the
second place it slows privilege checking for the typical case, since
instead of one check for the relation you have to do one for each
attribute. �There are some other problems too, like having to extend
pg_shdepend to include an objsubid column, and some other places where
the patch has to do awkward things because it's now lacking table-level
information about privilege checks.

I haven't read the patch, but there is also a semantic issue, namely what
happens to columns added after the grant. If the GRANT was to the table, new
columns should get the same privileges.

#9Stephen Frost
sfrost@snowman.net
In reply to: Andrew Dunstan (#7)
Re: column level privileges

* Andrew Dunstan (andrew@dunslane.net) wrote:

Tom Lane wrote:

I'm not sure where we go from here. Your GSOC student has disappeared,
right? Is anyone else willing to take up the patch and work on it?

No, he has not disappeared at all. He is going to work on fixing issues
and getting the work up to SQL99 level.

Great!

Your review should help enormously.

Stephen, perhaps you would like to work with him.

I'd be happy to.

Thanks,

Stephen

#10Stephen Frost
sfrost@snowman.net
In reply to: Tom Lane (#6)
Re: [PATCHES] column level privileges

Tom, et al,

Looks like Andrew's GSoC student has gotten busy at his new job, so I'm
back on to this and hope to have a new patch ready to go for the Sept.
commitfest. If anyone's interested in helping out, feel free to drop me
a line.

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

It occurs to me that there's something else to be thought about here.
Given a table against which some per-column GRANTs/REVOKEs have been
issued, what is the proper privilege state for a newly added column?

I'll look over what the spec has to say about it. I'm not sure which
way really makes the most sense. I'm tempted to say that if you've
started doing per-column GRANTs/REVOKEs then you should be on the hook
for explicitly saying what the permissions on the new column should be,
with the default being no permissions. As far as I'm concerned, that
doesn't break backwards compatibility (you've started using the new
grant/revoke syntax by this point already), it just might be breaking
some backwards expectations. :)

Thanks,

Stephen