"Chad R. Larson" <chad@DCFinc.com> writes:
> So, create a database for the user as the user, but deny him the ability to
> create databases or users.
This is actually a tad easier said than done, since the only way we
provide to create a database owned by a user is to create it *as* that
user. (CREATE DATABASE probably ought to have an OWNER option available
only to superusers, now that I think about it.) So you have two
possible approaches:
1. create user with createdb option, become user, create his database,
become superuser again, ALTER USER user NOCREATEDB.
2. create user without createdb option, create his database (as
yourself), then alter pg_database to make him the owner:
update pg_database set datdba =
(select usesysid from pg_shadow where usename = 'foo')
where datname = 'foo';
> Then he can dick with his own database to his heart's desire, but that
> would be his only sandbox.
You'll probably also want to ensure that each user can only connect to
his own database. The 'sameuser' option in pg_hba.conf will help here.
Don't forget to leave an escape hatch for the superuser (ie, you) to
connect to anything.
regards, tom lane