[PATCH] DefaultACLs

Started by Petr Jelinekover 16 years ago102 messages
#1Petr Jelinek
pjmodos@pjmodos.net
1 attachment(s)

Hello,

this is first public version of our DefaultACLs patch as described on
http://wiki.postgresql.org/wiki/DefaultACL .
It allows GRANT/REVOKE permissions to be inherited by objects based on
schema permissions at create type by use of ALTER SCHEMA foo SET DEFAULT
PRIVILEGES ON TABLE SELECT TO bar syntax. There is also ADD and DROP for
appending and removing those default privileges. It works for tables,
views, sequences and functions. More info about syntax and some previous
discussion is on wiki.

There is also GRANT DEFAULT PRIVILEGES ON tablename which *replaces*
current object privileges with the default ones. Only owner can do both
of those commands (ALTER SCHEMA can be done only by schema owner and
GRANT can be done only by object owner).

It adds new catalog table which stores the default permissions for given
schema and object type. We didn't add syscache entry for that as Stephen
Frost didn't feel we should do that (yet). Three functions were also
exported from aclchk.c because most of the ALTER SCHEMA stuff is done in
schemacmds.c.

The current version is fully working and includes some regression tests.
There is however no documentation at this moment.
Patch is against current Git HEAD (it is context diff).

--
Regards
Petr Jelinek (PJMODOS)

Attachments:

