Robert Haas <robertmhaas@gmail.com> wrote:
> Even aside from security exposures, how
> does a non-superuser who runs pg_dump know whether they've got a
> complete backup or a filtered dump that's missing some rows?
This seems to me to be a killer objection to the feature as
proposed, and points out a huge difference between column level
security and the proposed implementation of row level security.
(In fact it is a difference between just about any GRANTed
permission and row level security.) If you try to SELECT * FROM
sometable and you don't have rights to all the columns, you get an
error. A dump would always either work as expected or generate an
error.
test=# create user bob;
CREATE ROLE
test=# create user bill;
CREATE ROLE
test=# set role bob;
SET
test=> create table person (person_id int not null primary key,
name text not null, ssn text);
CREATE TABLE
test=> grant select (person_id, name) on table person to bill;
GRANT
test=> reset role;
RESET
test=# set role bill;
SET
test=> select person_id, name from person;
person_id | name
-----------+------
(0 rows)
test=> select * from person;
ERROR: permission denied for relation person
The proposed approach would leave the validity of any dump which
was not run as a superuser in doubt. The last thing we need, in
terms of improving security, is another thing you can't do without
connecting as a superuser.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company