Обсуждение: SQL for CREATE ROLE xxx SUPERUSER

Hi developers!

Testing the pgAdmin III 1.8.0  Beta 3 (Aug 10 2007, rev: 6546M). Client
Win XP, host: Debian Etch / PG 8.2.4 and Debian Sarge / PG 8.1.8

The reverse engineered SQL for superusers seems at fault. Applies to
both pg 8.2 and pg 8.1.


First example: everything looks normal:
CREATE ROLE tester;

=== quote
-- Role: "tester"

-- DROP ROLE tester;

CREATE ROLE tester
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
=== unquote


Second example: superusers get an extra line "UPDATE ..." that should
not be there. Especially as it refers to the OID which would be
different when recreating the user.
CREATE ROLE tester2 SUPERUSER;

=== quote
-- Role: "tester2"

-- DROP ROLE tester2;

CREATE ROLE tester2
  SUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
UPDATE pg_authid SET rolcatupdate=true WHERE OID=3714960::oid;
=== unquote


Regards
Erwin

Erwin Brandstetter wrote:
> Second example: superusers get an extra line "UPDATE ..." that should
> not be there. Especially as it refers to the OID which would be
> different when recreating the user.
> CREATE ROLE tester2 SUPERUSER;
>
> === quote
> -- Role: "tester2"
>
> -- DROP ROLE tester2;
>
> CREATE ROLE tester2
>  SUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
> UPDATE pg_authid SET rolcatupdate=true WHERE OID=3714960::oid;
> === unquote

Hi Erwin,

The update is supposed to be there - it enables direct catalog updates
for the user - a kind of super-superuser. You're correct about using the
OID though - replaced with the rolname.

Thanks, Dave.

dpage@postgresql.org wrote:
> Erwin Brandstetter wrote:
>> Second example: superusers get an extra line "UPDATE ..." that should
>> not be there. Especially as it refers to the OID which would be
>> different when recreating the user.
>> CREATE ROLE tester2 SUPERUSER;
>>
>> === quote
>> -- Role: "tester2"
>>
>> -- DROP ROLE tester2;
>>
>> CREATE ROLE tester2
>>  SUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
>> UPDATE pg_authid SET rolcatupdate=true WHERE OID=3714960::oid;
>> === unquote
>
> Hi Erwin,
>
> The update is supposed to be there - it enables direct catalog updates
> for the user - a kind of super-superuser. You're correct about using
> the OID though - replaced with the rolname.

Hi Dave!

But is the line needed at all?
Clearly it works the same way with just "CREATE ROLE tester SUPERUSER".

LG
Erwin

Erwin Brandstetter wrote:
> Hi Dave!
>
> But is the line needed at all?
> Clearly it works the same way with just "CREATE ROLE tester SUPERUSER".

It's not the same thing - pg_authid.rolsuper is the superuser flag,
pg_authid.rolcatupdate is the catalog update flag. It cannot be set with
CREATE or UPDATE ROLE, and is required for even a superuser to directly
update the catalogs (postgres has it by default though).

Regards, Dave.

dpage@postgresql.org wrote:
> Erwin Brandstetter wrote:
>> Hi Dave!
>>
>> But is the line needed at all?
>> Clearly it works the same way with just "CREATE ROLE tester SUPERUSER".
>
> It's not the same thing - pg_authid.rolsuper is the superuser flag,
> pg_authid.rolcatupdate is the catalog update flag. It cannot be set
> with CREATE or UPDATE ROLE, and is required for even a superuser to
> directly update the catalogs (postgres has it by default though).

The point is to cover the non-standard-setting of this flag.
_Every_ superuser gets this flag per default (at least in my tests in pg
8.1 and 8.2). The update line is redundant in this case.
If I set the flag to false in the catalog, then the update line vanishes
from the reverse engineered SQL.
So, the present logic works for non-superusers, but is backwards for
superusers.

Regards
Erwin

Erwin Brandstetter wrote:
> dpage@postgresql.org wrote:
>> Erwin Brandstetter wrote:
>>> Hi Dave!
>>>
>>> But is the line needed at all?
>>> Clearly it works the same way with just "CREATE ROLE tester SUPERUSER".
>>
>> It's not the same thing - pg_authid.rolsuper is the superuser flag,
>> pg_authid.rolcatupdate is the catalog update flag. It cannot be set
>> with CREATE or UPDATE ROLE, and is required for even a superuser to
>> directly update the catalogs (postgres has it by default though).
>
> The point is to cover the non-standard-setting of this flag.
> _Every_ superuser gets this flag per default (at least in my tests in pg
> 8.1 and 8.2). The update line is redundant in this case.
> If I set the flag to false in the catalog, then the update line vanishes
> from the reverse engineered SQL.
> So, the present logic works for non-superusers, but is backwards for
> superusers.

Ahh, gotcha. Fixed in SVN (ie. it now revokes catalog update if you
create a superuser with that option).

Thanks, Dave.