Re: [PATCH] DefaultACLs - Mailing list pgsql-hackers

I tried to check the default ACL behavior.
 postgres=# \c - ymj psql (8.5devel) You are now connected to database "postgres" as user "ymj". postgres=> ALTER
DEFAULTPRIVILEGES REVOKE INSERT ON TABLE FROM ymj; ALTER DEFAULT PRIVILEGES postgres=> CREATE TABLE t2 (x int, y text);
CREATETABLE postgres=> SELECT relname, relacl FROM pg_class WHERE oid = 't2'::regclass;  relname |      relacl
---------+------------------ t2      | {ymj=rwdDxt/ymj} (1 row)
 
 postgres=> INSERT INTO t2 VALUES (1, 'aaa'); ERROR:  permission denied for relation t2

It works for me fine, good, but ...
 postgres=> SELECT * INTO t3 FROM t1; SELECT postgres=> SELECT * FROM t3;  a |  b ---+-----  1 | aaa  2 | bbb (2 rows)
 postgres=> INSERT INTO t3 VALUES (3,'ccc'); ERROR:  permission denied for relation t3

In this case, the new table t3 is created with the default ACL which does not
allow to insert any values by the owner of the relation.

SELECT INTO does not check ACL_INSERT on the newly created tables, because
we had been able to assume the table owner always has privilege to insert
values into the new table.
So, OpenIntoRel() didn't check this obvious privilege.

But the default ACL feature breaks this assumption. The table owner may not
have privilege to insert values into new tables.
So, it is necessary to put actual access controls on the OpenIntoRel().

Thanks,

Tom Lane wrote:
> Petr Jelinek <pjmodos@pjmodos.net> writes:
>> [ latest default-ACLs patch ]
> 
> Applied with a fair amount of editorial polishing.  Notably I changed
> the permissions requirements a bit:
> 
> * for IN SCHEMA, the *target* role has to have CREATE permission on the
> target schema.  Without this, the command is a bit pointless since the
> permissions can never be used.  The original coding checked whether the
> *calling* role had USAGE, which seems rather irrelevant.
> 
> * I simplified the target-role permission test to is_member_of.  The
> original check for ADMIN seemed pointlessly strong, because if you're a
> member of the role you can just become the role and set owned objects'
> permissions however you like.  I didn't see the point of the CREATEROLE
> exemption either, and am generally suspicious of anything that would let
> people change permissions on stuff they didn't own.
> 
> One thing that seems like it's likely to be an annoyance in practice
> is the need to explicitly do DROP OWNED BY to get rid of pg_default_acl
> entries for a role to be dropped.  But I can't see any very good way
> around that, since the entries might be in some other database.  One
> thing that might at least reduce the number of keystrokes is to have
> REASSIGN OWNED act as DROP OWNED BY for default ACLs.  I can't convince
> myself whether that's a good idea though, so I left it as-is for the
> moment.
> 
>             regards, tom lane
> 


-- 
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai@ak.jp.nec.com>