defaultacls.diff.gzapplication/x-tar; name=defaultacls.diff.gzDownload
��\Jdefaultacls.diff�\mw������
����l��,K�����(J��c�ZJ�����"!�k�TI*�o���;3x!H��������ql	��`�0������x���9Mb�t���<�N]'u�hq����s?�l�5��z�;o6~�<9q��Y��Y�����?9<<��������:����x2}s;��/������}����x�*��s��$���=���a9�
������O��v��n����3��Kg��<�M���Y��'<�)_�'��<���m�c?��j��U0x�����\������Y�d������Y�����$��$�4�(�
����5�����?q+�@e�q�����a���l>s;��Y3�gMRg1|-��5������������@�*�Aa������[H����hM~�*yF+7p�d+��4���h5w�~��$eR�4�NOH���N� 9M�q�6:.r����l���~'���.{�~����	S�����QX�)�C'�dv��?�t�6�W�}�9	�������*&�bBJ��*Y�eu��J���+mVd�����'���������*�������8�EWc��VN��N���U�����I}����^Z���u5��X�
�F� ���e���<[������e�b�W�D�$Ic�vetu�hI��;'f���6�,[��R�B0�+�:����9R�`�@H0A�{�$[�EQ��������{��E�B=2;K�(e�.�
�Jf��������U���&s���I�Q\o��`��!|h�(����j�qd��~�5�!gQ�/|P�z�[�c�����i6��1$.y�b��O��X(S2�X^H�"��l!(�����K~��������������WH��jv��g��}V7���+���I��kZg�N�9�z-H����t��	�3�#��#t��R��c��4���u�I��+����r	MJ�������Gv;�~��PJ�������H]b��8D.����K��]��:� 'sbJ4��0�g�j4�Jx.z\;=�?�c��W���wWSvs;�~t5|3���S���Y��Q!d��_�
O�L�F�c@�?��sMD!��5g��8C��=����;&Y<CJ�sc�F4��m`|#�.�p��uDC��NI�*2���C��A������o���
/�Ep�#�@�V[h�A!KG^���3�,���]^\X���se�H/MD"'
�=���B
c������,v �{�`�|��
�u�f�:�J��V�����(i�����i�HBf�qJs\��gY�{�(�#&�y �>��/����.�w�����>�8f�(���������`j�������������������miJ��ns��y)����Y}%5d��(�/���F}x{;���]�1w�E����������+���j �0m��������W�'`T50�L��@U��� @;�����ZM���;+\k�u�B�E$q=?�O���*r���/�����t{���BC%�^��Th8�a���0��G��^/k5p��I$^]�����"S�t�
��@�U�|����`N#R�>j�~wuU��f��oAS�Y38g�@k����w�5O�y���"[������� ���W
�H-�^,�r��$�m�%��e��3�%���jR1��
��&�����c2�E^f�x�J�6�K����$����I���G��pxZ���{'-�:���F+LD��z����a�	�'������d��[y%���sJ�`o������`*����9�G=�<f.8$9@%��Od�d�-�x���[��(T)N���i����!�pe��c1*q
�>�.�J��)�.�y�+r��A�z��v��Dn(��������"'���XC��J��������e���r �q:~!y�~�!l;�������P���{r��np.@�I�n�<��G�v�B��|���������Q��`b7��8%��YaRh��[����y u��5i��]��:1:��]��hz�
)�Rv��)����z��Fe����+���UUvj�(r�z+'1����[����.l8�\z4�)
);IF���$\SRb�P�"�J7����e�����'�f�����f�
bBk{,c�P�1������� ��	R������j(�!�1%9)�A]Y�/��M�W��r�J����Nx0F2%7t���h� �|��> \�`d�����xGU��Qc){AL$���xp��C����G��	����5�A&�
���+�T�Ar�)�0JV�b��M������@{�:��`�����*��a�!m��!)��Np�����%���!���h�J9
��/�M=C�����
����R�)K��wo�~A�|����1�}0��2���
���0�����yL�o��y�y<��L|\���h6�T&F�ry.'�������bBdL���Y4����k!�� �_b�OB[�`�����VS���/��������M;�W	��
�����:�*u���l ���#���U��������,�<s��q��<�Xg��>�T�S��4R P�<���-W�-F�����e'����!������r����������M��m*�Qm�$�����Z�A�i�1���i�Y���%�6��E[�]�s��`bGsOv���Q=J��ttuz�����LzH����O�e	�'�)@@�A���72����m(�>�o�����n;&+�W������C�M��M�|�1��V����<�����aZ�Zt���
0�\[P�D�b�DB�Hc�@s���>�"�����<��E0^���5��~���J�>��1I�2� ��b��������p*�d�ZEq��w>��^'k'%�w�5�j��������I��T��7��?�_��������M0d����$�q2����\���i����),��m��}]�)v�������l���&�h`v*Ey�.��R�b�B�^5��t�d���BCP�����B?�?CUo�d�f(TT�G�]c�P�y9bp�$�A���kt�{m��Cm����6���Z����4K���)��e�o���i�
�YJ_�q<�����!�VmP[�n`B���}�(H�\�����|f|�W[l:��.J����Y�u��\�4��:�nD��{d������prU.;�\0Vj��i���6���x�/�Y����/�^���3@X��69`����������~�$��/�#����I���0�j�a��pc�D����b/�NNf��n�ww���T���
�@��-���=.	=V`�p������ �	�q�n�E�J�c�.���[�.$�p��6�/��<D��F�'LJ5�h6��T�R��j�����!����2��k��<@����#;����[��sz�!Z/z*�#pqN*�P�[F0��zV��6��.1����-_F���E<������q��?�C+�j��E������U2��K�H_-�'�U�^���-7��v|5���W}^�'Y�����@T���*��h�P�'�4�`�S�Z�r�u~��N���kv�!���%��m���G���}�'��+H`���<��"��Uv��dxk�������h� 0�6+�`w�G�Uf#I-�q[8$�~����� ?��1/S��5��D�B��������n���=S�j�������h���_s��R9�:��uL����)������c��6�KM4bu���Y�R�)�W	d������:��e{�T������l�����Gu5Z5~��m9Q���q���MVd��P��Z=���+�e2���<c(U���C�E�~���(������Ea�aGSz���?SN'��]?�u�7�E_��v�0�^�L�Z���U����;��
4�rvR�<	�pT��d5�%��o��_��#5�#N@
����}��}�&��^�i�w��t�Ek7R����$���8��]J��8Z�~(���H
��_f�,�I�����<�>�|��v�>sbsLU-��Dw����/��D�=9�7=�rU�{r.l�l��HK9�wV��Y��a�*.�[����\��L���u:��f��@�{�tw��d����$([�u{V�����d]'o��wZ.6�C�t�h#K/����zht���'���m�X����gD�
7��[�
)�w�n�e���	���� ��-P
��'j���L�hG�Pl
v8b�
�,6�
�`��44��%�qX�������N��G��������/n<p�N��/:99)C��&QX�v/��ALU�������+������,��dJ�R���[x�}C���"��sG���PJ�q;a������%���)��)�p�.���bn���;��C�����k��) ,��Gil��Z3S%$�,�����0V�����_~�H.*������a�c�{��������W[��a��@R�+��g��mv��c����5�����%K�����:�}������b%eGU�c�J�����y-�ssT(Q��|W$�T�B�T@$�*dR4-�D7��c d�E���#����>;��`���@�X���Jl��%�j9�n�
��b>�u:g��U��ae4e �7u��2��[�y�=_�g�3�.n����
\�����@��C"<d��#nt�o�3������R`�L~���2�� SI����0b����}�m,�@>bwR��Q�h=��|u��d�n7�v����P�&��QVX'dF�m������|.����S!$���}c�S���g��y�v�m��;���%��������P�u!��$��CV��=�"En����4�:�K�sn�7u`A��f������������������p�����y
r,�E�Y�����yl�ru����Ck�n����-����$��S;�.�d���Hh�����sqr���3�y[�I)�xRJWQZ]�$Z������3o���3m)9�����|!.;dV��`�HF����0�
�]�)�~w�������Jc�
$�\�[�a�ggV�������6�e�F�S��J�&]ID�l��&�x��"�E�F�En�}c���dml��� s�^���36Q��i��&����>1��H�����������a8-��Y�#q5���z�����c��G���G�\�d�3�	�5��e�,*���=F&��V��X���C0&�qD���y�q����������a:d������
w*��h�7��i�!a]�{�:fF�ku����Prg4��tq�Rj�^k�H������[���{,�lF��U�5��W��B�����W��c���� ��{��tT���Xr!��m���4���e����z8�P���<����U�v�0sF��"���`\��^}���8��R<�"�%�����J�5V_���{�������W���h�����L�\�
��i,6%_zF^v��F���2A^u���@r-���$+
�.#b�-��2�\:�-6��7��������(��`42�E�|P9�R�y&c��}=������f|8�^q����X�0��O+#'L�k��I��G��^]���A)�dc�{��X>�!O�q�u3�!o+��^Q?Wf8II��Vaq(G(���b�S|�0z��a<7�q�`mH�U�����8���U�����36,������~�=lYD,N��q��b�e��:)���P�2$$g��H����oGo��N4���PV����Cj�Q����{4)F/H�2`�|�b38c��������>��u�c��-T�����u�=ZY	��������������	/oe>�@_��w�~^�<U
^����lrIKuL{1�+��a<
��)�'�-+�/�_�k{� �����s���`()5��M�bnH	���7�}��nde�9R-����k&�z�1��4�BT�[�K�-/����,t�"��&FNG�m�-�(��C>��?���F_\z(c�����C�lqSO�����P��U�I��T�9���hn�+q�Q�8�W'����.���D�y�2�ch�R��`�^��X	Z�Iu��I*IK��8I2�Y -��2��.���~Q��j��������G���j�e������K���p��Q0Bo�I��iO������3������W�����=������S"r�VW��*���i���.���b�+}�TT�_��G�5�)e*,���E�Q~4���o{W�����?���y��<�9$EJ�%U�D;��%�Hm�[[��C�g�d8�%g����n�50����$���(4������N�� l��:�G������/����0#�+i
��)�i1.6�nN���88�����c*)��zk4��c��������<o���i��2|���7/<ww: ��o�����<��^ea�"������8;V!Q�D���dz��^\:W�+���B�x�wL�sc�����������@r��:���8�>?�v���C1������!�q�����9���<!z%��'O[�
�>T����V�V��F'���1:���1l1��[��
T�������Z���;4�7��	��]���!�kzCoL���(��/�d�g�e-�B^0b(���8S�`'L:��Gy	���tfT�F �)8�],@N���@�H�����q�g�-�E�c�0OI2���x�o�p!'�<A����-{��#f&)��1G��0�����Q��P%��V���M:����x)��$�Kr����������5d��)f,������0�d&~#0PX� ���Mt��f�>��:�:�0f�W���c�;����9>���{�R�!�;i2#�f��?w�����J�������a
��
���������"\ az��p4_���c�e��
gbC��x� ���+Y�mW����zo����6�C-�8vQk��b�x���4���"����9��e5����D��|�.��
,,kF!�����`��R��T�W"��E��>M�|W=�vV9N�C��QU��y�g)�������M�HY#��Z�c�V���g�g�8bG��������;=�x{pR�� )n�4F~�eyo]�E{�C^�<j`W!.������/n�9������o���0��_n���w�Fe�1���������a|����T�	�K��][y����(#�n���O��-��
�@���z�c^l�
���<�b�o6>h�9���na3|0�w�4�D
DG-Q<+�:=2�2��#�2Dm�@2�&]����t6L��~L�����m�.�kk����W*;�Ak/����s������r= �&q9��Q�5"���T'����1(�<�'��rm���p�RrG���^rTY ���w�)��8q4�����G��7�������c9#c�\�	�� �n�MY��E���h��Rt���#����h���?��Pu�2kH
,eT��f3����'9���A�M��s9��F<�R�k?�`a6����3,(�d�2���jKs��{���N�zj�b^���MlA�7�a���T���Z?-0��d��V���F���-�_[�8�������,��������I���}=����\�WMJ)</E��SS	�r�-bV��5
y����F7�T�@�6�������!���7C[�z��ZH4��7��~N�e�>��|!���yO}�Y<`��j��T>8s����[5�A���0��3���k���\����dE����m����T�
*���r��E�n�NYh(���,���{d{�a��|8U������M�
�m��m�@�%�m����w���E����!���F��x�h��$|�a��j�n%V���b��I%q�}�.���y���'��[Be@��2b�8f@ �����D�='����_99���� ��$�b65�J���~�&��}x��f���T�	2���|�)Ao?�Y�n��������@���8?Id�����4>U3���v����$3�t�T6�/�C5�����������f�����)�o5<0V�����F�Hd�
�p��V�0�9���E�l���P��� �}[J���������R*2���O���:3�A|�eNc^�i8qf�e/������t����
�9�/G���-
���7���^����y�����b�"����������
�[s}l�F$%���`���H�R�F1����!yh���|7�<�dn���T�E���N�wQ�g
���!��*���	mT�iu�H�h&�����;P/LO���o���9���S�N�Vz3���w����,��j��Kr�g��z���5g�y2M�������S���o��"E�u���	�.��i�����y�:���%��reaPiXk�X!#��2q�0@A����~
����d��L�5���0��"��0f�/0-�x�d�%;Q���nY�~��g2.| �9k��{s����Z�l��=�#���
;J��m EQ�Hz�����<��,&HR�3!J
�!D�5W�^��y<OU���G���;Q\�
�B���L���WR��*�����&D[&�6����y)��W�X���n�OD��'/��/G^��J��������xV�$�
��A��G��������-��.�!�s���3�1xE�q��������G���J�vW����0!���Oj����/bA�����g��#9D��~T����-�g�1��
��Hc����W� w8]������-���H�:3���s�x���������g����W	�>j�W0*��14&@�
�����1�f{���,���f/�h���r��2���Q���z�X��!�g�R���B���y�tw��x6���\��Wt��������?��
�������9{�[x ��N��o�~$2}�m�������E�!{�1}��*_�
���N���V��}�J�@��I�0'-���{x;w�q�~����:� �Z�&�C��B	Y5e��'����G����$��f�+���e��Rg��S��X4Cz��_PY�r��)�.�^^�=q��C��:�-�`���l�{{Q�W�a:������_	��66����w<�C�jn�fA]�P�i�����T$�5�
a`1��6H,��U)g�V���a�F��@R���aC+6�='�$�v�9m\�u���{�g�F���|2:����L���
��K��R��=s#�>8��j�[1��ub�,���u���z+�U	1�2��F����Il����Q���6����?����-WCQ�q�����I�E7��!nIM�L�c��~)/P,G4.?���p�HT�(Y@(s2wzM���=�~]��g�R�[#�v�X��F��5j4����S��C
��':��1�m��j�c�1(V�[l�����k2���Q���DT�H�����MpV��h�*c#�_PC7����?���F�f\��������J�#7�����2��87�;A�W�3D6�h�_BO#�
�Mn&�?1���p6 +�+
�K�jul�N8��V��e^'���*�D @�����A��tX3��v�����u�+�.�\sr.�'WoL���p��y�r������90����9$I�[z�������~���[����"�h&
�u�7��^�jQl��WY��E&n��:�D�|6��|
f�W G�3�S�.��5��<�.�	��|5o�Ze��1���+�����3��mQ�B�
!���U�uk�CS�����j����j5g+)=��/Ex��k6MY?%u�R�S�Eg��M���l	�y�k���������^$�Q�}�/pJ?�,�d3	�i��yW�����X-����4D)s9��xFK�X8��k�Z2�7	�}������xp-p*��l�R����M	��e.���[�cC�j������H���sn��x�I+��2����F�a��n�Z�H8�����!U���G���}��:�B#��1J������
�(���h:YT���q�.$��$Ee�h���SY����j�I�"+�9�������:�����U���a��Y{C{�L��k��
<
rRV����}/]��
��$\�� ���`�`�}?"48,��#_������vh���C�	9���
[�n+%����|��S9�Q&���3&����z:�=[�Dy�H��F�?�T��V���48��g	'�+upkxq��'�6Q<�r����"M�WK�[��]������3����*a+0�����Y�	������M����s��})z���H����O�F�����U*�i��  ��
��K	U��e
8����x����>�'k-L�n�yt|��+�M�Ro���X�#���h�,~j=�t����P��5���x�����
o�������I�>��;��c.���T8oTyP���MR@V=9{�����<:��<Yl�+*\���*���y��]�����6���x�Ri
v���(�����s���b	u����������zF�-���_�o�aEi�_0�fEu��G�dC����/}/)��(�"�=���-P���$���a/��za4w�*T:����H��"�F3����"@�C��������>�!*I�08��-@�S���g��7�\v��k�"r
V��\��������ot~L]�n�#��dt
���t�������?�DR�����.���xXW��H�����Y��1$��i��p6H��acw5�,�L@"�,p����h�.O��I�w|z����t.\�����Y5����j��2�1�=�X���N�����������Q���U���o$���v��!��2�c��T�|��%^��CB��#$�{/��Uun����~����p
I�[!J%�j����i6�������qc��%��[�D����<M[�X����%?��s@0D=n2�,.S��b<_��v��S@MCa>dIP{pZ~wy|r�=>lw����=���4�����F�1�T���������$���$����sxQ~�T��sa�GKz�3��e	�wv|�E������L?�]*����"����7�<JUD��F����v��R~�X���YO�OL3R?&GU���/�'��� *���������9�+p��8���JA�i"�l1����8^N0c����<�o"��=O������H��T*i��4UV)f�P>�lJ���{�q��R8h��~���.1�nH�?C}������~��A����Cf�����;e����P�i�3�f������[�V��E�|�-�i��O��n'v����e���������|@��$�lW���n\�����@��	�R0du������*9�l5��l��K ?UL��;�/QBMA|<'�����jH�'�Ca$��/;G=R����&a�h�G��9���^x�D���bT����X�b0���
L[)�5vE��q��:��[����������Z�cQ�}p:"�>,w��	d��#���)/t(dB���%���%;eB�VV
���o-����S�
Rn@����xU}�>}���!~LG>eP�|H�X>='��o�<3�1��� �Jr0��xUG�H�^�~�.��\�u0���zms9�I������,����	���.��>
�J�H��%���)^��O_O����Z������^���{�����?�'���(K��Qye�#���5��tz?n�
���q����W����CX����!vz��)T|=��S���"��U��jc���dj����]���U��_?0,{��ky(z��u}#�c�6�0-�3.�j�6�RV�a�u�z�<������.�T�	����q_
���G���79�f(|P��Q�b����5�������H����h�~,����#o
��w(i^j��8�S<x'�s���C@p���B$b!���$�D���$�E�{-L���y��H�/Qjy����&T�,�ri��0,��w�]Q�G��;��nT,q�����-|����u^�������jT#�D���p�=��S����&�.�R��[������Ex�Or�'��	M�W����$FiY�l��V1Yg.:Xd_t�8��;�M��^m����W*;�`w�^�v�S�y���[����\	�{��D�&�����p��"A��E?I�	Y�H��;�W%D��{���^�]����N�u/�mH�VF��������fT���-�����o&�t���T��.C"�~�M���}?_��#��p0|���7�=-�88=:{������Y����U<F{��
4�������n�R�������,B,gE*���R�U�g�b.�_����Afh�m������Q[��<[�0,����w�3��]�G2�[:"#;3`��j�<��������vZ�4��Q����(�W���2?J������O�2����������k�����@$,;���_�q� k�=X������\q���o�{#>�|�W�G\(���<��'_��t�l�~R�L,5�<�o���@X���y��n��/mKS=4Yq�XYQ�pU<�S[����N��|+��\�"���T���
�~?��QO�����U�F�S^c����:��$��x4���a���;���Fx�
d�_o��H
3x"�m
����/!6@4�!���	�:e,T���h����DD&-s��gv�w���,��Dlc��t=SN��3����q���e�������3��x�i�	ErH���j�J�����j�Ch�<[Pj���s��{����2?���wp!x�Y�j�TZ�WEl5�+����v�j��2�@���&�q��R?$��L��a<��C��;�<��e@�u����*oMs
��KbH�O��J��3�1�`��0���M
���1p�v�}��8��V�sF��b��������+PuxnO7���/�G�l0������e���M� ����~v��gw>hnQ�^�e���_��h������s��Fn���h��,���0��Wnv���h��8�G����p3����8lb�h��Q���\d�������&�5:�OX1�L���EzO��L����t��pKev��k�:Q�!�5��U*I��l�<�Z��uj��.��X�$z����e^�V��Z��N�[#�5���!��v_��=�-1v<����!�0i-H&K��H"|�.;o����FP!�q�qq�HE���$���-l������jo8Q	�@(-�_�%�{xt����M��JVV����}�.����L��A�����'�TH�����$@y��������m��`�0G���:*�c��>�9?����=�8����o����a{����!�8?����#{��������la����s.��o�����~�RD�,���������F<���#��=���O;��.<<w��G��0��'�0���b�_��,~ ����d3���Q!S
~�.{�_F��s���R������$%�/K�
f�Ex)�J\r���g�S}z=����#���Fr/�o�&w�Z����m�}�����R�=;Y���M!����DZdm��>�������|{���Uf^xv:w��IH)���F-��p�_^���{���m/�����G����m{k����:��*U8��!<��/9#�g�k�
�����a���w��D��'�Kss�T��Kz<��@y������Q*�S3��N���G�y��*��[��T��������cs��]���5����i=�^�@��D,j`���(��	��$�8|Y|���/�r�V�u~d��Q_F���kIV���ze���X�������/?1���u�����+��J9~����3.P
(����hw//N;0� �<��;��������KP�e?M&�O���B�� -�b��7�_�$�l3s�X�6�vl[��,���]�_�wp���������,�����f]�-��L(�NS�P���.����*�v���1���}l��HsksF�A�
�a�v��l��N�q'd�����*O@��#R�B��.Y<���8�s�P���GMR_>��$������jS�<��@�<7����^iT6���?B�FY��c��������%����0=�[y��F��9��Q�S��[�85��S�e���[�\��������Zbm��x��A����
�I�u������}0���4�����)"�G�E�7`G����:\�{�?p����{���q�=�����T����+k[���H[0�����O!�$�
���{�F&�3�v�-�/��
�-�z�H>�����~dKC�+u�������g��ZS2_�I����h��N�y)��F!�U����_��K������v�������'R-��=�y����/��9�N�`��U�{X��!_�2�:�#K2_v�����W�#�iq��P�0~��g�Uk yT��(��=^B�Z��d��d�,��F�n���9-n�����g�P�g�P�g�P�g�(����Y��Y������.���L=��y^ �������G��{?Y�
���>�B�g~R�g8)�3���.�'7�"������.���������S�UA�7�x�K���i��^�d��-2��� �M^R�p���,�d�fn�gWq5L��u��<����^\U�8����� uv�e��'�&J�EB �z�w�;�CI�@����dy]0��t������|����@����o
��,��R�|1s�RB^.`�\@F����7�&��,@���!����(����9D~�k5����a�����p��
�H����!��@��^@�<Y����������x��
5,H^\�3;�'��%`'8�#�s�7s	���
�fX�<��!cb�"B�OJ^�w��r+�"g����9� d�L�6�����y1��r������
2@@y�`�Z��0����N�p��s��%��{X��&�fS����_���)��=B�&O�{�Y��e�Y���Mfw~93�~��Fi����U�wV���^�=@-�d5�3j�f��M^,�5�����t��d�k��Y�����9r:
�����.�9�6h	8������������`//�d>�o�N����w��C��~AU��'�9W��q�*/Q���
Um2�U���
�^|`����(@(�ag�Gg���d�>����(�����_S���W*m�URR+iz@������4K��C�%�>�[���i�4=�^���~I�?�`"t�����*&E��T�BJ&Mh���f��Ej���I�?�i���&E��4}E�C�&M��zN�d�~����4N�P9iz@���!����Nj|Bj'�>�w����I��<Yt�Z�{����B���R?��!��NP@�WE�^�)���2��J(B�j����"t�J��(Ei�4=��R��.J��(Ei�(���R��G�tW!��;)����\���r��R*Gw�R��SK�����=��R��4S�PM�sT@7E��^�9�y�S��������
�C�i�(���r�9�{�T���RQ�OMe��z*B�*�(�����U�{uU��UVy�������*���W�Wae�=+J���{uV$���z�R��{�Z���"t�^��}�-B�j����r�yZ��"t�v��}�-J���|t����y
�M���lz�b���"t��K�j.��=�JRt��!M���U]��u��C�.Mh�T�!u���]��SxQ�O�e��*/J����t[�������^��{��
)����2��U_��}Q�O����:��/C/��
)�����_LVlf�9�B�O�}W>��TL���*�����jx�Y�O�����u�L��5Z��i�S�o�lb��|
c��w�����yS��A=���y��/�E3������K����-���:������|�(N����R>�_�#��	��r�����������c	�,�:�*��Gr�y����1�*
#2Nikhil Sontakke
nikhil.sontakke@enterprisedb.com
In reply to: Petr Jelinek (#1)
Re: [PATCH] DefaultACLs

Hi Petr,

this is first public version of our DefaultACLs patch as described on
http://wiki.postgresql.org/wiki/DefaultACL .

I have been assigned by Robert to do an initial review of your "GRANT
ON ALL" patch mentioned here
(http://archives.postgresql.org/pgsql-hackers/2009-07/msg00207.php)

Does this new DefaultACL patch nullify this earlier one? Or it is
different and should be looked at first since it was added to the
commitfest before the defaultACL patch? It is a bit confusing. Please
clarify.

Regards,
Nikhils

It allows GRANT/REVOKE permissions to be inherited by objects based on
schema permissions at create type by use of ALTER SCHEMA foo SET DEFAULT
PRIVILEGES ON TABLE SELECT TO bar syntax. There is also ADD and DROP for
appending and removing those default privileges. It works for tables, views,
sequences and functions. More info about syntax and some previous discussion
is on wiki.

There is also GRANT DEFAULT PRIVILEGES ON tablename which *replaces* current
object privileges with the default ones. Only owner can do both of those
commands (ALTER SCHEMA can be done only by schema owner and GRANT can be
done only by object owner).

It adds new catalog table which stores the default permissions for given
schema and object type. We didn't add syscache entry for that as Stephen
Frost didn't feel we should do that (yet). Three functions were also
exported from aclchk.c because most of the ALTER SCHEMA stuff is done in
schemacmds.c.

The current version is fully working and includes some regression tests.
There is however no documentation at this moment.
Patch is against current Git HEAD (it is context diff).

--
Regards
Petr Jelinek (PJMODOS)

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--
http://www.enterprisedb.com

#3Petr Jelinek
pjmodos@pjmodos.net
In reply to: Nikhil Sontakke (#2)
Re: [PATCH] DefaultACLs

Nikhil Sontakke wrote:

Does this new DefaultACL patch nullify this earlier one? Or it is
different and should be looked at first since it was added to the
commitfest before the defaultACL patch? It is a bit confusing. Please
clarify.

No, DefaultACLs applies to objects created in the future while GRANT ON
ALL affects existing objects.
DefaultACLs is more important functionality so it should probably take
precedence in review process.

There is however one thing that needs some attention. Both patches add
distinction between VIEW and TABLE objects for acls into parser and they
both do it differently. GRANT ON ALL works by adding ACL_OBJECT_VIEW and
tracks that object type in code (that was my original method in both
patches) while DefaultACLs uses method suggested by Stephen Frost which
is creating new enum with relation, view, function and sequence members
(those are object types for which both DefaultACLs and GRANT ON ALL are
applicable). The second method has advantage of minimal changes to
existing code.
It's pointless to use both methods so one of the patches will have to be
adjusted. The problem is that most people seem to dislike the addition
of ACL_OBJECT_VIEW but on the other hand I don't like the idea of adding
another object type variable into GrantStmt struct which would be needed
if we adjusted GRANT ON ALL to Stephen Frost's method.

--
Regards
Petr Jelinek (PJMODOS)

#4Nikhil Sontakke
nikhil.sontakke@enterprisedb.com
In reply to: Petr Jelinek (#3)
Re: [PATCH] DefaultACLs

Hi,

No, DefaultACLs applies to objects created in the future while GRANT ON ALL
affects existing objects.

I see.

DefaultACLs is more important functionality so it should probably take
precedence in review process.

There is however one thing that needs some attention. Both patches add
distinction between VIEW and TABLE objects for acls into parser and they
both do it differently. GRANT ON ALL works by adding ACL_OBJECT_VIEW and
tracks that object type in code (that was my original method in both
patches) while DefaultACLs uses method suggested by Stephen Frost which is
creating new enum with relation, view, function and sequence members (those
are object types for which both DefaultACLs and GRANT ON ALL are
applicable). The second method has advantage of minimal changes to existing
code.

I briefly looked at the DefaultACLs patch. Can you not re-use the
GrantStmt structure for the defaults purpose too? You might have to
introduce an "is_default" boolean similar to the "is_schema" boolean
that you have added in the "GRANT ON ALL" patch. If you think you can
re-use the GrantStmt structure, then we might as well stick with the
existing object type code and not add the enums in the DefaultACLs
patch too..

Regards,
Nikhils
--
http://www.enterprisedb.com

#5Petr Jelinek
pjmodos@pjmodos.net
In reply to: Petr Jelinek (#3)
Re: [PATCH] DefaultACLs

Nikhil Sontakke wrote:

There is however one thing that needs some attention. Both patches add
distinction between VIEW and TABLE objects for acls into parser and they
both do it differently. GRANT ON ALL works by adding ACL_OBJECT_VIEW and
tracks that object type in code (that was my original method in both
patches) while DefaultACLs uses method suggested by Stephen Frost which is
creating new enum with relation, view, function and sequence members (those
are object types for which both DefaultACLs and GRANT ON ALL are
applicable). The second method has advantage of minimal changes to existing
code.

I briefly looked at the DefaultACLs patch. Can you not re-use the
GrantStmt structure for the defaults purpose too? You might have to
introduce an "is_default" boolean similar to the "is_schema" boolean
that you have added in the "GRANT ON ALL" patch. If you think you can
re-use the GrantStmt structure, then we might as well stick with the
existing object type code and not add the enums in the DefaultACLs
patch too..

No we can't use the GrantStmt and I wasn't talking about using it. I was
talking about the change in GrantObjectType and differentiating VIEW and
TABLE in some code inside aclchk.c which people didn't like. We can use
the changed GrantObjectType in DefaultACLs and filter inapplicable types
inside C code as I do in GRANT ON ALL patch and it's what I did
originally, but submitted version of DefaultACLs behaves differently.

--
Regards
Petr Jelinek (PJMODOS)

#6Stephen Frost
sfrost@snowman.net
In reply to: Nikhil Sontakke (#4)
Re: [PATCH] DefaultACLs

Nikhil,

* Nikhil Sontakke (nikhil.sontakke@enterprisedb.com) wrote:

I briefly looked at the DefaultACLs patch. Can you not re-use the
GrantStmt structure for the defaults purpose too? You might have to
introduce an "is_default" boolean similar to the "is_schema" boolean
that you have added in the "GRANT ON ALL" patch. If you think you can
re-use the GrantStmt structure, then we might as well stick with the
existing object type code and not add the enums in the DefaultACLs
patch too..

Petr and I discussed this. Part of the problem is that the regular
grant enums don't distinguish between TABLE and VIEW because they don't
need to. We need to with DefaultACL because users see those as distinct
types of objects even though we track them in the same catalog.
Splitting up RELATION into TABLE and VIEW in the grant enum would
increase the changes quite a bit in otherwise unrelated paths.
Additionally, not all of the grantable types are applicable for
DefaultACL since DefaultACLs are associated with objects in schemas
(eg: database permissions, schema permissions, etc).

We can certainly do it either way, but I don't see much downside to
having a new enum and a number of downsides with modifying the existing
grant enums.

Thanks,

Stephen

#7Nikhil Sontakke
nikhil.sontakke@enterprisedb.com
In reply to: Stephen Frost (#6)
Re: [PATCH] DefaultACLs

Hi,

I briefly looked at the DefaultACLs patch. Can you not re-use the
GrantStmt structure for the defaults purpose too? You might have to
introduce an "is_default" boolean similar to the "is_schema" boolean
that  you have added in the "GRANT ON ALL" patch. If you think you can
re-use the GrantStmt structure, then we might as well stick with the
existing object type code and not add the enums in the DefaultACLs
patch too..

Petr and I discussed this.  Part of the problem is that the regular
grant enums don't distinguish between TABLE and VIEW because they don't
need to.  We need to with DefaultACL because users see those as distinct
types of objects even though we track them in the same catalog.
Splitting up RELATION into TABLE and VIEW in the grant enum would
increase the changes quite a bit in otherwise unrelated paths.
Additionally, not all of the grantable types are applicable for
DefaultACL since DefaultACLs are associated with objects in schemas
(eg: database permissions, schema permissions, etc).

Ok.

We can certainly do it either way, but I don't see much downside to
having a new enum and a number of downsides with modifying the existing
grant enums.

Sure, I understand. But if we want to go the DefaultACLs way, then we
need to change the "GRANT ON ALL" patch a bit too for the sake of
uniformity - don't we? There is indeed benefit in managing ACLs for
existing objects, so that patch has some value too.

Regards,
Nikhils
--
http://www.enterprisedb.com

#8Stephen Frost
sfrost@snowman.net
In reply to: Nikhil Sontakke (#7)
Re: [PATCH] DefaultACLs

Hey,

* Nikhil Sontakke (nikhil.sontakke@enterprisedb.com) wrote:

We can certainly do it either way, but I don't see much downside to
having a new enum and a number of downsides with modifying the existing
grant enums.

Sure, I understand. But if we want to go the DefaultACLs way, then we
need to change the "GRANT ON ALL" patch a bit too for the sake of
uniformity - don't we? There is indeed benefit in managing ACLs for
existing objects, so that patch has some value too.

I agree that they should be consistant. The GRANT ON ALL shares alot
more of the syntax with GRANT than DefaultACL though, which makes it a
more interesting question there. I can understand not wanting to
duplicate the GRANT syntax. I think my suggestion would be to add a
field to the structure passed around by GRANT which indicates if 'VIEW'
was requested or not in the command. This could be used both for GRANT
ON ALL and to allow 'GRANT ON VIEW blah' to verify that the relation
being granted on is a view.

Thanks,

Stephen

#9Petr Jelinek
pjmodos@pjmodos.net
In reply to: Stephen Frost (#8)
Re: [PATCH] DefaultACLs

Stephen Frost wrote:

I agree that they should be consistant. The GRANT ON ALL shares alot
more of the syntax with GRANT than DefaultACL though, which makes it a
more interesting question there. I can understand not wanting to
duplicate the GRANT syntax. I think my suggestion would be to add a
field to the structure passed around by GRANT which indicates if 'VIEW'
was requested or not in the command. This could be used both for GRANT
ON ALL and to allow 'GRANT ON VIEW blah' to verify that the relation
being granted on is a view.

I arrived into this conclusion too, but it adds a lot of clutter in
gram.y (setting that flag to false or something in many places, just to
use in in one place).

Originally I thought adding ACL_OBJECT_VIEW wasn't such a bad idea. But
after I looked more closely at the code, it it seems to me that having
same object type for VIEW and TABLE seems like the only logical reason
why GRANT uses separate object type enum at all (instead of using subset
of ObjectType like other commands do). If we went this path of
separating VIEW and TABLE in GRANT code it might be cleaner to remove
GrantObjectType and use ObjectType, but I don't think we want to do that.

--
Regards
Petr Jelinek (PJMODOS)

#10Joshua Tolley
eggyknap@gmail.com
In reply to: Petr Jelinek (#1)
Re: [PATCH] DefaultACLs

On Tue, Jul 14, 2009 at 11:10:00PM +0200, Petr Jelinek wrote:

Hello,

this is first public version of our DefaultACLs patch as described on
http://wiki.postgresql.org/wiki/DefaultACL .

I've been asked to review this. I can't get it to build, because of a missing
semicolon on line 1608. I fixed it in my version, and it applied cleanly to
head (with some offset hunks in gram.y). I've not yet finished building and
testing; results to follow later.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

#11Joshua Tolley
eggyknap@gmail.com
In reply to: Petr Jelinek (#1)
Re: [PATCH] DefaultACLs

On Tue, Jul 14, 2009 at 11:10:00PM +0200, Petr Jelinek wrote:

Hello,

this is first public version of our DefaultACLs patch as described on
http://wiki.postgresql.org/wiki/DefaultACL .

Ok, here's my first crack at a comprehensive review. There's more I need to
look at, eventually. Some of these are very minor stylistic comments, and some
are probably just because I've much less of a clue, in general, than I'd like
to think I have.

First, as you've already pointed out, this needs documentation.

Once I added the missing semicolon mentioned earlier, it applies and builds
fine. The regression tests, however, seem to assume that they'll be run as the
postgres user, and the privileges test failed. Here's part of a diff between
expected/privileges.out and results/privileges.out as an example of what I
mean:

ALTER SCHEMA regressns DROP DEFAULT PRIVILEGES ON TABLE ALL FROM
regressuser2;
***************
*** 895,903 ****
(1 row)

SELECT relname, relacl FROM pg_class WHERE relname = 'acltest2';
! relname | relacl
! ----------+------------------------------------------------------
! acltest2 | {postgres=arwdDxt/postgres,regressgroup1=r/postgres}
(1 row)

  CREATE FUNCTION regressns.testfunc1() RETURNS int AS 'select 1;' LANGUAGE
sql;
--- 895,903 ----
  (1 row)

SELECT relname, relacl FROM pg_class WHERE relname = 'acltest2';
! relname | relacl
! ----------+------------------------------------------
! acltest2 | {josh=arwdDxt/josh,regressgroup1=r/josh}
(1 row)

CREATE FUNCTION regressns.testfunc1() RETURNS int AS 'select 1;' LANGUAGE
sql;

Very minor stylistic or comment issues:

* There's a stray newline added in pg_class.h (no other changes were made to
that file by this patch)
* It feels to me like the comment "Continue with standard grant" in aclchk.c
interrupts the flow of the code, though such a comment was likely useful
when the patch was being written.
* pg_namespace_default_acl.h:71 should read "objects stored *in* pg_class"
* The comment at the beginning of InsertPgClassTuple() in catalog/heap.c
should probably be updated to say that relation's ACLs aren't always NULL by
default
* copy_from in gram.y got changed to to_from, but to_from isn't ever used in
the default ACL grammar. I wondered if this was changed so you could use the
same TO/FROM code as COPY uses, and then you decided to hardcode TO and FROM?

In my perusal of the patch, I didn't see any code that screamed at me as
though it were a bad idea; quite likely there weren't any really bad ideas but
I can't say with confidence I'd have spotted them if there were. The addition
of both the NSPDEFACLOBJ_* defines alongside the NSP_ACL_OBJ_* defines kinda
made me think there were too many sets of constants that had to be kept in
sync, but I'm not sure that's much of an issue in reality, given how unlikely
it is that schema object types to which default ACLs should apply are likely
to be added or removed.

I don't know how patches that require catalog version changes are generally
handled; should the patch include that change?

More testing to follow.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

#12Andrew Dunstan
andrew@dunslane.net
In reply to: Joshua Tolley (#11)
Re: [PATCH] DefaultACLs

Joshua Tolley wrote:

I don't know how patches that require catalog version changes are generally
handled; should the patch include that change?

The committer should handle that.

cheers

andrew

#13Petr Jelinek
pjmodos@pjmodos.net
In reply to: Joshua Tolley (#11)
1 attachment(s)
Re: [PATCH] DefaultACLs

Joshua Tolley wrote:

First, as you've already pointed out, this needs documentation.

/me looks at Stephen ;)

Once I added the missing semicolon mentioned earlier, it applies and builds

As we discussed outside of list, my compiler didn't picked that one
because I didn't use --enable-cassert .

fine. The regression tests, however, seem to assume that they'll be run as the
postgres user, and the privileges test failed.

Oh I thought they are always run under *database user* postgres, my bad,
I reworked them and the are probably better as a result.

Very minor stylistic or comment issues:

* There's a stray newline added in pg_class.h (no other changes were made to
that file by this patch)

Fixed, probably I either pressed enter by mistake while viewing that
file or it was some merging problem when updating my working copy.

* It feels to me like the comment "Continue with standard grant" in aclchk.c
interrupts the flow of the code, though such a comment was likely useful
when the patch was being written.

Ok I removed that comment.

* pg_namespace_default_acl.h:71 should read "objects stored *in* pg_class"

Fixed.

* The comment at the beginning of InsertPgClassTuple() in catalog/heap.c
should probably be updated to say that relation's ACLs aren't always NULL by
default

Done.

* copy_from in gram.y got changed to to_from, but to_from isn't ever used in
the default ACL grammar. I wondered if this was changed so you could use the
same TO/FROM code as COPY uses, and then you decided to hardcode TO and FROM?

Yes, that's more or less what happened.

In my perusal of the patch, I didn't see any code that screamed at me as
though it were a bad idea; quite likely there weren't any really bad ideas but
I can't say with confidence I'd have spotted them if there were. The addition
of both the NSPDEFACLOBJ_* defines alongside the NSP_ACL_OBJ_* defines kinda
made me think there were too many sets of constants that had to be kept in
sync, but I'm not sure that's much of an issue in reality, given how unlikely
it is that schema object types to which default ACLs should apply are likely
to be added or removed.

Well the problem you see is not really a problem IMHO because most of
code I've seen does not use actual catalog values inside gram.y/parser
so I didn't use them either.

But there is one problem there that also affects GRANT ON ALL patch and
that was discussed previously (see
http://archives.postgresql.org/pgsql-hackers/2009-07/msg00957.php and
the rest of the thread from there). One (or both) of those patches will
have to be adjusted but only commiter can decide that IMHO (I am happy
to make any necessary changes but I really don't know which of the 3
solutions is better).

I don't know how patches that require catalog version changes are generally
handled; should the patch include that change?

Than can reasonably be done only at commit time so it's up to commiter.

I attached updated version of the patch per your comments. Let's hope I
didn't introduce new problems :)

Thanks for your time.

--
Regards
Petr Jelinek (PJMODOS)

Attachments:

defaultacls.diff.gzapplication/x-tar; name=defaultacls.diff.gzDownload
#14Petr Jelinek
pjmodos@pjmodos.net
In reply to: Joshua Tolley (#11)
1 attachment(s)
Re: [PATCH] DefaultACLs

Hello,

while writing some basic docs I found bug in dependency handling when
doing SET on object type that already had some default privileges.
Attached patch fixes it, it also fixes thinko in parser (DROPing GRANT
OPTION behaves like REVOKE now). And there is also initial version of
those basic docs included (but you have to pardon my english as I didn't
pass it to Stephen for proofreading due to discovery of that bug).

--
Regards
Petr Jelinek (PJMODOS)

Attachments:

defaultacls-2009-07-19.diff.gzapplication/x-tar; name=defaultacls-2009-07-19.diff.gzDownload
#15Robert Haas
robertmhaas@gmail.com
In reply to: Joshua Tolley (#11)
Re: [PATCH] DefaultACLs

On Fri, Jul 17, 2009 at 9:46 PM, Joshua Tolley<eggyknap@gmail.com> wrote:

On Tue, Jul 14, 2009 at 11:10:00PM +0200, Petr Jelinek wrote:

Hello,

this is first public version of our DefaultACLs patch as described on
http://wiki.postgresql.org/wiki/DefaultACL .

Ok, here's my first crack at a comprehensive review. There's more I need to
look at, eventually. Some of these are very minor stylistic comments, and some
are probably just because I've much less of a clue, in general, than I'd like
to think I have.

First, as you've already pointed out, this needs documentation.

Once I added the missing semicolon mentioned earlier, it applies and builds
fine. The regression tests, however, seem to assume that they'll be run as the
postgres user, and the privileges test failed. Here's part of a diff between
expected/privileges.out and results/privileges.out as an example of what I
mean:

 ALTER SCHEMA regressns DROP DEFAULT PRIVILEGES ON TABLE ALL FROM
regressuser2;
***************
*** 895,903 ****
 (1 row)

 SELECT relname, relacl FROM pg_class WHERE relname = 'acltest2';
!  relname  |                        relacl
! ----------+------------------------------------------------------
!  acltest2 | {postgres=arwdDxt/postgres,regressgroup1=r/postgres}
 (1 row)

 CREATE FUNCTION regressns.testfunc1() RETURNS int AS 'select 1;' LANGUAGE
sql;
--- 895,903 ----
 (1 row)

 SELECT relname, relacl FROM pg_class WHERE relname = 'acltest2';
!  relname  |                  relacl
! ----------+------------------------------------------
!  acltest2 | {josh=arwdDxt/josh,regressgroup1=r/josh}
 (1 row)

 CREATE FUNCTION regressns.testfunc1() RETURNS int AS 'select 1;' LANGUAGE
sql;

Very minor stylistic or comment issues:

* There's a stray newline added in pg_class.h (no other changes were made to
 that file by this patch)
* It feels to me like the comment "Continue with standard grant" in aclchk.c
 interrupts the flow of the code, though such a comment was likely useful
 when the patch was being written.
* pg_namespace_default_acl.h:71 should read "objects stored *in* pg_class"
* The comment at the beginning of InsertPgClassTuple() in catalog/heap.c
 should probably be updated to say that relation's ACLs aren't always NULL by
 default
* copy_from in gram.y got changed to to_from, but to_from isn't ever used in
 the default ACL grammar. I wondered if this was changed so you could use the
 same TO/FROM code as COPY uses, and then you decided to hardcode TO and FROM?

In my perusal of the patch, I didn't see any code that screamed at me as
though it were a bad idea; quite likely there weren't any really bad ideas but
I can't say with confidence I'd have spotted them if there were. The addition
of both the NSPDEFACLOBJ_* defines alongside the NSP_ACL_OBJ_* defines kinda
made me think there were too many sets of constants that had to be kept in
sync, but I'm not sure that's much of an issue in reality, given how unlikely
it is that schema object types to which default ACLs should apply are likely
to be added or removed.

I don't know how patches that require catalog version changes are generally
handled; should the patch include that change?

More testing to follow.

So are these warts fixed in the latest revision of this patch?

http://archives.postgresql.org/pgsql-hackers/2009-07/msg01216.php

I am gathering that this patch is still a bit of a WIP. I think it
might be best to mark it returned with feedback and let Petr resubmit
for the next CommitFest when it is closer to being done. But I don't
want to do that if it's really already to go now.

I am also a bit unsure as to whether Josh Tolley is still conducting a
more in-depth review. Josh?

Thanks,

...Robert

#16Joshua Tolley
eggyknap@gmail.com
In reply to: Robert Haas (#15)
Re: [PATCH] DefaultACLs

On Wed, Jul 22, 2009 at 08:54:19PM -0400, Robert Haas wrote:

I am gathering that this patch is still a bit of a WIP.

I don't consider it a WIP. Petr posted a patch a couple of days ago, but I've
not been able to verify its changes or perform some additional testing I had
in mind, because of my own schedule. I probably should have made that clear a
while ago. I consider the ball entirely in my court on this one, and plan to
complete my review using the latest version of the patch as soon as my time
permits; I expect this will happen before Saturday.

I am also a bit unsure as to whether Josh Tolley is still conducting a
more in-depth review. Josh?

Yes, I am, but if you've read this far you know that already :)

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

#17Petr Jelinek
pjmodos@pjmodos.net
In reply to: Robert Haas (#15)
Re: [PATCH] DefaultACLs

Robert Haas wrote:

So are these warts fixed in the latest revision of this patch?

http://archives.postgresql.org/pgsql-hackers/2009-07/msg01216.php

I am gathering that this patch is still a bit of a WIP. I think it
might be best to mark it returned with feedback and let Petr resubmit
for the next CommitFest when it is closer to being done. But I don't
want to do that if it's really already to go now.

See http://archives.postgresql.org/pgsql-hackers/2009-07/msg01151.php
(the revision before the one you pasted).
The docs are not complete but that's up to Stephen, otherwise the patch
should be finished. But I am not the reviewer :)

Anyway, while this patch might not necessary get commited in this commit
fest, I'd still like to have opinion from one of the commiters on "the
VIEW problem" which also affects grant on all patch ( see
http://archives.postgresql.org/pgsql-hackers/2009-07/msg00957.php ) and
I fear "returned with feedback" might prevent that until next commit fest.

--
Regards
Petr Jelinek (PJMODOS)

#18Robert Haas
robertmhaas@gmail.com
In reply to: Joshua Tolley (#16)
Re: [PATCH] DefaultACLs

On Wed, Jul 22, 2009 at 11:21 PM, Joshua Tolley<eggyknap@gmail.com> wrote:

On Wed, Jul 22, 2009 at 08:54:19PM -0400, Robert Haas wrote:

I am gathering that this patch is still a bit of a WIP.

I don't consider it a WIP. Petr posted a patch a couple of days ago, but I've
not been able to verify its changes or perform some additional testing I had
in mind, because of my own schedule. I probably should have made that clear a
while ago. I consider the ball entirely in my court on this one, and plan to
complete my review using the latest version of the patch as soon as my time
permits; I expect this will happen before Saturday.

OK, I stand corrected. Thanks for the update.

...Robert

#19Robert Haas
robertmhaas@gmail.com
In reply to: Petr Jelinek (#17)
Re: [PATCH] DefaultACLs

On Wed, Jul 22, 2009 at 11:26 PM, Petr Jelinek<pjmodos@pjmodos.net> wrote:

Robert Haas wrote:

So are these warts fixed in the latest revision of this patch?

http://archives.postgresql.org/pgsql-hackers/2009-07/msg01216.php

I am gathering that this patch is still a bit of a WIP.  I think it
might be best to mark it returned with feedback and let Petr resubmit
for the next CommitFest when it is closer to being done.  But I don't
want to do that if it's really already to go now.

See http://archives.postgresql.org/pgsql-hackers/2009-07/msg01151.php (the
revision before the one you pasted).

Ah, woops. Sorry I missed that. Actually, now that I read it, I
remember that I saw it before, but I didn't remember that before
sending my previous email, of course...

The docs are not complete but that's up to Stephen, otherwise the patch
should be finished. But I am not the reviewer :)

Well, perhaps we had better prod Stephen then, since complete docs are
a requirement for commit.

Anyway, while this patch might not necessary get commited in this commit
fest, I'd still like to have opinion from one of the commiters on "the VIEW
problem" which also affects grant on all patch ( see
http://archives.postgresql.org/pgsql-hackers/2009-07/msg00957.php ) and I
fear "returned with feedback" might prevent that until next commit fest.

OK, hopefully one of them will chime in with an opinion. As I say, I
would like to see this get committed if it's done, I just wasn't sure
it was. But now it seems that was due to insufficiently careful
reading of the thread, with the exception of this issue and the need
to finish the docs.

Thanks,

...Robert

#20Stephen Frost
sfrost@snowman.net
In reply to: Robert Haas (#19)
Re: [PATCH] DefaultACLs

* Robert Haas (robertmhaas@gmail.com) wrote:

On Wed, Jul 22, 2009 at 11:26 PM, Petr Jelinek<pjmodos@pjmodos.net> wrote:

The docs are not complete but that's up to Stephen, otherwise the patch
should be finished. But I am not the reviewer :)

Well, perhaps we had better prod Stephen then, since complete docs are
a requirement for commit.

I didn't think the docs posted were terrible, but I am working on
improving them.

Anyway, while this patch might not necessary get commited in this commit
fest, I'd still like to have opinion from one of the commiters on "the VIEW
problem" which also affects grant on all patch ( see
http://archives.postgresql.org/pgsql-hackers/2009-07/msg00957.php ) and I
fear "returned with feedback" might prevent that until next commit fest.

OK, hopefully one of them will chime in with an opinion. As I say, I
would like to see this get committed if it's done, I just wasn't sure
it was. But now it seems that was due to insufficiently careful
reading of the thread, with the exception of this issue and the need
to finish the docs.

Working on it... Not that we've never committed stuff with less than
complete docs before.. ;)

Thanks,

Stephen

#21Nikhil Sontakke
nikhil.sontakke@enterprisedb.com
In reply to: Robert Haas (#19)
Re: [PATCH] DefaultACLs

Hi,

Anyway, while this patch might not necessary get commited in this commit
fest, I'd still like to have opinion from one of the commiters on "the VIEW
problem" which also affects grant on all patch ( see
http://archives.postgresql.org/pgsql-hackers/2009-07/msg00957.php ) and I
fear "returned with feedback" might prevent that until next commit fest.

OK, hopefully one of them will chime in with an opinion.  As I say, I
would like to see this get committed if it's done, I just wasn't sure
it was.  But now it seems that was due to insufficiently careful
reading of the thread, with the exception of this issue and the need
to finish the docs.

IMO, the committers should have a go at the "GRANT ON ALL" (simpler
than DefaultACLs) patch first. Whatever consensus is arrived for the
VIEW issue as part of its review can then spill over to the
DefaultACLs patch too.

As mentioned by Petr, he does not seem to be in a hurry to get the
DefaultACLs patch in, in this commitfest..

Regards,
Nikhils
--
http://www.enterprisedb.com

#22Peter Eisentraut
peter_e@gmx.net
In reply to: Petr Jelinek (#17)
Re: [PATCH] DefaultACLs

On Thursday 23 July 2009 06:26:05 Petr Jelinek wrote:

I'd still like to have opinion from one of the commiters on "the
VIEW problem" which also affects grant on all patch ( see
http://archives.postgresql.org/pgsql-hackers/2009-07/msg00957.php ) and
I fear "returned with feedback" might prevent that until next commit fest.

I see potential for confusion in that GRANT ON TABLE x works if x is a base
table or a view, but GRANT ON ALL TABLES would not affect views. Maybe you
need to make up a different syntax to affect only base tables, e.g., GRANT ON
ALL BASE TABLES.

#23Petr Jelinek
pjmodos@pjmodos.net
In reply to: Peter Eisentraut (#22)
Re: [PATCH] DefaultACLs

Peter Eisentraut wrote:

On Thursday 23 July 2009 06:26:05 Petr Jelinek wrote:

I'd still like to have opinion from one of the commiters on "the
VIEW problem" which also affects grant on all patch ( see
http://archives.postgresql.org/pgsql-hackers/2009-07/msg00957.php ) and
I fear "returned with feedback" might prevent that until next commit fest.

I see potential for confusion in that GRANT ON TABLE x works if x is a base
table or a view, but GRANT ON ALL TABLES would not affect views. Maybe you
need to make up a different syntax to affect only base tables, e.g., GRANT ON
ALL BASE TABLES.

That's not what I mean the problem is what is the best way of handling
the views in implementation itself (there were IIRC 3 possible solutions
devised and I don't think we have consensus on which is better).
In short,
1. add ACL_OBJECT_VIEW into GrantObjectType enum and track that inside code
2. create new enum with table, view, function and sequence objects in it
(that works well for DefaultACLs but not for GRANT ON ALL)
3. add some boolean into GrantStmt that would indicate that relation is
a view (that works for GRANT ON ALL but does not solve anything for
DefaultACLs)

Currently DefaultACLs patch uses method 2 (because Stephen does not like
method 1) and GRANT ON ALL patch uses method 1 and it might be better if
both patches uses only one of those.
If we went with method 1 we probably should just ditch GrantObjectType
alltogether and work with subset of ObjectType as other commands do (I
haven't found any reason for GrantObjectType to exist other than having
single object type for both TABLE and VIEW).
And If we choose not to use method 1 then we should probably go with 2
for DefaultACLs and 3 for GRANT ON ALL. That is unless somebody has a
better solution.

--
Regards
Petr Jelinek (PJMODOS)

#24Nikhil Sontakke
nikhil.sontakke@enterprisedb.com
In reply to: Petr Jelinek (#23)
Re: [PATCH] DefaultACLs

Hi,

I'd still like to have opinion from one of the commiters on "the
VIEW problem" which also affects grant on all patch ( see
http://archives.postgresql.org/pgsql-hackers/2009-07/msg00957.php ) and
I fear "returned with feedback" might prevent that until next commit fest.

I see potential for confusion in that GRANT ON TABLE x works if x is a base
table or a view, but GRANT ON ALL TABLES would not affect views. Maybe you
need to make up a different syntax to affect only base tables, e.g., GRANT
ON
ALL BASE TABLES.

That's not what I mean the problem is what is the best way of handling the
views in implementation itself (there were IIRC 3 possible solutions devised
and I don't think we have consensus on which is better).

Peter is raising a good question here and it's not related to the
implementation. What he is saying is that in your new implementation
if "GRANT ON ALL TABLES" is invoked, it will affect only RELKIND_TABLE
objects. Whereas the GRANT ON TABLE affects both RELKIND_TABLE and
RELKIND_VIEW types of objects (with and without your patch).

We could have brought in the differentiation with this patch to treat
views and tables separately. So a GRANT ON TABLE would just affect
tables. But I guess that will break existing user scripts which assume
it works against VIEWS too.

I don't know how acceptable the "ON ALL BASE TABLES" sounds to all.

Regards,
Nikhils

In short,
1. add ACL_OBJECT_VIEW into GrantObjectType enum and track that inside code
2. create new enum with table, view, function and sequence objects in it
(that works well for DefaultACLs but not for GRANT ON ALL)
3. add some boolean into GrantStmt that would indicate that relation is a
view (that works for GRANT ON ALL but does not solve anything for
DefaultACLs)

Currently DefaultACLs patch uses method 2 (because Stephen does not like
method 1) and GRANT ON ALL patch uses method 1 and it might be better if
both patches uses only one of those.
If we went with method 1 we probably should just ditch GrantObjectType
alltogether and work with subset of ObjectType as other commands do (I
haven't found any reason for GrantObjectType to exist other than having
single object type for both TABLE and VIEW).
And If we choose not to use method 1 then we should probably go with 2 for
DefaultACLs and 3 for GRANT ON ALL. That is unless somebody has a better
solution.

--
Regards
Petr Jelinek (PJMODOS)

--
http://www.enterprisedb.com

#25Joshua Tolley
eggyknap@gmail.com
In reply to: Petr Jelinek (#14)
Re: [PATCH] DefaultACLs

On Sun, Jul 19, 2009 at 06:13:32PM +0200, Petr Jelinek wrote:

Hello,

while writing some basic docs I found bug in dependency handling when
doing SET on object type that already had some default privileges.
Attached patch fixes it, it also fixes thinko in parser (DROPing GRANT
OPTION behaves like REVOKE now). And there is also initial version of
those basic docs included (but you have to pardon my english as I didn't
pass it to Stephen for proofreading due to discovery of that bug).

Am I the only one that gets this on make check, with this version (from
src/test/regress/log/initdb.log):

selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /home/josh/devel/pgsrc/pg85/src/test/regress/./tmp_check/data/base/1 ... FATAL: relation "pg_namespace_default_acl" already exists
child process exited with exit code 1
initdb: data directory "/home/josh/devel/pgsrc/pg85/src/test/regress/./tmp_check/data" not removed at user's request

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

#26Petr Jelinek
pjmodos@pjmodos.net
In reply to: Joshua Tolley (#25)
Re: [PATCH] DefaultACLs

Joshua Tolley wrote:

Am I the only one that gets this on make check, with this version (from
src/test/regress/log/initdb.log):

selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /home/josh/devel/pgsrc/pg85/src/test/regress/./tmp_check/data/base/1 ... FATAL: relation "pg_namespace_default_acl" already exists
child process exited with exit code 1
initdb: data directory "/home/josh/devel/pgsrc/pg85/src/test/regress/./tmp_check/data" not removed at user's request

Certainly never happened to me. Are you using any special parameters or
something (altho I don't have the slightest idea what could cause that)
? I run make check on patches using gcc under debian and msvc on vista
before sending them.

--
Regards
Petr Jelinek (PJMODOS)

#27Joshua Tolley
eggyknap@gmail.com
In reply to: Petr Jelinek (#26)
Re: [PATCH] DefaultACLs

On Sat, Jul 25, 2009 at 11:14:19AM +0200, Petr Jelinek wrote:

Joshua Tolley wrote:

Am I the only one that gets this on make check, with this version (from
src/test/regress/log/initdb.log):

selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /home/josh/devel/pgsrc/pg85/src/test/regress/./tmp_check/data/base/1 ... FATAL: relation "pg_namespace_default_acl" already exists
child process exited with exit code 1
initdb: data directory "/home/josh/devel/pgsrc/pg85/src/test/regress/./tmp_check/data" not removed at user's request

Certainly never happened to me. Are you using any special parameters or
something (altho I don't have the slightest idea what could cause that)
? I run make check on patches using gcc under debian and msvc on vista
before sending them.

I figured as much. I can't seem to get past this, despite a make distclean.
Suggestions, anyone?

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

#28Andrew Dunstan
andrew@dunslane.net
In reply to: Joshua Tolley (#27)
Re: [PATCH] DefaultACLs

Joshua Tolley wrote:

I figured as much. I can't seem to get past this, despite a make distclean.
Suggestions, anyone?

try a fresh checkout and reapply the patch?

cheers

andrew

#29Joshua Tolley
eggyknap@gmail.com
In reply to: Andrew Dunstan (#28)
Re: [PATCH] DefaultACLs

On Sat, Jul 25, 2009 at 03:50:06PM -0400, Andrew Dunstan wrote:

Joshua Tolley wrote:

I figured as much. I can't seem to get past this, despite a make distclean.
Suggestions, anyone?

try a fresh checkout and reapply the patch?

[ a couple git clean, git reset, make clean, etc. commands later... ]

Yeah, well, it works now. What's more, the problems I had with make check the
first time I tried this are no longer.

I've done all the looking I can think to do at the patch in its existing form,
and don't have any complaints. I realize, though, that there are open
questions about how this should work with, given the GRANT ON ALL patch. I'm
not sure I have comments in that regard, but I'll try to come up with some, or
at least to convince myself I don't have any for a better reason than just
that I wouldn't know what I was talking about. In the meantime, I think this
one is ready to be marked as ... something else. Ready for committer?

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

#30Robert Haas
robertmhaas@gmail.com
In reply to: Joshua Tolley (#29)
Re: [PATCH] DefaultACLs

On Sat, Jul 25, 2009 at 7:45 PM, Joshua Tolley<eggyknap@gmail.com> wrote:

that I wouldn't know what I was talking about. In the meantime, I think this
one is ready to be marked as ... something else. Ready for committer?

Sounds right to me.

...Robert

#31Joshua Tolley
eggyknap@gmail.com
In reply to: Petr Jelinek (#14)
Re: [PATCH] DefaultACLs

On Sun, Jul 19, 2009 at 06:13:32PM +0200, Petr Jelinek wrote:

while writing some basic docs I found bug in dependency handling when
doing SET on object type that already had some default privileges.
Attached patch fixes it, it also fixes thinko in parser (DROPing GRANT
OPTION behaves like REVOKE now). And there is also initial version of
those basic docs included (but you have to pardon my english as I didn't
pass it to Stephen for proofreading due to discovery of that bug).

Immediately after concluding I was done with my review, I realized I'd
completely forgotten to look at the docs. I've made a few changes based solely
on my opinions of what sounds better and what's more consistent with the
existing documentation. Do with them as you see fit. :)

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

#32Robert Haas
robertmhaas@gmail.com
In reply to: Joshua Tolley (#31)
Re: [PATCH] DefaultACLs

On Sat, Jul 25, 2009 at 8:39 PM, Joshua Tolley<eggyknap@gmail.com> wrote:

On Sun, Jul 19, 2009 at 06:13:32PM +0200, Petr Jelinek wrote:

while writing some basic docs I found bug in dependency handling when
doing SET on object type that already had some default privileges.
Attached patch fixes it, it also fixes thinko in parser (DROPing GRANT
OPTION behaves like REVOKE now). And there is also initial version of
those basic docs included (but you have to pardon my english as I didn't
pass it to Stephen for proofreading due to discovery of that bug).

Immediately after concluding I was done with my review, I realized I'd
completely forgotten to look at the docs. I've made a few changes based solely
on my opinions of what sounds better and what's more consistent with the
existing documentation. Do with them as you see fit. :)

Did you intend to attach something to this email?

...Robert

#33Joshua Tolley
eggyknap@gmail.com
In reply to: Robert Haas (#32)
1 attachment(s)
Re: [PATCH] DefaultACLs

On Sat, Jul 25, 2009 at 08:41:12PM -0400, Robert Haas wrote:

On Sat, Jul 25, 2009 at 8:39 PM, Joshua Tolley<eggyknap@gmail.com> wrote:

On Sun, Jul 19, 2009 at 06:13:32PM +0200, Petr Jelinek wrote:

while writing some basic docs I found bug in dependency handling when
doing SET on object type that already had some default privileges.
Attached patch fixes it, it also fixes thinko in parser (DROPing GRANT
OPTION behaves like REVOKE now). And there is also initial version of
those basic docs included (but you have to pardon my english as I didn't
pass it to Stephen for proofreading due to discovery of that bug).

Immediately after concluding I was done with my review, I realized I'd
completely forgotten to look at the docs. I've made a few changes based solely
on my opinions of what sounds better and what's more consistent with the
existing documentation. Do with them as you see fit. :)

Did you intend to attach something to this email?

...Robert

Well, yes, now that you mention it :) Trying again...

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

Attachments:

defaultacl.docs.patchtext/x-diff; charset=us-asciiDownload
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 34679d8..3eb92a4 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -3130,6 +3130,70 @@
  </sect1>
 
 
+ <sect1 id="catalog-pg-namespace-default-acl">
+  <title><structname>pg_namespace_default_acl</structname></title>
+
+  <indexterm zone="catalog-pg-namespace-default-acl">
+   <primary>pg_namespace_default_acl</primary>
+  </indexterm>
+
+  <para>
+   The catalog <structname>pg_namespace_default_acl</> stores default
+   privileges for newly created objects inside the schema.
+  </para>
+
+  <table>
+   <title><structname>pg_namespace</> Columns</title>
+
+   <tgroup cols="4">
+    <thead>
+     <row>
+      <entry>Name</entry>
+      <entry>Type</entry>
+      <entry>References</entry>
+      <entry>Description</entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry><structfield>defaclnamespace</structfield></entry>
+      <entry><type>oid</type></entry>
+      <entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.oid</literal></entry>
+      <entry>The OID of the namespace associated with this entry</entry>
+     </row>
+
+     <row>
+      <entry><structfield>defaclgrantobjtype</structfield></entry>
+      <entry><type>char</type></entry>
+      <entry></entry>
+      <entry>
+       <literal>r</> = table, <literal>v</> = view,
+       <literal>f</> = function, <literal>S</> = sequence
+      </entry>
+     </row>
+
+     <row>
+      <entry><structfield>defacllist</structfield></entry>
+      <entry><type>aclitem[]</type></entry>
+      <entry></entry>
+      <entry>
+       Access privileges that the object should have on creation.
+       This is NOT a mask, it's exactly what the object will get.
+       See
+       <xref linkend="sql-alterschema" endterm="sql-alterschema-title">,
+       <xref linkend="sql-grant" endterm="sql-grant-title"> and
+       <xref linkend="sql-revoke" endterm="sql-revoke-title">
+       for details.
+      </entry>
+     </row>
+    </tbody>
+   </tgroup>
+  </table>
+
+ </sect1>
+
+
  <sect1 id="catalog-pg-opclass">
   <title><structname>pg_opclass</structname></title>
 
diff --git a/doc/src/sgml/ref/alter_schema.sgml b/doc/src/sgml/ref/alter_schema.sgml
index 2458d19..62f4c2a 100644
--- a/doc/src/sgml/ref/alter_schema.sgml
+++ b/doc/src/sgml/ref/alter_schema.sgml
@@ -23,18 +23,46 @@ PostgreSQL documentation
 <synopsis>
 ALTER SCHEMA <replaceable>name</replaceable> RENAME TO <replaceable>newname</replaceable>
 ALTER SCHEMA <replaceable>name</replaceable> OWNER TO <replaceable>newowner</replaceable>
+
+ALTER SCHEMA <replaceable>name</replaceable> { SET | ADD } DEFAULT PRIVILEGES { { ON default_privileges 
+	TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ] } [AND ...] } [...]
+
+where <replaceable class="PARAMETER">default_privileges</replaceable> is:
+
+{ { TABLE | VIEW } { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
+    [,...] | ALL [ PRIVILEGES ] } |
+  SEQUENCE { { USAGE | SELECT | UPDATE }
+    [,...] | ALL [ PRIVILEGES ] } |
+  FUNCTION { EXECUTE | ALL [ PRIVILEGES ] } }
+  
+ALTER SCHEMA <replaceable>name</replaceable> DROP DEFAULT PRIVILEGES { { ON drop_default_privileges
+	FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] } [AND ...] } [...]
+
+where <replaceable class="PARAMETER">drop_default_privileges</replaceable> is:
+
+{ { TABLE | VIEW } [ GRANT OPTION FOR ]
+	{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
+    [,...] | ALL [ PRIVILEGES ] } |
+  SEQUENCE [ GRANT OPTION FOR ] 
+	{ { USAGE | SELECT | UPDATE } [,...] | ALL [ PRIVILEGES ] } |
+  FUNCTION [ GRANT OPTION FOR ] 
+	{ EXECUTE | ALL [ PRIVILEGES ] } }
 </synopsis>
  </refsynopsisdiv>
 
- <refsect1>
+ <refsect1 id="sql-alterschema-description">
   <title>Description</title>
 
   <para>
-   <command>ALTER SCHEMA</command> changes the definition of a schema.
+   You must own the schema to use <command>ALTER SCHEMA</>.
   </para>
 
+ </refsect1>
+
+ <refsect1 id="sql-alterschema-description-schemadefinition">
+  <title>Change the definition of a schema</title>
+
   <para>
-   You must own the schema to use <command>ALTER SCHEMA</>.
    To rename a schema you must also have the
    <literal>CREATE</literal> privilege for the database.
    To alter the owner, you must also be a direct or
@@ -42,7 +70,6 @@ ALTER SCHEMA <replaceable>name</replaceable> OWNER TO <replaceable>newowner</rep
    <literal>CREATE</literal> privilege for the database.
    (Note that superusers have all these privileges automatically.)
   </para>
- </refsect1>
 
  <refsect1>
   <title>Parameters</title>
@@ -77,6 +104,100 @@ ALTER SCHEMA <replaceable>name</replaceable> OWNER TO <replaceable>newowner</rep
     </listitem>
    </varlistentry>
   </variablelist>
+ </refsect2>
+ </refsect1>
+
+ <refsect1 id="sql-alterschema-description-defaultprivileges">
+  <title>Change the default privileges for new objects</title>
+
+  <para>
+    The <command>ALTER SCHEMA</> <literal>{ SET | ADD | DROP }</> <literal>DEFAULT PRIVILEGES</> command
+    allows you to define privileges for newly created objects in the schema.
+  </para>
+
+  <para>
+    See the description of the <xref linkend="sql-grant" endterm="sql-grant-title"> and
+    <xref linkend="sql-revoke" endterm="sql-revoke-title"> commands for more detailed
+    explanation of privilege system and the meaning of the privilege types.
+  </para>
+
+  <para>
+   The possible commands are:
+
+   <variablelist>
+    <varlistentry>
+     <term>SET</term>
+     <listitem>
+      <para>
+       Replaces the current default privileges with the newly specified ones.
+       Behaves like <command>GRANT</> on object that had no privileges set before.
+      </para>
+     </listitem>
+    </varlistentry>
+
+    <varlistentry>
+     <term>ADD</term>
+     <listitem>
+      <para>
+       Adds new default privileges. Behaves like standard <command>GRANT</>.
+      </para>
+     </listitem>
+    </varlistentry>
+
+    <varlistentry>
+     <term>DROP</term>
+     <listitem>
+      <para>
+       Removes specified privileges from defaults. Behaves like <command>REVOKE</>.
+      </para>
+     </listitem>
+    </varlistentry>
+
+   </variablelist>
+  </para>
+ </refsect1>
+
+ <refsect1 id="sql-alterschema-examples">
+  <title>Examples</title>
+
+  <para>
+   Grant select privilege for every new table in schema 
+   <literal>public</literal> to everybody:
+
+<programlisting>
+ALTER SCHEMA public SET DEFAULT PRIVILEGES ON TABLE SELECT TO public;
+</programlisting>
+  </para>
+
+  <para>
+   Grant <literal>webuser</literal <literal>SELECT</> privileges on all
+   new tables created in the <literal>users</literal> schema. Also grant
+   <literal>INSERT</>, <literal>UPDATE</>, and <literal>DELETE</> privileges
+   to the <literal>admin</literal> user.
+
+<programlisting>
+ALTER SCHEMA users SET DEFAULT PRIVILEGES ON TABLE SELECT TO webuser, admin AND UPDATE, INSERT, DELETE TO admin;
+</programlisting>
+  </para>
+
+  <para>
+   Give user <literal>webuser</literal> <literal>SELECT</> privilege on all new 
+   tables, views, and sequences, and <literal>EXECUTE</> on all new functions in
+   the <literal>public</literal> schema.
+   Allow the <literal>admin</literal> to not only select but also 
+   change the contents of new tables in the <literal>public</> schema, and grant those permissions
+   to other users. The <literal>admin</> user will also be allowed to select from new views, 
+   execute new functions, and read new sequences like <literal>webuser</literal>.
+   Finally, allow <literal>admin</> to update new sequences.
+
+<programlisting>
+ALTER SCHEMA public SET DEFAULT PRIVILEGES 
+    ON TABLE SELECT TO webuser, admin AND UPDATE, INSERT, DELETE TO admin WITH GRANT OPTION
+    ON VIEW SELECT TO webuser, admin
+    ON FUNCTION EXECUTE TO webuser, admin
+    ON SEQUENCE USAGE, SELECT TO webuser AND ALL PRIVILEGES TO admin;
+</programlisting>
+  </para>
  </refsect1>
 
  <refsect1>
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index bf963b8..c60cc4b 100644
--- a/doc/src/sgml/ref/grant.sgml
+++ b/doc/src/sgml/ref/grant.sgml
@@ -22,8 +22,8 @@ PostgreSQL documentation
  <refsynopsisdiv>
 <synopsis>
 GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
-    [,...] | ALL [ PRIVILEGES ] }
-    ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
+    [,...] | ALL [ PRIVILEGES ] | DEFAULT PRIVILEGES }
+    ON [ TABLE | VIEW ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
     TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
 GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( <replaceable class="PARAMETER">column</replaceable> [, ...] )
@@ -32,7 +32,7 @@ GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( <replaceable class="PARAMETE
     TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
 GRANT { { USAGE | SELECT | UPDATE }
-    [,...] | ALL [ PRIVILEGES ] }
+    [,...] | ALL [ PRIVILEGES ] | DEFAULT PRIVILEGES }
     ON SEQUENCE <replaceable class="PARAMETER">sequencename</replaceable> [, ...]
     TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
@@ -48,7 +48,7 @@ GRANT { USAGE | ALL [ PRIVILEGES ] }
     ON FOREIGN SERVER <replaceable>servername</> [, ...]
     TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
-GRANT { EXECUTE | ALL [ PRIVILEGES ] }
+GRANT { EXECUTE | ALL [ PRIVILEGES ] | DEFAULT PRIVILEGES }
     ON FUNCTION <replaceable>funcname</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) [, ...]
     TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
@@ -132,6 +132,8 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable
    include granting some privileges to <literal>PUBLIC</literal>.
    The default is no public access for tables, columns, schemas, and
    tablespaces;
+   For tables, views, functions and sequences the initial default privileges
+   can also be changed using <command>ALTER SCHEMA</command> command;
    <literal>CONNECT</> privilege and <literal>TEMP</> table creation privilege
    for databases;
    <literal>EXECUTE</> privilege for functions; and
@@ -339,6 +341,21 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable
       </para>
      </listitem>
     </varlistentry>
+
+    <varlistentry>
+     <term>DEFAULT PRIVILEGES</term>
+     <listitem>
+      <para>
+       Replace current privileges with the ones specified using 
+       <xref linkend="sql-alterschema" endterm="sql-alterschema-title">.
+       The <literal>WITH GRANT OPTION</literal> parameter is not applicable 
+       because it is copied from default privileges.
+       Note: this can actually <emphasis role="bold">revoke</emphasis> some 
+       privileges because it clears all existing privileges object has and 
+       replaces them with the default ones for the schema in which this object lives.
+      </para>
+     </listitem>
+    </varlistentry>
    </variablelist>
 
    The privileges required by other commands are listed on the
#34Petr Jelinek
pjmodos@pjmodos.net
In reply to: Joshua Tolley (#33)
1 attachment(s)
Re: [PATCH] DefaultACLs

Joshua Tolley weote:

On Sat, Jul 25, 2009 at 08:41:12PM -0400, Robert Haas wrote:

On Sat, Jul 25, 2009 at 8:39 PM, Joshua Tolley<eggyknap@gmail.com> wrote:

Immediately after concluding I was done with my review, I realized I'd
completely forgotten to look at the docs. I've made a few changes based solely
on my opinions of what sounds better and what's more consistent with the
existing documentation. Do with them as you see fit. :)

Applied with minor adjustments, attached updated patch.

--
Regards
Petr Jelinek (PJMODOS)

Attachments:

defaultacls-2009-07-26.diff.gzapplication/x-tar; name=defaultacls-2009-07-26.diff.gzDownload
��lJdefaultacls-2009-07-26.diff�=kW�����_�a��"�wB���a�`�63�'��#�6h�%�$��I����[j�����d�������zuUuu�q��@��^y�����Y�#�w5��;���*��'q9�����^ln����{�������[bc}}gk�����L��VWWg�_��!��������8T��>0���Lq�K'�8�g��Z�Ax��%5���j5��2��\u�������K�8@$^����8��N�����~:��F�aL���@@�xHJ�����a �_�"ohG3��<�Z:S6���l��w-��[,�����0��P��&��|y��0����I;��/�/�=W����k9�k�FM���}���/p$���aEC��p<N��������Z���$��.� d����`��5~.��FS�:r #82�q$c'�F�E��5�`G���}���Vrx�wQA�o����F�A�����T8���}|n������4C�E�Xk4y�~����'G"�=��������y�5�z��1S��X�^Ev��Q'd	�K����yb���~��#4���V��5�r��'���Q��m����X�9F�(x*��^�<F\0H����2k8�"c�I%�vB���I����w��}M{.O-��C;��g������o,�%?�m��N�������}q%GWJC%��d'��_�}P��%�Y��D�*���Ck&*��j����31D�6����qd����2�=?����`�3�D�ZG�4P����>R,/�����a�/a��:Lyt��������H
}��r�R ��������ZmoP���7f�D�hJr�r�B�$���+���
=�Z��i�������
�!G��0��VGt����5`����P|4[D�u�x��vR�M?w��3.A�2��
�5�@����z�������Y�����tf�y�szv���������n��v43%/�#�+;M~��i~�U|��p,��8���4�$�XN�����^( Y�������}�}��N��ke8�����Xr�v,������F��T�H��E�����ngO���#��(�E���D��H|G������8���~r�:nu��h��<�oD/��t}��q�}q.>�f��^/�7:  i�0
}YB�'q~����	��D�V�(�8��xg=�>��	��qv�������kHx��>�B�/~����^��iH����L��$u�j��N���>\�����t�C�sq���N�m,�	B����cP�gx�[����5��}"�n��O�_t��5%B���o�6�G���V���,�g��5��N�|�
E��?�������ocI_i3�$?�p����m�#���w5�2���j��=��f�1��=)+��nE��$�~U�3b����@.N�*7e�;_jR������������������������_Gx�,L$o���HF��(f�����`
FX��I8�������r�V��0+<�C�1a��v�!���=�<fw���Mk�?�y��_��W�����W����[V}}���@��	n���8X���Mm�6�&sA��v}���	u�G��B�.oN�x�2+�Y�9��f������9����
��
�!��i���$�$������<u�M*P]������U#���aIU����
lMi�"�0�!R@��x���&��:T<W2��Q�e6�(;�/����U�F��u����```���=�^=i��T�����]�3� �$e��
�R�E<��7��2i�s���(���L��!J�k_���mW�9O,�5�i�rD�NA��bY@\��/��j�=)�Z��8������f����0D�3��!
���"�)����_[O�`�O7�=���{{8�����Zg��c�?`�>�l>>�[=�1P)}����%�����q�.
���Z��(_7$W���� �_����w6j[;�
Q�M��������;y�yF.��<�dX	,n�,�#K��;R���i!G��D�~A��P�,��=��>���7�S��@���mw����S'\�kA�h��nv�pKm+-��`��2�y��D%ZK�n����HmJZ�<�N�b��>��"W;F��5}~�I�BiJbQ�9�d(��2Q�f$�p���r�v+���r3'����
�,��&JS���I`��5�U�6��ZU8"6��q�&��=�n
9�U���d�8%r�/��3Nd^�L�+��S�(�<��t�zn,&��\�C�`�!��\$�}���O�X&�v*�LC���%]����h�
cMb'��lb����P����>x"+�z������������^�����8��s����S����.���~Z
8�,%�J������Q)iv�L�P��Mvn��KI	T�=���Z�gsZH|��>��<��\S��X��Rqz���S��2?U�wU7���W�,J_��[�{F����l�!�"NDL�9������]�$����������B�����J��
�{��������ur�b���5W����L�w��y���-�5�
�������* z_��H����j���X2g������a�7��������pA���T�ej������<�����]�p��
������x���)
l���5�mn�Y(���S)���6_G�c@U��D�4�5F� x�9~�v��@UN��1�Z@�`��K����bi���*�����Kn���x��>;��A��J���s*E���m����RuF'1�R��m*�W$������}ksk�x�dk�����.�����s�/�����}�Pz�Sv��g<��k>C����sW������6���'�u�{4��C���]J��� -/'!��a�y~�
�S���ZY����^&�ptm�6U�z�t�.X1���C�:C�A�/�(�r���rP����d���E�-2N����4C�+}}@�Gx������c6�;7�W��d�����0�G�_8/5\>���y�)+�*����/������j^{�N}{o�V�X����s���LS�^&���^[�fz�O��o�������O���]$�\�f��l1�]`����,r��x�?m���I>��q�\��	����;�F�9����������-q	�������/��x	��X��n���������4��������#X��=<'��G�	�Z[�u&��^f����I�w�`�]�����?�Gi��9	|��l�b<����A!�4����w�1��E	5���-��9N9��CH^��w9�$IhS����=�������s}Ss��@w+=Kg���g���J����t=� =��0m3��6�[{���}�.�Z�p����0��+9$�>������T��=���L%�,����z���Im{����q,�0N<?^�b�����{�?!��.p�3������sE�^:t���>���I�`���	+���&�d����Gjk�%c��w_4���KF1�����>id��^)-8Q�H��)�y�L>����z�'��V���j�/���AB�����x����h���[�;z�U�������y/6�X% +y�&���aab�WM�F'��~fpU�hq�%�H�`+�,sl��F!
FV���>S��6,���!�qn�$��e����OV
o�����
G|w"c��Q�8]�
(�L���`O\�>�K�|�{����wQ��x��G��
<����mkcc#-��q��%��#�
/j�e���-�hI��K����l�bh��	�}�*�P�Bb���4�Lm}�%��pDW�<�N^��l�)�e�RN����6��������WT����{��u��k|#I}w����d��+7����g����C(\�^����y���)	�h1�4�T�'�B���������;��F�*dj�����m��l|��j5�p��s\Y[�_B���oU�)��
D��1!(r�^s��)��'�u@��Y�9R����J�9�����":�M�j\W����=h�!�)lwX���j4O�'G}��"\8c �@��Q����*�"��^Z!.�����Y�����g.���.r��~��H�=� g8��>K,1�K���k��WE��}�^�H��3*@������F}��Y\�o� !SM��,�����)��|R��V�G�W��K9����	�/��
�W�NH��7��j����i+/K{�Q4�Vf$\��	�2����R��j*�4���Q�E	��j��iw@�� �Nxx�������R��]�xXD$�� '�k#�o�B�oj�hHX��@S��oJ�G����W<m
N�Z�#�7U�B�������!M��[y:7��!��"VK����b�a�/*�v|t��?�����`��l��J=q#���>��h�'�C?�|��*�i���\�k��4~��g����@�a'��"�!���9kX�]zk�c�?��h(�>�D@�L]�.�����P/e����\3�Xq]�Q�~�RQ�����u�9������*������m��V���LH"U���	�n���f8x��=��}��sy�d��nQ�ax3������2����,�ff��F�TsF�,�[�n�s���yS� ���5�-J�$JA%fj�'0J���Y\��?q��Zm�(T%N�k$I��*�H�p���G!���d�����x%q���l���������K��q�P�i��S�T�F��x�xa�����K0�(~9=�Q��$���C���`��������Q1Th����=�@�<���)�n:�<>,F��� s���T\T/C��
d�+�BF�h�Ej�`�?'���d���l�����x@W����1pe�I�-a$�b��gj�9����e�����2T�����bf� r������D��aW&U����b��@�,+�)0r��?�)$5�C/��v�+���l��?���EctT��|�9��R�c�P�1����
X�U��t�G,��kU�b�tD�%�D�2�N{�"Z�&�/RT�*e[I?����%��)G�����F��C��_���9�F�qAS�0�1c)z�ce�[\	y�+����zf�����`���IG�uCdB�,*��F��m���������������XT��	�[�3K����F���[����%�X	�.�:�1�QHd�>��o�����$p"����,��d���u���j�)�������(
w�o�3�����9��lP!B��O�����_��e�����N�i���2ks9e��O� ��`� ��3g`���"c�Sd���H����'�Z�������	�����me�X=��Y�V.����'L����3�^�5���J`�u����\���x�^�f&2���G&^s2�)y���x}�b����b����z}�k)={�GM��HS P�E�w'�[�S���e����{�3t5�ii#��wE��`{�x
� iOMSQ��EQ�����X��L;-�&9on�{r�������ks/��h�xm�9_�}<���DG�PR"�K���
��]�,!���};��/q���v����0��������� 1�$���A'�+:��t�����W��2*��������0y�>����VN�u�������ZT�ld��H8%�����W,���(�V/�i�:L�D��x$<%.J�`I�;�����o�J%�h`����{�Tp*�O<��(U��
��1]3W����yd��k���������R:���~��k�����m���F�%>}y�^���e��������2I���U��MV�^N���by_v4U��U8�_	���T��
,T>�����K�����zA��+S?	���������YQ�E�~��bPc��Zx���D`vWgc��x��D�����;)�.�6�;0I��c�2�?�y�5itR���R�����:������itI���������Y�
�� qw��\�+�p��������ar�Y�nA������W2	�}R���@ugG.�&��KL.�8\\S���Z0vjz�i���,�g�+�g�R)�}!�M�4j�_{_���������'��6���d��E����V�'3��.O�l���H�������EU�����,'���d<&
@�P(T=��o��M�q��3�p]�0a�B�D��_T����I�C�0^�6lt���0�"���M���6j�A���f����V%ab+����_������X)��7HU#����2��B���1y�t�q�[g����H��e��Gk�>U$��m�3/xn�I�&��^��x���x�<B�'#_��7��5���:v�:�-��Z������^nX;���Cz���h��-��F7N�&GdM{����K8�����)�����Q��SZ�����<�h/?=8�����-�B��T?�
U����0�Q�����X�8����&���cR�H�������Gk.?a��0%�<�����8d���?������!f����d����i?�s��������N���rm�|�;���?;;:y�����vX9�?�S��Il����!��s��U�/Bdw����-�~�����z,������H���.x�J��Z��_r�������
�(��sL�1��Dt&A�y>��X�+��w�]}����<��d��QY+L��dp����gr������Fh�k��u�����*xj|�ZW)�Q ��R�Q�7�`�k)����G�V�L��k�$��;�(8�N�
�g�O�4�����p_}��a)����S�Vm:f+��S�{~.��A�~���^�L-����?i5��w��mTQV(�ly���a�Y�q�s��o���?������=��HA��o�[G����[�J7�s��vG$�jX��!5��DA���B�hR�g���v���. O��E����d>G&y:��1���y����J�C�T}EH6�{�UP0���UB�5k������k���K�jVY�5����jn�?MgwSU����{0u�'="�3^������Z���7�k��������;�u�q�����s���{bw��*��6
��)��1������dwj���K;�_���������d����k
����f3����T���P*���>]$#/�k����l��D�Dm��2#��o�{���-F}\��O�l�T�T�B�xq7-?D�Td.1.E6dyv��)���x~��kO0N�G@��K�s�#�H��-gd�NjY-�Y�3A�BH��2���2^c.���^��PBD����8��$?^��#Ct"Z6Q��-1������#�>�M�Pz6X���Bl	��T	2Uc�l������#��;���b���y����v�V�VW$������*�o�p�_�C$r���D����W�k�ktl�"�p�|�!�TH�P���dv���-�"�L���tJo��!z9F��f�Z�w>����v�Q���F�<B�����xkH��E���>��YV��J�Z
��:U��:��9�~����r�N�tH�;_��]e�D;�b�`4������;q����������o���,�pA�Ate�����`�h��Mv7���u��Q�#y���������}�9Q�T1���
�R�J�7rX��MT������e��^;�����[o	���/��>@��-��L�6}c���	����r��'�M|�v�\�d�����t?��}s�\��y��t=�9�1*g<)�Y$;O�CZ�X8/��]��t�����t�
�X4]Z���i7����x�n7VZL
�s���;�4�T���q���2?���(�b9�?���#��J���w��7%i')�I����������XA%��L33B��2G�jF��X��"� �f���}���Y��r��7B�����$������f� ��A	�J��:�,�Qak����C���7�r��m��F0D���(���'��8Q7[���Wc�Z����^��3'F�X��z�"Q�d�#�p6&���1�_4������Ae��@��d1tl��W�0����J/Fz�?�����E�|t�
~����bX[�(���u��?���%n���I���#|a�Db��$����(/�%�<R5v��V�[�5�{{I��g�[�G=����(It-��s���f7'`l4��7�V/��R�P��I��s��
���Cd����Jy�����:)�����,l��,bO�V!���~��4D,����l�R��M��ap;�^N���,>��Z�CD��D�.7�sj�jf����)x�������"��t���N��4���������kI�i���%H�'u��0��
��eV�1���T��>G*��]�Io�% s_�`#�Y��:���D�D�D���S��n;6]�K>��o
�'p���@
h��cKD��vJ�(�VqTy�����E��I�;6u:q�]��n>e1S*�#��w����a��S^u�����$��%��'d�-Bb�;T'�P\�N��N:���=a�;Q1�U�q
�nn�+[t���_��[����b����BG�=|�T����]����nj� �h�D���U�*Q��kE�v�����<}G�v�O���p�=��8����(r��y�q���3�=&�2�uS������b6o���@�3�Kbbd���L�j�	�Lg�0�����G ��i+��� �gHU�c�d��rz�?���'�p\�����%*���,�>���i.�v�,)U
c�U8�\��j�~�j9�`4����E����W5�xC���c���`0`�k�����#5���T��}���E�2(
P�V�^Y�<*V���	{R$2�]�8��-D�#1��N��	o,��tT�}���po����4��C��������./t~���Xr��[��t6�W�N�aIq^�WnX���Th%sh��Z����:�9feU�/l!�^������V�/j�z���J�kG-���N���@�ONe�=-��"����#WX<,@���;��~���3�����-�s�}�:I��E�(zIS�3�CZ���
}?R]g$w�_q�NUe6�	���J�G2GU�?��J�-wZ���,��w /Q9��Z��59x!��M�m��-3�J��N+J%��<� ���WB��F�h5-j������r���0Ugc6�(NX�E���\U��`��I��j*��7�c9�|���8�I����G[��Y\-��0IeV������l��!O_�WI�/��N������bt������a�m��U���0�xc�E(�X����T��4e�rpz����u�X,��O�z��YN��9������@iB������/4��>5������!��S����U�dV�hL(��?xl]s��:����i����}�h�J[C�.jZ�K�B���m`a�k�'�x��P�����lC�O����F�'��]z�d�a �c�1N�H.�M�x��tBv���O}�	��D�����N���������������^6�{M��te��MiIyu��w�
}��f/����j�eg���B�a�AD����:^��jb#�r�p�]�o�w����?���ut����l+>���+1UK?�d+�/k�>�Yai����m3���8��AL�X	������N���IN�
��� �&��5B�7�B��P�u�!	k$����F�)������-^�;��0������+�hL9��&��QP���Yr���`�SP��*�400%4#ee�SsG�	�g����` ��e_������u�O�(4���	J&�j�f��#37�}�9���F�Y':8����9U��-����+���oUK
�����V������A�����w�����a��m�hH���bT�w��w0[~������y����ng�=��]8�}cq�3�K�Av�>�����jV6���s�"�����s�����[�q��%��L0z:������8_�?`�%�?d��7���#R^���$�������A�T���w���������4��!"M�u"��,_le�!n�<Q�a���26f#��8�\k����|�i�|W��n��l6��X��/.&L1��I.�b�X.c`\��C[���v�0&�Wh|j2:��2%�5/�b�=��w�KB��O�
���f�Yj�Fl|-u��r�6r����V���U���}k
��a��wr(���ON�����+9���	�Q~]�[��^��������U���z�&��N ��?'�����"��6����[ �
n�P-~eHn��\	��M�����B��
8���6����Y���w�t�k�]�diCXhYL63�<��L���a9�+��/UVh+U���\i�d�V2O���0����+N��%����x�F�0��1m�#_?��V��^�b�=,UZEW��G������@�����aE�o���-���,(2������A�tv���UO���`�`4�a!l�K	��
H����<������>I����e�I_����]w�i#��S���J)
�,��7U����iNI�9v�Z��Og�,9��������_�K?�,����V���^g��/�P��/�q]m�@t}k=���h��f���dR��"z�e��������XA�V�n������U~�����.��<ZdN���U��m��Q�����������2G�,�H=�kg�)7;���,���=����!M�!��aS��"��7w�*�5����2+��^�7�8����'�v�t�e���W�P(���~������;�NgX�3��Y����J�;%-ux�i��y��
^6�y�h4l����Vk5��=q�X1��*BS��T��H]=�����i������
I����V\\��������i\��=�N�����VMJ�)</)������r�=�,��5
E����~|��4�����;��9�7��<w����_{P$u��b���o>��r!���yO{����|)t7�{gnY49}��Y�n���y��������]�8sm�;�R��t�h_����TR����v/Nv[|�BB�!���8]�i{��B�P��k.���r�"d��a�]�R�b������5������rW��%(���>����)��@;`��i��46����"����y�$���y�7�p�RXBeB�O���+��c$�*���X6	�|�`�,� {y}���SXX:�R�g���ot������:]d��|��)B{��?A�B��8%��.f�����������T�o���	g��l��jb��������:�?��d��R�h^��A��L����Hg�z����)��jx��D����F�7-�Th;�8����l��t����?�u�����J
��BW����*[$a��nR?F��eG��mY�D|D�4Q��|�O�!�����g�Q��`	��oF��T�������.G����-�~x[�kw�������=%.VG�����o	5i	/TA���jk�N��x�f����/od��@"���(����2Y��������%�������/4����F���|	��jHb`�kC�]g7$>M�������OY��Ga���W����x�����a2�m����O!3��(s!2��v=F���;�c��%��;���#���"
��n������b{�u��k1�]��
U�hCZ���Y�0��XV'SIE3�����M����1�(���9�sJT/��L�^z+��(���-�
q�[�5%�8������D�N+�t�8�t3����n�K��8�v�A��P*�&���������y�--�HR�3)J��)L
,t����1ORI�'	���:q�h$��z�F�/=���������g���"���^R_�&�#��j��	�>�z����d��������<j]�x��$M����MP���N�|��k�[�a��z]`��� c�rfc��u�W����8#��U��w�=
�+��Q�u�YM�������a'Qdm�H>��D�E=���Q�
�8I�-�32��Y��[�����5?�B�����e(+����,�b��d���Q���)}�*��+��f3�<�������Q�����u+�yK�Yl�-�N�4[�/-^�����r��2����a���f�X1e�!�g�R���B���<B��Bm<�l=�P�����a�8����������_���j������+<��[����s^�N��Hg��
���*}��}������E�a��9}
�����
�f����s+us��J�E��wPP��04��;�8����C��:�	�m��f�G#�
�B	[6%O1��-���e��`��"�j�����:���R��%�tl�����#;����|.zsz��/�����_n���]�}�����?���=��
<������F
ZP� ��)a�<*��cl��3F��j������[-q�l�	78���kEt������o�
�����3��^w�Y�u���Or^������g�>koY Z,UY�<�{8�;\#�Yqa��s��u��F�e�Z��z,�b���v���R���	�P��o���$�O�wk6��4`U�����R�!Q��P����n�[rd��.D��wa�C�_ R�7��w�?�t�h����l�Vkw�u�\�"�V��p3�R���B�^�1�a�3����V��R�Un�rB���W�t� A�!b~	���pV�������E�b,`��&�sK����������
�tX���]J>�}m��
�L��\(���pqv�����x'���	> y�@l��C��<��KyYw�=��,���������"[XNBaJ�V�$lJ�81(������<�k���N=&���n�������
��,��gK[�"�'yg�'�|xs�ie��l*����'���s|����R
���a�u[�C�H�v,�b~��������Xt��������9���M�h�A�2�V��o��]��#�)[����!qb;�<!�a�"�(2��65W�5�|&�KMt�`F��s���^�KX�9�g3vL���P��A&�V��x���Ob����(gt3[d 7�$�����J�"��z�A>u�����p������u��B9z��5\H#`��F�i��n�Z�X:���j��A\U����G�'��	Mf�GA/�'_�8���e�,�Eu��{*���t�?!)������r*�/l�^SG��ta���l
�GZ�V���\#��5�*��.�f���7�g�����Z� *��5e��}������Q*�{Z�s��5���TEG����P]!4��^�]5e�Q5��J��>�2%6:�3USSs`�K:���x�C���&��K����nW*���x�
*�������U;����#�����W(]��-YF���8�&��J	���=>���_q��b����;Q���X�\S}��Y<��Q��{���z9�U�����p��wEB�V�*1����x�GQi7qmc�����Rg_5Vv�����>Zkm����7�����U@B�9���i�/���\Y����z����������u�n�8�f�lx�-%�gW�������F�L�v��p��UI�=���pd�O�^\��<<��	�*6����3zJv0�����?�\�F�Q����j�a�3(�TS\��1�/4a��8@��Q|���M4&��~�����g$��pi�����}6��
Y
C/���%�,���1�t;I��hu�$i���3����i��P���Fb+��>-Z�N����)�D���_\���W���I8��������T�������<���w�I��"�E�����������8�.�7� �%�]Ct^��]~�_����������������H�]*�c�A+E�<�A��n�%����5��vW��",,�+�=���^����8���N{�:���G�����<P���k���
rGI�G���5��3���z{�H4E�G,���"S���?F���@���t4����j�]	^���js��Qm��o<�s83�"(m�X�/;�/L��|.�Q�G4e��@tO���L�����<��L�O��8��Kq:��q����z�3=�������UN�<���u���i�����C���A�b�Y�E�'\@��Z�6�������V2UK�	O��B�
�;��]�S���c�1V�����!]�i[=��e�����VY]�{mn�t1��x
�9�)��������/�.&W�Qu�5���8:����Ev�����b������l���X
��F�����K���8!��^e��O�Z�A�p��������Lm���yT�Uq��i�������-�b�H�|�q��%�[�)(g�e���G��l��f�*o�9��E�x[F
frT���|��A������5����� j
f�-��� U�Fk�:&_7��1d����/G��OO�x�?��$z<��0^�Nrzt��"BF��'RJ�K�Gu��ks!�<�"R��%��O�"�����h��e_����������q����B\�g��X�%6GL�k!�7����^���1�X40
��^U��K��T�_f��G����t�^�\Nn2u��r�_k�������O���!>o|U�\������V>�����j�A������|v��_�G/�r�:���x6��l~������s6�4mE;
���V����W���W/�W(8�2=���I?�	"Y�$����b�*e�z��b��9���vi7^�b��7"{��ky({��M�}#�[Q6��,G��T�6�R6�a��4�r���E�T�c�2Ua��u7�V+���A��0 ~�>����{�aH�-&bo]~{�����B���
��C*���60Z9���`��Ws���q��>Z�o<��:D���s\�H��kI��,�"�^����^����
�;����qQ�x.�y��{�M���FV {X)��I����<��h��m��9�w������Z^oo>�����
f����$�}�G��X��bfD�&r���k+������p��K�F`��b!	�W������Me�������A[����hR&��f�=����p��,1�����`�K�g`��L��������S���,�\GL4�&����E�A�=�1y�T������(|�~��.�{���.�*
������v��.�wE/w�������x��d~:��>������z���bw��VL?���z���������w���pr����U2�9��AJ[��L;�n�����]���fU9+S���D|���j2#���OuJ&��A����������xJHje/^��M(���?&W!�������,�9�&��@�P7�9��5�������N����EO�u���,����Aa'*)���~����_z[���7U���W��������/��+�8�x-uZ�����:Qq�q�U���#M�Q*>��v���h��;[t����RC����P�m�i�����C�xe[���W���~ceC����x:��ol'4�����o�l
,s��>�84g2�^�s_���~�57��M�
�����S�\c����:���������G����0i[��6PMq�
dt#���9��������_��@4�����Su�������� �*�R#�h�M�����o��I��Y��Qn�S��7W�*��������q��������w�+�t���3����(��6�n<HY{�s�+[N
��;&�����U� �x���I:���?0��Ch��R���
o�����"�N���;�TL����|���T/�9H����Kw�Uuk��a�0�>���D�T���2���l��y7xS��=d���@�Gp(!�	b����!bg ]���C���tx*O7	&FC|Sd \�H�;�>��7��k�4��~��0��>��|h��d�h�2��&C�h1��a3FR���7,���Hp�%V������n8��>��syT���|��|��[�#&��FEvS�<���*�G�,�c����,?a��2��/�<f�2�4�F/���f�K�V��U�w$����W�5�i����Y�2�N����
��%K��I�\�f�������;�B�^K�!"���	<�Ec'���=���a*����/MQ%����o{[�f4��
.��8b
�:`I
���-(l������s��&j��A�:���K����{<o�� ��L�Z.�#c2���T��~�L�:��O���1`��R���������'��f$(���&fTU!Fh�q����|Xd���h]zG���C���E�����k`>�O�$R�C�X�����/o� ��i��m�^��#f���C��"�X
����YV�C�����e;�R���k��n�o+�%
+�iTMs��'��X��5�x���T����%�\��`��E�������,�����!�1�9!5�����s|K���P�&����1`�99�<:�!k.#q�;LPJ�����!����z�����:�S��Zd�y/P���Va�?��������|�����G����e��#�����U�����@���o�z%�x�G�@^3]��;F�l
���R����5�����F����r��o�v�*�����9�}����"�fb��q�PEy�M!�����;x�W�~�9��@.0q<1K���4��w���m�������^��iq
U��f)��O��v����E������%*�|~r*Y�=�����������p8��y�����]O��� '�g���04��������>�~l[;([�_o$	���:-6�o��V��'���:pDP�p��������HeO���C^�A_h��^�
�|���<�$�l�!��.P[�r����Y���{~��������eR�V�����G�
�66��� o��w�N�HijW��z���A�����~��X�J��cqJ<�Wy�r��iT�������������O���zF�}�S�4���Uxrg���v�����d�Y��W���
S_�@�����	�D�C�-�Jl�^����%��R��6��{o.��uztb��ESX!�5�w���)T��?��{�M�����Z`u������������|+:���a�����C��~�wU��'�D��L����|��>�b�^8E�i����Pv����0y��r9�����`�������o��y_g�%Z2'X��-��|0�Th5'�m�q�@^�����K^J��UJF37�!/?-��%yX��ay�1���qM�Q������<Y�M�W�Z�%-���K�6*������.vY��B% ��,��y�</
�!g�Mk@Y��>�@���G�W>�Ga�Z�z�&����g�8~����a��v����UV����y���y���y���y����`��}�3�����wiAe��4+�+=��Y8y\N��'�o:I��B	��ON�''{�����8��Yp�����.���+R����C�U��oRq��W��4��pj/���D�,f/>a���<�|,#_O��m��*���7���b����l�O��+9��bYR:��*�;Agel�0C>�	���2�U�F�<O�J�{�-���W��W}�>�P�<��M���<+���<_�������x�����!@�]���<@����!�C^%-'�4L�P�!�|�K�948���6<&!�V���n���{1�d���&�7X9����2�0G����f
��H��KhuJ+A6�o��#�r�$,G�<��C��=��8	8y��	�VdE�?x�&���,h2oi�\�1�bru�������
2�,x��FTR9yL�E�32�K�yna?�dG+������l�Qc��"�����";�GI������b�zUt���\���gw~=+��y�g��V�V2z�����}%�d5�si!f?9T��X�k.#����`��0� �=����Q�s`�l<A�E��$�s �l��Y�(5ku��*S���`����;y\��g��
��&�UI��.��\"W�V,���2���Q:%/G |eb�����^[�Q��q��G������%	U;N�:.���)E��6�*�
��4=`W���aI��%%�!��*�-i�����K�0/iz����201�����^���lL�}!#���L��73Y�23U�����4��S���lM����!k���M�`o2t��I�C'M��4=`sR���I�V'5>!��.�;iz����Y�,�o��=Yt�Za}R���T���I���k�bt�	�|�o�2��F(F�Z��k�bt�J��(EY�4=`�R��-J��(EY�8�g�R���\�k�*���KwMR.��I�t�(U�;V)N���8|v)E�T�!��>FLS��M�3��8�Nq^��E���,��h��@��*N���z�F��^#���T��3S��������*N�Y�,��T��^[�{�U�e�bt����{�U��5XYt����}&+&�^�+���Ya�����E����k��t�a����-F���zQ�V��k��t�y��}�-��lz��e��&.�^jX���k�����K�g�����]�|��e�~S��l]��!c���]������}�.N��8�g���E���l^E�m�������}f/E��C�/C�[�L�~���l_��3~���k�����/jC�H[���f+w3���3�O�W1��J���4j�,4��`�c����G�'��F���Vg/nu�$�
��&~������
��{������P���ZH�J1��b��e�ubD��|X��W
��
,�����#��qC�+b���4|�H�?�
�R��o��z����������(t��Y=�H�KO��y?�pa
#35Stephen Frost
sfrost@snowman.net
In reply to: Stephen Frost (#20)
Re: [PATCH] DefaultACLs

* Stephen Frost (sfrost@snowman.net) wrote:

* Robert Haas (robertmhaas@gmail.com) wrote:

On Wed, Jul 22, 2009 at 11:26 PM, Petr Jelinek<pjmodos@pjmodos.net> wrote:

The docs are not complete but that's up to Stephen, otherwise the patch
should be finished. But I am not the reviewer :)

Well, perhaps we had better prod Stephen then, since complete docs are
a requirement for commit.

I didn't think the docs posted were terrible, but I am working on
improving them.

Thanks to Joshua, there weren't really many changes I found for the
docs. Here they are anyway:

grant.sgml:

+ Replace current privileges with the ones specified using 
+ <xref linkend="sql-alterschema" endterm="sql-alterschema-title">.
+ The <literal>WITH GRANT OPTION</literal> parameter is not applicable 
+ because it is copied from default privileges.
+ Note: this can actually <emphasis role="bold">revoke</emphasis> some 
+ privileges because it clears all existing privileges object has and 
+ replaces them with the default ones for the schema in which this object
+ resides.

How about:

Replaces current privileges with the default privileges, as set using
<xref linkend="sql-alterschema" endterm="sql-alterschema-title">, for
this object type in its current schema.
The <literal>WITH GRANT OPTION</literal> parameter is not applicable
because it is copied from default privileges.
Note: This can <emphasis role="bold">revoke</emphasis> privileges! This
will clear all existing privileges first! If no default privilege has
been set, the object will have all privileges revoked!

Otherwise, I thought the docs looked pretty good.

Thanks,

Stephen

#36Joshua Tolley
eggyknap@gmail.com
In reply to: Stephen Frost (#35)
Re: [PATCH] DefaultACLs

On Tue, Aug 04, 2009 at 01:28:00PM -0400, Stephen Frost wrote:

Thanks to Joshua, there weren't really many changes I found for the
docs. Here they are anyway:

Yay, I was useful! :)

How about:

Replaces current privileges with the default privileges, as set using
<xref linkend="sql-alterschema" endterm="sql-alterschema-title">, for
this object type in its current schema.
The <literal>WITH GRANT OPTION</literal> parameter is not applicable
because it is copied from default privileges.
Note: This can <emphasis role="bold">revoke</emphasis> privileges! This
will clear all existing privileges first! If no default privilege has
been set, the object will have all privileges revoked!

Otherwise, I thought the docs looked pretty good.

No complaints here, FWIW.

- Josh

#37Tom Lane
tgl@sss.pgh.pa.us
In reply to: Stephen Frost (#35)
Re: [PATCH] DefaultACLs

I looked at this patch a bit. I haven't actually read any of the code
yet, just reviewed the on-list discussions and the docs, but I think I can
make a few comments at the definitional level.

First: there's already been some discussion about the VIEW problem.
I understand that the patch adds a "GRANT ON VIEW" syntax that works
like "GRANT ON SEQUENCE" in that if you say VIEW it insists the object
really is a view, but without taking away the existing laxness that
allows you to still say GRANT ON TABLE for a view. This seems reasonable
in isolation, but it creates a big problem for both this patch and the
GRANT ON ALL patch, in that it's unclear how to treat views if they
could be considered either tables or views. Unfortunately I think
we have no choice but to live with the "existing laxness", because
(a) to do otherwise will break pretty nearly every pg_dump script, and
(b) the SQL standard says we have to accept "GRANT ON TABLE view".
In fact "GRANT ON VIEW" is not in the SQL standard and there is no good
reason to expect applications will adopt it at all.

So my feeling is that adding GRANT ON VIEW is a bad idea. The main
argument for doing it seemed to be that the author wanted to be able
to grant different default privileges for tables and views, but I'm
unconvinced that there's a strong use-case for that. You could very
easily have one set of default privileges for all of tables, views,
and sequences, simply ignoring any bits that weren't relevant for
particular objects. It seems to me that that would handle common
cases just fine. For complicated cases not so much, but it's not
clear to me that filtering by the object subtype is all that useful
for complicated cases anyway. People will want more functionality
than that. Which leads into my next item.

Second: both this patch and GRANT ON ALL are built on the assumption
that the only way to filter/classify objects is by schema membership.
Now I don't object to that as an initial implementation restriction,
but I don't like hard-wiring it into the syntax. It is very clear to me
that we'll want other filter rules in the future --- an immediate example
is being able to say that new children of an inheritance parent table
should inherit its GRANTs. So to my mind, designing the syntax around
ALTER SCHEMA is right out. Maybe we could do something like
ALTER DEFAULT PRIVILEGES ON TABLES IN SCHEMA foo GRANT ...
where the "IN SCHEMA foo" part would be subject to generalization later.
This also matches up a bit better with the proposed syntax for GRANT
ON ALL (which also uses "IN SCHEMA foo").

Third: speaking of syntax, I don't like the way that this is gratituously
different from GRANT/REVOKE. I don't like using ADD/DROP instead of
GRANT/REVOKE, nor the unnecessary AND business. I think we should
minimize confusion by using something that is spelled as nearly like
GRANT/REVOKE as possible.

Fourth: the system's existing treatment of default permissions is
owner-dependent, that is the implied set of permissions is typically
GRANT ALL TO <owner> (from himself, with grant option). I do not
understand how schema-level default ACLs will play nicely with that,
except in the special case where the schema owner also owns every
contained object. If you copy the schema-level ACL directly to a
contained object with a different owner it will definitely be the wrong
thing, but if you try to translate the ownership it will confuse people
too. And confusion in a security-related behavior is a Bad Thing.
Furthermore, having the schema owner able to control the grants made
for objects not owned by him is a huge security hole.

What I suggest as a way to resolve this last point is that a default ACL
should apply only to objects owned by the user who creates/modifies the
default ACL. In this view, the question of which schema the objects are
in is just an additional filter condition, not the primary determinant
of which objects a default ACL applies to. Every user has his own set
of default ACLs. (This is another reason not to design the syntax
around ALTER SCHEMA.)

regards, tom lane

#38Petr Jelinek
pjmodos@pjmodos.net
In reply to: Tom Lane (#37)
Re: [PATCH] DefaultACLs

Tom Lane wrote:

So my feeling is that adding GRANT ON VIEW is a bad idea. The main
argument for doing it seemed to be that the author wanted to be able
to grant different default privileges for tables and views, but I'm
unconvinced that there's a strong use-case for that. You could very

Yes that was the intention. I do have users in my databases with access
privileges to VIEWs but not to underlying TABLEs so it seemed like a
good idea to be able to do that with DefaultACLs and GRANT ON ALL.

Second: both this patch and GRANT ON ALL are built on the assumption
that the only way to filter/classify objects is by schema membership.
Now I don't object to that as an initial implementation restriction,
but I don't like hard-wiring it into the syntax. It is very clear to me
that we'll want other filter rules in the future --- an immediate example
is being able to say that new children of an inheritance parent table
should inherit its GRANTs. So to my mind, designing the syntax around
ALTER SCHEMA is right out. Maybe we could do something like
ALTER DEFAULT PRIVILEGES ON TABLES IN SCHEMA foo GRANT ...
where the "IN SCHEMA foo" part would be subject to generalization later.
This also matches up a bit better with the proposed syntax for GRANT
ON ALL (which also uses "IN SCHEMA foo").

Actually I was planning to extend GRANT ON ALL - if it was accepted - to
include more filters (something like OWNED BY for example).

Third: speaking of syntax, I don't like the way that this is gratituously
different from GRANT/REVOKE. I don't like using ADD/DROP instead of
GRANT/REVOKE, nor the unnecessary AND business. I think we should
minimize confusion by using something that is spelled as nearly like
GRANT/REVOKE as possible.

ADD/DROP was side product of having SET and that "unnecessary AND business".
If we went with that syntax you proposed we could just put exact same
syntax as GRANT and REVOKE after the filtering option, that should be
close enough :). I remember Stephen being against having GRANT in ALTER
SCHEMA but I doubt he would be against having it in completely new ALTER
DEFAULT PRIVILEGES statement.

Fourth: the system's existing treatment of default permissions is
owner-dependent, that is the implied set of permissions is typically
GRANT ALL TO <owner> (from himself, with grant option). I do not
understand how schema-level default ACLs will play nicely with that,
except in the special case where the schema owner also owns every
contained object. If you copy the schema-level ACL directly to a
contained object with a different owner it will definitely be the wrong
thing, but if you try to translate the ownership it will confuse people
too. And confusion in a security-related behavior is a Bad Thing.
Furthermore, having the schema owner able to control the grants made
for objects not owned by him is a huge security hole.

We were actually discussing this with Stephen yesterday as something
similar occurred to me too. The patch as submitted just does the copy,
after the discussion I added post-processing on object creation which
translates everything to owner but I guess there is no point in
submitting that now.

What I suggest as a way to resolve this last point is that a default ACL
should apply only to objects owned by the user who creates/modifies the
default ACL. In this view, the question of which schema the objects are
in is just an additional filter condition, not the primary determinant
of which objects a default ACL applies to. Every user has his own set
of default ACLs.

We could certainly do that. I wonder what we should do about inheritance
of default privileges between the roles if we did this - should it just
be what I set is mine and my parent roles do not affect me or should it
get default privs from parent roles and merge them with mine when I
create the object ? Also when creating new default privileges entry
should we use some template which would give owner all privileges like
GRANT does when there are no existing privileges on object or should we
just use blank and leave it to user to grant himself default privileges
on objects he will create ?

I don't think there is any point in you looking at code since
DefaultACLs might need serious rewriting.

GRANT ON ALL is a bit different story, there I can just remove all that
VIEW stuff, although I would very much like to have the ability to
affect only VIEWs. On the other hand removing VIEW as separate object
type would remove my biggest problem with how both patches are
implemented (I mentioned it few times in the previous discussion) so
from that standpoint it might be a good thing.

--
Regards
Petr Jelinek (PJMODOS)

#39Tom Lane
tgl@sss.pgh.pa.us
In reply to: Petr Jelinek (#38)
Re: [PATCH] DefaultACLs

Petr Jelinek <pjmodos@pjmodos.net> writes:

Tom Lane wrote:

What I suggest as a way to resolve this last point is that a default ACL
should apply only to objects owned by the user who creates/modifies the
default ACL. In this view, the question of which schema the objects are
in is just an additional filter condition, not the primary determinant
of which objects a default ACL applies to. Every user has his own set
of default ACLs.

We could certainly do that. I wonder what we should do about inheritance
of default privileges between the roles if we did this - should it just
be what I set is mine and my parent roles do not affect me or should it
get default privs from parent roles and merge them with mine when I
create the object ?

I don't believe there is any "inheritance" needed or involved. A
default ACL would only be looked up for use at the instant of creating
an object, and what you'd look for is one owned by the same userID that
is going to own the object being created. Anything else will be too
complicated to be understandable. The commands that actually
create/alter a default ACL would work on those belonging to whatever
the effective userID is.

Also when creating new default privileges entry
should we use some template which would give owner all privileges like
GRANT does when there are no existing privileges on object or should we
just use blank and leave it to user to grant himself default privileges
on objects he will create ?

It should start from the same initial state you'd have if you didn't
have a default ACL. Anything else violates the principle of least
astonishment.

regards, tom lane

#40Petr Jelinek
pjmodos@pjmodos.net
In reply to: Tom Lane (#39)
Re: [PATCH] DefaultACLs

I had some time to work on this patch, and I implemented the ALTER
DEFAULT PRIVILEGES syntax as proposed by Tom and adjusted some other
stuff, but before I can submit the new patch for commitfest there is
still this fundamental issue about how it should behave.

The situation is as following. Josh's and Stephen's idea was basically
to solve something like this: you are a dba, you give some users
privileges to create tables and you want those new tables to have same
privileges no matter who created them.
But if I understood Tom's suggestions correctly then his approach does
not solve this at all since every one of those users with CREATE TABLE
privileges would have to also set same DEFAULT PRIVILEGES and the dba
would have no say in the matter.

I personally can see use cases for both but I don't really see any
reasonable way to have both at the same time.

--
Regards
Petr Jelinek (PJMODOS)

#41Josh Berkus
josh@agliodbs.com
In reply to: Petr Jelinek (#40)
Re: [PATCH] DefaultACLs

Petr,

But if I understood Tom's suggestions correctly then his approach does
not solve this at all since every one of those users with CREATE TABLE
privileges would have to also set same DEFAULT PRIVILEGES and the dba
would have no say in the matter.

This latter approach benefits nobody. If default's can't be set by the
DBA centrally, the feature is useless.

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

#42Petr Jelinek
pjmodos@pjmodos.net
In reply to: Josh Berkus (#41)
1 attachment(s)
Re: [PATCH] DefaultACLs

Josh Berkus wrote:

But if I understood Tom's suggestions correctly then his approach does
not solve this at all since every one of those users with CREATE TABLE
privileges would have to also set same DEFAULT PRIVILEGES and the dba
would have no say in the matter.

This latter approach benefits nobody. If default's can't be set by the
DBA centrally, the feature is useless.

I agree, however I assume I understood Tom properly since he didn't reply.

So I've been working on solution with which I am happy with (does not
mean anybody else will be also though).
I created a new version with syntax devised by Tom and one which is user
centric, but also one with which DBA can control default privileges.
The attached patch adds syntax in this format:
ALTER DEFAULT PRIVILEGES [ IN SCHEMA schema_name(s) ] [ FOR ROLE
role_name(s) ] GRANT privileges ON object_type TO role(s);
Obviously it sets default privileges for new objects of given object
type created by role(s) specified using FOR ROLE (is that syntax ok?)
clause and inside specified schema(s).
If user omits IN SCHEMA it applies database wide. Database wide settings
are used only if there is nothing specified for current schema (ie no
cascading/inheritance).
If FOR ROLE is omitted then the privileges are set for current role.
Only superusers and users with ADMIN privilege (we might want to add
specific privilege for this but ADMIN seems suitable to me) granted on
the role can use FOR ROLE clause.
The order of FOR ROLE and IN SCHEMA clauses does not matter.

Some of my thoughts on the changed behavior of the patch:
There is no need to be schema owner anymore in this implementation since
the privileges are handled quite differently.
There are no longer issues about who should be grantor (discussed on IRC
only, there was problem that schema owner as grantor didn't seem logical
and we didn't know owner at the time we created default privileges).
Also there is no longer a problem with what should be template for
privileges because we now know the owner of the object at default
privileges creation time (which we didn't before as it was all schema
based) so we can use standard template as used by GRANT.
The whole thing is more consistent with GRANT.
The patch is also a bit smaller :)
It's not as easy to do the original idea of setting default privileges
for schema for all users with CREATE privilege on schema but it can
still be done, one just have to update default privileges every time
somebody is granted that privilege, and DBA can still have control over
it all.

Hopefully this will at least inspire some more discussion on the matter.

--
Regards
Petr Jelinek (PJMODOS)

Attachments:

defacl-2009-09-14.diff.gzapplication/x-tar; name=defacl-2009-09-14.diff.gzDownload
#43Josh Berkus
josh@agliodbs.com
In reply to: Petr Jelinek (#42)
Re: [PATCH] DefaultACLs

Petr,

It's not as easy to do the original idea of setting default privileges
for schema for all users with CREATE privilege on schema but it can
still be done, one just have to update default privileges every time
somebody is granted that privilege, and DBA can still have control over
it all.

Sounds like a good solution. Thanks for persisting with this.

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

#44Jan Urbański
wulczer@wulczer.org
In reply to: Petr Jelinek (#42)
Re: [PATCH] DefaultACLs

Petr Jelinek wrote:

So I've been working on solution with which I am happy with (does not
mean anybody else will be also though).

Hi Petr,

I'm reviewing this patch and after reading it I have some comments.
Unfortunately, when I got to the compiling part, it turned out that the
attached patch is missing src/include/catalog/pg_default_acls.h (or is
it just me?).

I'll continue reviewing while waiting for a new patch with that header
file and in any case will send the full review tommorrow.

Cheers,
Jan

#45Petr Jelinek
pjmodos@pjmodos.net
In reply to: Jan Urbański (#44)
1 attachment(s)
Re: [PATCH] DefaultACLs

Jan Urbański napsal(a):

Petr Jelinek wrote:

So I've been working on solution with which I am happy with (does not
mean anybody else will be also though).

Hi Petr,

I'm reviewing this patch and after reading it I have some comments.
Unfortunately, when I got to the compiling part, it turned out that the
attached patch is missing src/include/catalog/pg_default_acls.h (or is
it just me?).

I'll continue reviewing while waiting for a new patch with that header
file and in any case will send the full review tommorrow.

Hmm looks like I forgot to git add that file in the new branch, sorry
about that.
Attached patch should have the missing file (otherwise it's same).

--
Regards
Petr Jelinek (PJMODOS)

Attachments:

defacl-2009-09-18.diff.gzapplication/x-tar; name=defacl-2009-09-18.diff.gzDownload
#46Jan Urbański
wulczer@wulczer.org
In reply to: Petr Jelinek (#45)
Re: [PATCH] DefaultACLs

Hi,

here's a (late, sorry about that) review:

== Trivia ==

Patch applies cleanly with a few 1 line offsets.

It's unified, not context, but that's trivial.

The patch adds some trailing whitespace, which is not good (git diff
shows it in red, it's easy to spot it). There's also one
hunk that's just an addition of a newline (in
src/backend/catalog/aclchk.c, -270,6 +291,7)

== Code ==

There's a few places where the following pattern is used:
if (!stmt->grantees)
whereas I think the project prefers:
stmt->grantees != NIL
Same for if (schemas) => if (schemas != NULL)

I'm not sure if this pattern in src/backend/catalog/aclchk.c is the best
option:
if (rolenames == NIL)
rolenames = lappend(rolenames, makeString(pstrdup("")));
if (nspnames == NIL)
nspnames = lappend(nspnames, makeString(pstrdup("")));
Appending an empty string and then checking in strlen of the option
value is 0
is ugly.

In SetDefaultACLs the OidIsValid(roleId) is not necessary, maybe better
put in
assert(oidIsValid). The caller always puts a valid rolename in there. Or
maybe
even better: make the caller pass an InvalidOid for the role if no is
specified. This way the handling arguments is more uniform between
SetDefaultACLs and ExecDefaultACLsStmt.

The logic in get_default_acl and pg_namespace_object_default_acl could be
improved, for instance get_default_acl checks if the result of the scan
is null
and if is, returns NULL. At the same time, the only calling function,
pg_namespace_object_default_acl checks the isNull flag instead of just
checking
if the result is NULL.

Also, pg_namespace_object_default_acl could just do without the isNull out
parameter and the same goes for get_default_acl. Just return NULL to
indicate
an invalid result and declare a isNull in get_default_acl locally to use
it in
heap_getattr. This also saves some lines in InsertPgClassTuple.

Also, in InsertPgClassTuple change the order of the branches:
+   if (isNull)
+       nulls[Anum_pg_class_relacl - 1] = true;
+   else
+       values[Anum_pg_class_relacl - 1] = PointerGetDatum(relacl);
to have the same pattern as the next if statement.

Also, changing tests like if (!isNull) to if (relacl) makes it more
natural to
see sequences like if (relacl) { do-stuff; pfree(relacl); }

In ExecDefaultACLsStmt this fragment:
else if (strcmp(defel->defname, "roles") == 0)
{
if (rolenames)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("conflicting or redundant options")));
drolenames = defel;
}
Should test if (drolenames), because currently it's possible to do:

alter default privileges for role test for role test grant select, insert on
table to test;

Maybe add a unit test for that.

A comment in dependency.c function getDefaultACLDescription with
something like
"shouldn't get here" in the default: switch branch could be useful,
cf. getRelationDescription.

In ExecGrantDefaults_Relation there's a hunk:
+       if (isNull)
+           elog(ERROR, "no DEFAULT PRIVILEGES for relation");
Maybe you could move it higher, no need to do other stuff if it's going
to fail
afterwards anyway.

ExecGrantDefaults_Function and ExecGrantDefaults_Relation could maybe share
code? They look quite similar, although it might not be so easy to
factor out
common functionality.

The "unrecognized GrantStmt.objtype: %d" error message needs better
wording I
think.

No code patch removes rows from pg_default_acls, so it might accumulate
cruft. Maybe a drop default privileges? Or maybe revoking all would delete
the row instead of setting it? It has the same meaning, I guess...

== Compiling and installing ==

My gcc complains about

gram.y: In function ‘base_yyparse’:
gram.y:1128: warning: assignment from incompatible pointer type
gram.y:1135: warning: passing argument 1 of ‘lappend’ from incompatible
pointer type
gram.y:1135: warning: assignment from incompatible pointer type
gram.y:1136: warning: assignment from incompatible pointer type

Regression tests fail because of the username mismatch

! DETAIL:  drop cascades to default acls for role postgres on new
relation in namespace regressns
--- 951,957 ----
! DETAIL:  drop cascades to default acls for role wulczer on new
relation in namespace regressns

== Testing ==

Tab completion is not up to speed - annoying ;)

The functionality worked as advertised, although I was able to do the
following:

postgres=# create role test login;
CREATE ROLE
postgres=# \c - test
psql (8.5devel)
You are now connected to database "postgres" as user "test".
postgres=> alter default privileges grant select on table to test;
ALTER DEFAULT PRIVILEGES
postgres=> \c - wulczer
psql (8.5devel)
You are now connected to database "postgres" as user "wulczer".
postgres=# drop role test;
DROP ROLE
postgres=# select * from pg_default_acls ;
defaclrole | defaclnamespace | defaclobjtype | defacllist
------------+-----------------+---------------+-----------------------
16384 | 0 | r | {16384=arwdDxt/16384}

The numeric defaclrole and defacllist don't look good.

While testing I also got a "unexpected object type: 1248" from
src/backend/catalog/pg_shdepend.c, but was unable to reproduce it.
This happened after I did a combination of DROP OWNED BY and REASSIGN
OWNED for
a role that has membership in other roles and that all had default
privileges
in a schema.

== Docs ==

Need to document that FOR USER also works (as a synonym for FOR ROLE) in the
synopsis of ALTER DEFAULT PRIVILEGES.

Add examples of usage of the FOR USER/FOR ROLE syntax and explain what
they do.

In the ALTER DEFAULT PRIVILEGES synopsis there's a copy-paste-o because
it says
you can do:
ALTER DEFAULT PRIVILEGES IN SCHEMA s GRANT DEFAULT PRIVILEGES ON TABLE
TO test;

The wording of "with grant options parameter is not applicable" in the
sql-grant page should mentiont that you also can't do:
GRANT DEFAULT PRIVILEGES ON s.test2 TO test;
so it should be indicated the "TO rolename" is also not applicable.

== Other ==

I'm not really sure what's the use of GRANT DEFAULT PRIVILEGES... Is it for
backward-sanitizing existing databases?

I haven't tested performance at all, I thinks it doesn't make sense for
a patch
like this.

Don't know about the relationship with the spec, I suspect there's no such
thing in there. But the feature has been reworked to match what the
community
wanted and I think that the syntax is quite good and the use cases are
there.

Not tested on Windows, probably not important.

Having said all that, I'm moving the patch to "Waiting on author".

Best,
Jan

#47Petr Jelinek
pjmodos@pjmodos.net
In reply to: Jan Urbański (#46)
1 attachment(s)
Re: [PATCH] DefaultACLs

Jan Urbański napsal(a):

Hi,

here's a (late, sorry about that) review:

Thanks for the comprehensive review!

It's unified, not context, but that's trivial.

It's not, I have git configured to produce context diffs (see
http://wiki.postgresql.org/wiki/Working_with_Git#Context_diffs_with_Git ).

== Code ==

There's a few places where the following pattern is used:
if (!stmt->grantees)
whereas I think the project prefers:
stmt->grantees != NIL

Ok changed that.

I'm not sure if this pattern in src/backend/catalog/aclchk.c is the best
option:
if (rolenames == NIL)
rolenames = lappend(rolenames, makeString(pstrdup("")));
if (nspnames == NIL)
nspnames = lappend(nspnames, makeString(pstrdup("")));
Appending an empty string and then checking in strlen of the option
value is 0
is ugly.

I changed that, I had to change the way SetDefaultACLs is called a bit
anyway (to fix the problem with dependencies you mentioned below).

In SetDefaultACLs the OidIsValid(roleId) is not necessary, maybe better
put in
assert(oidIsValid). The caller always puts a valid rolename in there. Or
maybe
even better: make the caller pass an InvalidOid for the role if no is
specified. This way the handling arguments is more uniform between
SetDefaultACLs and ExecDefaultACLsStmt.

Same as above.

The logic in get_default_acl and pg_namespace_object_default_acl could be
improved, for instance get_default_acl checks if the result of the scan
is null
and if is, returns NULL. At the same time, the only calling function,
pg_namespace_object_default_acl checks the isNull flag instead of just
checking
if the result is NULL.

Also, pg_namespace_object_default_acl could just do without the isNull out
parameter and the same goes for get_default_acl. Just return NULL to
indicate
an invalid result and declare a isNull in get_default_acl locally to use
it in
heap_getattr. This also saves some lines in InsertPgClassTuple.

I agree, btw it actually does not save anything in InsertPgClassTuple,
but it does save few lines in ProcedureCreate.

Also, in InsertPgClassTuple change the order of the branches:
+   if (isNull)
+       nulls[Anum_pg_class_relacl - 1] = true;
+   else
+       values[Anum_pg_class_relacl - 1] = PointerGetDatum(relacl);
to have the same pattern as the next if statement.

Done.

In ExecDefaultACLsStmt this fragment:
else if (strcmp(defel->defname, "roles") == 0)
{
if (rolenames)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("conflicting or redundant options")));
drolenames = defel;
}
Should test if (drolenames), because currently it's possible to do:

Typo, fixed.

A comment in dependency.c function getDefaultACLDescription with
something like
"shouldn't get here" in the default: switch branch could be useful,
cf. getRelationDescription.

Ok.

In ExecGrantDefaults_Relation there's a hunk:
+       if (isNull)
+           elog(ERROR, "no DEFAULT PRIVILEGES for relation");
Maybe you could move it higher, no need to do other stuff if it's going
to fail
afterwards anyway.

Ok, I moved it just after owner check.

ExecGrantDefaults_Function and ExecGrantDefaults_Relation could maybe share
code? They look quite similar, although it might not be so easy to
factor out
common functionality.

They don't really, they operate on different tables and the only partly
shared part is the indexes and acl dependency update.

The "unrecognized GrantStmt.objtype: %d" error message needs better
wording I
think.

Well, that exact same message is in other two places in original code, I
just copy pasted it.

No code patch removes rows from pg_default_acls, so it might accumulate
cruft. Maybe a drop default privileges? Or maybe revoking all would delete
the row instead of setting it? It has the same meaning, I guess...

Now that I fixed DefaultACLs removal on role drop this is no longer true
(previously it was only dropped with schema a leftover from original
design).
Also revoking everything is not same as having no DefaultACLs, with no
DefaultACLs current behavior is used (owner gets all privs and public
might get something depending on object type).

== Compiling and installing ==

My gcc complains about

gram.y: In function ‘base_yyparse’:
gram.y:1128: warning: assignment from incompatible pointer type
gram.y:1135: warning: passing argument 1 of ‘lappend’ from incompatible
pointer type
gram.y:1135: warning: assignment from incompatible pointer type
gram.y:1136: warning: assignment from incompatible pointer type

Fixed.

Regression tests fail because of the username mismatch

! DETAIL:  drop cascades to default acls for role postgres on new
relation in namespace regressns
--- 951,957 ----
! DETAIL:  drop cascades to default acls for role wulczer on new
relation in namespace regressns

Removed that part from regression.

== Testing ==

The functionality worked as advertised, although I was able to do the
following:

postgres=# create role test login;
CREATE ROLE
postgres=# \c - test
psql (8.5devel)
You are now connected to database "postgres" as user "test".
postgres=> alter default privileges grant select on table to test;
ALTER DEFAULT PRIVILEGES
postgres=> \c - wulczer
psql (8.5devel)
You are now connected to database "postgres" as user "wulczer".
postgres=# drop role test;
DROP ROLE
postgres=# select * from pg_default_acls ;
defaclrole | defaclnamespace | defaclobjtype | defacllist
------------+-----------------+---------------+-----------------------
16384 | 0 | r | {16384=arwdDxt/16384}

The numeric defaclrole and defacllist don't look good.

Fixed, DefaultACLs for role are now dropped in DropRole.

While testing I also got a "unexpected object type: 1248" from
src/backend/catalog/pg_shdepend.c, but was unable to reproduce it.
This happened after I did a combination of DROP OWNED BY and REASSIGN
OWNED for
a role that has membership in other roles and that all had default
privileges
in a schema.

Yeah that was because I was adding ACL dependencies and wasn't removing
them in DROP OWNED BY. Fixed. This is why I had to change the way
SetDefaultACLs is called, so it could be called from DROP OWNED BY.

== Docs ==

Need to document that FOR USER also works (as a synonym for FOR ROLE) in the
synopsis of ALTER DEFAULT PRIVILEGES.

Added.

Add examples of usage of the FOR USER/FOR ROLE syntax and explain what
they do.

Added simple example.

In the ALTER DEFAULT PRIVILEGES synopsis there's a copy-paste-o because
it says
you can do:
ALTER DEFAULT PRIVILEGES IN SCHEMA s GRANT DEFAULT PRIVILEGES ON TABLE
TO test;

Eh, right, fixed.

The wording of "with grant options parameter is not applicable" in the
sql-grant page should mentiont that you also can't do:
GRANT DEFAULT PRIVILEGES ON s.test2 TO test;
so it should be indicated the "TO rolename" is also not applicable.

Done.

== Other ==

I'm not really sure what's the use of GRANT DEFAULT PRIVILEGES... Is it for
backward-sanitizing existing databases?

Yes (especially in combination with GRANT ON ALL if it gets in).

Not tested on Windows, probably not important.

I did test it on Windows ;)

Having said all that, I'm moving the patch to "Waiting on author".

I'll changed it back to "needs review" since I made quite a few changes
(per your review).

--
Regards
Petr Jelinek (PJMODOS)

Attachments:

defacl-2009-09-22.diff.gzapplication/x-tar; name=defacl-2009-09-22.diff.gzDownload
�d��Jdefacl-2009-09-22.diff�<kw�6���_�j�Tr$���k��������\InwO��C���5E�$��6����)R���&��������A�q�cV�O��Y�N`oF!�7�y��[^0���F+:�������j����Fc��
������W{{�666V�~V��W��H��0�n����n��c��8D���K �^�knF�����7�y-��\��,���'u�����-���G���{������}k�����"  O����������3�����fc�y����~y�!�nj��Ts+�$���39[��#�A�#&��p�������A�|~��3;�V���	��r!jV�-k$� ���q�-f~�e����b�����{�A�>����X3n�kr?]�\�M�;�7���������yTq�#;t���Y���� G-S�{8���-Y5v��!�l/<�$L�-�eG�������sAn,����~Z�E<u�rv�DsF�q�Q�����XR����`��;IcV�K�u��S�p#F���},q������|�3/�riM��M��?��Ih�1�����\��V������i��h?Y�=�U������o�9�c?^�6��������T�d���0����0
���f��2�Z6���4����Id�%g�4Xx�Z7��;�i$8(A���;`�Y�u���� Uw��o����u=�Mxl��snl�]����Dxu�����!Yl�A7�
�N��|T[9�l�������B~\�
��p$��q:<�\/j��'�`�x"��������)��
h����9(c�S��%L~��S�6-��.���K2D�Y;�����5����U!�2��0q(*B�1�{<�M�"B~2Zu,B��8���_�6�Ix��d��?7�g$���$�0��}�}���5�T��6 =�8�ox��Zl����'��#+3gF;�O�}�+��2���2BK�p��r}%�,�W4��Nw�#���|����� b����4�y�P�^�]'�+�/u+Hio4�wF��F����S(���eK[�Xo���_�F���"�:�������<1�{1��g��z]��&,�l������I��uu>��u~�����}azFQ55�����R/��,�on���f�x�bv����<��gnd��88B"����'k�Y(�HpDx5�0��*4��{�`&;�r�����pD���*cV�}�P��cLOm��s���KP�e���4'E"�{?�Gn$��T��P�X�R��i���d��y�}`W}���X�D���0c��iN���*�{�>���`����o[i�=�3�A8�q�\�a���k�m�#
����46���)d������Y�u1����������s<�W�'�2�����wuq,�z��v�}q�����26	T�r"���9p����)���~<oM�.����{u	�kH�����vy��y�v[m�;�[g����{9�����A�-q�o�!��!�O$����
������{���vp��Oa��\�G!�1-��gP��������vn���E��O	�WeN{���Q3���[�������_� |5�~�u��t�� �4b�d�T��vE	�I
���}�b�)��B�e3��4��6b�����QZ^�\�{P�������|fO!M�?c����iXBqo��7����v��Si�p�T�������ERSL��]XgL���(�<��"
�yX�����������HE����'^0�$��S���v��O�Q[`�� f����}�������u�`����J���YX+��V����o]'ww��D���/�I!QR��Lvj�y��lU
1�l1��9"�=���x
��^���y�������	��7��{�k���>��y�H��e�����
=�!��9���;\�R+��P��\\��[>�_R��a�6W����I��K�K�^�+tQ��N�*������a�R���M0���hb��rR�4�N"$��Cx��NkC�MY��=I�A<e�mnf�z����3�+�?����K���8���G�;i�2��m������n1�X�O$�,�
�@���A��ry����fIz��7I��������{�	.�,$���,�b��@��@Q��+������rf��gA��MM�i��*�m"���8�,�ql^��`k �x��p*��Cqn���|%\)��d�\�yEMe�e�������W2n�iY��!��W��d,AL'}�2O���G�N��/����q&�I�,��AC��)8��c�AW��h�A�"�C,v��������D"+jyj]�d�������� D����#�� ��,��I���J!���T�;n/@$S�S�.$��^������]�=u}���&f�Y���;��\�o0�!�t~)cFa�,|�%����f�5�D��y,�4'!�������2I#Q�Ld`6^3X��i�S�b�k@e?�L���|I�T.7��T��2�����6{���I1A��F�l3�5�,�
���r�L(A+��7�*��;:I��2q���������?�(�8�"�G*'V���(�b�j���')�C�oU��
���R��L�_�
��F�yXS�<+�s�5I�g���K��
�lf���c*�O�����@��1w:,�>�u�����F<<zJ�������z@��F��L�����r�Q3���\9����o,�;��F��3��5�L��
���������S�y#��Y��3��qr�y*��P����+26P�wR��V7���8.�(�����g*��
��`�~���c��<+�����~<���O���Nm7��/�~S*��H�Z:�Z�|���Jwhw�r����uz�W��"MT|�Z�{S{�gj�8Wmw��1�_�����<���kd�7��5G�	�*@MQ����T��\b��N��
'���}���y��s/3N�p^p�_����&E��aR���������}a��~b�����k�{;�+��`���������H�$�,u
Ir/�m��LSu�B$�2���:�������rXMV$DmW� x�%:H�7��pR�H��)�u}7v-/�n"������X��E�D?&��?��5o��K��T�z�~{I�O�k�RF-�-YV����UWh�f�����fwo����o\��{U�}�
����~L�TK�y`���<���y��s��=�c=A�.��^H��SXU�����B
���,��7FQmI���:������k�&�F��V��H�8f9���7SH@!��%�
t`��+Y���B��y�(u��"������/� �5�l>� ����ayx�z����:���I�{g�h{�
#:�����9���IlR��Z�aP<"���&���x�H�M�����u��0.+/iE�h,S!���+k���l-�~�{�:au�L�V������Z���jU6�{�m7{oF|��Y]�2����L��[
��L������y���s`�&�pR��z��,�������}�T��%�t���i�#�#�F[���o���9^O���V��m���F�y����������� K��np�k��?���{�}v���X�3��s��w��Q�v�Js��"��4v'�)3�3k�������d���4T7�P�}"�W="mX��)�`+�Bb�]W���"���}5B�tu�]o�=�n��W�r{��gY��Fc��~�?�]��A��j�<�y]�5v
���M�������)�����n�@��?yQ���BL���\�D!�'�!H�^n�^�N�r��j{�q�������}b.��
<����&p���6����g�PZ��������'^c��>��hdr�{��tv�Da�����,8��[���e��2���}�0!}��U��Q�k��[�T���!��!8�!���FWHV^����N�������d������@S�����8`�q
��X�t ��Zq^,� �Yq�/f54]%0P�1AI���7>����[6
n�=��B'��cS+
WG.���bz�����H<�����p�:>?i�xu���3������m��S��W��7�������NbK�#
q�*/�2l���	��O�=�$KJ��S�S������1��q��E�?YP��*��s���x����������ug4��}m�W��L����C��6n64D.�@��!v�&��b�!>�5��eM�p�,�x�r6��+�f�-�����3-� �uDb3�$�Z���������2��W;/k�[���~��C�������R*�Q*u����s�$n	�7�	��e����`�k�0P�E�D{�H+��d�����c�	4���R#�1��+�^R��%F"��	���d���/=
l;1��pR3^��@����"%r/�#T
 Xd@6x�,�P�4V��u���Q9(�"�J'jIe��7?�>T�
��eO+����qa"C'S4AR�=��Qe��
���h�R�C{6���:��En�XY���U��-�INE�4�DO�|�@S����j��T�[l��wO���?.��	���1��U�Q{���M������e��r�*ML��;Z�h�Hs�����B���|I�L��(o4�m���
�	���c��@���i�����z0�E@��U�)�b
�H�/��G����n��-�����#�.��e��fJF��E����W'WM������
2��/���N�s9����	<���+*�"�����ka3��������$��*�@�	n��}�������`%����d�.r���m^3�sG��-��P����T*I����pnZ?���3����!u���7���$P�h&�3��;k�]����(�4+jR��VxI�sgi��v|,�B���W�Gg����e��}J<�7��Fn�1xCH���
,�;r�����������sO�[7��d���L�G�}��J��i�
������a�s������Un�1���~�+e���<���=)��(���v��1�(^�.P����*�O[�:.z�����?m��'o�2/���|�9~Lq6<!�c��X�K�r/�r����a�]0���~��`����]�r9?����R6V��f}��tK���1��$��D�7 ���V�Lj��}9=�Cq
��@e�nR|�LV���E�@J���<�
�s�� h������&���G���~������@�%�p����Y���������s�m�j���S�5+���Yffqt��4�a�����Ii(�I����E������g����9������v�a~�t�]B*-��=n3b��������a��R�Fp6�X���4�i�8 6#��=�
mO����H�8H�D�z��W�����T��TY��T�?���V��p��ZY�Z5�����L�v&�F��G����xr���A���l9l1~v��cAq+cL�� G���!�}f'�)�~L�5������"�v�7c���2?�k��"�N��P�,�k|��ZM:�/�Z�	� <���$��p���0T;j,�5����r����*�H��J� �/��4��k6�S��:`�o7��:�Nd���?r�
$XtOd�	s���W���R������|1������i���!���D���!��;�������m�������e#��������Y�j
RnYm�,@�X*7�l��[�)98�QTi�� �^��w���k��Hd��B:d�g����u�=^�������6�����n�?H����6R��@�+��c
�W�9P9Y������5����7Ni%m��h�hF�r
m,����RJ��L140��
1D}��2^f��?Z��g��"_�e�
�J�tD���,Jq���0XL���������y��74����N
!��d#�S�J���l�R�j�<6����4������^�:�q_����Y���������m3�,H|>�@%<�r��Q������!���_�y�)�gh���o�����)x�a���e���b��x��O��k�X�b����/j�]����BV�u�����m����8���E����nd�f'�`�N0�'�;��#����d�������]�-�$���8P���Uu���tH4=���}�?�w��������?S���!X����g����e&n�����������J�p��B3���[���v���o��%�K����Z��F3e��\��a��C���0I����\g��mdP��Jc.z%2���Ue����U,������2�8En���#pE�����%(�0��f"�~+R��{�):}>�%6ZN��'����_|��{����',
��}��{�s���j�*9b�l��+�������p'���8�K����s���]R^Sb���tY��a�g9���*��&��l����&1�����/D�������_�������S��Xo������l4��SbP�7�	�����x�hN�����I�I�R�D��#*�	�_�g������U2��>���F��t�%�<f����|y<��D=B����R��q6�?-K�5Bc���bLds
��`�KM���m,�H�R2(�
���m��~�~�\��7H����-�M$r��d}0�/I�����G���q�O;��B�B���e?]bk/���d$H�v%g���H��h���5�V��t~sWR��l����$����?���P���y�&����O�h�� `T~%pGx��Q�@���e�VUj��$�WI<���{qmd��I~�r/.c�}�v���K-J�8�.�J�����J�����ea�G��Z[V��I&��4��
�����W��Lxu����".��k`�K��F���|"K��Q�"4���R��Nq�~SUn����F� ��{��]���0����mt>�TZ2��K����p>
����CQ[���pa�am*�F�u4�N
w�*���(��%+���[��1�F�����\FB�><!��1�~|����1�Wxg���t�/� e����%���&3��|���{������>D��}�������sf2�>n|�������<��[��J����-�o�h�����|nc�����J~/�D�_��yd	@�P��wS���������+�+}}�o��7S��������;ye$Gf1,�����H;9nK��f}�����r�v/��h�W�-�#�x�n(t2t>E���=}��[e�dcc�����)o�h��������</��>^S�[_��'��i��7�/�b�"a�n71s&h����,�8�ui_a9���?i�!B?�����g�5z��BZ69��6��X�:�&-�����YrA�����-��C
?��������U7��=�#(�����h�?���|�������w���������a��z���+�f5��U���jp��~�R�
��zyrb��DNf����4���n��?�������?�g�@�})���~=)��~[�s���z���wB���������g�����*�?9����v���Q7�bGZ���)~�����+���nR�:"C�/�\9��N'�v�u����jD�v�����^���gO�������y��a��K�	�^ �V��G/6��SwT+�����Qv�7�����b�o^�����������YIy�36dg���z�H�8���5�!����!��-��6R��N|�� �F����\�i���ZwJ�/�~���)%�c�T6��!�=Q_I������|L7H��6a�c��1S���s�J���TQ
�����}����%SJ�nx���6�U\�|��^�@2�Jr�X[%��yY���e�T�?�b��M����r�����=>5#�����u��gR4�����j2��_�-�W��Bj��M���� JIo=�<��-M_[N�����#��������lc'����'����W$��f���k�Z�~8<>���:�T�N��yJ�u���`��c�JF�"��Ux��";����2u���F�ogi0Ho�<��PX��O���������:^1��f��cq�����YB<h*��0h���-��\�����W���x������Q�B��������|��Mm����L���bpx�F�����`L
���'E,�\������N���PH�E��������V~�5��M�]V�`/C��nW����Y������-f�Y��!���W)l����p����
����bA��%W��>Y�1*x�q��aB��?EP�W�������u�{�8�#!�?Hs�E�����u��<N���d�f���<o����f���W~����`�����`�it���	l���'����s���6 K'��=���5�P��6t1t��������n���S+9@�y�����t�O����{!��xW���k����\��p�N�5�(|�b�<�;�\b
��������s���QA�E�]D����m����8m��a��0j'B��I+�u+V\���D�z��t(����a\[6h�.�*��!`B-Mi\,��,'8���,���L���~�{.��y��o�;��2�k�
����V�J�5���1�{���~���K�3�rl���q��2=�?�a;�������];�[����[�g0v|�����l\���0��]"A���#AE�{��m��/'x!�L�R��q(��D��YN!���I&�c��P���!l���P�q=]�6��w��Y=+a*����	�N�z}8t���y���I����E���5�o[M���]VZ�
|Z��"�h�I/.OO/�����`�b�����`�
��<���o�O����9u"�����/p> [F��,V�zp/w�V`g������.'c���'h|��s������t13D��D���k�ZwX.���z��,�����i�:���u��q����-u�u�v�B�dX�����m�mW�v;+f�`E���_���Z��E�
c"�Sd������ �D�D�q��w{�����U�U�5��l�,��^�B�~p����/\EA��^"������'����h�9�2'�{,�P������^���C�����������_w%�(eQ�+m�h���FyK}Lqu�����)F��L���W�:7Cv���b���L�����b���B��ZD��T�T�L�������������[�O/���d.F��9}z�W�qh���7�����c�o&����M��0������}�8�n:�f����Z�7�j�Q�`[�w��cA��W�y!�xH)��3��|a�����?ll��$��I��t��XG��p�+����"�w�K�v:v>n15��&��$H�*�F�Xq�k�V
��UIF�������.���_H�#,�)pM=�-t[%��?�"�)��4
i&�w�����c%�&@��+��mE�V�j�U�@Q�e_|}���xF��N��M��'��}k+��}�ST��`�~�U��}%f�!���B��u�-�P�������>�~��d�!���Ql����1���d���5������`���
��o
JOR������|#�[��1���P�r|��_^�3���R�lZ&1���F��5[)�L��~~�=Q�q=  P`d��$��)z��r�o9�P�������������^5p�Ze/6H��Y�cM��+
��u����*z@T[v��V��������9��@�;�.����F�9<2f|�BD��5�c��O
9L|6G�����Xh����
�Yc���-���������79����������#��/��mp���'���)�J)��Jk���j-����P
"�g�?d����Q��5�?ow5����)2@�:�=_,�����xLg���e^�As���L�bf�8Z��m�sku�v#K���p}��<���~�z�*�8
m}i�+E��.��,uNX���0	.��j5�L��4�D�Wp#�w9������/VO��}�l��zy��T��p����V��"�Y���<�J��s;/?G_��FT����r��CA��1^~�Xp0�����.Qy���	i������4-��($r:N��B�^����0��zq�����#�K���������)��V(� �A����}0I����p(�J��l�=-G�<�J��J��w�ko��)���%��s�!��F'j5-!Q�Y�#�p��x�_m15f�"#^�e1
��tfW���]�lM���R���1����/��j����*�(=�<�3������z��1������r�Y���pYT�@�T���@���hE'��^8���l����N��	�#�_���i��p�7�}�,G<_g	�-����������b)��	����x�$h���}D(���x�1���1��H����T9�)�aI�����FH���E����w�@�8��a����Hx,2�RU?�~�bc� ��=NiWC�i�-�95���7��Q��_���;&Y��������xL������-[Z[3�HX-H���H'�����|d�c��}�s�rL^�����7������~8��y�b��5��;�m_�����R��.��1�)p�xJ�\3�6��2c�>�)+�M��l������R��5`����>�/L�Ud���ZJa�I.�o3���^����
p\�ryX����+|Y����M��X�D5G��!I�^~^,�^��
CF�q~�W�����jK�A-�}5����~e����t1s������om��iq����b� ��J���`�)�^:���QnMF��
AC������5�?����z%�+;��U���sz���.0�V�p��&���4f�m(�kW���G�
��=h���v<h�c^�_`k�g.k�b�g�
W��r=�$� ?��:��,�����o����P�z7j6,��f�5[F������~l���y����-�%���(�k�{���5����F����:>���j1���������$��]#b�Hf�b\iT�u`F��N+^��F�eH�vA���:S����L��3`�T^�!3+7�"]��%�'�������S���y��k�x>����\�`��<�V����T�X����ia5��E����f��0��3�:��!	,>N���^� �nDm�����:
�2����f��mGG�S�����S@LzRi�-�R"k�����=#�%�H�n+F��A)��oj�S�Z���c�y���*S<�y3�U��,
e=�[\4gb�9���5E�gS�T"��e�U8R�D���;�3���b���(a�BlN,�-XTe�eGq�bP$=i������ j�S����*�\�8�������t9 �l��A���D��)��ju����
�x�I�-z��'���@cA\��V�o8>Rrf�a)�K+�)�.F�"�z)����I�gO~@!����$%�)i�B_r��C{U�������PO�@�LpR9��������
��>��o|Zr�?��a��������{[4�@x����~E�����Q�'\ao����/�����%�<����_x�*$�'���E��/�s{p=�%�R<�EB��^�t�l�:/����d����^������&��2�J��U����������{=�4�7V,2
�oU�
��O���W�&�B��RG�a��8���n�h��P�/�G���:bh�4���u+H���c��}��#�����H�#�#~���=�5���M�%S���7�zk���d*C�hQ�@�P�q3Z�	i�����	�|�~�H��q)N)�A�Va�9������c�9<�2��1�����=`�2+���������<?s(q���
�R��1��Is(n�����R��1-
4LY�F��j�����T�0��$��*�H*��[�����#��Q���5H��6Euu&	���G��H�>N������&���	'���EYT��������L�T�����m=U.$��#N���7s�q���M	��$��m�f#�$�(��c@���%�[������/��a:� yO����dI�:Y�����)����^��G��|���kSK��g��,+��!hjv���1�B4������k�����q����'����[�������1�
�}e����� 4���1����1A{�������:�1@�4���jK�%v4��C�5�m�n���
�x�*���hMJn�)9UP���(0�;]rD���M������Y�l����0���V:q|��%P�EZ���JE�dXkU.�r��W���&�@������W���]|�8ZX�?�"�����Q�NZ��4�#���s��82x��;�\�^�[na���im����nC���X���&���T���y�FHgu/p>	���N���?��;�B�a	�j]��&��tX�C������������{������)2o����&��NF��;�� U�iI�(��2�	&w����,l�}�V�x�AT���*�#~��[�c���w�g'G=iwO�F�����*iXI����x����A�������[mE�����u�K.�j+���}#��Nj���d'-��o���`��
gw��0����nH�[B!f%��l���T�����8N������^��1�z�e���:����q#n�3�Lz�4�I���z��l���[��%�@��>����jv�*�I�<����n��$��O��g�U�R
�)��I�Z�A�iH���W}<�ON1�Ao���wtP�f�L����������Hk7t�Z�-�(lH���p^m(h'��P�jJ;�mv�z�e��z�]��v�Gq>�wY����>�17����<���_���F�mN05K:93�`�m����Y�wi��"�Q;�ry�jU����[�!����(uo���������G��c�BE&����a���+�s�o�H����w�{g�����9���)�h���Z�&�����Y��(�n�r��!/&|���������n�Y��6Y������#�}��n��>4{w������%<4��w��q�5�5����\�����������W�����1�%���s&cU�:y�Q����"4����pO@���%�F�I���nYO����`W6�&�w^���-�V�LV������ ���G��
/3�|�1��\}?�`�����Ia1Y�����jD�Xh
�f|k5s�Y��o1��F�������d�*^���\4Q�A|q<��j���d2���Wb:�W4[�������n���7��c���>t�)*\�_�H�B�O�Lf�W���?���i�W�����?�q<g�Hn�. ��a�,���E��=N���:�B�RO�xR��9�!������e�j�c}�e	9���2

r��p	 T�������P�T=.�t����w:���P�m��Vj���j�����m����E''����-���'��1������[b�L�)P�����e�����?�OK�:�#���oC�@_�rOnP��RsbM��W}$�`D����/���|�.����U��C�������tAA�el�'�������r�p�_�Zp�Zm����u(�Z�0�6jPe���j~!�)���Z����i�p�)Q�C�X$�����85"��*��V�v@�*�IV,�^%J�����U��E��d7j>���%�5�5����B�
��4���~--�(eP���w�/�/�i�.�W7u���(��!�o�h�dX�%_[���������z}���@��V#����+~UG��s�{wz����K���.hB ��"�qx<8���{��(&cA�4"B�]���'|L�'/_<�~Z�v!�{srF��MF&k�5��x�:�n�t����z{�w|nK�O�Eq�'B�z��!g�����'��/�#����j��L�)�rROk��
M��ce�"+,���V��(�������g�O~��M������T��34P����������*��4~A8�@��=9%�9�=P���f�����je-�K\�W�Q���`��
�A���!�\�7���<!�\�.��A<4B:c�Y������JS,����E���*�m���93����`U�f����m����)���E�'9�a�"���9����m�w���&I��]�7{G��5�[��Y���e� �����K7]��r��R
��x����J������^���k�G�5A����Cg+y�I~�\���^e����VJ�wW�-��k���)L�������,�	���]i��PN9a�{���������j�oT�^V������U�����9a����h�������ma6���������������$-�Ubx�A9^f�z}�v����g�rx	���42`�������d=��lwND��)��x���8�_���0Pj�z#��p��F��5(W�G�������;X����	�
&����_�xI��>v�jz�v�nk\.��f�3e�7���-�}�#-�dJI4�� �������v�F�bg������$i���*��z��E�PR6�z�6���pkC�C�1�4�XV`%�d�1�i���3�z�Ll�c�@Ac��7"+����*��c��O�20��z����`>���2)�q�U`��_��n9������]�q�U��=�T�Z�V+c����j�3���z5�[������������l�����Y�R�/��2�}�{��D�*�MZ�}���p�����{n3�"�
p4ae�a�O��,�He�RD������d�!K��`J�����v��ynt�v�k��IL�&~���gZe)����������=e�2��
����o�d��i ��S���!���b�&�������y����=X�Yo���^������_KD2.����:�f���@����?xg4�HT��HpJg'W�X{�WiuM��O�$�ky:d	G~��1)C��J������p��c:�5_�U�H9%��?5�V��&�'{����L��Q�+�sm���^�5Y�P�	�����l�]~P��]�4W����z�ik��2���	/��P�Wb��^�I�6����}��p���t�^�?<:�����k]�����v�+�5*��D�s�^�{�L�
�]�y����;������S�b�C=���
T>b�&�
I����_L'S�I�~� O�|���X3��������(]l�Z�����<Y^.�������_d�4�da���p��������
*���x6�eZN>�3(P2���#��\�����������L~�8�P���e��>�l���{����������|c,���{�f������Y��	~w����NF����|��!*�e7�QHC5d����w���^r�X)'��F��/s6�/�����?^����b��F�G���
���#O=�0!�%
����w��/�k�{h�a�}� �����Y'V�J����'��:D���K�����!
:�Q�������U�2�(z�i��_�zx�q�@���J������p��Q����4�S+'�F�������d9��N��f�]}��4@7�~�"u��S�B���2�������U<�4�3�Sd�����G�A��z�����|2I��|R��c��/DS��N�yz��������|o���lLp�����-������w!����M�����Y�Cs�-Z����f��S�QE������MG��K)l�j�b�:<������^{}o�Km�.��kI�[��z�+%�2F��l�,�7�T�Z��
m�<BJ���f�x���F��p�������$#aa���|�!�_6Gi'k(|�xF��X�H|����Mv;j�"t�pGe#��K��W����v@�/��DG����*�e�
�7�:u�k��:q�\nU��Q;�����I��n}�C����K���;Q�vF�c��$��l�u�
W)�P��������Jr�����{�'}���Y�'��z=��}Q"Y�\���l��z�e��m(�W���+x/r�*��������,@��|F�����Ez9,,H��9~�2���N�����{g������f�!x�J�0n^T��r�����q ^�Y��i���$�6 &	S�U^�e+!��rJr����	�a�����2�&���&����'@ZMq��\g��M�<gn�������U�O)�������e��S,������'����A�0��y�U�'��@����U��F���8��k�����dT�>gv���k�OK����Vk�J9(h�
�xeC�����k���yQ���B���<�m��X���'�|�d�L�d ���!a��B�I*l���@������Rf���d
~������]���W��v� j�c�����3D����CZ�Jm>�fUm���v:V1BT�36�U�_��X\��5�yR=�3b��[�@[#������f:�b�'H2����4x�:4�|���%]�imnx8�)�A[�����b^Y����I����$!�����[	��g
��C��,��������x����u,Q��!��lH���h��?[��*�y�?����p�\=��v-�c0�8��Z������,��:,f*���U��0���t�.������!w��lN����y7�}
����C�n'oN��'t��h��K�+��8�i�;��-(�&�f��9l:5G �H��d�EL�JF-�IZd|�|H�B	k����x�Zu��E������I�4�Y-cm�V�L����}�w���y��L5��]=�eg��B���=7am���@�}���'2�EA�vn�w��
S�4� TtS/��A��0��H�8l} �v��������V|���{b}��e|�;�8Kx\O�P�a�8N�{�1N����������<'�����\�3K���6�$Y|b���WQ:>7<������j��g-
�OY�
�������T��^<P��E�����G�;j5[�����4����Z�6q�������z@�
������>a��H<�iu���c�,�6���}���}���u���R�@��/>,�8!g�O����bh��7A]�q�Z�j��D9M���wt�����]��h��f��e��a`>�|
O��C1�^	�1b�|���������;��2��T7�$��
)��-��.$)�^�lt>c��z� ��>Q���K\I�aU7����b��"���e��x���E�%������"�2����cg�={��������~�����.��Xf|2�������?��v���rbhs�9�Z����������H0�0��Q����������`�[��Jy�
�5�����o{�r&�:w�of�Q�������P2�B�� ���W��|N��79ja��S[���X���_M�W��&	o0�)��z��JpOO�g����>�@@�*�� ��q>+�yC�F�R%$&���6<��o#u���!Z���L������p@z�r	�(6��1
)@sL�����m�z�2m7��`m�Fk$��l*�az#�x?�a��:vnH#�����p}ygS{���.�:K^K�����r?M��|�u�
�3#�Q�:��_{������N^�pj���6���i�N��*Y����
�>��!���O~d��&QQ$F��%1T�8���S�0�LO�����}������N�����������n�/��4MTw�	��B$����@GpYo�b��s����\s��<���>z-\�\�s�:[`��~/BC;?L�4�P�9��r���k��E`�.V�o@������RO%�c]\���X��_�X�` �42�"s�`A�,��H���4��D�$�JS�2\�(��,�(�k����l���d�P2�7���v�Z�vk�r|Q���@��5��)�N�:d��1"!q�fI�Iep���Z �����/U	�'X�uy��~!R�f���OR�z�j�C4TMF�Vo
�j�U�:�;�!.�w�]�cO����7���#��3�w���p���6�Xh`c�3�<y�Y����(Yg|-�E.k������ 	�] ^��!�5X����S�b����@,m�-��H�V�w���7��q�5��^���8W7�2}�#z������v�(h��K�����������BZ�l���?tZ
�%�+W�����`���]�����R���b��5��\�� �,�-�[���/�$��~!Q�OV�D��{��D_,�b��t}?CV(���"���N
#48Petr Jelinek
pjmodos@pjmodos.net
In reply to: Petr Jelinek (#47)
1 attachment(s)
Re: [PATCH] DefaultACLs

I made some more small adjustments - mainly renaming stuff after Tom's
comment on anonymous code blocks patch and removed one unused shared
dependency.

--
Regards
Petr Jelinek (PJMODOS)

Attachments:

defacl-2009-09-23.diff.gzapplication/x-tar; name=defacl-2009-09-23.diff.gzDownload
#49Jan Urbański
wulczer@wulczer.org
In reply to: Petr Jelinek (#48)
Re: [PATCH] DefaultACLs

Petr Jelinek wrote:

I made some more small adjustments - mainly renaming stuff after Tom's
comment on anonymous code blocks patch and removed one unused shared
dependency.

Hi,

the patch still has some issues with dependency handling:

postgres=# create role test;
CREATE ROLE
postgres=# create role test2;
CREATE ROLE
postgres=# create schema s;
CREATE SCHEMA
postgres=# alter default privileges in schema s for user test2 grant
insert on table to test;
ALTER DEFAULT PRIVILEGES
postgres=# drop role test2;
DROP ROLE
postgres=# drop schema s;
ERROR: could not find tuple for default acls 16387
postgres=#

At this moment pg_default_acls is empty and schema s is undroppable... I
got an unexpected server exit after that once, when I executed \ds from
psql, but unfortunately don't have a backtrace (forgot to ulimit -c
unlimited). The next time I tried to provoke that backend crash I
failed: after the "ERROR: could not find tuple for default acls 16387"
I'm only stuck with an undroppable schema, but the rest of the system
works normally.

Apart from that all my complains from the previous review seem to be
addressed, except for the tab completion... Not sure if it's mandatory
for commit, but it sure would be useful.

Marking as "Waiting on Author".

Cheers,
Jan

#50Petr Jelinek
pjmodos@pjmodos.net
In reply to: Jan Urbański (#49)
1 attachment(s)
Re: [PATCH] DefaultACLs

Jan Urbański napsal(a):

Petr Jelinek wrote:

I made some more small adjustments - mainly renaming stuff after Tom's
comment on anonymous code blocks patch and removed one unused shared
dependency.

Hi,

the patch still has some issues with dependency handling:

postgres=# create role test;
CREATE ROLE
postgres=# create role test2;
CREATE ROLE
postgres=# create schema s;
CREATE SCHEMA
postgres=# alter default privileges in schema s for user test2 grant
insert on table to test;
ALTER DEFAULT PRIVILEGES
postgres=# drop role test2;
DROP ROLE
postgres=# drop schema s;
ERROR: could not find tuple for default acls 16387
postgres=#

Fixed and added regression test for dependency handling.

--
Regards
Petr Jelinek (PJMODOS)

Attachments:

defacl-2009-09-24.diff.gzapplication/x-tar; name=defacl-2009-09-24.diff.gzDownload
��U�Jdefacl-2009-09-24.diff�<kw�H�����ag2�~�5������;�x��=����ZH�$L����~��j		l'���sw3	�GuUu�[-��Y�>qcfm;�����d�m�Vly�$j���|���l�`��=h4����8p��������'[[[ka?�����o��G�w_�������h�;x	��Oc������1��?�����������������e{Q���f��?nFq��c�����d(q O���b<���'1g�_��7������Y���"�C���p���VhI0�)gr%��c�A�#&��tX������A�|����r+�F��E
��ZUr�	����cvx��e�'a��3;�����b�O���'���~`M����X��-~g�w���+>�!�m�8�����
������
����x��n=��Uc�{�12�����J�D{j�(;\��_��<�������/����Z�S�)g�N4g��7hY
��� ���)����4fEQ`�$XK7�B�1^�����������^y��+8}275��K'���`bR��s��Z�&���g���8b!�,THV!�Sc�._Vs�������q|-�������@�O��\��(~�`
�6{��������E�I��VL"#,9����s����&_�y`O#�1@	�?�����nj����zo�1��e���<6������%�C>NT&���[l�t	�e\���Aur����Hf3��MMg��������g@�F#�������zQc�<��E�9@���g��E4�H!Nn@�m�A#�"�,���1-9��!0h���1�B~x�2@���h�o�[������}���qDN��:(*B�1�{<�M�"B~2�:
!�M�N(���$�$<=W�d��?7�c$���$�0��}�]���5�T�C��|��<��n�L�u_��S�����3����>���s�X��|�%
V�E0�\_I|
��<�����H�o�5��03},1��RL�<4��Ws7I��K��	R�������wG�Q,������Iy��N"�����2CB���"�:��K�[xL������3@W�.mO!�F���[�A���}���.�:�v���v_�F=�(���K�fea���n��7�S��
3��
<1;~��N��37�]#�u������p�J9\=L_�
E�M��M �]�,�����pD���*cV�}�T5X�c,Om��s���(���e3�I@������IT����X�-;�]�?�U��f�uFD�s�Z�~�1P��4'��m
��{���\���O�7�4�����u/�<F.��|��j�i�c
����44�����O�A����u1��������X���������v
]�cpu}q"���g����	p�;����$P����C]���3�R�zl����6�=��-������H�����vy�c�s��6�-��3�I���t`m@�}�kF\�[�H�f���#����F>}%����>������6���2��r�5_H��U����m�5����w)���l��U���E�L��I��:��� �K����O�_�D�M#6AL&z�A��l����0����*����Q1/�[v0����L���,����
�����������3{
!h��wG�
a��1���/b�������!�T�������ERSL�nD�3&p�W�x�ii�<�I�pn4��;v�/9R�h�!5����,I�r��b��n��t��b��`�]�H[-��-����T�j������
�U��t���"DfN��&�DIb!8���|1�\;��B��b�Ks�DB{ $!A�����y���!Li�P�H������J����<U������jrJ��������Wp�{\�R�jv�Zn.�?������$��F����~��l�����c�����@X�R�@h��T��I��}�&XBfi4!ai9�A�{'�	�����@3��$�F��,Y{���x�����R�0b
�g"W�<5�-����iq���vw�HeN���u��;G�b�?�N�H0Y�[��@��A��ry����fIz��7I��������{�	.�-$���,�b��@��HQ��k������rf��gA��-M�i��*�m"���8�,�ql^��`k ����p*��Cqn���|%X)��d�\�yEMe�e�������W2�|�
���r��N�+px2� ���>�������'e��@�g��8���X�S����`z�S�1� �+um��E� D�!;��pDh����
�BO�%H&�)��l��	B$�X0B��
�{�bO�$|=b
����2���D2�?�! ����_}����d����:w5�Z^��b�Xrq
����0k����U\����]D���O��M�HYK��@t�\�`�Vb���$�D�3���x�`���
����	�1*��f�L�+��r�����B��I�'�$���0�N��	�4�U��q�yf�U��L,��t`B	Z����qU�/���Hb����\Wdg������ ��X!���P�M��V�2��4��u�_*���kHMF.0�n]d7�:���aM���9�Q#��I*?���+E8�V�d3�{���`<�
����*�c�tTv}H�����xx��|��-'ou��1
��������r�Q3�\x����C�W����~��j����
�L�����%
�}�so���J#��C?f�1����T����k}
Wdl��o��o�n��_s\ A>�H%�}�T���;�p�*�W��yV�/�Y���������~�/_S*��H�Z:����
���m���;J��O��&=�+lY�&*��
-<xU{~`j�8Wmw��1W���K�yq����o4��:U���P�y�T��\b��N��
'���N}w��y��s/3O�p^p�_����&E��aR���������}a��ya����/k�{�+��`���������H�$�,u
Ir/�6�}L���V!�U�Y�W��Q�wmq9�&+��+��!x�%:L�7��pR�H��)�u}7v-/�n"������X��E�D?$��?��7'����rN�n=h������5y)#-�-YV����UWh�f�!����f�������~y����"T%����������Ay"��y��/��/{�����H�z!�nOaU_���.)H@�{n����E��7_�P�JhVLG��M[��q�ZDX�av0G��a0�����Bz
�},�pg�3�\�J��F
%NT�A������ �?�j�nv�����������
�G�Q�9�����<�'g��A��q+���G���p��@$L�I�'k1K�A���"������q"I@6F��S��R��r���I�q�L9���������8���A��0��31�[_�wb�jUF��U����^���e�~�7]�1�����y��M�n�
��4}Y��lMn������Y����IO��.���K.�ni���G�G���D��~c�p���Y��j�/v����h8�v��{�4���v�J����_�����W'}v���X�3��s���8��p�V��_Ex�i�NSf>g�\79�����P#��� P���r��x�#�p��E|\r���<# ��u;,2)�L�W3�JWWn��������]�z�-����5�����g�o�^
�x{��<�{Y�7v
���M�������)����7���7�`���!�)�����+�(���B8$�q�wk�_&�{�W{����E�z�v��>���H��@X|�k��6����g�PZ��������'^c��>��hdr���M�����\=,ZW-�Y`(��F
�4?f���|�4!}��u��Q�k���T���!��!8�!���fWHV^���2�������Q���f{x�M=
3~���
�[c= @����k�qx���,f������t��@�@��h�/.x���)X�i�$�$�@:�M�(X�����x�����x��;���u�=m�x}���
3������m��S������LGvvj��:�-��4���@��z���~l6&cG|j����q�A����E�0�9��~��m������P���]t�������W�@��*�1!�!��P�(X
����;�y�kc�r.V��]�t��q���!�p�]����<�a�����,��a�t(kW��;��y,_�7+�he/Ed��i������_h��B[�bw���dK_�=�����{�Q�P���~�0�T~�T���-]�,<����A$|�H�Z�a	_q���g��R�9�b.�+��D���%�Ah�7O8$,�l��+������T{I����0��N���.V8
�����60��-'��u7g�	[
P����C��a�j������� �E��&�T�l��}(�	�V�b\�D-�������jU�� ���dgMj��
���)� U����j��2o��QL@4diB{6���:��En�XYV��U�
;��\��i���R	�4����U��&K�
��N`�I��=���b�����U�q��*e��=pN�~{��Y�z8Is�Z���d��/�?�����{
�te��<-8_�>Sz��{�y�a���z������~|�gf�4WL��V=�����2S��:��2`����&c�t�� �r(\��;��exX\�����l*�jdJ8{�������X�6<����g�KW��\���������Q'�e��s�e��Eo�FG��*�j�P��K0�`�f83��>X��wv�;����<���s���x��JB��7q�J�(i����M�g2vK��`���A9��uCb���R�W���
�gp��tg-�+R�����b
@mBJ�
/)�v�� .m��")��(pU�F|�	Y�|�h���Sz3	k���7T�T������#z?�w�d0<�u��\�{-����oc3Q
LW�n��?N�=*lmu�����O��.��r�0������y�����z��=���T�1��2�#���\�
�DP
�4�!�}T'��DP
�4��N�2[���a��?�8�����py,������vI�����Yy���&��/0@�e)����)+ec�j��K���|3��A
�O�z���i%�$l��W�6a?��aq�T�m�&��d�[^��40��'Sa�a��p�$��f��I��2��Yzm�_%dvg,>g�����o!b�'k�%bq���\DxG-��+���}
�Jn���Y���!]�7M~���'mR
�vo*����Q�v�|`�������C���w��"C��"�yW��J���e������k}�5L�P���.�f�����&=���f�}y�����[���4���H���8|�1{���R��Rei;S}h��+[UK�B�!�Y�Z5�����#�d;�
h����E���|<O���� jCG
��?F��a�8��1&
Fn���\���>3����a?���C���JkO;������L�w�F����Ss.���r�����I��E��@ ��
���i�~E8jm^��5���d�H��B6���4Rj�R"��K"��#��
�(E�X��;�CqU�������O��|	�c�\9��5(b��Tk����<_�s��}v�9��A�u�*rmaHy�f���"s+�C[�{����r��n5���'5!3@�����[V�%P5���3[%�VvJNNoU�`h7n��{|�����V$�tf!'����Ik������7��e���>|`��A��$�jgL)_~ ���i�1��[�t����+e&/EcM�Q���)m���X���^Y����@��%���}C���S�,��l���!�y6��!�U]�P�0���NGD�=��w�1���d�om�Q^������|C�Dw�h��$qP�"(P��E��`��HW���Q7�<�y��C����������7FH���Q�:UI?w��c#��G�i��\�{�.�b�#��2��o�d
�Z���[��'��~�|�9f��a��{"^&�S2�Z�V��A�c{���FA��E|�~^���s�t�N���m�Y��8��,��������z?�N�!�8�``'����#�h���������zwu�d��l�'�@���V�����f������{T\�{���?S���!X���g���\27b�XO��w/��c\�[8��t�Jy���r�]�8�����(\��l-�_�����k	.��0s��K�R��]J�_�3X��2(xN������G���������yw1�P���-�X�b����;�V���L���E��y�>�@��g��F�	���d��{���>�{����E��{��>�{��]QM�@%G�������B���0�pg��e��@wwa�p���K��cJlx0�.�_#:��,��;[����d����=�����r����"����`���+�����S��Xo������l4��SbP�7�	�������hN{����I�I�R�D��#*�	�_��������U2��>���F��t�e�<f���j�<��E�!]���L�~�8���e�����p1&�9�K�S0��&v���6�i�-)�n�X�-��~v~�~�\��7H����-�M$r����0�/I�����G���q�O;��B�B���e?]bk/���d$H�v-g���H��h���5�V��do~{_V��l����,����?��P����x�&����O�h��0*��#<x�(|��������Y+�*�����$�]����1�D��$�K���1�>@kqi���w�X	K��
��j�p���������#L^�-��j��$��l�\��w5�&���� �@@��t�����X�2����F"�6���j��������T��S��T��&og��7H���Hl�����Dfg�!���`\KT���6MP#������;AylF���|�7���x}�"�H��X.#��o(O�#m��jL���/�K:�Y�H���^��A���>������x��h7�f��#��06RbnU!�|�����x.�Zk�`<F	��>���jD����fJ����Tsk6D��
hs���<+-�&�6`����>���	l����C_�����>���t�1�����x:���Y�05��h�=���2
{����Z}}����!���?'���y���@V��i'��=n��>t��2��1r}odAi�H��M�+�%�r�xq>����%��k
s�`A3/���:r�L,���D���nb�L���;Y�q
%���r��i�B?�R��s��
����Z6y��6��X��&-�����Y"8���{�r��(�}"Eo)��/��m(�z��F��R���~:Z-c� ��k���O8��_<���x�4����0(�*�'�,�_�5�$@�j��z���,��*L�����;�B0���R�t/���S]��O��A;3�}���J��8��p�a9JLj���XO������<�^04�b�d�V����
.�-K������U@%�d7���<�������A�_s?`�N�O*q�K��f����G�����*�?>����w��T��mi�2\,����������;�W��H!6r�������5,'�f��;U���a/=<S�{��,���f�I�;�/��Tz�hdb�-���WO��N�~�
��9^|�G���9>}Y#�+�^�B�����pgl�N�!Fg���#�P�����Xe��zz>�Thl	i�_�ZdK����w3�z�(��L�9�v���P�X�����
�#�����������y�lO�SO���v��J2C'�����J���g*U���)g���{\��
������<��|�nS%Z����g
}��ta 1���=6��B~Y.�H�Rt*���<�@�t,I�*7�9PIF����,����%Lw���L�������Qf�����j7P���}�)����D)��'������k��I[��s������/����b��u���$��Lp���Q�G�>�YG�j�	w0Om����LQa�a�Td���_dI��U����(�����%��Q(
��I��y�=37�Y�o�Y�lrHN�Z�2>IV�M�QbC�z"��e����b?���R���1�xc���bB*�@V�$[\(
��d�j���f
�����;*�P:8��yj���/�8)`�����]uB������S�N�2����Z_v
x>C�s�U ��H"����Gz�n�43e�y���nst ;�E[�:��:*mjo���r0�X�~9���OVy�
��E�}qx�G��O��"�<�=zsl��+��H���\u��/�~�>��?9=��Y�03�����������g ����?�G�����1�y���y����l����e�_����	�gOf#f��>����M!C���q����8��|1���L�r�G����#���v/�?�j���u
������V�������Rl��a~'��c��q8�5����q�~7*H;����������?R:'��MZQ"�5~��Co�0�E�a��k�����000���x�l���U�8C��Z���X��YnrFQY�Z��J�-=1x/v1����"�).?��
x�hi��_�����<o!�x/q��0l�5�/l
���1:�rV�r��\����@�������o=�/n������P��m��������VV��m�k/�x���RL��&���g�N����E&�b��0�Bd>2!��a\O��-���E��95+O*3���
	o��d�f�>�q��<�0�$%AO���������&Am��6>UJ�zZg�B�h�I7-O����CC��~2�,�ZK�I
��������]�6��V����S'�+/���
�*�UfQz���\��KX��t�:+������.��qO�1p��r�����v��b��.���EsT�������A�#t;�B������M;��>=��`N�L�n^�����^�u���Z��d����HB6��\�_��|�JU`Lp
h�Q��D"�������v�:8�;�e��f;���*�Y���� /,��.gg��o��U�_��/���n������H��9�@���~OE	��8���e��=4��=998z���uG��25������[i����87�v�}�b�y��8<�HQ�f�v����x����	���k���5(�wBKxV*����N���;1�S�j+���`6���j2A�w��t��������~�1��i���al
F�������\�o���x�dps��Z�����Y��5�X�����'���1>��E�nh����lH�6���q��$DJ8���#OI8�
���y�;��w+2���a�l`$��V+�W�U�,��n@Q�n I��;����!�ia��:�����n���^�?%w��!�\�����R�������}�`e��z������>������O[��3�?t�J(�5�2GJ�����gT��`0�b5���C_�f�!��R����r{([<��R{Z�hb_")�'J9zI��a�'6�p0����������CM�_�Z�|����,r���J��4��{��D������r��\
��M�$Fz>�����rn�:����u�B�FT�U����#)��3	u]M�,���)��X���P��U�b�����7��+@�� �]�?a\�����:vb%x��;=�k}�����Nr�
�U�v�JV��3�p!����1�s$��>��z�9?l,���T}���m�rn���kT�������aMP�B��L�_�������.8�_����}���p����f�=�+�n����P(�����A����E�����?ow5�I��)2@�8�=_,����xLg���e^�As���L�bf�8���cqv�n�if�8�n.Sb��p��2`C��;��G��'�m$��4�������	��;��%�%�����M-1A)��5����@�0fk��9���p�E�Dq9�V�qU�=�'�� ���o�*�$�rxn�M��+����q"������U��w(��2��+O�
�v3���'*�11!
��s������D�@�i\(��r&\��i-nQ�4��9`z��W�<�[����&�d��kt`Ln�?�>�d��<{�GK�*����+�R�����������rrI���k������-KH�nU�@��='2���C�CLM�U��Hi|��u*���2Ue�,[S&������sL�2����!�Z��t�J"
67������j8�d.w1�p����y�R	=�7s.�J��rS��(����������'@�i������1�y��+S5�.������������,�����Z������c9\,�733s�O�-��{�y��)&�"54��i;=��!4%4,E�az[����w�O��r'/34����q`
��gO���Td����~<����&T���4�]
y�=���������F}�A����|d������ci��4���;[ ���f���Z�����N��%V/�����J��4�6�X�r���uo�S��/��p|�s[���#���mX]�X,����]���u�D���������}��t�M�o�Ee��n�'���v�3�����|e��rl;����R
�N�oi;������e�h����;�x4���/k3B����	=k���h�z��8$������e�+��a�ha?����ss?�UM`I?��6�� �����;r��&���T�6�mTTA��9�)6
���d�	f����G`�?���d#��4TJx�,d�\���:��@�hT���S	 \����:��
���mE
���jb��Lc�����v5J�:
� 
��f�;l���<�����{&���'|��p!W)�L��y���.\��9������j��V���o5�Q���&�=���%��k��[�K��Q�����w�kH���y���gG�|<C��bCW��Z�	I���]#b�(c�q�Ym5*�a���m�k1#�(���.��t�z�bJ:Q����\b�+���:`f��f^�����Y������BYz���j�g*������n>�v1\�3m5��������f��(��@�)�.����\.0u]X��8����y�I`�q�l��z�N3����b'�6�<�>��>l��MY�>�/�L1q�K���LK���Wk���H��1��g�Nz��O�j�H�a'����L�H��d�We�4��4�p��j]'-��o)�>��Z���m���H�:r$�G���1���24;��wE	�b#pb�m��*C��8����IK,�L`�HQ��b����T��	�H�GT'���'���ie��.�_j=�^R|�H}3������[�P��)�k����3t��X����H���f�@.�8�,Hd������Dz,'%�=���+��P��U
}�%�?���<��D����zR�rg���~�|����~���g���OK����<l:�������o���z�������!�����?,��+���v�%�Q�=J"}�3�����'��{Bkk]�,�BI�7�_���1_$4���K'�v���W;
M�g����T'��i��n/S�d,X�VAY=����=������1�sC�"�P�Z�?�Q�t�?}�z`�-���.�q��.<Bq������j������3/u��v9hH��k)H���c%�}��#�����H�#�#~��x=����-�%S��l�zk���d*C�hQ��P�q3Z�	i������{��~�H��q)�=)�A�R�8��������)<�2��1�����=`�2+�����+����<?s(q���
�R��1��Is(n�����R��1-
4LY�F��j�����T�0��$��*�H*��[�����#��Q���5H��6Euu&	���G��@�>N������&���	'���EYT��������L�T�����m=U.$�&N��<4I
�qN��M	�3��W2
�H�$F�I�QL���r�+K���/��'�
_���k�A�������&=t��G��SvL'��<�����;�����<�O��YV�9C���<9cf�h���e���M���~�����^D�"�;nQ2^�F[��*8t�u�"W���|��$`p�pO	�{���~���Q�9��_�1EV[�/�����Ymyw+�
PU��SU��FkRr�6H�����s2���%@�	��D��^�����6]��S��l��WK^_��Mn�T4M��v��Y�t��N\��'��TL^�<A�~|�����7���E��*��}*����u���N��B�	8�J�� �w�C������������V�8����J��A��i�0l�L�����qj�tV����PW�T0���C����.4�����-�JK�e>�<���J�����������Z-�"��z�nh��ng�Z���6R����	"�*3�`r'��j ��v��n���mD����2��W^�u;�N���}qz|��v���jF���?}*UYY����x����A���z���WkG������NB�p���6��A�t|��kX���'�����4�Y������i^n��k�&}�pv���1�"y�Z��d�P�h
1�s�ds�p��`lI�r4���8N������^�B7�z�p��z5n+�IwRsNz�4�I���}��l�qF��p��~�P�~���Qo4�h?Td�	��9������H�����+�Pv�r^w|���e9
����"i;�_s���j��������������_9t�x*��i�(C|O��%<��Anm9��a���1�#������:�~X��F�������|�)�dSi�H����!F
3����f��s>�wl35wf9�� XC����������[�Z�R��Z�xT��!�p��(��������G��c��PQW�-��!^��|��F��c���������~�=M����b���n��@l�k	�W������� h�7m�G��������V�;-t�o�lO�V����ou���7G�r�����C�u��V�y�9��kx��T��������i�G�q��F���}�������g<0'���K�mcNU�*!y�{Q����0����p/R���%�F�I�Z��7G�S�h�����m��	��4�TRtef�+��
b�M~�^��P����x��H�����*��dA�w�/���$`�5�w�;����*�M�x���7>N�Vh)�T������������k�k�V�d��%��,���K����b�����]�v1_�r
�o�����c���T��������j��Y����7LL3�85�	�����9�AEr��c���Qt�)v;�~��v9k����<��)E������	L����o���T�����%�����44�g�%�te+�~#�X
R=���.��
�&��d/,@�I���[��>��z'j6���m
�T�B}S*�7�?bX��%Lf6��e�M�+���M�������{�#��DA�1������}t�5,����(�'�4`hx��
FJs4�m�������Q$^!��b��r�������-�1<�\��	�/*��a~C0kc��Y�v�������Oa�
U������R�0?���b�*,bJ2�%��E�y�cG�VkF��g�����IJ*�����V �$��������~<�����B�{����y��������r/�\h���7��/�y�.�g�t���(t��_n���dBQ�����$@B���`��>q����k�x^����������;9�O��5��U4�yS��88�������v)�#O�4"�_�7�g|��g/_��z��r!�����d���Lvk���Iu�����gO���������8>�M������d�5Y�>�_��9��.w����3������=�;�]kq��n,|������u���
��{�Y���^��<���4��w����
�������=(��1��T������Y����	�E��B>���2����,���kA^���J�B&~y�?�W������4W6���^�&��9R����'o�T����dO�.B�7Alk�/������5�Z�g���E�f�
�FS"���{{x!��(�
���;�������������$�>t!���=��������O���`n0S������F��2��������x�����J����� !^����k�G6�A/���C3y�IJ�\���z��t��E�)8��p��]��
NaZf���;=�B5�bWt�&��7�����cR6���Y�������sC���W���c����_C��v�9"�\��0���AM9ARvmr��'d�+���/3�S�
O�F���_P���������3i������N���j{�L����f�B��R��p~y��������~���k���|��0����g�������M`�
�Q0�L�(���Kr����W+d\����d����st�~�4��[�[dK��D�hw���
:�Z���E�V����E����%1�1)+6%{���E6dR��!{��!��k���
���1���XV`C&�l�
�i�dV��!{�,�bC��RA�W��$+/!1��a�����O�2��V�����`>���2)�r�Y`��_��n�+0���o�����F��Z��qN���a��S���:���a���?s�~��v�>bf��U*��c&;K���J,w%�"����^��;�Z�25:#`VR;�;����f>.E��*h�J�<�;��*�h���,^����B9ai�����n;=����np��C�	�|9����c�C��nggj������o���e�/*D��������@�}���C����(�Oz�{o
���V#������������G����=�d8���4jXk0��wv@�D�����tvrE��G�|�W7D�a*�(L"����C�p�����JE�QjJ�=�%�����:D�)G���������s��	�3i����Pj4�Wkz��dy�%����Yw1�A��N�����op.�"GO��	,\ ���V�'&���vC�w���jz=&}��c�H�6�5�������>��`����K���{�j'�!��Q�Z�MD%����g_�t���"Y�0�+�G�����KO���?��)ml�r�c�Q��)}����6��N����x��9��O���f�I�����(_l�z�����<Y^.���8o9��>>���h�.�����������9�8T<g��l����\�3(P������\�E���������L��8z����e����b���{����������| c�������)	
b����n�w��NF���[�|��!*�e7�QH�:d��?������^r�X�:k�f��/S]�/�[��o8|{|�����!����d�� <������d�@�3J�c�fX�Mk�����	
���D�r�T���?r��!�y�P^>�wOP�!�Go��dl{&�����6��i9n��0V�y������k
\��0�cMs<�B���`J��8|��t���k�k�A����s��&�����"�NRt�</1��\FcC��a����g�&��{���q����?8;����-�<�L�x9����9���+��T�������7*��~1_�[�v>���C�����FG�z]��3 �pT��J��R�4k��eh�;E����N1��9�H46�������vw)�mZ�T�Y��1�����k��-t���E�-����X���Rr1!c�L����Bx�]��U	��-�#�T5���1������]I���+~@2 �3 ��W)�"��(8J��C���B.�2D��4�sn��Q�'���;*;��^ZK	��^H|k�����b�Nq�)���2yQ�J��)���S��{�N�O���������s{o�XN�>������%�|=
�m�>T`��
u#�#v�JY�:5�t���;z���O<��o��x�d���/�O�}��$g�Lz���P����88����j����J�����q�ho�!�6�i��K�y"����%�L�]��m����<�u����������/a���+��s�&%&� >���L���K�D6����)-�ts>�M���aT|t�3��[�?�"Y�����x�����
2J������:?V���>�V�\�pX5����1`�N�2v[�Zf�����]��X�S�E�z�VsAA�(M�5 ��L��a��l�2O�S����Y���q9���b+����2� Kf�I&C�P�����TM4Cw�E:Ov����?�24$�;6T6}v��[�t��B�qd1�@D�>C��iY�����*IPkEKQ�b��c�D%`c[�~�|���M��'��<#�-��4�0bR�_I8��c+��p2��(�O��IC�g��s���<b�g:��
P�=�^�dyl�[k��9����Tf��kz��]��J�>U��Tz�7g��i���{��S=�^��`�z�����6<���X�D�Gx��*	3��'"�������s��
����t\��dv��/�2����A|D�~���f=`mj@��(��%�`PD��k�;�`6'MeXk��������E��lD����+���\IN��%�5F���4������l���6��c�@�n�2�#&�����$�,����^U1���S)�o��X���.��M����jk+��eG�,e�U������&j��������2`���B����4�v�����}����2�FA���n����� S�6�6�$S/�,BI�0��n�u}L��z�8������N\�����>�	�2>k���`x����#������c�60gD�f7��j��<'�����\�3K!����$Y|b��n]Q�>�<��m���j��w#J��oE��������V1�^�P��J���*%b�7j���J{4������Z�r����9���Tv�W��A_|���xA��6�Q�i)���g���r
��n����[:	(������c�&�������mh�&���?.Q�P���(��v�������
��������I:����B��y�w(v���HCb�{�A�R���rq/�X�6�v�$S��$����q�8%�>�g������A�G*��u���!Q5��v5��^��]���cM�����Dby7��\-b*�;h?�p�����`z|,=8:������A8#����|3����.�f�� '�F]�2��?�O��H�?����CS�������)����� ��g��`\s^��y���.gb�s'���a6�N���%�/t���?}
Q����~���\#�������X��$I�9a��2�\7���h"�$�� BI�����^��s.��d�4���!���/(@��b�7Ey,�`:�m��
�����L����e����L�5���q�|�r	#(6��1:*@sL����^l�y�2�8��`m�F-#��l*�dz#�x?�a��:v�J7��|yx��������]imr�s�%���5k�i��&�`��N�V�D\7� }3������rf��k��y1G��Q�N����f,�;��1�Y%K��ptW!]��?2�xf��o��Z@�$n���P���(�JI�~{V �=�{4�&7A�j�m�0>\j�-�����3���{&^��M�v^�`C$���@�mYo�b��s����\s��<���>zm\�\�K�\[$a��� �C;?L�4�P�9��r�W�������]�����7�����N�����3�����v?Pe�I;�>g0\��!Mq��!�n�m[b�^���9�Hu��	"�*��G�v�"aI���d�TMYpbT��Z�tWW���z:����#>�h�CY�|���X��s��\�����'>
���c����x�F)����y)>F�`�;�U���M�;Y5���&���7��n-�)G�5���p6]�HbZ(q����Z���W*�E-�T1 �n�@����!w\�d����5�o�%��dV-�4�]��F��I��$�p����Zs+��W"e����KE����9kC�d�����M����VG�_��\��`9����p5*[�{-d�t2�G����&mV�&[�P�}����E�;���7��H�a�!M�Y���p���H(���l���bt������g[L�}�e���.R�����Z'���J����k��'��Z���z��mv�Q�k�D��VK����U=��e�S�J�W��������`x\}��He�y����3>���7����w�K^����is���_b�?��� 9���3�p�x2��'����84$�d�f����_h�Z5j�,��V���&J���Je��e��_D^I��T��F2�V����//�l�`�UZ��*-��S
#51Jan Urbański
wulczer@wulczer.org
In reply to: Petr Jelinek (#50)
Re: [PATCH] DefaultACLs

OK, the previous problem went away, but I can still do something like that:

postgres=# create role test;
CREATE ROLE
postgres=# create role test2;
CREATE ROLE
postgres=# create database db;
CREATE DATABASE
postgres=# \c db
psql (8.5devel)
You are now connected to database "db".
db=# alter default privileges for role test2 grant insert on table to test;
ALTER DEFAULT PRIVILEGES
db=# \c postgres
psql (8.5devel)
You are now connected to database "postgres".
postgres=# drop role test2;
DROP ROLE
postgres=# \c db
psql (8.5devel)
You are now connected to database "db".
db=# select * from pg_default_acls ;
defaclrole | defaclnamespace | defaclobjtype | defacllist
------------+-----------------+---------------+--------------------------------------
16385 | 0 | r |
{16385=arwdDxt/16385,test=a/wulczer}
(1 row)

db=#

Dependencies suck, I know..

Jan

#52Petr Jelinek
pjmodos@pjmodos.net
In reply to: Jan Urbański (#51)
1 attachment(s)
Re: [PATCH] DefaultACLs

Jan Urbański napsal(a):

Dependencies suck, I know..

Cross-database dependencies do.

I had to make target role owner of the default acls which adds some side
effects like the fact that it blocks DROP ROLE so DROP OWNED BY has to
be used.
As for REASSIGN OWNED, it does not reassign anything (I don't think it's
a good idea to reassign default acls) it just spits warning with hint
what to do if user plans to drop the role.

--
Regards
Petr Jelinek (PJMODOS)

Attachments:

defacl-2009-09-24-2.diff.gzapplication/x-tar; name=defacl-2009-09-24-2.diff.gzDownload
#53Jan Urbański
wulczer@wulczer.org
In reply to: Petr Jelinek (#52)
Re: [PATCH] DefaultACLs

Petr Jelinek wrote:

Jan Urbański napsal(a):

Dependencies suck, I know..

Cross-database dependencies do.

I had to make target role owner of the default acls which adds some side
effects like the fact that it blocks DROP ROLE so DROP OWNED BY has to
be used.
As for REASSIGN OWNED, it does not reassign anything (I don't think it's
a good idea to reassign default acls) it just spits warning with hint
what to do if user plans to drop the role.

OK, so that addresses my last gripe.

It seems that when you try to drop a role to which you have granted some
privileges before, you can't, and when you REASSIGN OWNED, it doesn't
help. So maybe it's not even necessary to give a warning when REASSIGN
OWNED is called on default ACLs.

Only loose end is tab completion which can probably be added later on.

I'm also not sure if we wouldn't like to have ALTER DEFAULT PRIVILEGES
FOR ALL ROLES (or something similar), so you won't have to ALTER DEFAULT
PRIVILEGES for each developer you gave a DB login to.

Petr told me that was the previous design but has been shot down - I
found references to that on the mailing list, but most complains were
about tying the syntax to ALTER SCHEMA. Since we now have ALTER DEFAULT
PRIVILEGES I think it might make sense to introduce a way to set the
default privileges for all roles (and give superusers the right to do it).

This can be added later on, but maybe we could make the syntax work so
when you do ALTER DEFAULT PRIVILEGES without FOR ROLE you set them for
all roles in the current DB and if you want to set them for yourself,
you need to specify FOR ROLE <yourrole>. That'd be a minor change in the
patch.

Setting to "Ready for Committer" (and leaving to the committer the
decision whether to support "FOR ALL ROLES" and what to do about the
warning).

Jan

#54Tom Lane
tgl@sss.pgh.pa.us
In reply to: Petr Jelinek (#52)
Re: [PATCH] DefaultACLs

Petr Jelinek <pjmodos@pjmodos.net> writes:

[ latest version of DefaultACLs patch ]

I started looking through this patch, but found that it's not nearly
ready to commit :-(. The big missing piece is that there's no pg_dump
support for default ACLs. That's a bigger chunk of code than I have
time/interest to write, and I don't think I want to commit the feature
without it. (I'm willing to commit without tab completion or any
psql \d command to show the defaults, but pg_dump just isn't optional.)

There is another large problem, too. The patch seems to have
only half-baked support for global defaults (those not tied to a
specific schema) --- it looks like you can put them in, but half
of the code will ignore them or else fail while trying to use them.
This isn't just a matter of a few missed cases while coding, I think.
The generic issue that the code doesn't even think about addressing
is which default should apply when there's potentially more than one
applicable default? As long as there's only global and per-schema
defaults, it's not too hard to decide that the latter take precedence
over the former; but I have no idea what we're going to do in order
to add any other features. This seems like a sufficiently big
conceptual issue that it had better be resolved now, even if the first
version of the patch doesn't really need to deal with it.

Also, the GRANT DEFAULT PRIVILEGES thing just seems completely bizarre,
and I'm not convinced it has a sufficient use-case to justify such a
strange wart on GRANT. I think we should drop it. Or at least it needs
to be proposed and discussed as a separate feature. Maybe it would seem
less strange if the syntax was "RESET PRIVILEGES ON object".

A couple of minor gripes:

Per recent discussion, names of system catalogs shouldn't be plural.

elog() is not suitable for user-facing errors. For example in
ExecGrantStmt_Defaults, the grammar does not prohibit the unsupported
target object types, so you need to throw a nicer error there. Or
else reject them in the grammar. There seem to be a number of other
places where elog is used but the error is perfectly likely to be caused
by a user mistake.

regards, tom lane

#55Petr Jelinek
pjmodos@pjmodos.net
In reply to: Tom Lane (#54)
Re: [PATCH] DefaultACLs

Tom Lane wrote:

Petr Jelinek <pjmodos@pjmodos.net> writes:

[ latest version of DefaultACLs patch ]

I started looking through this patch, but found that it's not nearly
ready to commit :-(. The big missing piece is that there's no pg_dump
support for default ACLs. That's a bigger chunk of code than I have
time/interest to write, and I don't think I want to commit the feature
without it. (I'm willing to commit without tab completion or any
psql \d command to show the defaults, but pg_dump just isn't optional.)

Yeah I completely forgot about pg_dump just like I did with anonymous
code blocks :-(

There is another large problem, too. The patch seems to have
only half-baked support for global defaults (those not tied to a
specific schema) --- it looks like you can put them in, but half
of the code will ignore them or else fail while trying to use them.
This isn't just a matter of a few missed cases while coding, I think.
The generic issue that the code doesn't even think about addressing
is which default should apply when there's potentially more than one
applicable default? As long as there's only global and per-schema
defaults, it's not too hard to decide that the latter take precedence
over the former; but I have no idea what we're going to do in order
to add any other features. This seems like a sufficiently big
conceptual issue that it had better be resolved now, even if the first
version of the patch doesn't really need to deal with it.

Half of the code will ignore them ? They are ignored if schema specific
defaults were set.
Yes I haven't tried to solve the problem of having non-hierarchical
filters for defaults and if we require that then this patch is dead for
(at least) this commitfest, because at the moment I don't even know
where to begin solving this.

Also, the GRANT DEFAULT PRIVILEGES thing just seems completely bizarre,
and I'm not convinced it has a sufficient use-case to justify such a
strange wart on GRANT. I think we should drop it. Or at least it needs
to be proposed and discussed as a separate feature. Maybe it would seem
less strange if the syntax was "RESET PRIVILEGES ON object".

I vote for dropping it then.

--
Regards
Petr Jelinek (PJMODOS)

#56Josh Berkus
josh@agliodbs.com
In reply to: Tom Lane (#54)
Re: [PATCH] DefaultACLs

There is another large problem, too. The patch seems to have
only half-baked support for global defaults (those not tied to a
specific schema) --- it looks like you can put them in, but half
of the code will ignore them or else fail while trying to use them.
This isn't just a matter of a few missed cases while coding, I think.
The generic issue that the code doesn't even think about addressing
is which default should apply when there's potentially more than one
applicable default?

I thought the idea was to simply avoid that situation. Maybe we want to
forget about global defaults if that's the case, and just do the ROLE
defaults.

I thought we were trying to keep this solution as simple as possible.
It's meant to be a simple feature for simple use cases. I know we all
love making stuff as ornate and complex as possible around here, but
that kind of defeats the purpose of having DefaultACLs, as well as
setting the bar unreasonably high for Petr. Asking him to
future-filter-proof the feature assumes that there will be future
filters, which I'm not convinced there will.

I certainly haven't seen any good use case for having multiple
conflicting defaults. In fact, I thought we'd agreed that any complex
cases would be better handled by PL scripts.

pg_dump support is required though.

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

#57Tom Lane
tgl@sss.pgh.pa.us
In reply to: Josh Berkus (#56)
Re: [PATCH] DefaultACLs

Josh Berkus <josh@agliodbs.com> writes:

This isn't just a matter of a few missed cases while coding, I think.
The generic issue that the code doesn't even think about addressing
is which default should apply when there's potentially more than one
applicable default?

I thought the idea was to simply avoid that situation. Maybe we want to
forget about global defaults if that's the case, and just do the ROLE
defaults.

That seems like a pretty dead-end design.

I thought we were trying to keep this solution as simple as possible.
It's meant to be a simple feature for simple use cases. I know we all
love making stuff as ornate and complex as possible around here, but
that kind of defeats the purpose of having DefaultACLs, as well as
setting the bar unreasonably high for Petr. Asking him to
future-filter-proof the feature assumes that there will be future
filters, which I'm not convinced there will.

I already mentioned one case that there's longstanding demand for, which
is to instantiate the correct permissions on new partition child tables.

But more generally, this is a fairly large and complicated patch in
comparison to the reward, if the intention is that it will never support
anything more than the one case of "IN SCHEMA foo" filtering.

regards, tom lane

#58Josh Berkus
josh@agliodbs.com
In reply to: Tom Lane (#57)
Re: [PATCH] DefaultACLs

Tom,

I thought the idea was to simply avoid that situation. Maybe we want to
forget about global defaults if that's the case, and just do the ROLE
defaults.

That seems like a pretty dead-end design.

Well, the whole purpose for DefaultACLs is to simplify administration
for the simplest use cases. If we add a large host of conflicting
options, we haven't simplified stuff very much.

I already mentioned one case that there's longstanding demand for, which
is to instantiate the correct permissions on new partition child tables.

Wouldn't that be handled by inheritance?

But more generally, this is a fairly large and complicated patch in
comparison to the reward, if the intention is that it will never support
anything more than the one case of "IN SCHEMA foo" filtering.

I thought we were doing ROLEs?

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

#59Tom Lane
tgl@sss.pgh.pa.us
In reply to: Josh Berkus (#58)
Re: [PATCH] DefaultACLs

Josh Berkus <josh@agliodbs.com> writes:

But more generally, this is a fairly large and complicated patch in
comparison to the reward, if the intention is that it will never support
anything more than the one case of "IN SCHEMA foo" filtering.

I thought we were doing ROLEs?

The owning-ROLE match is required, else you have issues with exactly
what the ACL really means. What we're discussing is what other filters
might exist to determine which objects are affected. The patch already
tries to handle the cases of "all owned objects" and "all owned objects
in schema X", and I think it's inevitable that people will want other
cases.

regards, tom lane

#60Josh Berkus
josh@agliodbs.com
In reply to: Tom Lane (#59)
Re: [PATCH] DefaultACLs

Tom,

The owning-ROLE match is required, else you have issues with exactly
what the ACL really means. What we're discussing is what other filters
might exist to determine which objects are affected. The patch already
tries to handle the cases of "all owned objects" and "all owned objects
in schema X", and I think it's inevitable that people will want other
cases.

Yeah, I'm thinking we should back off from filters for 8.5; we could do
them for 8.6, maybe. I'm one of the people who prefers a schema-based
system, but I'll do without one if it means we can keep things *simple*
(and get the feature in in 8.5).

I think trying to make this patch a panacea in the first iteration is
liable to backfire. Especially since we're doing GRANT ALL ON at the
same time.

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

#61Tom Lane
tgl@sss.pgh.pa.us
In reply to: Josh Berkus (#60)
Re: [PATCH] DefaultACLs

Josh Berkus <josh@agliodbs.com> writes:

I think trying to make this patch a panacea in the first iteration is
liable to backfire.

I did not suggest any such thing --- the current scope of functionality
is fine by me for a first cut. What I *am* saying is that we had better
have some idea of how we are going to extend it when (not if) we try
to extend it. Otherwise we are likely to find we painted ourselves
into a corner.

Especially since we're doing GRANT ALL ON at the same time.

That's another patch that has an *excellent* chance of getting rejected
on pretty much the same grounds.

regards, tom lane

#62Cathy Mullican
cmullican@gmail.com
In reply to: Josh Berkus (#58)
Re: [PATCH] DefaultACLs

On 9/28/09, Josh Berkus <josh@agliodbs.com> wrote

I already mentioned one case that there's longstanding demand for, which
is to instantiate the correct permissions on new partition child tables.

Wouldn't that be handled by inheritance?

I wish, but no:
http://www.postgresql.org/docs/current/interactive/ddl-inherit.html
The first paragraph under "5.8.1 Caveats":
Table access permissions are not automatically inherited. Therefore, a user
attempting to access a parent table must either have permissions to do the
same operation on all its child tables as well, or must use the
ONLYnotation. When adding a new child table to an existing inheritance
hierarchy, be careful to grant all the needed permissions on it.

#63Robert Haas
robertmhaas@gmail.com
In reply to: Tom Lane (#54)
Re: [PATCH] DefaultACLs

On Mon, Sep 28, 2009 at 1:32 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

The generic issue that the code doesn't even think about addressing
is which default should apply when there's potentially more than one
applicable default?  As long as there's only global and per-schema
defaults, it's not too hard to decide that the latter take precedence
over the former; but I have no idea what we're going to do in order
to add any other features.  This seems like a sufficiently big
conceptual issue that it had better be resolved now, even if the first
version of the patch doesn't really need to deal with it.

I haven't read the patch, but it seems like one possible solution to
this problem would be to declare that any any DEFAULT PRIVILEGES you
set are cumulative. If you configure a global default, a per-schema
default, a default for tables whose names begin with the letter q, and
a default for tables created between midnight and 4am, then a table
called quux created in that schema at 2:30 in the morning will get the
union of all four sets of privileges.

...Robert

#64Tom Lane
tgl@sss.pgh.pa.us
In reply to: Robert Haas (#63)
Re: [PATCH] DefaultACLs

Robert Haas <robertmhaas@gmail.com> writes:

I haven't read the patch, but it seems like one possible solution to
this problem would be to declare that any any DEFAULT PRIVILEGES you
set are cumulative. If you configure a global default, a per-schema
default, a default for tables whose names begin with the letter q, and
a default for tables created between midnight and 4am, then a table
called quux created in that schema at 2:30 in the morning will get the
union of all four sets of privileges.

Hmm ... interesting proposal. Simple to understand and simple to
implement, which are both to the good. I'm not clear though on whether
this behavior would be useful in practice. Any comments from those
who've been asking for default ACLs?

One potential trouble spot is that presumably the built-in default
privileges (eg, PUBLIC EXECUTE for functions) would *not* cumulate
with user-specified defaults. So you'd have a behavior where a
function would not get PUBLIC EXECUTE automatically if it matched
any of the available defaults, but would get it if it managed to
miss matching them all. I am not sure if that's bad or not, but
it seems kind of inconsistent.

regards, tom lane

#65Robert Haas
robertmhaas@gmail.com
In reply to: Tom Lane (#64)
Re: [PATCH] DefaultACLs

On Mon, Sep 28, 2009 at 10:44 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

I haven't read the patch, but it seems like one possible solution to
this problem would be to declare that any any DEFAULT PRIVILEGES you
set are cumulative.  If you configure a global default, a per-schema
default, a default for tables whose names begin with the letter q, and
a default for tables created between midnight and 4am, then a table
called quux created in that schema at 2:30 in the morning will get the
union of all four sets of privileges.

Hmm ... interesting proposal.  Simple to understand and simple to
implement, which are both to the good.  I'm not clear though on whether
this behavior would be useful in practice.  Any comments from those
who've been asking for default ACLs?

One potential trouble spot is that presumably the built-in default
privileges (eg, PUBLIC EXECUTE for functions) would *not* cumulate
with user-specified defaults.

Why not?

...Robert

#66Stephen Frost
sfrost@snowman.net
In reply to: Robert Haas (#65)
Re: [PATCH] DefaultACLs

* Robert Haas (robertmhaas@gmail.com) wrote:

One potential trouble spot is that presumably the built-in default
privileges (eg, PUBLIC EXECUTE for functions) would *not* cumulate
with user-specified defaults.

Why not?

How would you have a default that says "I *don't* want public execute on
my new functions"?

Stephen

#67Petr Jelinek
pjmodos@pjmodos.net
In reply to: Tom Lane (#57)
Re: [PATCH] DefaultACLs

Tom Lane napsal(a):

I thought we were trying to keep this solution as simple as possible.
It's meant to be a simple feature for simple use cases. I know we all
love making stuff as ornate and complex as possible around here, but
that kind of defeats the purpose of having DefaultACLs, as well as
setting the bar unreasonably high for Petr. Asking him to
future-filter-proof the feature assumes that there will be future
filters, which I'm not convinced there will.

I already mentioned one case that there's longstanding demand for, which
is to instantiate the correct permissions on new partition child tables.

Just FYI, this one is still hierarchical (database->schema->parent table).

--
Regards
Petr Jelinek (PJMODOS)

#68Petr Jelinek
pjmodos@pjmodos.net
In reply to: Stephen Frost (#66)
Re: [PATCH] DefaultACLs

Stephen Frost napsal(a):

* Robert Haas (robertmhaas@gmail.com) wrote:

One potential trouble spot is that presumably the built-in default
privileges (eg, PUBLIC EXECUTE for functions) would *not* cumulate
with user-specified defaults.

Why not?

How would you have a default that says "I *don't* want public execute on
my new functions"?

This is actually problem that applies to whole Robert's proposal. How
would you define you don\t want insert on new tables in schema when you
granted it for whole database. I don't think any kind of mixing of
different default privileges is a good idea. I was thinking about
rejecting creation of conflicting default privileges but that would be
impossible to detect before object creation which is too late.

--
Regards
Petr Jelinek (PJMODOS)

#69Petr Jelinek
pjmodos@pjmodos.net
In reply to: Tom Lane (#61)
Re: [PATCH] DefaultACLs

Tom Lane napsal(a):

Especially since we're doing GRANT ALL ON at the same time.

That's another patch that has an *excellent* chance of getting rejected
on pretty much the same grounds.

I don't really see how this applies to GRANT ON ALL. You don't have to
predict future there so if you have conflict in multiple filters user
specified you simply throw error.

--
Regards
Petr Jelinek (PJMODOS)

#70Robert Haas
robertmhaas@gmail.com
In reply to: Stephen Frost (#66)
Re: [PATCH] DefaultACLs

On Mon, Sep 28, 2009 at 11:47 PM, Stephen Frost <sfrost@snowman.net> wrote:

* Robert Haas (robertmhaas@gmail.com) wrote:

One potential trouble spot is that presumably the built-in default
privileges (eg, PUBLIC EXECUTE for functions) would *not* cumulate
with user-specified defaults.

Why not?

How would you have a default that says "I *don't* want public execute on
my new functions"?

Hmm...

Maybe instead of having built-in default privileges, we could view
each user as having their global default ACL pre-initialized to that
same set of privileges (of course we needn't store it unless and until
they modify it). Then they could add to those or take away from them,
plus add additional privileges at other levels.

...Robert

#71Petr Jelinek
pjmodos@pjmodos.net
In reply to: Robert Haas (#70)
Re: [PATCH] DefaultACLs

Robert Haas napsal(a):

On Mon, Sep 28, 2009 at 11:47 PM, Stephen Frost <sfrost@snowman.net> wrote:

* Robert Haas (robertmhaas@gmail.com) wrote:

One potential trouble spot is that presumably the built-in default
privileges (eg, PUBLIC EXECUTE for functions) would *not* cumulate
with user-specified defaults.

Why not?

How would you have a default that says "I *don't* want public execute on
my new functions"?

Hmm...

Maybe instead of having built-in default privileges, we could view
each user as having their global default ACL pre-initialized to that
same set of privileges (of course we needn't store it unless and until
they modify it). Then they could add to those or take away from them,
plus add additional privileges at other levels.

That's how it works now actually, the problem is that when you grant
something in the chain you can't revoke it anywhere else in the chain
when you are merging privileges as you proposed.

--
Regards
Petr Jelinek (PJMODOS)

#72Tom Lane
tgl@sss.pgh.pa.us
In reply to: Petr Jelinek (#71)
Re: [PATCH] DefaultACLs

Petr Jelinek <pjmodos@pjmodos.net> writes:

That's how it works now actually, the problem is that when you grant
something in the chain you can't revoke it anywhere else in the chain
when you are merging privileges as you proposed.

To allow that, you have to have some notion of a priority order among
the available defaults, so that you can sensibly say that A should
override B. Which is easy as long as they've got hierarchical scopes,
but that doesn't seem like a restriction that will hold good for future
extensions.

regards, tom lane

#73Robert Haas
robertmhaas@gmail.com
In reply to: Tom Lane (#72)
Re: [PATCH] DefaultACLs

On Tue, Sep 29, 2009 at 11:05 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Petr Jelinek <pjmodos@pjmodos.net> writes:

That's how it works now actually, the problem is that when you grant
something in the chain you can't revoke it anywhere else in the chain
when you are merging privileges as you proposed.

To allow that, you have to have some notion of a priority order among
the available defaults, so that you can sensibly say that A should
override B.  Which is easy as long as they've got hierarchical scopes,
but that doesn't seem like a restriction that will hold good for future
extensions.

Yeah. Right now AIUI we have these:

grant default privileges across the board
grant default privileges to objects in schema X

In the future maybe we might have:

grant default privileges to all objects EXCEPT those in schemas A, B, C.

I'm not real sure whether this sensible and I'm not real sure whether
it's too baroque to be worthwhile, but I am fairly confident that it
doesn't create any nasty semantic problems, which can't be said for
any approach that involves permissions for schemas "overrriding" the
global permissions, which will break down horribly as soon as we want
to do anything orthogonal.

...Robert

#74Petr Jelinek
pjmodos@pjmodos.net
In reply to: Tom Lane (#72)
Re: [PATCH] DefaultACLs

Tom Lane napsal(a):

Petr Jelinek <pjmodos@pjmodos.net> writes:

That's how it works now actually, the problem is that when you grant
something in the chain you can't revoke it anywhere else in the chain
when you are merging privileges as you proposed.

To allow that, you have to have some notion of a priority order among
the available defaults, so that you can sensibly say that A should
override B. Which is easy as long as they've got hierarchical scopes,
but that doesn't seem like a restriction that will hold good for future
extensions.

I am aware, I knew all that has been said so far at the time I sent in
the patch actually. That's why I am very skeptical about having those
future non-hierarchical filters, I just don't see a way to make it happen.
Also when you go to some insane complexity of default privileges that
don't respect your database structure then you either want to handle it
programatically as Josh said or you want to create new subroles what
have create something privilege and different default privileges instead
of hoping that the database will somehow magically do the right thing
about default acls conflicts.

--
Regards
Petr Jelinek (PJMODOS)

#75Josh Berkus
josh@agliodbs.com
In reply to: Tom Lane (#64)
Re: [PATCH] DefaultACLs

Tom,

Hmm ... interesting proposal. Simple to understand and simple to
implement, which are both to the good. I'm not clear though on whether
this behavior would be useful in practice. Any comments from those
who've been asking for default ACLs?

I'd be fine with it. My goals here are to make my life easier by
eliminating the need for complex GRANT scripts in the simplest cases,
and (more importantly) to get web developers to use database object
permissions *at all* by making them easy to manage. Robert's suggestion
fulfills this.

Again, for people who have complex or high security cases, you'd still
need a script. That's fine; object permissions are an NP-complete
problem anyway, and no feature we adopt is going to cover everyone. But
right now we have an issue that probably 98% of Postgres users don't use
object permissions *at all* because they're "too difficult to manage".

If I had a dollar for every person I saw running Postgres in production
as the "postgres" user, or with full public permissions on everything, I
could shut down PGX and go back to pottery.

One potential trouble spot is that presumably the built-in default
privileges (eg, PUBLIC EXECUTE for functions) would *not* cumulate
with user-specified defaults. So you'd have a behavior where a
function would not get PUBLIC EXECUTE automatically if it matched
any of the available defaults, but would get it if it managed to
miss matching them all. I am not sure if that's bad or not, but
it seems kind of inconsistent.

Yeah, but the fact that functions have PUBLIC EXECUTE by default is
inconsistent. All DefaultACLs would be doing is inheriting this
inconsistency. Again, I'm OK with that.

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

#76Petr Jelinek
pjmodos@pjmodos.net
In reply to: Tom Lane (#54)
1 attachment(s)
Re: [PATCH] DefaultACLs

Hi all,

because it seems like merging privileges seems to be acceptable for most
(although I am not sure I like it, but I don't have better solution for
managing conflicts), I changed the patch to do just that.

Also I think I addressed all other problems pointed out by Tom. Namely I
added pg_dump support (hopefully) and \ddp command for psql. Still no
tab competition though. I removed that GRANT DEFAULT PRIVILEGES
statement, changed user facing elog()s to ereport()s, and I changed
catalog name to singular.

--
Regards
Petr Jelinek (PJMODOS)

Attachments:

defacl-2009-09-30.diff.gzapplication/x-tar; name=defacl-2009-09-30.diff.gzDownload
#77Tom Lane
tgl@sss.pgh.pa.us
In reply to: Petr Jelinek (#76)
Re: [PATCH] DefaultACLs

Petr Jelinek <pjmodos@pjmodos.net> writes:

because it seems like merging privileges seems to be acceptable for most
(although I am not sure I like it, but I don't have better solution for
managing conflicts), I changed the patch to do just that.

It's not clear to me whether we have consensus on this approach.
Last chance for objections, anyone?

The main argument I can see against doing it this way is that it doesn't
provide a means for overriding the hard-wired public grants for object
types that have such (principally functions). I think that a reasonable
way to address that issue would be for a follow-on patch that allows
changing the hard-wired default privileges for object types. It might
well be that no one cares enough for it to matter, though. I think that
in most simple cases what's needed is a way to add privileges, not
subtract them --- and we're already agreed that this mechanism is only
meant to simplify simple cases.

regards, tom lane

#78Stephen Frost
sfrost@snowman.net
In reply to: Tom Lane (#77)
Re: [PATCH] DefaultACLs

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

Petr Jelinek <pjmodos@pjmodos.net> writes:

because it seems like merging privileges seems to be acceptable for most
(although I am not sure I like it, but I don't have better solution for
managing conflicts), I changed the patch to do just that.

It's not clear to me whether we have consensus on this approach.
Last chance for objections, anyone?

I don't like it, but at the same time I'd rather have it with this than
not have anything.

The main argument I can see against doing it this way is that it doesn't
provide a means for overriding the hard-wired public grants for object
types that have such (principally functions). I think that a reasonable
way to address that issue would be for a follow-on patch that allows
changing the hard-wired default privileges for object types. It might
well be that no one cares enough for it to matter, though. I think that
in most simple cases what's needed is a way to add privileges, not
subtract them --- and we're already agreed that this mechanism is only
meant to simplify simple cases.

This doesn't actually address the entire problem. How about
schema-level default grants which you want to override with per-role
default grants? Or the other way around? Is it always only more
permissive with more defaults? Even when the grantee is the same?

I dunno, I'll probably just ignore the per-role stuff, personally, but
it seems more complex without sufficient definition about what's going
to happen in each case.

Thanks,

Stephen

#79Tom Lane
tgl@sss.pgh.pa.us
In reply to: Stephen Frost (#78)
Re: [PATCH] DefaultACLs

Stephen Frost <sfrost@snowman.net> writes:

This doesn't actually address the entire problem. How about
schema-level default grants which you want to override with per-role
default grants? Or the other way around? Is it always only more
permissive with more defaults? Even when the grantee is the same?

Well, bear in mind that we're *only* going to allow these things
per-role, so as to avoid the problem of translating ACLs to a different
grantor. So the main case that's not being solved is "I'd like to
grant privileges XYZ everywhere except in this schema". I'm willing to
write that off as not being within the scope of a simple mechanism.

regards, tom lane

#80Stephen Frost
sfrost@snowman.net
In reply to: Tom Lane (#79)
Re: [PATCH] DefaultACLs

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

Stephen Frost <sfrost@snowman.net> writes:

This doesn't actually address the entire problem. How about
schema-level default grants which you want to override with per-role
default grants? Or the other way around? Is it always only more
permissive with more defaults? Even when the grantee is the same?

Well, bear in mind that we're *only* going to allow these things
per-role, so as to avoid the problem of translating ACLs to a different
grantor. So the main case that's not being solved is "I'd like to
grant privileges XYZ everywhere except in this schema". I'm willing to
write that off as not being within the scope of a simple mechanism.

Erm, wait, we're going to drop the only piece of this that outside folks
have actually been asking for? Specifically, having per-schema default
ACLs? Big -1 for even bothering to add the complexity if we're not
going to address what end users are actually looking for. Perhaps I
missed where the issue with assigning the right grantor was, but that
feels very much like an implementation detail we can certainly solve and
document which way we decided to solve it (either schema owner, which
would be my preference, or object creator, which would be acceptable as
well).

That's my thoughts on it.

Thanks,

Stephen

#81Tom Lane
tgl@sss.pgh.pa.us
In reply to: Stephen Frost (#80)
Re: [PATCH] DefaultACLs

Stephen Frost <sfrost@snowman.net> writes:

Erm, wait, we're going to drop the only piece of this that outside folks
have actually been asking for? Specifically, having per-schema default
ACLs?

They are per-schema for the objects belonging to the granting user.
Otherwise you have a bunch of nasty issues, including the prospect
of non-superusers being able to control the privileges granted on
objects that don't belong to them.

regards, tom lane

#82Petr Jelinek
pjmodos@pjmodos.net
In reply to: Tom Lane (#81)
Re: [PATCH] DefaultACLs

Tom Lane wrote:

Stephen Frost <sfrost@snowman.net> writes:

Erm, wait, we're going to drop the only piece of this that outside folks
have actually been asking for? Specifically, having per-schema default
ACLs?

They are per-schema for the objects belonging to the granting user.
Otherwise you have a bunch of nasty issues, including the prospect
of non-superusers being able to control the privileges granted on
objects that don't belong to them.

Also it's not really a problem since superuser or role admin can set
these for other roles.

--
Regards
Petr Jelinek (PJMODOS)

#83Robert Haas
robertmhaas@gmail.com
In reply to: Tom Lane (#77)
Re: [PATCH] DefaultACLs

On Thu, Oct 1, 2009 at 1:37 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Petr Jelinek <pjmodos@pjmodos.net> writes:

because it seems like merging privileges seems to be acceptable for most
(although I am not sure I like it, but I don't have better solution for
managing conflicts), I changed the patch to do just that.

It's not clear to me whether we have consensus on this approach.
Last chance for objections, anyone?

The main argument I can see against doing it this way is that it doesn't
provide a means for overriding the hard-wired public grants for object
types that have such (principally functions).  I think that a reasonable
way to address that issue would be for a follow-on patch that allows
changing the hard-wired default privileges for object types.  It might
well be that no one cares enough for it to matter, though.  I think that
in most simple cases what's needed is a way to add privileges, not
subtract them --- and we're already agreed that this mechanism is only
meant to simplify simple cases.

I'm going to reiterate what I suggested upthread... let's let the
default, global default ACL contain the hard-wired privileges, instead
of making them hardwired. Then your objects will get those privileges
not because they are hard-wired, but because you haven't changed your
global default ACL to not contain them.

...Robert

#84Petr Jelinek
pjmodos@pjmodos.net
In reply to: Robert Haas (#83)
Re: [PATCH] DefaultACLs

Robert Haas napsal(a):

On Thu, Oct 1, 2009 at 1:37 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Petr Jelinek <pjmodos@pjmodos.net> writes:

because it seems like merging privileges seems to be acceptable for most
(although I am not sure I like it, but I don't have better solution for
managing conflicts), I changed the patch to do just that.

It's not clear to me whether we have consensus on this approach.
Last chance for objections, anyone?

The main argument I can see against doing it this way is that it doesn't
provide a means for overriding the hard-wired public grants for object
types that have such (principally functions). I think that a reasonable
way to address that issue would be for a follow-on patch that allows
changing the hard-wired default privileges for object types. It might
well be that no one cares enough for it to matter, though. I think that
in most simple cases what's needed is a way to add privileges, not
subtract them --- and we're already agreed that this mechanism is only
meant to simplify simple cases.

I'm going to reiterate what I suggested upthread... let's let the
default, global default ACL contain the hard-wired privileges, instead
of making them hardwired. Then your objects will get those privileges
not because they are hard-wired, but because you haven't changed your
global default ACL to not contain them.

That's somewhat how I implemented it although not just on global level
but in any single filter, what we now have as defaults (before this
patch) is used as template for default acls and you can revoke it. You
just can't revoke anything you granted anywhere in the default acls chain.

--
Regards
Petr Jelinek (PJMODOS)

#85Petr Jelinek
pjmodos@pjmodos.net
In reply to: Petr Jelinek (#84)
1 attachment(s)
Re: [PATCH] DefaultACLs

Petr Jelinek napsal(a):

Robert Haas napsal(a):

I'm going to reiterate what I suggested upthread... let's let the
default, global default ACL contain the hard-wired privileges, instead
of making them hardwired. Then your objects will get those privileges
not because they are hard-wired, but because you haven't changed your
global default ACL to not contain them.

That's somewhat how I implemented it although not just on global level
but in any single filter, what we now have as defaults (before this
patch) is used as template for default acls and you can revoke it. You
just can't revoke anything you granted anywhere in the default acls chain.

Reminds me I forgot to adjust the docs. Attached patch fixes that (no
other changes).

--
Regards
Petr Jelinek (PJMODOS)

Attachments:

defacl-2009-10-02.diff.gzapplication/x-tar; name=defacl-2009-10-02.diff.gzDownload
#86Kevin Grittner
Kevin.Grittner@wicourts.gov
In reply to: Petr Jelinek (#85)
Re: [PATCH] DefaultACLs

Robert Haas <robertmhaas@gmail.com> wrote:

let's let the default, global default ACL contain the hard-wired
privileges, instead of making them hardwired.

+1

-Kevin

#87Josh Berkus
josh@agliodbs.com
In reply to: Kevin Grittner (#86)
Re: [PATCH] DefaultACLs

On 10/3/09 8:09 AM, Kevin Grittner wrote:

Robert Haas <robertmhaas@gmail.com> wrote:

let's let the default, global default ACL contain the hard-wired
privileges, instead of making them hardwired.

Wow, that would be great. It would meant that DBAs could change the
global default permissions.

--Josh

#88Tom Lane
tgl@sss.pgh.pa.us
In reply to: Petr Jelinek (#85)
Re: [PATCH] DefaultACLs

Petr Jelinek <pjmodos@pjmodos.net> writes:

[ latest default-ACLs patch ]

Applied with a fair amount of editorial polishing. Notably I changed
the permissions requirements a bit:

* for IN SCHEMA, the *target* role has to have CREATE permission on the
target schema. Without this, the command is a bit pointless since the
permissions can never be used. The original coding checked whether the
*calling* role had USAGE, which seems rather irrelevant.

* I simplified the target-role permission test to is_member_of. The
original check for ADMIN seemed pointlessly strong, because if you're a
member of the role you can just become the role and set owned objects'
permissions however you like. I didn't see the point of the CREATEROLE
exemption either, and am generally suspicious of anything that would let
people change permissions on stuff they didn't own.

One thing that seems like it's likely to be an annoyance in practice
is the need to explicitly do DROP OWNED BY to get rid of pg_default_acl
entries for a role to be dropped. But I can't see any very good way
around that, since the entries might be in some other database. One
thing that might at least reduce the number of keystrokes is to have
REASSIGN OWNED act as DROP OWNED BY for default ACLs. I can't convince
myself whether that's a good idea though, so I left it as-is for the
moment.

regards, tom lane

#89Tom Lane
tgl@sss.pgh.pa.us
In reply to: Josh Berkus (#87)
Re: [PATCH] DefaultACLs

Josh Berkus <josh@agliodbs.com> writes:

On 10/3/09 8:09 AM, Kevin Grittner wrote:

Robert Haas <robertmhaas@gmail.com> wrote:

let's let the default, global default ACL contain the hard-wired
privileges, instead of making them hardwired.

Wow, that would be great. It would meant that DBAs could change the
global default permissions.

Committed that way. One possible disadvantage down the road is that
people may now have the "default" privileges instantiated in their
databases, which will pose a hazard if we ever want to change the
default privilege sets. I imagine that we could have pg_upgrade go
through and modify the contents of pg_default_acl if we got in a bind
over this, but it's going to be something to think about.

regards, tom lane

#90Brendan Jurd
direvus@gmail.com
In reply to: Tom Lane (#88)
Re: [PATCH] DefaultACLs

2009/10/6 Tom Lane <tgl@sss.pgh.pa.us>:

Applied with a fair amount of editorial polishing.  Notably I changed
the permissions requirements a bit:

Thanks and congratulations! I'm really looking forward to this feature.

I pulled the latest sources and gave it a whirl. Things worked as
expected in psql, but I was a little surprised when I headed into the
documentation. The first place I visited was Chapter 20 - Database
Roles and Privileges, but there was no mention of the default ACLs
feature in there.

If you head into the SQL Reference, it's all there under ALTER DEFAULT
PRIVILEGES, but that's only helpful if you already know that it's
there and what the command is called. At the moment the only way
you'd find out about Default ACLs via the documentation is if you
noticed the link several paragraphs into the reference for GRANT.

Perhaps we should have something in Chapter 20 along the lines of
"Rather than tediously assigning privileges to objects every time you
create them, you can set default privileges on a schema" etc.

IMO we should be actively pointing people, especially Postgres/DBA
newbies, to this very useful functionality.

Cheers,
BJ

#91Petr Jelinek
pjmodos@pjmodos.net
In reply to: Tom Lane (#88)
Re: [PATCH] DefaultACLs

Tom Lane napsal(a):

Petr Jelinek <pjmodos@pjmodos.net> writes:

[ latest default-ACLs patch ]

Applied with a fair amount of editorial polishing. Notably I changed
the permissions requirements a bit:

Thank you very much Tom.

One thing that seems like it's likely to be an annoyance in practice
is the need to explicitly do DROP OWNED BY to get rid of pg_default_acl
entries for a role to be dropped. But I can't see any very good way
around that, since the entries might be in some other database. One
thing that might at least reduce the number of keystrokes is to have
REASSIGN OWNED act as DROP OWNED BY for default ACLs. I can't convince
myself whether that's a good idea though, so I left it as-is for the
moment.

Yeah I am not happy about this either but there is not much we can do
about it. Btw I think in the version I sent in REASSIGN OWNED acted as
DROP OWNED for default ACLs.

--
Regards
Petr Jelinek (PJMODOS)

#92Tom Lane
tgl@sss.pgh.pa.us
In reply to: Petr Jelinek (#91)
Re: [PATCH] DefaultACLs

Petr Jelinek <pjmodos@pjmodos.net> writes:

Tom Lane napsal(a):

One thing that seems like it's likely to be an annoyance in practice
is the need to explicitly do DROP OWNED BY to get rid of pg_default_acl
entries for a role to be dropped.

Yeah I am not happy about this either but there is not much we can do
about it. Btw I think in the version I sent in REASSIGN OWNED acted as
DROP OWNED for default ACLs.

IIRC it just threw a warning, which didn't seem tremendously useful to
me.

regards, tom lane

#93Tom Lane
tgl@sss.pgh.pa.us
In reply to: Brendan Jurd (#90)
Re: [PATCH] DefaultACLs

Brendan Jurd <direvus@gmail.com> writes:

I pulled the latest sources and gave it a whirl. Things worked as
expected in psql, but I was a little surprised when I headed into the
documentation. The first place I visited was Chapter 20 - Database
Roles and Privileges, but there was no mention of the default ACLs
feature in there.

I looked at that (as well as the material in section 5.6) and concluded
that it was a once-over-lightly presentation that didn't really need to
delve into this. But if you want to work it over, feel free to send in
a docs patch. Personally I'd like to see less duplication between 5.6
and 20.3, but I'm not quite sure how to refactor it --- the material is
arguably somewhat relevant in both places.

regards, tom lane

#94Petr Jelinek
pjmodos@pjmodos.net
In reply to: Tom Lane (#92)
Re: [PATCH] DefaultACLs

Tom Lane napsal(a):

Petr Jelinek <pjmodos@pjmodos.net> writes:

Tom Lane napsal(a):

One thing that seems like it's likely to be an annoyance in practice
is the need to explicitly do DROP OWNED BY to get rid of pg_default_acl
entries for a role to be dropped.

Yeah I am not happy about this either but there is not much we can do
about it. Btw I think in the version I sent in REASSIGN OWNED acted as
DROP OWNED for default ACLs.

IIRC it just threw a warning, which didn't seem tremendously useful to
me.

Oh did it ? Then I must have discarded that idea for some reason. I
probably didn't want to be too pushy there.

--
Regards
Petr Jelinek (PJMODOS)

#95Brendan Jurd
direvus@gmail.com
In reply to: Tom Lane (#93)
Re: [PATCH] DefaultACLs

2009/10/6 Tom Lane <tgl@sss.pgh.pa.us>:

Brendan Jurd <direvus@gmail.com> writes:

I pulled the latest sources and gave it a whirl.  Things worked as
expected in psql, but I was a little surprised when I headed into the
documentation.  The first place I visited was Chapter 20 - Database
Roles and Privileges, but there was no mention of the default ACLs
feature in there.

I looked at that (as well as the material in section 5.6) and concluded
that it was a once-over-lightly presentation that didn't really need to
delve into this.

Well 5.6 goes as far as to give mention about WITH GRANT OPTION, so I
don't think we'd be going too deep to give a pointer regarding default
ACLs as well.

I don't think we need a fully detailed explanation of default ACLs
here, just a brief mention and a pointer to the reference page.

But if you want to work it over, feel free to send in
a docs patch.

I'll have a play around with it and see if I can come up with wording
that I like.

Personally I'd like to see less duplication between 5.6
and 20.3, but I'm not quite sure how to refactor it --- the material is
arguably somewhat relevant in both places.

I hear you about the duplication. 20.3 is so brief that I'm quite
tempted to just remove it and instead have a paragraph referencing
GRANT and REVOKE somewhere in 20.1.

Cheers,
BJ

#96KaiGai Kohei
kaigai@ak.jp.nec.com
In reply to: Tom Lane (#88)
Re: [PATCH] DefaultACLs

I tried to check the default ACL behavior.

postgres=# \c - ymj
psql (8.5devel)
You are now connected to database "postgres" as user "ymj".
postgres=> ALTER DEFAULT PRIVILEGES REVOKE INSERT ON TABLE FROM ymj;
ALTER DEFAULT PRIVILEGES
postgres=> CREATE TABLE t2 (x int, y text);
CREATE TABLE
postgres=> SELECT relname, relacl FROM pg_class WHERE oid = 't2'::regclass;
relname | relacl
---------+------------------
t2 | {ymj=rwdDxt/ymj}
(1 row)

postgres=> INSERT INTO t2 VALUES (1, 'aaa');
ERROR: permission denied for relation t2

It works for me fine, good, but ...

postgres=> SELECT * INTO t3 FROM t1;
SELECT
postgres=> SELECT * FROM t3;
a | b
---+-----
1 | aaa
2 | bbb
(2 rows)

postgres=> INSERT INTO t3 VALUES (3,'ccc');
ERROR: permission denied for relation t3

In this case, the new table t3 is created with the default ACL which does not
allow to insert any values by the owner of the relation.

SELECT INTO does not check ACL_INSERT on the newly created tables, because
we had been able to assume the table owner always has privilege to insert
values into the new table.
So, OpenIntoRel() didn't check this obvious privilege.

But the default ACL feature breaks this assumption. The table owner may not
have privilege to insert values into new tables.
So, it is necessary to put actual access controls on the OpenIntoRel().

Thanks,

Tom Lane wrote:

Petr Jelinek <pjmodos@pjmodos.net> writes:

[ latest default-ACLs patch ]

Applied with a fair amount of editorial polishing. Notably I changed
the permissions requirements a bit:

* for IN SCHEMA, the *target* role has to have CREATE permission on the
target schema. Without this, the command is a bit pointless since the
permissions can never be used. The original coding checked whether the
*calling* role had USAGE, which seems rather irrelevant.

* I simplified the target-role permission test to is_member_of. The
original check for ADMIN seemed pointlessly strong, because if you're a
member of the role you can just become the role and set owned objects'
permissions however you like. I didn't see the point of the CREATEROLE
exemption either, and am generally suspicious of anything that would let
people change permissions on stuff they didn't own.

One thing that seems like it's likely to be an annoyance in practice
is the need to explicitly do DROP OWNED BY to get rid of pg_default_acl
entries for a role to be dropped. But I can't see any very good way
around that, since the entries might be in some other database. One
thing that might at least reduce the number of keystrokes is to have
REASSIGN OWNED act as DROP OWNED BY for default ACLs. I can't convince
myself whether that's a good idea though, so I left it as-is for the
moment.

regards, tom lane

--
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai@ak.jp.nec.com>

#97Petr Jelinek
pjmodos@pjmodos.net
In reply to: KaiGai Kohei (#96)
Re: [PATCH] DefaultACLs

KaiGai Kohei napsal(a):

I tried to check the default ACL behavior.

It works for me fine, good, but ...

postgres=> SELECT * INTO t3 FROM t1;
SELECT
postgres=> SELECT * FROM t3;
a | b
---+-----
1 | aaa
2 | bbb
(2 rows)

postgres=> INSERT INTO t3 VALUES (3,'ccc');
ERROR: permission denied for relation t3

In this case, the new table t3 is created with the default ACL which does not
allow to insert any values by the owner of the relation.

SELECT INTO does not check ACL_INSERT on the newly created tables, because
we had been able to assume the table owner always has privilege to insert
values into the new table.
So, OpenIntoRel() didn't check this obvious privilege.

But the default ACL feature breaks this assumption. The table owner may not
have privilege to insert values into new tables.
So, it is necessary to put actual access controls on the OpenIntoRel().

That's strange behavior I agree. However I don't see how default ACLs
changed it in any way, owner could REVOKE his privileges before.

--
Regards
Petr Jelinek (PJMODOS)

#98Petr Jelinek
pjmodos@pjmodos.net
In reply to: Petr Jelinek (#94)
Re: [PATCH] DefaultACLs

Petr Jelinek napsal(a):

Tom Lane napsal(a):

Petr Jelinek <pjmodos@pjmodos.net> <mailto:pjmodos@pjmodos.net> writes:

Tom Lane napsal(a):

One thing that seems like it's likely to be an annoyance in practice
is the need to explicitly do DROP OWNED BY to get rid of pg_default_acl
entries for a role to be dropped.

Yeah I am not happy about this either but there is not much we can do
about it. Btw I think in the version I sent in REASSIGN OWNED acted as
DROP OWNED for default ACLs.

IIRC it just threw a warning, which didn't seem tremendously useful to
me.

Oh did it ? Then I must have discarded that idea for some reason. I
probably didn't want to be too pushy there.

Now I remember why - consistency with ACLs on object. REASSIGN OWNED
does not drop any GRANTed ACLs on any object, so it seemed appropriate
to only drop default ACLs in DROP OWNED BY along with ACLs on objects.

--
Regards
Petr Jelinek (PJMODOS)

#99KaiGai Kohei
kaigai@kaigai.gr.jp
In reply to: Petr Jelinek (#97)
Re: [PATCH] DefaultACLs

Petr Jelinek wrote:

KaiGai Kohei napsal(a):

I tried to check the default ACL behavior.

It works for me fine, good, but ...

postgres=> SELECT * INTO t3 FROM t1;
SELECT
postgres=> SELECT * FROM t3;
a | b
---+-----
1 | aaa
2 | bbb
(2 rows)

postgres=> INSERT INTO t3 VALUES (3,'ccc');
ERROR: permission denied for relation t3

In this case, the new table t3 is created with the default ACL which does not
allow to insert any values by the owner of the relation.

SELECT INTO does not check ACL_INSERT on the newly created tables, because
we had been able to assume the table owner always has privilege to insert
values into the new table.
So, OpenIntoRel() didn't check this obvious privilege.

But the default ACL feature breaks this assumption. The table owner may not
have privilege to insert values into new tables.
So, it is necessary to put actual access controls on the OpenIntoRel().

That's strange behavior I agree. However I don't see how default ACLs
changed it in any way, owner could REVOKE his privileges before.

I don't think the default ACL feature should do something ad-hoc here.

Is there anything necessary more than adding permission checks to insert
values into the new table?

Thanks,
--
KaiGai Kohei <kaigai@kaigai.gr.jp>

#100Alvaro Herrera
alvherre@commandprompt.com
In reply to: Petr Jelinek (#98)
Re: [PATCH] DefaultACLs

Petr Jelinek escribi�:

Petr Jelinek napsal(a):

Tom Lane napsal(a):

Petr Jelinek <pjmodos@pjmodos.net> <mailto:pjmodos@pjmodos.net> writes:

Tom Lane napsal(a):

One thing that seems like it's likely to be an annoyance in practice
is the need to explicitly do DROP OWNED BY to get rid of pg_default_acl
entries for a role to be dropped.

Yeah I am not happy about this either but there is not much we
can do about it. Btw I think in the version I sent in REASSIGN
OWNED acted as DROP OWNED for default ACLs.

IIRC it just threw a warning, which didn't seem tremendously useful to
me.

Oh did it ? Then I must have discarded that idea for some reason.
I probably didn't want to be too pushy there.

Now I remember why - consistency with ACLs on object. REASSIGN OWNED
does not drop any GRANTed ACLs on any object, so it seemed
appropriate to only drop default ACLs in DROP OWNED BY along with
ACLs on objects.

That seems reasonable to me too ...

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

#101Tom Lane
tgl@sss.pgh.pa.us
In reply to: Petr Jelinek (#97)
Re: [PATCH] DefaultACLs

Petr Jelinek <pjmodos@pjmodos.net> writes:

KaiGai Kohei napsal(a):

SELECT INTO does not check ACL_INSERT on the newly created tables, because
we had been able to assume the table owner always has privilege to insert
values into the new table.
So, OpenIntoRel() didn't check this obvious privilege.

But the default ACL feature breaks this assumption. The table owner may not
have privilege to insert values into new tables.
So, it is necessary to put actual access controls on the OpenIntoRel().

That's strange behavior I agree. However I don't see how default ACLs
changed it in any way, owner could REVOKE his privileges before.

The point is that some rows got into the new table, which should have
been disallowed if CREATE TABLE AS is considered to represent a CREATE
followed by an INSERT. However, I disagree that this necessarily
represents a bug in the permissions checks. We could perfectly well
define that INSERT means the right to insert into the table *after it is
created*, and that CREATE TABLE AS does not involve this right. I think
that that is a reasonable and potentially useful thing to do, whereas
defining it as KaiGai-san suggests would have no usefulness whatsoever.
What's more, CREATE TABLE AS is specified in the SQL standard, and I see
nothing in the standard mentioning that it requires INSERT privilege.

regards, tom lane

#102KaiGai Kohei
kaigai@ak.jp.nec.com
In reply to: Tom Lane (#101)
Re: [PATCH] DefaultACLs

Tom Lane wrote:

Petr Jelinek <pjmodos@pjmodos.net> writes:

KaiGai Kohei napsal(a):

SELECT INTO does not check ACL_INSERT on the newly created tables, because
we had been able to assume the table owner always has privilege to insert
values into the new table.
So, OpenIntoRel() didn't check this obvious privilege.

But the default ACL feature breaks this assumption. The table owner may not
have privilege to insert values into new tables.
So, it is necessary to put actual access controls on the OpenIntoRel().

That's strange behavior I agree. However I don't see how default ACLs
changed it in any way, owner could REVOKE his privileges before.

The point is that some rows got into the new table, which should have
been disallowed if CREATE TABLE AS is considered to represent a CREATE
followed by an INSERT. However, I disagree that this necessarily
represents a bug in the permissions checks. We could perfectly well
define that INSERT means the right to insert into the table *after it is
created*, and that CREATE TABLE AS does not involve this right. I think
that that is a reasonable and potentially useful thing to do, whereas
defining it as KaiGai-san suggests would have no usefulness whatsoever.
What's more, CREATE TABLE AS is specified in the SQL standard, and I see
nothing in the standard mentioning that it requires INSERT privilege.

It is also an issue due to the differences in perspectives and security models.

My major concern is come from the viewpoint of MAC which tries to control
data flows based on sensitivity levels.
So, if the default PG model considers that "CREATE TABLE AS" is an atomic
operation, not a pair of CREATE and INSERTs, I think it is an approach to
understand this behavior.
In my preference, this perspective should be documented somewhere.
(source code comments or official documentation?)

BTW, in the MAC model, we must strictly prevent a user with classified
security level to write any data into objects with unclassified security
level. It is called "write-down", being equivalent to information leaks.

Thanks,
--
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai@ak.jp.nec.com>