Horton

Thread

  1. Priviliges on tables and views

    D'Arcy Cain <darcy@druid.net> — 1998-01-13T15:44:32Z

    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.

  2. Re: [HACKERS] Priviliges on tables and views

    Vadim B. Mikheev <vadim@sable.krasnoyarsk.su> — 1998-01-14T03:19:50Z

    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

  3. Re: [HACKERS] Priviliges on tables and views

    D'Arcy Cain <darcy@druid.net> — 1998-01-14T03:49:13Z

    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.

  4. Re: [HACKERS] Priviliges on tables and views

    Bruce Momjian <maillist@candle.pha.pa.us> — 1998-01-14T14:48:33Z

    > 
> 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