Re: column level privilages error

On Wed, 2012-01-25 at 13:01 +0100, bdmytrak(at)eranet(dot)pl wrote:
> Hi,
> thanks for quick reply.
> application parameters:
> 1. pgAdmin 1.14.1 on Windows 7 Enterprise 32 bit
> 2. Server: PostgreSQL 9.1.2, compiled by Visual C++ build 1500, 64-bit on Windows Server 2008 R2 Standard
> scenario:
> 1. as user postgres create table
> Table script (table is created before by postgres):
> CREATE TABLE pdi."tblTest"
> (
> "RowId" serial NOT NULL),

There's one harmful parenthesis here.

> "RowValue" text,
> "NewColumn" text,
> CONSTRAINT "tblTest_pkey" PRIMARY KEY ("RowId" )
> )
> WITH (
> OIDS=FALSE
> );
> ALTER TABLE pdi."tblTest"
> OWNER TO postgres;
> GRANT ALL ON TABLE pdi."tblTest" TO postgres;
> GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES ON TABLE pdi."tblTest" TO "MyGroup";
> after table is created ACL looks like:
> ACL {postgres=arwdDxt/postgres,MyGroup=arwdx/postgres}
> 2. log in as different user (bdmytrak) who is member of "MyGroup" and not member of "dbRoles"

What is dbRoles? first time I heard of it.

> 3. navigate to schema "pdi" with granted usage to public.
> 4. navigate to table "tblTest"
> 5. as user bdmytrak I try to change column level privilages using Column properties window on "NewColumn" node.
> 6. pgAdmin generates script: GRANT ALL("NewColumn") ON pdi."tblTest" TO GROUP "dbRoles"; - this is of course correct scirpt.
> 7. press OK button
> 8. no error/waring message is shown - I assume script has been executed correctly

PostgreSQL itself doesn't send back an error. It just adds a WARNING
message in its log:

WARNING: no privileges were granted for column "NewColumn" of relation
"tblTest"

For the record, pgAdmin doesn't show WARNING message (for example, I
think you'll agree we don't want to bother the user with auto creation
of sequence when a user adds a column of pseudo-type serial, or with the
auto creation of index when a user adds a primary key).

BTW, Dave hacked a quick patch during char(11) this summer to display
such notice. This will be a nice addition to the next release. But in
the meantime, there's nothing I can do.

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
PostgreSQL Sessions #3: http://www.postgresql-sessions.org