Re: column level privilages error
| От | bdmytrak@eranet.pl |
|---|---|
| Тема | Re: column level privilages error |
| Дата | |
| Msg-id | 978904407.243.1327492899621.JavaMail.omnix02@wlas2 обсуждение исходный текст |
| Ответ на | column level privilages error ("bdmytrak@eranet.pl" <bdmytrak@eranet.pl>) |
| Ответы |
Re: column level privilages error
(Guillaume Lelarge <guillaume@lelarge.info>)
|
| Список | pgadmin-support |
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
В списке pgadmin-support по дате отправления: