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

BUG #1113: Default template databases grant CREATE to PUBLIC

From
"PostgreSQL Bugs List"
Date:
The following bug has been logged online:

Bug reference:      1113
Logged by:          Oliver Elphick

Email address:      postgresql@packages.debian.org

PostgreSQL version: 7.4

Operating system:   Debian Linux

Description:        Default template databases grant CREATE to PUBLIC

Details:

The default database created by initdb (in template0 and template1) grants
CREATE permission on the public schema to PUBLIC.  Therefore any user is
able to create a table or function, including a function that can bring down
the machine by (for example) recursively calling itself.  By default, any
user can create objects in template1, as well.

The default should be for CREATE permissions on the public schema to be
revoked from PUBLICc.

This might break old applications which have not been updated to take
account of schemas; the workaround for them would be to grant permissions in
template1.public as appropriate.

Debian bug ref: #239811

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

From
Tom Lane
Date:
"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> The default should be for CREATE permissions on the public schema to be
> revoked from PUBLICc.

Not a chance.

            regards, tom lane

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

From
Tom Lane
Date:
Oliver Elphick <olly@lfix.co.uk> writes:
> On Wed, 2004-03-24 at 15:55, Tom Lane wrote:
>> "PostgreSQL Bugs List" <pgsql-bugs@postgresql.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