Subject: Re: SET ROLE documentation not entirely correct
From: Joe Conway <mail@joeconway.com>
To: Steven Winfield <Steven.Winfield@cantabcapital.com>,
 "pgsql-docs@lists.postgresql.org" <pgsql-docs@lists.postgresql.org>
References: <155603476764.1372.10888362840885095061@wrigleys.postgresql.org>
 <03796ce5-8366-cd32-0436-9981ffbeb993@joeconway.com>
 <E9FA92C2921F31408041863B74EE4C2001BD62E4D4@CCPMAILDAG03.cantab.local>
 <c02a61b1-3daf-672c-f750-4198bb312445@joeconway.com>
Openpgp: preference=signencrypt
Autocrypt: addr=mail@joeconway.com; keydata=
 mQINBEpXMCsBEADDnXUQzjlyi/cX02Gtdy2CLcroE5CsC7DJKdOBDbfgn0kfiIYoV5JniG4l
 VyzZUodY8yUAagqLYolh0UkBzs9N+qkm7erde4ypw3jzVQ37BuzIvk3nMUbuDZDgxWqX+nVS
 sKc+BQ5BpzgCHg48leoRO2ohjvYnUhgH3j2rFZCzaj6qQ7mv+XoxOJmUlVQtG06Jwkk7Vu14
 7U9nMMM6hyUKzVnmCphnlcMNo26UyVU70MwFfFJgcI0c5fpp8byN56eD6VJVnufO5WAuEhzE
 qcrSJR2FAlmM90GBY+6vP29twLDCHuSFvrnujNCx/BvCC/a3/gPvyAFp4JtMm9eXAmq3m/Kw
 94nTJXVdcbQeQQDp3KIG7MmWS4lnGvPn8v0CjgNaLvZXFLo1FgmUVsyEq1Lww4iRLa6sbpXJ
 ESx15UEue1k1YZM9C+4F/o3aeKNsAienjw2EXFzcaxIg/C4P493VMi3Qa8ycVxR5iYhUbYdo
 DFIUQhbFNsYfrtW/qZAELT3FCYFpZYG01e9Hj+cBrXXgyDDkQ5Lq4mlvmkRvuxn61V6Au4HA
 0sJiCox5pM1FvzT+aI8HY1BYaiB9Pl4fhpKgmhhlSuglk9v39S4jmlUIb45iLAUVpeNM6Qjm
 69pf5da9sm4aGFa7YlDSKf/WcU7z9ITZxsilOi2n7YJiwG7kTQARAQABtB9Kb2UgQ29ud2F5
 IDxtYWlsQGpvZWNvbndheS5jb20+iQI8BBMBAgAmAhsDBwsJCAcDAgEGFQgCCQoLBBYCAwEC
 HgECF4AFAlpoksUCGQEACgkQMyt+aLaZQ0p4XQ/9F4WSf4ZFuL3ae356ApVqnLxphHXeXHli
 ePZe7qosvXMQLN9Y2m5cQwqojsQh4pZujIi4Iwxw7ORg6HbMO8dMhyu8TlDavErcgO561hG7
 /TCcCLf5SkadxBqYWAJH09W8S2iC3gQtS+qs4Y7sIvRi881EsPzG5Lrzu7Ei8ufqsSnbLUQk
 zAaKjK3IzAFvzA3VCg/8Aqhf8obhqL0j5I5DNOpAt2U4gkEzIrX1g5xSXpRLMHLEK7hPViY8
 pFQyRvyZioE4hckNsnidCgiv4HefJUFRIVxDkHBeLhTzqTPloesFT/bskhckIS6F4suMx044
 WTTW6UiARuWw3aaBTmxay2rjPCsGaJPawzY0O3HlNlZherCzuBpa+Qk1kMLkcxMlYjCrMgIR
 YyBpIK46ltPQaog4Ujh21w1Z8m/ufG5v/HTuLdXM9hGfTlW7aY6psCAV/MwfycDHLRMc+UY7
 W2cp6DlWQ7GHLP+vzvBcl5Odi04EhcYnHMZjKvRFsxhE0LZcTejrtxPkDgR9G59irlIx1+py
 ziK2B0hs7Er1Yq7qQGQiQGLoglSNDsvsUeyMGyabasmUd5xrRO35SpGv+hyYGAZ3Pyk9Vtxl
 CO7BJkZiV9YSMHp6hOdmYZd4SZRChd9sKd6eSw1xIlBALz1ZHuQVMBuZvuVGFsS81Zqckb/2
 D5q5Ag0ESlcx7QEQAKhm5UhGfbe3hM4yCaa/pPaNaswlB2tK1DHAjDlDm7gDVEodfsrvAFUU
 unLodlvCd0g/GpLmXXdROU/I+HusVqQhDbVeHMwO2VUVHamRWqDfrCjMYVxLz80d96TYyeyW
 6RUNkJmXJAOKgy8veI7Z1F+j1gXQR7UlzonnSkWbs0oQ3RglvyqhkUzWtXU7F+j2hQ2vQsct
 Lnq3lYo0bYkZ3K6RyKZXSly9Hd7DzYkMzz7StNRlyeQJxplPrVPup7N8SqDrI126sq0cwdUB
 Gn1sKVoO+bGidBjhJgxouNPkMJlPjbKOgb7tv5ZnM33FGXNdC5sPKoZ4mC+gaqRHYaH/V61v
 fCo1CYNLIK+Cj+Oua8mQ6CPeOb5VKcJ0JM96eGNlzF9EawppOG7xCDcbmBEr0m1go9MHEUel
 0L1i8RSDYOHQQkLT2MVWYf4UsRlEGbME+kF7DEX9Fvie7qglFTXC2d+YVN73AnXcoM/IYUOR
 DXjVVaqQoHuA4uxwJ31o/rEaOihr2yCScr8mB8J3b3KyNfuCBm5gN0tUQw+t5szMYurDpZ1G
 L4O7btaQ5u4iJMfRTCnPrzW+wkdVrao7AmRNWEtSlXsycClKFCHz/Ej5rpXOKSp+a/xcaFX1
 v2eS/+Ujp5ESu9p57JwlP6aSqYBiOALRJXZ9DVtZL96okjzXjrd7ABEBAAGJBD4EGAEIAAkF
 AkpXMe0CGwICKQkQMyt+aLaZQ0rBXSAEGQEIAAYFAkpXMe0ACgkQN/L3QzX32GV1Jw//fhvL
 Mk1YNS/2N5jc5HYmEjcL2OWBLLrO5RMCaOgCNmYsepkigy2U+GSloVy8Y40/UfYzIoNf1DiB
 Qyj5Ipa4eMjfKA7+1q43a5wkDDWogFNflKTTNemwrHDKR1WWDrxt8uAgJdlBGDQA7A0q3D2i
 Xhd7ALLOHhuXDY1++N9TK9KMfV90Oay0mIMLQhTG/UgsgrYYb78H2pk8XMYZY29vgZlj9VH2
 f1RpOoh7lCaB+2glN27Rzh17q0/zEjwXk4sDyYHRIycaqnM4ozSnliXyoWM5bvmqSGBCm5jl
 BjNW+cFbFy15U595AIXXiUXl8hsTF2JWwgHr+hYdtoEH3oEOxzM18ENXKTTgN+BafdMsah4e
 HIcVvrVrH6rlsPwCvk3o/PJ07qVbUW+dbZG8UB1JwqUUqGhrCk87kztajlvZGZWxWDSB4Lnb
 T6Jc0L7/YXG8U3sJ2OtZ95Lal5XfNo0yBdPflmalH3xf/EcxaYFETbDrY5UgEVa2HBw8XzKk
 rTbM8rD318M+jBOuL6o3cGMOeFOm4C51fJZmOCFd03LhMkoj9IbLIPGKosIBsxb7zP5YLAbI
 b3el6pvo9CKtNuR+IcVOoFtH+KVOxGsbbxFEFbsAmPMreF1hIv+l+HXBaDhUkaUmpJ1Mhxol
 aM6O/LGG4VQV7wBQFunVaN8EPqMqYDph3A//TjQVd8fqvw5Ezj1958IUmV0jxeGX2m/fspGh
 njF+6Adx3PFbZoKyW71cA/TnlHVYCaizjYYRNMC3g5VFytOYOZFi0GKsxQTX5PcPhDjFFTUv
 FVVZwDYUK3QlC+wGDTeNWfAJIZnTPPIXSiHzh3si+ngKjE+ngcRF1VY5K4xvc6ss6uq8kI8L
 Ct+TpOMr99ijaYEbMZ8WJagynD5BLNYxpP6CCTMDi1Xqydg0E/mhR9w9aUOvdTZw1OOKtLnz
 B1E0wFbzJXEvjP2eEVNlStS5RTawFDpqxGwXJcHqe8qtg8B+C1r47RmHnKKm+DTOPFQWTj83
 l775aNMzAHm+Pr1TdSA5XRyQ3wxwYKfhp1suKhILUZLjbiCQf92RZQ6yEdXskMr2A4n0GQWq
 Q8k7r9QPqtUP7lsiyyDem7r9h5mAm94r3Td22L3JaSHkBD+4jC4gIXeYoyd+lTz/zYOx0Fri
 J8dXHalmyTGsSxV7FXv1/thVEf6R7TDdVwhqf/WL3uWTmVBR0tJscG+9iCVch/OG/uCDkWZQ
 sOTotqj6f5jSZI+NOUqt62+8i8gjYEa7zRZ2HXFOTV8UduCjheWB2VCUj5EjFjTMjfTVkyZ5
 SiFht7bM5l/odoYU6vn37/5s/ZsMTZX1zG5oXv6iULrHLYHhUxR93FlVyn4DiHsWND/Idj65
 Ag0ESlcyJwEQAOkTBb9yDhJbMUgvhM11rZwT5tm4Y9TqtEHn0Zy3t9g7bdFFpMvav/KENd3o
 AtLFpMDf+H3AggFk4ftUwJwiVgJ88ilvCynJUGXiuYIaexY4DLgn4xpnuiEpYEFVdWnlw7dW
 VTc62exfqIz9bSWRzwfBCY9ruYGEb4RDPDSNSAVyI7sxHzef2asiYxIcxrTrw5VugWNlPZcV
 5/EJ6PUvATjBF2TBkXV7KOciQng2tsQGrGMkY5mduNqwpuh6zfPcVF8LeObe96wv5ZhPRpO7
 9nef7hnK2lJogp3JIo558Jlbz9WHtQEMZR85+bUhtI825QyNAFz3Jrn7NMgvDikc2OrWo7YM
 gMC5hDSWVFqA6/EQCNnDWGABWgeYHZFpnPwsvUWIYdhSilUuj/Tuzvz9ZmucFNbQbauDQw6V
 Q38ofGnoYDZFJsGncprB8dBi4tDrIQ+1RlIh6C2Z/eMipqJOT26+spluTjouvnKT0S5yOgyX
 0PjbsysgwQdCGNJLHOjhHbSpSmOLaduV3CQo/0+DHT/TBjYfIXjTWouY9TkGxG4eNrxU0u2x
 Ay5bMqOPmsFdjLTWlQUlF/fTMhB54XwI3FHWgnSnXZzStDTmTebLNdT/ftgliAzA81uMj49j
 0exv731/v+7udLA1bV8gnZ01zQCASDpWiRQR3fgwcugSUqgRABEBAAGJAh8EGAEIAAkFAkpX
 MicCGwwACgkQMyt+aLaZQ0pwAQ//bjcWnZg/jjRQ9gbZUGMqniItZYRglBMKIqt4Fia379Jm
 HwTvavnFkJ8XMZ56UB0FIrgS+sUkRH6cPRQR+7Qi392LD021DXgSsz9CwFHjFyBGHwLEOTRc
 fYQbtJy0shHDJB4aQTOX3ERDH1PsvJNuevmQMzS0DWFav9+xMz9rKP4N+HffoBIZE0C1xIE4
 3nD4eLsbycte9sVIrmlNuUti3qUxJAQw8HwfJ6ZbBInHxquApR16uD1u99o6XlndFrDlY22t
 RmHCM0bR81GfGNdcU3Uo+rG/R/k4qa7s9/dgKvMbyH3fHhp/ceKag80Xo8IFurRl0ZJP3sHJ
 2QDHCVLat7jRZ+43hi1WlIhFbrgn6IyI0i7XR/W8JjrC5MsKq4TUwGH077sU/kcHYebVJZRb
 UUst2hAGHDFVBcG12qoKf+ltL9qXJc1y7BGeCoUW6QjOpljpq6ZL4FQUsM0RSRjs5egE3szP
 cIf5SyPK6WDOApoAq6M7BBFMGDZwEylYMtr0YekA1u86UA9D2xwLHEbBBp/uiby1c9JbPJ1P
 n8zJP8WZNeRw4Q9TtqVK09+oLirMUSpIDd6KdZ1VgRxOK2re7tjDvkVuYsSrsiJ+1iJNEnp9
 iK0ok0DlJpSCe6KhkxpaTdeoWMXdKuJWec0NIqoAd54ZgBPnr+UPxTixgPq/p6Q=
