Re: DROP PRIVILEGES OWNED BY

Hi,

This week I had a problem where I wanted to drop only the privileges a
certain role had in the system, while keeping all the objects. I
couldn't figure out a reasonable way to do that, so I've attached a
patch for this to this email. Please consider it for inclusion into
9.5. The syntax is:

DROP PRIVILEGES OWNED BY role [, ...]

I at some point decided to implement it as a new command instead of
changing DropOwnedStmt, and I think that might have been a mistake. It
might have made more sense to instead teach DROP OWNED to accept a
specification of which things to drop. But the proposal is more
important than such details, I think.

.marko

On Mon, Dec 15, 2014 at 9:43 AM, Marko Tiikkaja <marko(at)joh(dot)to> wrote:
> Hi,
>
> This week I had a problem where I wanted to drop only the privileges a
> certain role had in the system, while keeping all the objects. I couldn't
> figure out a reasonable way to do that, so I've attached a patch for this to
> this email. Please consider it for inclusion into 9.5. The syntax is:
>
> DROP PRIVILEGES OWNED BY role [, ...]
>
> I at some point decided to implement it as a new command instead of changing
> DropOwnedStmt, and I think that might have been a mistake. It might have
> made more sense to instead teach DROP OWNED to accept a specification of
> which things to drop. But the proposal is more important than such details,
> I think.
You should consider adding it to the upcoming CF:
https://commitfest.postgresql.org/action/commitfest_view?id=25
Regards,
--
Michael

On 12/15/2014 02:43 AM, Marko Tiikkaja wrote:
> This week I had a problem where I wanted to drop only the privileges a
> certain role had in the system, while keeping all the objects. I
> couldn't figure out a reasonable way to do that, so I've attached a
> patch for this to this email. Please consider it for inclusion into
> 9.5. The syntax is:
>
> DROP PRIVILEGES OWNED BY role [, ...]
>
> I at some point decided to implement it as a new command instead of
> changing DropOwnedStmt, and I think that might have been a mistake. It
> might have made more sense to instead teach DROP OWNED to accept a
> specification of which things to drop. But the proposal is more
> important than such details, I think.

DROP seems like the wrong verb here. DROP is used for deleting objects,
while REVOKE is used for removing permissions from them. REVOKE already
has something similar:

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM heikki;

Following that style, how about making the syntax:

REVOKE ALL PRIVILEGES ON ALL OBJECTS FROM <role>

or just:

REVOKE ALL PRIVILEGES FROM <role>;

- Heikki

On 12/17/14 5:37 PM, Heikki Linnakangas wrote:
> On 12/15/2014 02:43 AM, Marko Tiikkaja wrote:
>> The syntax is:
>>
>> DROP PRIVILEGES OWNED BY role [, ...]
>
> DROP seems like the wrong verb here. DROP is used for deleting objects,
> while REVOKE is used for removing permissions from them. REVOKE already
> has something similar:
>
> REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM heikki;
>
> Following that style, how about making the syntax:
>
> REVOKE ALL PRIVILEGES FROM <role>;

I don't have a problem with that. It would probably work, too, since
FROM is already fully reserved.

.marko

On Thu, Dec 18, 2014 at 1:43 AM, Marko Tiikkaja <marko(at)joh(dot)to> wrote:
> I don't have a problem with that. It would probably work, too, since FROM
> is already fully reserved.
Marking patch as returned with feedback as there has been no input
from Marko in the last couple of weeks.
--
Michael