Re: DEFAULT PRIVILEGES incorrect

Hi,

I have evaluated the code and found another issue while trying to resolve
this bug.

Lets assume we have created 3 roles foo1, foo2 and bar1 and one schema
"test".
Now using foo1 role we have given SELECT privileges to bar1 as mentioned
below:

SELECT ROLE foo1;
ALTER DEFAULT PRIVILEGES IN SCHEMA test
GRANT SELECT ON TABLES TO bar1;
RESET ROLE;

And using foo2 role we have given ALL privileges to bar1:
SELECT ROLE foo2;
ALTER DEFAULT PRIVILEGES IN SCHEMA test
GRANT ALL ON TABLES TO bar1;
RESET ROLE;

so if you look at the output of pg_default_acl table, it will look as below:

defaclrole schema defobjtype defaclacl
-------------------------------------------------------------------------------------------
21633 21637 r (table) {bar1=r/foo1}
21634 21637 r (table) {bar1=arwdDxt/foo2}

where 21633 is oid of foo1 and 21634 is oid of foo2 and 21637 is oid of
test3.

So now we will have 2 granters(foo1, foo2) and 1 grantee(bar1) on schema
test

so to resolve the main issue mentioned by Erwin we have to show 2 ALTER
DEFAULT PRIVILEGES statement for both granter role in reversed engineering
query.

This solution will create another issue in property dialog of schema.

In property dialog -> default privileges tab -> roles and privileges are
listed only for current role but not for all granters.
so in our case only for current role (lets assume its foo1) it will show
default privileges but not for granter foo2.

I think resolving this issue is itself a big project which would take a lot
of time.

Let me know your thoughts on the same.

Regards,
Sanket Mehta
Sr Software engineer
Enterprisedb

On Tue, Feb 16, 2016 at 4:55 PM, Erwin Brandstetter <brsaweda(at)gmail(dot)com>
wrote:

> Hi Sanket!
>
> The point is that "FOR ROLE foo" can only be omitted in the DDL command if
> the *current *role is identical to the role for which it applies.
>
> I would strongly advise not to try and build in this syntax shortcut at
> all. It should be much simpler and less error prone to always add the
> appropriate FOR ROLE clause in the reverse engineered DDL.
>
> Currently this is missing and effectively incorrect.
>
> Regards
> Erwin
>
> On Tue, Feb 16, 2016 at 12:11 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>
>>
>>
>> On Tue, Feb 16, 2016 at 11:06 AM, Sanket Mehta <
>> sanket(dot)mehta(at)enterprisedb(dot)com> wrote:
>>
>>>
>>> On Tue, Feb 16, 2016 at 4:34 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>>
>>>>
>>>>
>>>> On Tue, Feb 16, 2016 at 10:53 AM, Sanket Mehta <
>>>> sanket(dot)mehta(at)enterprisedb(dot)com> wrote:
>>>>
>>>>> Hi Dave,
>>>>>
>>>>> Below is my understanding of this issue:
>>>>>
>>>>> In ALTER DEFAULT PRIVLEGES statement, "FOR ROLE <target_name> "part is
>>>>> missing.
>>>>>
>>>>
>>>> That's what Erwin has reported, yes.
>>>>
>>>>
>>>>>
>>>>> as per postgresql documentation target_name should follow below rule:
>>>>>
>>>>> target_name:
>>>>> The name of an existing role of which the current role is a member. If FOR
>>>>> ROLE is omitted, the current role is assumed.
>>>>>
>>>>> So target_name should be owner of this schema which is "foo".
>>>>>
>>>>
>>>> I don't think the owner has anything to do with it. It says the name of
>>>> an existing role (of which the current role is a member), or the current
>>>> role if unspecified.
>>>>
>>>
>>> In that case, it should be FOR ROLE 'postgres' as postgres is the
>>> current role.
>>>
>>
>> No, because in Erwin's example he has this:
>>
>> SET ROLE foo;
>>
>>
>>
>>>
>>>>
>>>>>
>>>>>
>>>>> Please let me know if I am right in my understanding or something is
>>>>> missing.
>>>>>
>>>>>
>>>>>
>>>>> Regards,
>>>>> Sanket Mehta
>>>>> Sr Software engineer
>>>>> Enterprisedb
>>>>>
>>>>> On Wed, Feb 10, 2016 at 3:53 PM, Sanket Mehta <
>>>>> sanket(dot)mehta(at)enterprisedb(dot)com> wrote:
>>>>>
>>>>>> Hi Dave,
>>>>>>
>>>>>> Sure I will look into it and get back.
>>>>>>
>>>>>> Regards,
>>>>>> Sanket Mehta
>>>>>> Sr Software engineer
>>>>>> Enterprisedb
>>>>>>
>>>>>> On Wed, Feb 10, 2016 at 3:03 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>>>>>
>>>>>>> Sanket - can you take a look at this when you get a minute please?
>>>>>>>
>>>>>>> Thanks.
>>>>>>>
>>>>>>> On Wed, Feb 10, 2016 at 4:23 AM, Erwin Brandstetter <
>>>>>>> brsaweda(at)gmail(dot)com> wrote:
>>>>>>> > This bug has not been addressed in pgAdmin 1.22.0, yet.
>>>>>>> >
>>>>>>> > If I run this as user postgres:
>>>>>>> >
>>>>>>> > ALTER DEFAULT PRIVILEGES FOR ROLE foo IN SCHEMA test
>>>>>>> > GRANT SELECT ON TABLES
>>>>>>> > TO bar;
>>>>>>> >
>>>>>>> > Then the SQL pane says (still as user postgres):
>>>>>>> >
>>>>>>> > ALTER DEFAULT PRIVILEGES IN SCHEMA test
>>>>>>> > GRANT SELECT ON TABLES
>>>>>>> > TO bar;
>>>>>>> >
>>>>>>> > Which is incorrect and misleading.
>>>>>>> >
>>>>>>> > Regards
>>>>>>> >
>>>>>>> > Erwin
>>>>>>> >
>>>>>>> > On Thu, Aug 13, 2015 at 2:45 PM, Erwin Brandstetter <
>>>>>>> brsaweda(at)gmail(dot)com>
>>>>>>> > wrote:
>>>>>>> >>
>>>>>>> >> The display of ALTER DEFAULT PRIVILEGES statements can be
>>>>>>> incorrect.
>>>>>>> >>
>>>>>>> >> I reported this bug in 2013, but it seems like it never got
>>>>>>> through:
>>>>>>> >> http://www.postgresql.org/message-id/528C2D00.6010905@falter.at
>>>>>>> >>
>>>>>>> >> Details, with steps to reproduce:
>>>>>>> >> https://redmine.postgresql.org/issues/694
>>>>>>> >>
>>>>>>> >> Just tested with version 1.20 again and the situation seems
>>>>>>> unchanged.
>>>>>>> >>
>>>>>>> >> Regards
>>>>>>> >> Erwin
>>>>>>> >
>>>>>>> >
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Dave Page
>>>>>>> Blog: http://pgsnake.blogspot.com
>>>>>>> Twitter: @pgsnake
>>>>>>>
>>>>>>> EnterpriseDB UK: http://www.enterprisedb.com
>>>>>>> The Enterprise PostgreSQL Company
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Dave Page
>>>> Blog: http://pgsnake.blogspot.com
>>>> Twitter: @pgsnake
>>>>
>>>> EnterpriseDB UK: http://www.enterprisedb.com
>>>> The Enterprise PostgreSQL Company
>>>>
>>>
>>>
>>
>>
>> --
>> Dave Page
>> Blog: http://pgsnake.blogspot.com
>> Twitter: @pgsnake
>>
>> EnterpriseDB UK: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>
>