pgsql: Handle policies during DROP OWNED BY

Handle policies during DROP OWNED BY

DROP OWNED BY handled GRANT-based ACLs but was not removing roles from
policies. Fix that by having DROP OWNED BY remove the role specified
from the list of roles the policy (or policies) apply to, or the entire
policy (or policies) if it only applied to the role specified.

As with ACLs, the DROP OWNED BY caller must have permission to modify
the policy or a WARNING is thrown and no change is made to the policy.

Branch
------
master

Details
-------
http://git.postgresql.org/pg/commitdiff/833728d4c8832f1d37e7aeaa723c8bc4045df32e

Modified Files
--------------
src/backend/catalog/pg_shdepend.c | 13 ++
src/backend/commands/policy.c | 256 +++++++++++++++++++++++++++++
src/include/commands/policy.h | 2 +
src/test/regress/expected/rowsecurity.out | 14 ++
src/test/regress/sql/rowsecurity.sql | 18 ++
5 files changed, 303 insertions(+)

On 12/11/15 4:12 PM, Stephen Frost wrote:
> As with ACLs, the DROP OWNED BY caller must have permission to modify
> the policy or a WARNING is thrown and no change is made to the policy.

That warning doesn't tell the user anything about how to fix the
situation or whether or why the situation is a problem and what to do
about it.

* Peter Eisentraut (peter_e(at)gmx(dot)net) wrote:
> On 12/11/15 4:12 PM, Stephen Frost wrote:
> > As with ACLs, the DROP OWNED BY caller must have permission to modify
> > the policy or a WARNING is thrown and no change is made to the policy.
>
> That warning doesn't tell the user anything about how to fix the
> situation or whether or why the situation is a problem and what to do
> about it.

I modeled it after the other warnings which are output by DROP OWNED BY
when it's unable to perform the requested drop. I'm not against trying
to add something, but you tend to get a bunch of those messages at once
which means having a hint would result in a bunch of repeated messages
and I don't think that'd be very helpful. Further, it's essentially a
'permission denied' type of error, which generally means that the
individual who is running it can't do anything to fix it anyway.

I'm not against looking to improve things here, but I don't think just
trying to make a change here makes sense. We could throw a warning+hint
at the end of DROP OWNED, if anything wasn't able to be dropped, which
provided more information, perhaps. I'm not convinced that would really
be very useful to the individual running the command and would need to,
in essence, be "please get someone with higher privileges to run this,
or get them to give you permission to run it". I don't think we really
want to go there (anyone else recall the "please see your network
administrator" errors..?).

If I'm misunderstanding your thoughts here, please let me know.

Thanks!

Stephen

On Sat, Dec 12, 2015 at 4:35 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Peter Eisentraut (peter_e(at)gmx(dot)net) wrote:
>> On 12/11/15 4:12 PM, Stephen Frost wrote:
>> > As with ACLs, the DROP OWNED BY caller must have permission to modify
>> > the policy or a WARNING is thrown and no change is made to the policy.
>>
>> That warning doesn't tell the user anything about how to fix the
>> situation or whether or why the situation is a problem and what to do
>> about it.
>
> I modeled it after the other warnings which are output by DROP OWNED BY
> when it's unable to perform the requested drop. I'm not against trying
> to add something, but you tend to get a bunch of those messages at once
> which means having a hint would result in a bunch of repeated messages
> and I don't think that'd be very helpful. Further, it's essentially a
> 'permission denied' type of error, which generally means that the
> individual who is running it can't do anything to fix it anyway.
>
> I'm not against looking to improve things here, but I don't think just
> trying to make a change here makes sense. We could throw a warning+hint
> at the end of DROP OWNED, if anything wasn't able to be dropped, which
> provided more information, perhaps. I'm not convinced that would really
> be very useful to the individual running the command and would need to,
> in essence, be "please get someone with higher privileges to run this,
> or get them to give you permission to run it". I don't think we really
> want to go there (anyone else recall the "please see your network
> administrator" errors..?).
>
> If I'm misunderstanding your thoughts here, please let me know.

This appears to address one of the open items at
https://wiki.postgresql.org/wiki/PostgreSQL_9.5_Open_Items -- if so,
please update that page.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> This appears to address one of the open items at
> https://wiki.postgresql.org/wiki/PostgreSQL_9.5_Open_Items -- if so,
> please update that page.

Done (and re-done with the wiki restore).

No open items remain against 9.5.

Thanks!

Stephen

On Fri, Dec 18, 2015 at 11:40 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
>> This appears to address one of the open items at
>> https://wiki.postgresql.org/wiki/PostgreSQL_9.5_Open_Items -- if so,
>> please update that page.
>
> Done (and re-done with the wiki restore).
>
> No open items remain against 9.5.

Woohoo. And also, phew.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company