Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id AB61C64FCCB
	for <pgsql-admin-postgresql.org@postgresql.org>;
	Mon, 15 Sep 2008 18:07:29 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 11058-04 for <pgsql-admin-postgresql.org@postgresql.org>;
	Mon, 15 Sep 2008 18:07:26 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from BAY0-OMC3-S10.bay0.hotmail.com (bay0-omc3-s10.bay0.hotmail.com
	[65.54.246.210])
	by postgresql.org (Postfix) with ESMTP id 1672D64FCC6
	for <pgsql-admin@postgresql.org>; Mon, 15 Sep 2008 18:07:24 -0300 (ADT)
Received: from BAY105-W19 ([65.54.224.119]) by BAY0-OMC3-S10.bay0.hotmail.com
	with Microsoft SMTPSVC(6.0.3790.3959);
	Mon, 15 Sep 2008 14:07:23 -0700
Message-ID: <BAY105-W1914C2300B5F700EB8BC82A6520@phx.gbl>
Content-Type: multipart/alternative;
	boundary="_1945eecd-401d-48b8-9e9c-8ad92e3bc3c5_"
X-Originating-IP: [75.171.133.90]
From: Trent Pingenot <pintj@hotmail.com>
To: <pgsql-admin@postgresql.org>
Subject: Permissions Scenerio
Date: Mon, 15 Sep 2008 15:07:22 -0600
Importance: Normal
MIME-Version: 1.0
X-OriginalArrivalTime: 15 Sep 2008 21:07:23.0091 (UTC)
	FILETIME=[0C411A30:01C91777]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200809/148
X-Sequence-Number: 30241

--_1945eecd-401d-48b8-9e9c-8ad92e3bc3c5_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hello list=2C

I've been a PostGres user for a while=2C but am just now having to implemen=
t some security for a project.  The hope is that we can create an environme=
nt through Roles that would allow users the ability to create a database(s)=
 and have access to their database(s) but not have access to others' databa=
ses. I've been able to get part of the way there to create a user with crea=
teDB privileges and recording them in the pg_hba.conf file. However=2C when=
 my test user creates a new database=2C they are the owner of that database=
 but can't use it.  Is there a way to avoid having to add user /database en=
try in
the pg_hba.conf file every time a user creates a new database?

Here is the current hba conf file I have

host    all         postgres         127.0.0.1/32          md5  # Super use=
r admin account
host    samerole         testuser         127.0.0.1/32          md5
host    template_db         all         127.0.0.1/32          md5

Under this conf file my testuser can create a new db=2C but then doesn't ha=
ve access to it b/c no entry exsists in the conf file. =20

This is probably an easy change but being new to PostGres security I'm not =
seeing it.=20

If I can't do this with Roles=2C can I do it with schemas to give users onl=
y access to their own stuff within a database?

Thanks in advance for any guidance

- Trent



--_1945eecd-401d-48b8-9e9c-8ad92e3bc3c5_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<style>
.hmmessage P
{
margin:0px=3B
padding:0px
}
body.hmmessage
{
FONT-SIZE: 10pt=3B
FONT-FAMILY:Tahoma
}
</style>
</head>
<body class=3D'hmmessage'><div style=3D"text-align: left=3B">Hello list=2C<=
br><br>I've been a PostGres user for a while=2C but am just now having to i=
mplement some security for a project.&nbsp=3B The hope is that we can creat=
e an environment through Roles that would allow users the ability to create=
 a database(s) and have access to their database(s) but not have access to =
others' databases. I've been able to get part of the way there to create a =
user with createDB privileges and recording them in the pg_hba.conf file. H=
owever=2C when my test user creates a new database=2C they are the owner of=
 that database but can't use it.&nbsp=3B Is there a way to avoid having to =
add user /database entry in<br>the pg_hba.conf file every time a user creat=
es a new database?<br><br>Here is the current hba conf file I have<br><br>h=
ost&nbsp=3B&nbsp=3B&nbsp=3B all&nbsp=3B&nbsp=3B&nbsp=3B&nbsp=3B&nbsp=3B&nbs=
p=3B&nbsp=3B&nbsp=3B postgres&nbsp=3B&nbsp=3B&nbsp=3B&nbsp=3B&nbsp=3B&nbsp=
=3B&nbsp=3B&nbsp=3B 127.0.0.1/32&nbsp=3B&nbsp=3B&nbsp=3B&nbsp=3B&nbsp=3B&nb=
sp=3B&nbsp=3B&nbsp=3B&nbsp=3B md5&nbsp=3B # Super user admin account<br>hos=
t&nbsp=3B&nbsp=3B&nbsp=3B samerole&nbsp=3B&nbsp=3B&nbsp=3B&nbsp=3B&nbsp=3B&=
nbsp=3B&nbsp=3B&nbsp=3B testuser &nbsp=3B&nbsp=3B&nbsp=3B&nbsp=3B&nbsp=3B&n=
bsp=3B&nbsp=3B 127.0.0.1/32&nbsp=3B&nbsp=3B&nbsp=3B&nbsp=3B&nbsp=3B&nbsp=3B=
&nbsp=3B&nbsp=3B&nbsp=3B md5<br>host&nbsp=3B&nbsp=3B&nbsp=3B template_db &n=
bsp=3B&nbsp=3B&nbsp=3B&nbsp=3B&nbsp=3B&nbsp=3B&nbsp=3B all&nbsp=3B&nbsp=3B&=
nbsp=3B&nbsp=3B&nbsp=3B&nbsp=3B&nbsp=3B&nbsp=3B 127.0.0.1/32&nbsp=3B&nbsp=
=3B&nbsp=3B&nbsp=3B&nbsp=3B&nbsp=3B&nbsp=3B&nbsp=3B&nbsp=3B md5<br><br>Unde=
r this conf file my testuser can create a new db=2C but then doesn't have a=
ccess to it b/c no entry exsists in the conf file.&nbsp=3B <br><br>This is =
probably an easy change but being new to PostGres security I'm not seeing i=
t. <br><br>If I can't do this with Roles=2C can I do it with schemas to giv=
e users only access to their own stuff within a database?<br><br>Thanks in =
advance for any guidance<br><br>- Trent<br><br><br></div></body>
</html>=

--_1945eecd-401d-48b8-9e9c-8ad92e3bc3c5_--