TODO item: set proper permissions on non-system schemas

Hi,

I see this TODO item:

* %Set proper permissions on non-system schemas during db creation

I think a quetion here is wich are non-system schemas?

i guess "public" is one of these... but in pg_namespace i have no way
to know that. Is there another way? or maybe we need an attribute to
know that?

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

Jaime Casanova <systemguards(at)gmail(dot)com> writes:
> I see this TODO item:
> * %Set proper permissions on non-system schemas during db creation
> I think a quetion here is wich are non-system schemas?

There's considerable feeling that that TODO item is bogus anyway.
It was pushed in by people who think that the current behavior is
wrong, but they haven't justified a change IMHO. I think the first
part of working on this is to propose a behavior that everyone will
accept --- which schemas to touch is part of that.

regards, tom lane

On 2005-09-01, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Jaime Casanova <systemguards(at)gmail(dot)com> writes:
>> I see this TODO item:
>> * %Set proper permissions on non-system schemas during db creation
>> I think a quetion here is wich are non-system schemas?
>
> There's considerable feeling that that TODO item is bogus anyway.
> It was pushed in by people who think that the current behavior is
> wrong, but they haven't justified a change IMHO. I think the first
> part of working on this is to propose a behavior that everyone will
> accept --- which schemas to touch is part of that.

The issue that I've seen is that currently, allowing non-superusers to
create databases in a useful manner requires all sorts of hoop-jumping
to allow the database owner to end up owning the "public" schema. The
simplest solution at present is to remove public from template1 and have
the new db owner create it themselves, which of course interferes with
the (pre 8.1) defaults for createlang etc. (Since the new db owner can't,
for example, install plpgsql themselves, it has to be installed in
template1 in some schema _other_ than public.)

(Another wart that could do with looking into is that such a non-superuser
database owner can't prevent xid wrap in his database regardless of how
often he vacuums it.)

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

Andrew - Supernews <andrew+nonews(at)supernews(dot)com> writes:
> On 2005-09-01, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> There's considerable feeling that that TODO item is bogus anyway.

> The issue that I've seen is that currently, allowing non-superusers to
> create databases in a useful manner requires all sorts of hoop-jumping
> to allow the database owner to end up owning the "public" schema.

The part of this that hasn't been justified to my satisfaction is *why*
the database owner should own the public schema. He doesn't get to own,
say, the integer plus operator.

There is some merit in the thought that the DB owner should be able to
grant and revoke access on the public schema, but that no longer
requires ownership, only membership in an appropriate role.

> (Another wart that could do with looking into is that such a non-superuser
> database owner can't prevent xid wrap in his database regardless of how
> often he vacuums it.)

The DB owner shouldn't really be responsible for vacuuming anyway.

regards, tom lane

On 2005-09-01, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Andrew - Supernews <andrew+nonews(at)supernews(dot)com> writes:
>> On 2005-09-01, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> There's considerable feeling that that TODO item is bogus anyway.
>
>> The issue that I've seen is that currently, allowing non-superusers to
>> create databases in a useful manner requires all sorts of hoop-jumping
>> to allow the database owner to end up owning the "public" schema.
>
> The part of this that hasn't been justified to my satisfaction is *why*
> the database owner should own the public schema.

He should certainly be able to drop it, in addition to being able to
control access to it.

> There is some merit in the thought that the DB owner should be able to
> grant and revoke access on the public schema, but that no longer
> requires ownership, only membership in an appropriate role.

How would that work without superuser intervention, given that the
ownership of public would be the same in all databases regardless of
who created them?

>> (Another wart that could do with looking into is that such a non-superuser
>> database owner can't prevent xid wrap in his database regardless of how
>> often he vacuums it.)
>
> The DB owner shouldn't really be responsible for vacuuming anyway.

Debatable.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

Andrew - Supernews <andrew+nonews(at)supernews(dot)com> writes:
> On 2005-09-01, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> There is some merit in the thought that the DB owner should be able to
>> grant and revoke access on the public schema, but that no longer
>> requires ownership, only membership in an appropriate role.

> How would that work without superuser intervention, given that the
> ownership of public would be the same in all databases regardless of
> who created them?

Change the ownership of public in template1 to be a "dbadmin" group.
Grant membership in "dbadmin" to all the DB owners. End of problem.

regards, tom lane

On 2005-09-01, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Andrew - Supernews <andrew+nonews(at)supernews(dot)com> writes:
>> On 2005-09-01, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> There is some merit in the thought that the DB owner should be able to
>>> grant and revoke access on the public schema, but that no longer
>>> requires ownership, only membership in an appropriate role.
>
>> How would that work without superuser intervention, given that the
>> ownership of public would be the same in all databases regardless of
>> who created them?
>
> Change the ownership of public in template1 to be a "dbadmin" group.
> Grant membership in "dbadmin" to all the DB owners. End of problem.

Only if all db owners are equivalent.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

Andrew - Supernews <andrew+nonews(at)supernews(dot)com> writes:
> On 2005-09-01, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Change the ownership of public in template1 to be a "dbadmin" group.
>> Grant membership in "dbadmin" to all the DB owners. End of problem.

> Only if all db owners are equivalent.

If you don't want some of them to have control over their public
schemas, you don't grant them membership in this group. (Which,
by the way, is *more* flexible than a hack to reassign the schema
ownership to the DB owner automatically.)

regards, tom lane

On 2005-09-01, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Andrew - Supernews <andrew+nonews(at)supernews(dot)com> writes:
>> On 2005-09-01, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Change the ownership of public in template1 to be a "dbadmin" group.
>>> Grant membership in "dbadmin" to all the DB owners. End of problem.
>
>> Only if all db owners are equivalent.
>
> If you don't want some of them to have control over their public
> schemas, you don't grant them membership in this group.

What if you don't want them to have control over _each other's_ public
schemas?

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

Tom Lane wrote:

>Change the ownership of public in template1 to be a "dbadmin" group.
>Grant membership in "dbadmin" to all the DB owners. End of problem.
>
>
>
>

Won't that suddenly grant the owner of foo_db dbadmin rights in bar_db?
That seems to violate the principle of least surprise.

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> Tom Lane wrote:
>> Change the ownership of public in template1 to be a "dbadmin" group.
>> Grant membership in "dbadmin" to all the DB owners. End of problem.

> Won't that suddenly grant the owner of foo_db dbadmin rights in bar_db?
> That seems to violate the principle of least surprise.

I'm assuming here that the various dbowners aren't even allowed to
connect to each others' databases.

regards, tom lane

On 2005-09-01, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>> Tom Lane wrote:
>>> Change the ownership of public in template1 to be a "dbadmin" group.
>>> Grant membership in "dbadmin" to all the DB owners. End of problem.
>
>> Won't that suddenly grant the owner of foo_db dbadmin rights in bar_db?
>> That seems to violate the principle of least surprise.
>
> I'm assuming here that the various dbowners aren't even allowed to
> connect to each others' databases.

Which implies either that you limit each dbowner to one db (in which case
why give them createdb privilege in the first place) or that you require
superuser intervention to modify pg_hba for each database created.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services