Re: patch for type privileges - Mailing list pgsql-hackers

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