Message-ID: <95864cbc-4548-2cc5-86de-2a41641ee9d1@joeconway.com>
Date: Thu, 1 Aug 2019 14:48:30 -0400
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.8.0
MIME-Version: 1.0
In-Reply-To: <c02a61b1-3daf-672c-f750-4198bb312445@joeconway.com>
Content-Type: multipart/signed; micalg=pgp-sha512;
 protocol="application/pgp-signature";
 boundary="qvRV9VQW6ozUELoQFzuo9e7dwOFsUrsza"
Precedence: bulk

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--qvRV9VQW6ozUELoQFzuo9e7dwOFsUrsza
Content-Type: multipart/mixed; boundary="DzENM2QL7tST31SbNPKJRRacWhLRa9Gag";
 protected-headers="v1"
From: Joe Conway <mail@joeconway.com>
To: Steven Winfield <Steven.Winfield@cantabcapital.com>,
 "pgsql-docs@lists.postgresql.org" <pgsql-docs@lists.postgresql.org>
Message-ID: <95864cbc-4548-2cc5-86de-2a41641ee9d1@joeconway.com>
Subject: Re: SET ROLE documentation not entirely correct
References: <155603476764.1372.10888362840885095061@wrigleys.postgresql.org>
 <03796ce5-8366-cd32-0436-9981ffbeb993@joeconway.com>
 <E9FA92C2921F31408041863B74EE4C2001BD62E4D4@CCPMAILDAG03.cantab.local>
 <c02a61b1-3daf-672c-f750-4198bb312445@joeconway.com>
In-Reply-To: <c02a61b1-3daf-672c-f750-4198bb312445@joeconway.com>

--DzENM2QL7tST31SbNPKJRRacWhLRa9Gag
Content-Type: multipart/mixed;
 boundary="------------011905E1D0D138C7CC38DD06"
Content-Language: en-US

This is a multi-part message in MIME format.
--------------011905E1D0D138C7CC38DD06
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

On 4/27/19 4:02 PM, Joe Conway wrote:
> On 4/25/19 11:25 AM, Steven Winfield wrote:
>> Again, that's much clearer than what is currently there. It might help=

>> if some of the language/definitions from pg_has_role() is used, though=
=2E
>>=20
>> For example:
>> A role X is a "MEMBER" of another role Y if there is a chain of GRANTs=

>> from X to Y via zero or more intermediate roles. This allows X to
>> execute "SET ROLE Y".
>> Additionally X has "USAGE" of Y if X and all the intermediate roles (b=
ut
>> *not* necessarily Y) are marked INHERIT. In this case X automatically
>> has the privileges of Y, without the need to "SET ROLE Y".
>=20
> I've been whacking this around for the better part of the afternoon and=

> came up with the attached. I think it is correct, and better than my
> previous proposal, but possibly need more polish. Comments welcome.


I've been sitting on this change a while and want to get it pushed.

