Re: Schema (namespace) privilege details - Mailing list pgsql-hackers

> > Another thing that would be needed to prevent users from creating
new
> > tables is to prevent them from creating schemas for themselves.  I
am not
> > sure how to handle that --- should the right to create schemas be
treated
> > as a user property (a column of pg_shadow), or should it be
attached
> > somehow to the database (and if the latter, how)?
>
> I think only the database owner should be able to create schemas in
> their own database. That way if I want a user to be able to create
> tables, I just grant them CREATE in the public schema, or create a
> schema for them.

If owners could be groups, I'd tend to agree.  I'm tired of setting up
general admin logins and giving a group of people a single key for
doing system changes.  Anytime someone has to leave the company we run
around and issue new keys.

I really want to allow a small group to have control of the
development db but not in other DBs (other projects generally).
Granting superuser status isn't appropriate.  But, giving a group
control over an individual database (schema or otherwise) is extreamly
useful.  Production basically has the same thing but a different
group -- who know enough not to touch anything without a patch and
change control being issued by development which has been approved by
the resident DBA.

I'd really like to see a schema owner have full control over all
objects in a schema, and likewise a database owner have full control
over their database.   My POV for large systems.



Lets look at small ones.  Database usage in webhosting companies is on
the rise.  With the changes to pg_hba.conf to allow specific users
access to specific databases it can now be easily sold as a part of a
hosting package.

FTP accounts on a server always have a master.  Larger clients will
often create a directory structure in such a way that various web
developers can work in various parts without having to worry about
accidentally touching others stuff. BUT the master account can still
override the entire set if necessary.  They own parent, they flip
permissions to suit themselves if they're blocked by them.


Postgresql needs something similar to be easily sold as a service.
The person actually paying for the DB installation would of course be
the owner of the DB.

In the event of a company, the buyer may allow others to do work
(consultants? employee? friend?).  They create a user, a schema and
put the user to work.  User does something they shouldn't and is
removed for it.  Owner wants to clean up the mess or continue
maintainence.  How do they do this?  Owner isn't a superuser as
they're simply buying DB services from an Application hosting company.
They can't login as the user as they don't have the password (user
took it with them). ** I forget whether changing ownership of an
object would require superuser access or just ownership of the parent
object. **  So, they're left with calling the hosting company to clean
up the mess for them (not something we'd want to do).


With Postgresql 7.3 the above is a likley scenario at the company I
work for as we would like to offer this type of service along side the
other DBs we currently host -- and it's very close to being feasible.
What I need is a per DB superuser / supergroup which cannot do things
like drop database (even their own preferably as that ends in a tech
support call to have it recreated), create untrusted procedures /
languages, and other nerveracking abilities.

Giving the database owner, or better a group at the database level an
ACL to accomplish any job within their own database (including user
creation -- but we can get around that with a control panel to do it
for them) that an otherwise untrusted user should be allowed to looks
very good to me.