RE: [ADMIN] Any user can create table in any database?

[KOOPMAN,JON (A-SantaClara,ex1)]

Thanks for the quick response,

My thoughts on this are:

 o SQL command "create database" should take argument for
   DBA username.  Currently you must do a create database,
   and then if you want it owned by someone else, update
   the "pg_database" class manually with a 'sysid' for
   the new DBA extracted from the pg_user/pg_shadow class.
 o By default only DBA (or superuser enabled DB users) can
   create new objects in a database.
 o Can extend ability to create objects in database to other
   users through grant/revoke calls on the database.
   Essentially a database has an ACL similar to regular
   relations (classes, views, indexes, etc.).  grant/revoke
   privileges can only be set on a database by a superuser,
   the DBA, or another user given grant/revoke privilege
   (distinctly different from write privilege as many more
   users would need to create tables than change overall
   permissions on the database.)
 o Can change default permissions for new databases by using
   grant/revoke calls on the "template1" database.  Of course
   only superusers or the overall DB installation admin can
   make these changes on the "template1" database.
 o Possibly add a permission structure to database level ACL
   that can turn off read privilege to a database to certain
   /all users.  Is there any reason to limit database
   connectivity different than grant/revoke on individual
   database objects?

Thanks,

Jon Koopman                                     Agilent Technologies
[EMAIL PROTECTED]         R&D Software Development Engineer

-----Original Message-----
From: Peter Eisentraut [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 16, 2000 4:44 AM
To: KOOPMAN,JON (A-SantaClara,ex1)
Cc: '[EMAIL PROTECTED]'
Subject: Re: [ADMIN] Any user can create table in any database?


On Wed, 15 Mar 2000, KOOPMAN,JON (A-SantaClara,ex1) wrote:

> In the documentation page:
> 
> http://www.posgresql.org/doxlist.html - Chapter 8. Security in
> the first section I see:
> 
> "Each user in Postgres is assigned a username, and (optionally) a
> password.  By default, users do not have write access to databases
> they did not create."

The documentation is wrong. You can currently create anything in any
database you have access to. We're currently discussing a way to improve
that in 7.1. Care to share your ideas?

-- 
Peter Eisentraut                  Sernanders v�g 10:115
[EMAIL PROTECTED]                   75262 Uppsala
http://yi.org/peter-e/            Sweden