Re: patch for type privileges

On 2011-12-07 19:59, Peter Eisentraut wrote:
> Two excellent finds. Here is an updated patch with fixes.

Thanks.. I'm sorry I cannot yet provide a complete review, but since the
end of the commitfest is near, I decided to mail them anyway instead of
everything on dec 15.

* ExecGrant_type() prevents 'grant usage on domain' on a type, but the
converse is possible.

postgres=# create domain myint as int2;
CREATE DOMAIN
postgres=# grant usage on type myint to public;
GRANT

* Cannot restrict access to array types. After revoking usage from the
element type, the error is perhaps a bit misleading. (smallint[] vs
smallint)

postgres=> create table a (a int2[]);
ERROR: permission denied for type smallint[]

* The patch adds the following text explaining the USAGE privilege on types.

For types and domains, this privilege allow the use of the type or
domain in the definition of tables, functions, and other schema objects.

Since other paragraphs in USAGE use the word 'creation' instead of
'definition', I believe here the word 'creation' should be used too.
IMHO it would also be good to describe what the USAGE privilege is not,
but might be expected since it is such a generic term. USAGE on type:
use of the type while creating new dependencies to the type, not usage
in the sense of instantiating values of the type. If there are existing
dependencies, revoking usage privileges will not return any warning and
the dependencies still exist. Also other kinds of exceptions could be
noted, such as the exception for array types and casts. The example you
gave in the top mail about why restricting access to types can be
useful, such as preventing that owners are prevented changing their
types because others have 'blocked' them by their usage, is something
that could also help readers of the documentation understand why
privileges on types are useful for them (or not).

* The information schema view 'attributes' has this additional condition:
AND (pg_has_role(t.typowner, 'USAGE')
OR has_type_privilege(t.oid, 'USAGE'));

What happens is that attributes in a composite type are shown, or not,
if the current user has USAGE rights. The strange thing here, is that
the attribute in the type being show or not, doesn't match being able to
use it (in the creation of e.g. a table). Maybe that is not intended,
but I would expect it matching:

postgres=# create user c;
CREATE ROLE
postgres=# create type t as (a int2);
CREATE TYPE
postgres=# \c - c
You are now connected to database "postgres" as user "c".
postgres=> select udt_name,attribute_name from
information_schema.attributes;
udt_name | attribute_name
----------+----------------
t | a
(1 row)

postgres=> \c -
You are now connected to database "postgres" as user "c".
postgres=> \c - postgres
You are now connected to database "postgres" as user "postgres".
postgres=# revoke usage on type int2 from public;
REVOKE
postgres=# \c - c
You are now connected to database "postgres" as user "c".
postgres=> select udt_name,attribute_name from
information_schema.attributes;
udt_name | attribute_name
----------+----------------
(0 rows)

postgres=> create table m (a t);
CREATE TABLE
postgres=> insert into m values (ROW(10));
INSERT 0 1
postgres=>

Conversely:

postgres=# grant usage on type int2 to public;
GRANT
postgres=# revoke usage on type t from public;
REVOKE
postgres=# \c - c
You are now connected to database "postgres" as user "c".
postgres=> select udt_name,attribute_name from
information_schema.attributes;
udt_name | attribute_name
----------+----------------
t | a
(1 row)

postgres=> create table m2 (a t);
ERROR: permission denied for type t
postgres=>

regards,
Yeb Havinga