Обсуждение: CREATE OPERATOR query

Hi,

While trying to create regression tests for CREATE OPERATOR, I am able to create an operator despite not have USAGE / ALL access to the given argument type. Shouldn't the following SQL throw an error? 

BEGIN TRANSACTION;

CREATE ROLE rol_op3;

CREATE TYPE type_op3 AS ENUM ('new', 'open', 'closed');

CREATE FUNCTION fn_op3(type_op3, type_op3)

RETURNS type_op3 AS $$

    SELECT NULL::type_op3;

$$ LANGUAGE sql IMMUTABLE;

REVOKE ALL ON TYPE type_op3 FROM rol_op3;

SET ROLE rol_op3;

CREATE OPERATOR #*# (

   leftarg = type_op3,

   rightarg = type_op3,

   procedure = fn_op3

);

RESET ROLE;

ROLLBACK;

This what the doc says:

> To be able to create an operator, you must have USAGE privilege on the argument types and the return type, as well as EXECUTE privilege on the underlying function. If a commutator or negator operator is specified, you must own these operators.

Any pointers would be helpful.

Thanks

--

Robins Tharakan

Robins Tharakan escribió:

> Further, I seem to have a similar problem with the EXECUTE check
> requirement as well.
> Sample SQL given below.
>
> BEGIN TRANSACTION;
> CREATE ROLE rol_op3;
> CREATE TYPE type_op3 AS ENUM ('new', 'open', 'closed');
> CREATE FUNCTION fn_op3(type_op3, type_op3)
> RETURNS type_op3 AS $$
>     SELECT NULL::type_op3;
> $$ LANGUAGE sql IMMUTABLE;
> REVOKE EXECUTE ON FUNCTION fn_op3(type_op3, type_op3) FROM rol_op3;

Doesn't PUBLIC still have EXECUTE permissions on this function?

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Robins Tharakan escribió:

> Yes, but the documentation states that EXECUTE permission for the function
> is required to even CREATE the operator.

What I mean is that your new role still has said permission through
PUBLIC (a pseudo-role which is automatically granted to all other roles
and cannot be revoked), even if you revoke it directly.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services