Thread: column level privilages error

column level privilages error

From
"bdmytrak@eranet.pl"
Date:
<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> 

Re: column level privilages error

From
Guillaume Lelarge
Date:
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



Re: column level privilages error

From
"bdmytrak@eranet.pl"
Date:
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),
  "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"
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
9. check ACL or privilages - nothing has been changed, no ACL is displayed for column, and table script is the same.

to check it works I changed column level privilages using postgres user and:
1. ACL is set to  {dbRoles=arwx/postgres}
2. Column script has been changed to 
-- Column: "NewColumn"

-- ALTER TABLE pdi."tblTest" DROP COLUMN "NewColumn";

ALTER TABLE pdi."tblTest" ADD COLUMN "NewColumn" text;
GRANT SELECT("NewColumn"), UPDATE("NewColumn"), INSERT("NewColumn"), REFERENCES("NewColumn") ON pdi."tblTest" TO "dbRoles";

3. Table scrip has been changed to:
-- Table: pdi."tblTest"

-- DROP TABLE pdi."tblTest";
CREATE TABLE pdi."tblTest"
(
 "RowId" integer NOT NULL DEFAULT nextval('pdi."tblTest_RowId_seq"'::regclass),
 "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 "MyBroup";
GRANT SELECT("NewColumn"), UPDATE("NewColumn"), INSERT("NewColumn"), REFERENCES("NewColumn") ON pdi."tblTest" TO "dbRoles";

When You execute script GRANT ALL("NewColumn") ON pdi."tblTest" TO GROUP "dbRoles" using Query Tool (user bdmytrak) then waring message is dislpayed and no changes to DB is made;
This is WARNING not ERROR message.

This is described in postgres documentation (http://www.postgresql.org/docs/9.1/static/sql-grant.html)
"When a non-owner of an object attempts to GRANT privileges on the object, the command will fail outright if the user has no privileges whatsoever on the object. As long as some privilege is available, the command will proceed, but it will grant only those privileges for which the user has grant options. The GRANT ALL PRIVILEGES forms will issue a warning message if no grant options are held, while the other forms will issue a warning if grant options for any of the privileges specifically named in the command are not held. (In principle these statements apply to the object owner as well, but since the owner is always treated as holding all grant options, the cases can never occur.)"

Hope this will help You.

Regards,
Bartek


--- Oryginalna wiadomość ---

Od: Guillaume Lelarge [mailto: guillaume@lelarge.info]
Wysłane: Monday, January 23, 2012 09:06 PM
Do: bdmytrak@eranet.pl
Kopia: pgadmin-support@postgresql.org
Temat: Re: [pgadmin-support] column level privilages error
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 using user 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 logged to).
> PostgreSQL generates warning message in that case. I think it is good idea to show this Warning to pgAdmin user or remove this 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


--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support

Re: column level privilages error

From
Guillaume Lelarge
Date:
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



Re: column level privilages error

From
"bdmytrak@eranet.pl"
Date:
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.
 dbRoles is an another role like MyGroup.

Thanks for patch. 

Regards,
Bartek




--- Oryginalna wiadomość ---

Od: Guillaume Lelarge [mailto: guillaume@lelarge.info]
Wysłane: Wednesday, January 25, 2012 11:11 PM
Do: bdmytrak@eranet.pl
Kopia: pgadmin-support@postgresql.org
Temat: Re: [pgadmin-support] column level privilages error
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

Re: column level privilages error

From
Guillaume Lelarge
Date:
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



Re: column level privilages error

From
"bdmytrak@eranet.pl"
Date:
PostgreSQL provides Error code 01007 (privilege_not_granted) and 01006 (privilege_not_revoked),  is it possible to handle these exceptions?
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...

Regards, 
Bartek



--- Oryginalna wiadomość ---

Od: Guillaume Lelarge [mailto: guillaume@lelarge.info]
Wysłane: Thursday, January 26, 2012 10:14 PM
Do: bdmytrak@eranet.pl
Kopia: pgadmin-support@postgresql.org
Temat: Re: [pgadmin-support] column level privilages error
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


--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support

Re: column level privilages error

From
Guillaume Lelarge
Date:
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



Re: column level privilages error

From
"bdmytrak@eranet.pl"
Date:
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.
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). 

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).
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?

Hope You will find elegant solution.

Regards,
Bartek


--- Oryginalna wiadomość ---

Od: Guillaume Lelarge [mailto: guillaume@lelarge.info]
Wysłane: Monday, January 30, 2012 05:17 PM
Do: bdmytrak@eranet.pl
Kopia: pgadmin-support@postgresql.org
Temat: Re: [pgadmin-support] column level privilages error
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


--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support

Re: column level privilages error

From
Guillaume Lelarge
Date:
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