Any user able to connect to a database can create tables/etc

Robert Watson (robert(at)fledge(dot)watson(dot)org) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Any user able to connect to a database can create tables/etc

Long Description
There is no access control mechanism by which users can be allowed
to connect to a database, but not create tables. Ideally, only the
DBA would be able to create new tables, or some ACL would exist
on the database to limit which users could create tables. As it
stands, this is a severe limitation for sites that wish to allow
mutually suspicious users to host different databases on the same
backend.

One solution might be to add an ACL to the database itself
enumerating various rights for various principals, including:

connect (can connect to the database at all)
create (can create tables, views, et al)
delete (can delete tables, views, et al)

You could imagine other rights being necessary or useful also.
This type of feature would make PostgreSQL far more useful in
ISP/ASP environments.

Sample Code

No file was uploaded with this report

On Fri, Aug 25, 2000 at 03:47:16PM -0400, pgsql-bugs(at)postgresql(dot)org wrote:
> Robert Watson (robert(at)fledge(dot)watson(dot)org) reports a bug with a severity of 2
> The lower the number the more severe it is.
>
> Short Description
> Any user able to connect to a database can create tables/etc
[snip]
>
> connect (can connect to the database at all)
> create (can create tables, views, et al)
> delete (can delete tables, views, et al)
^^^^^^
Shouldn't this one be called 'drop' privilege?

This is something I would also like to have. It is to be noted that another
opensource project (that we all know about..) supports that... :->

There might be a workaround that I am not aware of either... (and if so,
I'd like to hear it!)

just my 1/50$
antoine

--
o Antoine Reid o> Alcohol and calculus <o>
<|> antoiner(at)hansonpublications(dot)com <| don't mix. Never drink |
>\ antoiner(at)edmarketing(dot)com >\ and derive. /<

On Fri, 25 Aug 2000, Antoine Reid wrote:

> > connect (can connect to the database at all)
> > create (can create tables, views, et al)
> > delete (can delete tables, views, et al)
> ^^^^^^
> Shouldn't this one be called 'drop' privilege?

Yup, it should be. I got distracted while filling out the form and typed
in the wrong thing on returning.

> This is something I would also like to have. It is to be noted that
> another opensource project (that we all know about..) supports that...
> :->
>
> There might be a workaround that I am not aware of either... (and if so,
> I'd like to hear it!)

Sounds good to me.

I'd also like to see support for UNIX domain sockets credential passing
authentication for local database connections sometime, but I haven't had
a chance to hack on that at all. In the mean time, I've been forcing
local connections to use TCP/IP via PGHOST=localhost and using identd,
disabling the trust setting, but that's not really ideal.

Robert N M Watson

robert(at)fledge(dot)watson(dot)org http://www.watson.org/~robert/
PGP key fingerprint: AF B5 5F FF A6 4A 79 37 ED 5F 55 E9 58 04 6A B1
TIS Labs at Network Associates, Safeport Network Services