Обсуждение: Permissions not working?

Something seems to be wrong here unless I am doing something wrong that
I can't see.  Here is a dump of one session.

vex=> select version();
version
-------------------------------------------------------------------------
PostgreSQL 6.3.2 on i386-unknown-netbsd1.3B, compiled by gcc 2.7.2.2+myc1
(1 row)

vex=> SELECT relname, relacl FROM pg_class, pg_user
vex->   WHERE ( relkind = 'r' OR relkind = 'i' OR relkind = 'S') AND
vex->     relname = 'client' AND
vex->     usesysid = relowner
vex->   ORDER BY relname;
relname|relacl
-------+--------------
client |{"=","root=r"}
(1 row)

vex=> select user;
getpgusername
-------------
carol
(1 row)

vex=> select * from client;
ERROR:  client: Permission denied.

Exactly what I expected.  Now, here is another.

trends=> select version();
version
--------------------------------------------------------------------------
PostgreSQL 6.4.0 on i386-unknown-netbsd1.3.2, compiled by gcc 2.7.2.2+myc1
(1 row)

trends=> SELECT relname, relacl FROM pg_class, pg_user
trends->   WHERE ( relkind = 'r' OR relkind = 'i' OR relkind = 'S') AND
trends->     relname = 'client' AND
trends->     usesysid = relowner
trends->    ORDER BY relname;
relname|relacl
-------+---------------
client |{"=","db=arwR"}
(1 row)

trends=> select user;
getpgusername
-------------
darcy
(1 row)

trends=> select * from client;
[Rows returned as if I had permissions]

Am I missing something or did table permission protections get lost on
the way to 6.4?

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

>
> Something seems to be wrong here unless I am doing something wrong that
> I can't see.  Here is a dump of one session.
>
> version
> -------------------------------------------------------------------------
> PostgreSQL 6.3.2 on i386-unknown-netbsd1.3B, compiled by gcc 2.7.2.2+myc1
>
> relname|relacl
> -------+--------------
> client |{"=","root=r"}
>
> getpgusername
> -------------
> carol
>
> vex=> select * from client;
> ERROR:  client: Permission denied.
>
> Exactly what I expected.  Now, here is another.

    I  assume user carol does not have usesuper set in pg_shadow.

> version
> --------------------------------------------------------------------------
> PostgreSQL 6.4.0 on i386-unknown-netbsd1.3.2, compiled by gcc 2.7.2.2+myc1
>
> relname|relacl
> -------+---------------
> client |{"=","db=arwR"}
>
> getpgusername
> -------------
> darcy
>
> trends=> select * from client;
> [Rows returned as if I had permissions]

    Here I assume user darcy has usesuper set in pg_shadow. Check
    and correct me if I'm wrong. The superuser flag is set if you
    allow darcy to create users on createuser time.

> Am I missing something or did table permission protections get lost on
> the way to 6.4?

    The only thing changed is  that  relations  accessed  due  to
    rewrite  rules get checked against the owner of the relation,
    the rules are fired for (ev_class attribute  of  pg_rewrite).
    This  was  only  done for read access due to rules in 6.3 and
    now does also check for append/write access since we open all
    rules to regular users in 6.4.

    Someone  can  now  setup a view from tables she has access to
    and then grant access to the view but does not need to  grant
    access to the tables the view is made of. This is how we make
    pg_user (a view) publicly readable but protect pg_shadow (the
    selected table) from public access.

    Or  someone  can  setup rules on insert, update and delete to
    one table (granted) that do logging of these  events  into  a
    log table (not granted).

    All  the  required  permissions are checked during the actual
    query rewriting.  Thus, later ACL changes will  correctly  be
    in effect. Example:

    Table t1 owner user_a granted select to user_b
    View  v1 owner user_b granted select to user_c

    user_c can select from v1

    Now user_a revokes select on t1 from user_b

    user_c gets 't1: permission denied' on select from v1

    But if user_b is a superuser (usesuper set)

    user_c can still select from v1


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #

Thus spake Jan Wieck
>     I  assume user carol does not have usesuper set in pg_shadow.

Correct.

>     Here I assume user darcy has usesuper set in pg_shadow. Check
>     and correct me if I'm wrong. The superuser flag is set if you
>     allow darcy to create users on createuser time.

Correct again.  I half suspected something like this.  Perhaps the
prompt in createuser should be changed to reflect that the user is
being granted full superuser privileges rather than just being able
to create more users.

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