Skip site navigation (1) Skip section navigation (2)

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: (view raw, whole thread or download thread mbox)
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

pgsql-admin by date

Next:From: Simone TelliniDate: 2001-08-21 13:41:53
Subject: Re: Multiple Servers
Previous:From: C. BensendDate: 2001-08-21 12:05:41
Subject: Re: Multiple Servers

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group