Re: two questions about pg 9.0

Hi Guillaume,
Thanks for your reply I did a bit more testing with the superuser priv issue, and now I came to the conclusion that pgAdminIII may be doing something silly.

I created a user: bob

In pgAdminIII I checked off the box for: can inherit from parent role, can create db object, superuser
Got the following sql:
CREATE ROLE bob LOGIN
ENCRYPTED PASSWORD 'md51e9484aace238e7cb2609130fd87646e'
SUPERUSER INHERIT CREATEDB NOCREATEROLE;
UPDATE pg_authid SET rolcapupdate=false WHERE rolname='bob';

Than I created bobb
In pgAdminIII I checked off the box for: can inherit from parent role, can create db object, superuser, Can modify catalog directly
Got the following sql:
CREATE ROLE bobb LOGIN
ENCRYPTED PASSWORD 'md51e9484aace238e7cb2609130fd87646e'
SUPERUSER INHERIT CREATEDB NOCREATEROLE;

Conclusion:
Sql level superuser = pgAdminIII superuser + can modify catalog directly

This is misleading, I would call it a pgAdminIII bug but who knows maybe it is a feature...

Sincerely,
Kasia

-----Original Message-----
From: Guillaume Lelarge [mailto:guillaume(at)lelarge(dot)info]
Sent: Tuesday, October 12, 2010 10:59 AM
To: Kasia Tuszynska
Cc: pgsql-admin
Subject: Re: [ADMIN] two questions about pg 9.0

Hi,

Le 12/10/2010 19:25, Kasia Tuszynska a écrit :
> [...]
> 1. Permissions:
> When I create a login role with superuser privileges in pgAdminIII I have the option of indicating that the superuser will also have the permission to: "can modify catalog directly", when I look at the sql that is generated by that gui, I do not see a corresponding sql permission, I see:
> "CREATE ROLE kasia LOGIN ENCRYPTED PASSWORD 'md5ff0508b7bb33909c096d1b447a17d09b'
> SUPERUSER VALID UNTIL 'infinity';"
> A. What does "can modify catalog directly" actually do? I looked it up and I found : "This option determines whether the role can modify system catalog directly or not." But that really does not tell me much.

Yeah, we kinda suck at documenting things :)

It refers to the rolcatupdate column in the pg_authid system table.
Which is a permission to update the system catalogs (system tables).

> B. Why is there no sql level equivalent? When I revoke the "can modify catalog directly" permission I get the following sql: UPDATE pg_authid SET rolcatupdate=false WHERE OID=17331::oid;

You create a superuser, which already has this capacity. So we don't
need to add something else. Of course, if you uncheck it, we'll have to
update the system table to set it to false.

--
Guillaume
http://www.postgresql.fr
http://dalibo.com