Steven Winfield seemed happy with it -- any other comments before I
commit? Also this seems like it ought to be back-patched, but any
thoughts on that?

Thanks,

Joe

--=20
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

--------------011905E1D0D138C7CC38DD06
Content-Type: text/x-patch;
 name="set-role-docs-00.diff"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
 filename="set-role-docs-00.diff"

diff --git a/doc/src/sgml/ref/set_role.sgml b/doc/src/sgml/ref/set_role.s=
gml
index 0ef6eb9..d219a1e 100644
*** a/doc/src/sgml/ref/set_role.sgml
--- b/doc/src/sgml/ref/set_role.sgml
*************** RESET ROLE
*** 42,48 ****
 =20
    <para>
     The specified <replaceable class=3D"parameter">role_name</replaceabl=
e>
!    must be a role that the current session user is a member of.
     (If the session user is a superuser, any role can be selected.)
    </para>
 =20
--- 42,48 ----
 =20
    <para>
     The specified <replaceable class=3D"parameter">role_name</replaceabl=
e>
!    must be a role that the current session user is a <literal>MEMBER</l=
iteral> of.
     (If the session user is a superuser, any role can be selected.)
    </para>
 =20
*************** RESET ROLE
*** 63,78 ****
    <title>Notes</title>
 =20
    <para>
