Обсуждение: column level privilages error

<font color="#000000" face="verdana" size="4"><font face="arial" size="2">HI all,<br />I think, there is little bug in
columnprivilage mechanism. The bug occures when You try to set column privilages using user name who has no rights to
dothis.<br />For table there is no tab "Privilages", for columns it exists and accepts configuration. Even when You
acceptYour configuration (OK button) there is no error/warning message. It looks like new configuration has been fully
accepted.<br/>Unfortunately I spend hour or even more looking for mistake in privilages (my mistake: forgot the user I
havebeen logged to).<br />PostgreSQL generates warning message in that case. I think it is good idea to show this
Warningto pgAdmin user or remove this tab from column properties.<br /><br /><br />Regards,<br />Bartek</font></font> 

On Thu, 2012-01-19 at 14:13 +0100, bdmytrak@eranet.pl wrote:
[...]
> I think, there is little bug in column privilage mechanism. The bug occures when You try to set column privilages
usinguser name who has no rights to do this.
 
> For table there is no tab "Privilages", for columns it exists and accepts configuration. Even when You accept Your
configuration(OK button) there is no error/warning message. It looks like new configuration has been fully accepted.
 
> Unfortunately I spend hour or even more looking for mistake in privilages (my mistake: forgot the user I have been
loggedto).
 
> PostgreSQL generates warning message in that case. I think it is good idea to show this Warning to pgAdmin user or
removethis tab from column properties.
 

I don't think I understand what you mean. I tried to connect as an
unprivileged user, and, if I try to add/change a permission, I always
get the PostgreSQL error denying me to do it.

Maybe I didn't undertand what you meant.


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

On Wed, 2012-01-25 at 13:01 +0100, bdmytrak@eranet.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

On Thu, 2012-01-26 at 08:20 +0100, bdmytrak@eranet.pl wrote:
> Thanks,
> I manually edited table script (there was DEFAULT nextaval...) so I
> missed last parenthesis - my fault.
> I agree pgAdmin shouldn't display all warnings, but I think some of
> them shouldn't be ignerd - like this one.

Well, then, the question is: how do we know which one we should display?
trying to guess by comaring sentences won't work.


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

On Mon, 2012-01-30 at 13:13 +0100, bdmytrak@eranet.pl wrote:
> PostgreSQL provides Error code 01007 (privilege_not_granted) and 01006
> (privilege_not_revoked),  is it possible to handle these exceptions?

We never use the error codes. We simply display the error messages from
the server.

And doing an exception for some error codes doesn't seem a good idea.

> I am not sure if codes are used at present (according to
> documentation: "Some are not actually used at present, but are defined
> by the SQL standard." source:
> http://www.postgresql.org/docs/9.1/static/errcodes-appendix.html).
> 
> 
> Or simply remove this tab (like on table level) if user is not allowed
> to change privilages...

Well, it would be great to be able to use the privileges and make sure
the user cannot do something in the UI that he's not allowed on the
server. When someone will want to do something like this, he'll need to
have a complete patch. Not only the column level privileges. And it
would be a major work AFAICT.


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

On Mon, 2012-01-30 at 22:19 +0100, bdmytrak@eranet.pl wrote:
> You handle it somehow for tables (there is no privilage tab in table
> properies when You cannot change privilages). I suppose it is done
> based on ACL for table.

No. On PostgreSQL, it depends if you are superuser or an owner. There
are no ACL granting the rights to alter a table. Within pgAdmin, we only
check if you can create a table in the selected schema.

> This behaviour is not symmetric - works on tables and does not work on
> columns. It leads to misunderstandings, just like in my case. I was
> sure privilages has been granted (no error/warning message has been
> displayed). 

Yes, but we can't do anything about this. PostgreSQL also sends a
warning message, and we don't display those because we don't want to
annoy the user with too many messages.

> I also think it is possible to recognize user ability to change column
> level privilages based on ACL (WITH GRANT - signed as star in ACL).

Sure, I don't deny that.

> If the user has privilages WITH GRANT OPTION, eg. 
> GRANT UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE
> public."tblTest" TO user;
> GRANT SELECT ON TABLE public."tblTest" TO user WITH GRANT OPTION;
> he is allowed to grant select on columns of this table for another
> user. Interesting thing is that, when You (as "user" from my example)
> try to execute:
> GRANT ALL("Column1") ON public."tblTest" TO public;
> then only SELECT privilage on "Column1" is granted - as it is expected
> based on "user" privilages.
> 
> 
> BTW PostgreSQL generates NOTICE for auto creation of sequence for
> pseudo-type serial not WARNING, so maybe it is good idea to treat
> WARNINGS in the same way as ERRORS?

You'll still get all the warnings messages, and people might not want to
get that.


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