pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedCREATE OPERATOR query
6+ messages / 2 participants
[nested] [flat]
* CREATE OPERATOR query
@ 2013-05-22 00:20 Robins Tharakan <tharakan@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Robins Tharakan @ 2013-05-22 00:20 UTC (permalink / raw)
To: pgsql-docs
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CREATE OPERATOR query
@ 2013-05-22 00:32 Robins Tharakan <tharakan@gmail.com>
parent: Robins Tharakan <tharakan@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Robins Tharakan @ 2013-05-22 00:32 UTC (permalink / raw)
To: pgsql-docs
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;
SET ROLE rol_op3;
CREATE OPERATOR #*# (
leftarg = type_op3,
rightarg = type_op3,
procedure = fn_op3
);
RESET ROLE;
ROLLBACK;
Thanks.
--
Robins Tharakan
On 22 May 2013 05:50, Robins Tharakan <tharakan@gmail.com> wrote:
> 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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CREATE OPERATOR query
@ 2013-05-22 01:42 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Robins Tharakan <tharakan@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Alvaro Herrera @ 2013-05-22 01:42 UTC (permalink / raw)
To: Robins Tharakan <tharakan@gmail.com>; +Cc: pgsql-docs
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
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CREATE OPERATOR query
@ 2013-05-22 02:03 Robins Tharakan <tharakan@gmail.com>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 6+ messages in thread
From: Robins Tharakan @ 2013-05-22 02:03 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: pgsql-docs
Yes, but the documentation states that EXECUTE permission for the function
is required to even CREATE the operator.
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.
Am I missing something obvious here?
--
Robins Tharakan
On 22 May 2013 07:12, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> 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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CREATE OPERATOR query
@ 2013-05-22 03:33 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Robins Tharakan <tharakan@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Alvaro Herrera @ 2013-05-22 03:33 UTC (permalink / raw)
To: Robins Tharakan <tharakan@gmail.com>; +Cc: pgsql-docs
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
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CREATE OPERATOR query
@ 2013-05-22 21:33 Robins Tharakan <tharakan@gmail.com>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 0 replies; 6+ messages in thread
From: Robins Tharakan @ 2013-05-22 21:33 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: pgsql-docs
Makes perfect sense. Thanks and appreciate a prompt response.
Would update the tests accordingly.
--
Robins Tharakan
On 22 May 2013 09:03, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> 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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2013-05-22 21:33 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-05-22 00:20 CREATE OPERATOR query Robins Tharakan <tharakan@gmail.com>
2013-05-22 00:32 ` Robins Tharakan <tharakan@gmail.com>
2013-05-22 01:42 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2013-05-22 02:03 ` Robins Tharakan <tharakan@gmail.com>
2013-05-22 03:33 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2013-05-22 21:33 ` Robins Tharakan <tharakan@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox