Обсуждение: Strange behavior of "grant temp on schema"

Hello!

Please help to understand why the line 35 ("grant temp on schema public
to sec_privilege") generates an error "ERROR:  invalid privilege type
TEMP for schema" and successfully created function "readonly" at the end
of listing, but if it is removed, the function in lines 45-49 will not
be created?

Note also that if I try to issue a "grant temp on database security to
sec_privilege;", it also does not result to the creation of the
"readonly" function.

PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit


   1 \set AUTOCOMMIT off
   2 \set ON_ERROR_STOP on
   3 \timing
   4
   5 begin transaction;
   6
   7 create user sec_owner inherit;
   8 create user sec_privilege inherit;
   9 create user sec_ordinary inherit;
  10
  11 commit;
  12
  13 \set AUTOCOMMIT on
  14
  15 create database security with owner sec_owner;
  16
  17 \c security
  18 \set AUTOCOMMIT off
  19
  20 begin transaction;
  21
  22 revoke all on database security from public;
  23 revoke all on schema public from public;
  24
  25 grant connect on database security to sec_privilege;
  26 grant connect on database security to sec_ordinary;
  27
  28 grant create on database security to sec_privilege;
  29 grant create on schema public to sec_privilege;
  30
  31 \set ON_ERROR_STOP off
  32
  33 -- This statement generates error: "ERROR:  invalid privilege type
TEMP for schema"
  34 -- However without this statement, "create function" is fail... Why?
  35 grant temp on schema public to sec_privilege;
  36
  37 commit;
  38
  39 \c "dbname=security user=sec_privilege"
  40
  41 \set ON_ERROR_STOP on
  42
  43 begin transaction;
  44
  45 create or replace function readonly() returns text as $$
  46 begin
  47     return user;
  48 end
  49 $$ language plpgsql volatile security definer;
  50
  51 commi

--
Regards, Andrew G. Saushkin

I apologize for my carelessness. Error rollback code including all
completed "revoke". Therefore, users can create functions. If you add
another commit before "grant temp on schema public to sec_privilege;" it
will be seen that create a function is also not possible.

>   14
>   15 create database security with owner sec_owner;
>   16
>   17 \c security
>   18 \set AUTOCOMMIT off
>   19
>   20 begin transaction;
>   21
>   22 revoke all on database security from public;
>   23 revoke all on schema public from public;
>   24
>   25 grant connect on database security to sec_privilege;
>   26 grant connect on database security to sec_ordinary;
>   27
>   28 grant create on database security to sec_privilege;
>   29 grant create on schema public to sec_privilege;

insert commit here...

>   30
>   31 \set ON_ERROR_STOP off
>   32
>   33 -- This statement generates error: "ERROR:  invalid privilege type
> TEMP for schema"
>   34 -- However without this statement, "create function" is fail... Why?
>   35 grant temp on schema public to sec_privilege;
>   36
>   37 commit;
>   38