!    Using this command, it is possible to either add privileges or restr=
ict
!    one's privileges.  If the session user role has the <literal>INHERIT=
S</literal>
!    attribute, then it automatically has all the privileges of every rol=
e that
!    it could <command>SET ROLE</command> to; in this case <command>SET R=
OLE</command>
!    effectively drops all the privileges assigned directly to the sessio=
n user
!    and to the other roles it is a member of, leaving only the privilege=
s
!    available to the named role.  On the other hand, if the session user=
 role
!    has the <literal>NOINHERITS</literal> attribute, <command>SET ROLE</=
command> drops the
!    privileges assigned directly to the session user and instead acquire=
s the
!    privileges available to the named role.
    </para>
 =20
    <para>
--- 63,94 ----
    <title>Notes</title>
 =20
    <para>
!    Role "X" is a <literal>MEMBER</literal> of role "Y" if there is a ch=
ain of
!    grants between them. Additionally, role "X" has <literal>USAGE</lite=
ral>
!    of "Y", if "X", and all intermediate roles (but not necessarily "Y" =
itself),
!    are marked with the <literal>INHERIT</literal> attribute (which is t=
he default).
!    See the <link linkend=3D"functions-info"><function>pg_has_role()</fu=
nction>
!    </link> for more information.
!   </para>
!=20
!   <para>
!    Every role automatically has all the privileges it has been granted =
directly,
!    as well as that of every role with which it has <literal>USAGE</lite=
ral>.
!    However role attributes (as defined by <command>CREATE ROLE</command=
> or
!    <command>ALTER ROLE</command>) are not directly acquired from other =
roles.
!    Role attributes may only be gained via the <command>SET ROLE</comman=
d> command.
!   </para>
!=20
!   <para>
!    The <command>SET ROLE</command> command drops all privileges assigne=
d directly
!    to the session user and instead acquires the privileges available to=
 the target
!    role, including any roles for which the target has <literal>USAGE</l=
iteral>.
!    The net effect is that the <command>SET ROLE</command> command may e=
ither add
!    privileges or restrict privileges. When the session role has
!    <literal>USAGE</literal> on the target role, the remaining privilege=
s will be
!    equal to, or a subset of, the original privileges. On the other hand=
,
!    when the session role does not have <literal>USAGE</literal> on the =
target
!    role, the privileges may be completely different, and possibly expan=
ded.
    </para>
 =20
    <para>

--------------011905E1D0D138C7CC38DD06--

--DzENM2QL7tST31SbNPKJRRacWhLRa9Gag--

--qvRV9VQW6ozUELoQFzuo9e7dwOFsUrsza
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAEBCgAdFiEEg14x9eymXoJyHrH+N/L3QzX32GUFAl1DM/4ACgkQN/L3QzX3
2GUWzQ//acXs5OFNvxTkN7BCI7s1M3roxEYnbHxo2ceK9aOPSX7bC3xpk/y2Hake
sBEsBisXknuW/nep2NJV+aP5YCzc5nE7/mLj91xzXBDBooj4QYEUS3NCCDZXyo4k
ZcDKnS1dBeFByswZRgwpMUtLpYPqtPrQKBGSvL2ubqpSGBFG3n+Lwkqs1sXMxi4s
jBFpDB8W1iTrcif3E4hJeVqwmo2EZSaCWOnT1m3dmjrJjnsU9pYCiHBFTLwxOfpy
auzoRq39D/YOdkmeT3ZYr7byYZ9IKXsfOESqoey+jfBLmn3RQbW67+ORxqirhV9Z
l7Yp1dzJEDUI6EawJ6cvJr437FKSyxTRdkXrS4t08U4y1SdU43SoIa31R0bUiQMi
fH5wmMNmXowaWgXp+7UkwXR+1zgw4amoMG9ywIAxQtsP3lq8tK7O/caWC7WwjdG5
Nwmk2KVkBK4641tfKYS6gYoReb1Wo1rwjyPotmiPFc0fJvy3oLSWMQdr+GbVTCrk
WvgiVeo004nNE2wsAyJXnrQGmQ+d2/Qw+uUZbUyYVBFPmQfrBITGf+LsnDeCI0oC
JEZSj0cZ4eB9+2A/m6rs2F09R1SxaiSYgpYGJgCC3cRVzmt6G4ZA3VCC3D8KXBXO
R02NLnLEj4YAr5zu08/Zzjgm6rvHyn+U48d2HrrolNzL47xEby4=
=yQyP
-----END PGP SIGNATURE-----

--qvRV9VQW6ozUELoQFzuo9e7dwOFsUrsza--