Re: Multiple Servers

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Chad R(dot) Larson" <chad(at)DCFinc(dot)com>
Cc: "C(dot) Bensend" <benny(at)bennyvision(dot)com>, pgsql-admin(at)postgresql(dot)org
Subject: Re: Multiple Servers
Date: 2001-08-21 13:35:41
Message-ID: 28937.998400941@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

"Chad R. Larson" <chad(at)DCFinc(dot)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

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Simone Tellini 2001-08-21 13:41:53 Re: Multiple Servers
Previous Message C. Bensend 2001-08-21 12:05:41 Re: Multiple Servers