Thread: 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.