Re: BUG #1113: Default template databases grant CREATE to PUBLIC

Oliver Elphick <olly(at)lfix(dot)co(dot)uk> writes:
> On Wed, 2004-03-24 at 15:55, Tom Lane wrote:
>> "PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)org> writes:
> The default should be for CREATE permissions on the public schema to be
> revoked from PUBLICc.
>>
>> Not a chance.

> But why?

I didn't think this actually deserved any discussion, but if you insist:

(a) This is a much bigger backwards-compatibility hit than you claim.
All applications designed before 7.3 (and a lot of later ones) are going
to assume that they can create objects without thinking about
schema-level privileges.

(b) There are several options for a DBA to change that behavior if he
doesn't like it, ranging from dropping create on public, to dropping
public completely, to actually solving the complained-of problem by
revoking public usage rights on the function languages. He can also
choose to do any of these things in template1 so that they automatically
apply to later-created databases. So this is not a matter of lack of
functionality, it is a question of whether to impose your notion of
a good default behavior on everyone else.

(c) The notion that forbidding function creation improves security is
wrongheaded. Anyone who can issue arbitrary SQL commands can tie
your database in knots anyway, for instance by requesting huge joins.
Furthermore it is trivial to create a temporary function, eg

joedb=> create temp table t(f1 int);
CREATE TABLE
joedb=> create function pg_temp_1.f1() returns int as 'select 1' language sql;
CREATE FUNCTION

so unless you propose to also disable temp-table creation by default,
revoking object creation in PUBLIC isn't going to slow down an attacker
at all.

regards, tom lane