Strange behavior of "grant temp on schema"

From: "Andrew G(dot) Saushkin" <ags(at)bsse(dot)ru>
To: pgsql-general(at)postgresql(dot)org
Subject: Strange behavior of "grant temp on schema"
Date: 2013-08-02 20:44:56
Message-ID: 51FC1A48.9020009@bsse.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrew G. Saushkin 2013-08-02 21:01:43 Re: Strange behavior of "grant temp on schema"
Previous Message Jeff Janes 2013-08-02 19:08:47 Re: demystifying nested loop vs. merge join query plan choice