Re: DEFAULT PRIVILEGES incorrect
| От | Sanket Mehta |
|---|---|
| Тема | Re: DEFAULT PRIVILEGES incorrect |
| Дата | |
| Msg-id | CA+yw=mPthm34Xgr3r5E6yKDYMnwunpccs0eLy4=8U_Y1xSVcFQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: DEFAULT PRIVILEGES incorrect (Erwin Brandstetter <brsaweda@gmail.com>) |
| Ответы |
Re: DEFAULT PRIVILEGES incorrect
(Dave Page <dpage@pgadmin.org>)
|
| Список | pgadmin-support |
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@gmail.com> wrote:
ErwinHi Sanket!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.
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.Currently this is missing and effectively incorrect.RegardsOn Tue, Feb 16, 2016 at 12:11 PM, Dave Page <dpage@pgadmin.org> wrote:On Tue, Feb 16, 2016 at 11:06 AM, Sanket Mehta <sanket.mehta@enterprisedb.com> wrote:On Tue, Feb 16, 2016 at 4:34 PM, Dave Page <dpage@pgadmin.org> wrote:On Tue, Feb 16, 2016 at 10:53 AM, Sanket Mehta <sanket.mehta@enterprisedb.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 MehtaSr Software engineerEnterprisedbOn Wed, Feb 10, 2016 at 3:53 PM, Sanket Mehta <sanket.mehta@enterprisedb.com> wrote:Hi Dave,Sure I will look into it and get back.Regards,Sanket MehtaSr Software engineerEnterprisedbOn Wed, Feb 10, 2016 at 3:03 PM, Dave Page <dpage@pgadmin.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@gmail.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@gmail.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
В списке pgadmin-support по дате отправления: