Hi,
Using pgAdmin 1.22.1 on Windows 7 against Postgres 9.3 on CentOS 6.5.
The view descriptions in the "SQL pane" of pgAdmin do not include
column-specific privileges. This has the unfortunate side effect that if
the definitions in the "SQL pane" are trusted to re-create the view the
column-specific privileges are lost.
Steps to reproduce:
create table test (id integer, int_value integer, num_value
numeric, txt_value text);
create view v$test as select id, int_value, num_value, txt_value
from test;
create role myrole;
grant select, delete on table v$test to myrole; grant insert (int_value, num_value, txt_value) on table v$test
to
myrole; grant update (int_value, num_value) on table v$test to myrole;
When viewed in pgAdmin 1.22.1 the view definition is:
-- View: "v$test"
-- DROP VIEW "v$test";
CREATE OR REPLACE VIEW "v$test" AS SELECT test.id, test.int_value, test.num_value,
test.txt_value FROM test;
ALTER TABLE "v$test" OWNER TO postgres; GRANT ALL ON TABLE "v$test" TO postgres; GRANT SELECT, DELETE ON
TABLE"v$test" TO myrole;
which is missing the column-specific insert and update privileges.
The issue is not present when privileges are granted directly to a table:
create table test2 (id integer, int_value integer, num_value
numeric, txt_value text);
grant select, delete on table test2 to myrole; grant insert (int_value, num_value, txt_value) on table test2 to
myrole; grant update (int_value, num_value) on table test2 to myrole;
The table description in the "SQL pane" is:
-- Table: test2
-- DROP TABLE test2;
CREATE TABLE test2 ( id integer, int_value integer, num_value numeric, txt_value text )
WITH ( OIDS=FALSE ); ALTER TABLE test2 OWNER TO postgres; GRANT ALL ON TABLE test2 TO postgres;
GRANTSELECT, DELETE ON TABLE test2 TO myrole; GRANT UPDATE(int_value), INSERT(int_value) ON test2 TO myrole;
GRANTUPDATE(num_value), INSERT(num_value) ON test2 TO myrole; GRANT INSERT(txt_value) ON test2 TO myrole;
which does include the column-specific privileges granted.
Kind regards
-- Jan Holst Jensen, Biochemfusion Aps (biochemfusion.com), Copenhagen,
Denmark