Priviliges on tables and views
Since PostgreSQL doesn't have column level permissions, I tried to do
something with views like this.
CREATE TABLE account (
uid int, # Unique UID for account
login char8, # User login - must also be unique
cdate date, # Creation date
a_active bool, # true or false
gedit bool, # edit privs for group
bid int, # reference to billing group table
password text, # Encrypted password
gcos text, # Public information
home text, # home directory
shell char8); # which shell
CREATE UNIQUE INDEX account_uid ON account (uid);
CREATE UNIQUE INDEX account_login ON account (login char8_ops);
REVOKE ALL ON account FROM PUBLIC;
CREATE VIEW passwd AS SELECT uid, login, bid, gcos, home, shell
FROM account WHERE a_active = 't';
REVOKE ALL ON passwd FROM PUBLIC;
GRANT SELECT ON passwd TO PUBLIC;
Unfortunately this doesn't work. The VIEW inherits the permissions
from the table it is a view of. It seems to me that allowing a view
to define permissions separately from its parent would be a useful
thing. So, does anyone know if this behaviour is allowed by the
SQL spec and if it is allowed, would this be difficult to do?
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
D'Arcy J.M. Cain wrote:
REVOKE ALL ON account FROM PUBLIC;
CREATE VIEW passwd AS SELECT uid, login, bid, gcos, home, shell
FROM account WHERE a_active = 't';REVOKE ALL ON passwd FROM PUBLIC;
GRANT SELECT ON passwd TO PUBLIC;Unfortunately this doesn't work. The VIEW inherits the permissions
from the table it is a view of. It seems to me that allowing a view
to define permissions separately from its parent would be a useful
thing. So, does anyone know if this behaviour is allowed by the
SQL spec and if it is allowed, would this be difficult to do?
This is allowed by SQL and this is very useful thing. Not easy to implement:
views are handled by RULES - after parsing and before planning, - but
permissions are checked by executor (execMain.c:InitPlan()->ExecCheckPerms()).
Vadim
Thus spake Vadim B. Mikheev
CREATE VIEW passwd AS SELECT uid, login, bid, gcos, home, shell
FROM account WHERE a_active = 't';REVOKE ALL ON passwd FROM PUBLIC;
GRANT SELECT ON passwd TO PUBLIC;Unfortunately this doesn't work. The VIEW inherits the permissions
from the table it is a view of. It seems to me that allowing a view
to define permissions separately from its parent would be a useful
thing. So, does anyone know if this behaviour is allowed by the
SQL spec and if it is allowed, would this be difficult to do?This is allowed by SQL and this is very useful thing. Not easy to implement:
views are handled by RULES - after parsing and before planning, - but
permissions are checked by executor (execMain.c:InitPlan()->ExecCheckPerms()).
Oh well. Is it worth putting on the TODO list at least? Maybe someone
will get to it eventually.
In the meantime, how close are we to being able to update views? I can
do what I want that way - just make two tables with public perms on
one but not the other and make a view for the combined table instead
of for a subset of a table.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
Thus spake Vadim B. Mikheev
CREATE VIEW passwd AS SELECT uid, login, bid, gcos, home, shell
FROM account WHERE a_active = 't';REVOKE ALL ON passwd FROM PUBLIC;
GRANT SELECT ON passwd TO PUBLIC;Unfortunately this doesn't work. The VIEW inherits the permissions
from the table it is a view of. It seems to me that allowing a view
to define permissions separately from its parent would be a useful
thing. So, does anyone know if this behaviour is allowed by the
SQL spec and if it is allowed, would this be difficult to do?This is allowed by SQL and this is very useful thing. Not easy to implement:
views are handled by RULES - after parsing and before planning, - but
permissions are checked by executor (execMain.c:InitPlan()->ExecCheckPerms()).Oh well. Is it worth putting on the TODO list at least? Maybe someone
will get to it eventually.In the meantime, how close are we to being able to update views? I can
do what I want that way - just make two tables with public perms on
one but not the other and make a view for the combined table instead
of for a subset of a table.
Certainly is a good item for the TODO list. Added:
* Allow VIEW permissions to be set separately from the underlying tables
--
Bruce Momjian
maillist@candle.pha.pa.us