Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 63DE2633535
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Sun,  1 Nov 2009 14:04:31 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 40378-10 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Sun,  1 Nov 2009 18:04:22 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from col0-omc2-s14.col0.hotmail.com (col0-omc2-s14.col0.hotmail.com
	[65.55.34.88])
	by mail.postgresql.org (Postfix) with ESMTP id 0EC8A633468
	for <pgsql-sql@postgresql.org>; Sun,  1 Nov 2009 14:04:21 -0400 (AST)
Received: from COL122-W34 ([65.55.34.72]) by col0-omc2-s14.col0.hotmail.com
	with Microsoft SMTPSVC(6.0.3790.3959);
	Sun, 1 Nov 2009 10:04:14 -0800
Message-ID: <COL122-W3402F3A4C1F2DF3A50ECC5CDB40@phx.gbl>
Content-Type: multipart/alternative;
	boundary="_af92c9f9-e037-4da7-8ff5-8f8a6f0666ef_"
X-Originating-IP: [86.134.103.165]
From: Andrew Hall <andrewah@hotmail.com>
To: PostgreSQL Mailing List <pgsql-sql@postgresql.org>
Subject: PostgreSQL Security/Roles/Grants
Date: Sun, 1 Nov 2009 18:04:14 +0000
Importance: Normal
MIME-Version: 1.0
X-OriginalArrivalTime: 01 Nov 2009 18:04:14.0686 (UTC)
	FILETIME=[B8D88FE0:01CA5B1D]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.109 tagged_above=-10 required=5
	tests=BAYES_05=-1.11, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200911/1
X-Sequence-Number: 33352

--_af92c9f9-e037-4da7-8ff5-8f8a6f0666ef_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Hi=2C

I come from an Oracle background and wonder if anyone could provide some ba=
ckground information on how best to implement an application security schem=
e in PostgreSQL using roles / grants.

I'd just like to outline first how I'd approach security in Oracle:

There is a notion of both:

1. Default Roles -> a role which is activated at login time. Oracle imposes=
 a limit on the number of default roles which any given user can have.

2. Non-default role -> a role which has to be explicitly activated during t=
he lifecycle of an application in order to gain access to database resource=
s. There are no limits on the number of non-default roles. This type of rol=
e helps us to only provide a user with the minimal set of privileges that t=
hey require at any given time=2C and minimise their access to database reso=
urces.

I have looked through the PostgreSQL documentation=2C and cannot find anyth=
ing analogous to the 'non-default role' which I have outlined above - altho=
ugh obviously it does support roles.

I just want to confirm that all roles in postgreSQL are activated at login =
time?

Secondly=2C is there a limit on the number of roles which can be assigned t=
o a user (or more accurately a 'login role') in postgreSQL?

Many thanks=2C

Andrew.

 		 	   		 =20
_________________________________________________________________
New Windows 7: Find the right PC for you. Learn more.
http://www.microsoft.com/uk/windows/buy/=

--_af92c9f9-e037-4da7-8ff5-8f8a6f0666ef_
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:Verdana
}
--></style>
</head>
<body class=3D'hmmessage'>
Hi=2C<br><br>I come from an Oracle background and wonder if anyone could pr=
ovide some background information on how best to implement an application s=
ecurity scheme in PostgreSQL using roles / grants.<br><br>I'd just like to =
outline first how I'd approach security in Oracle:<br><br>There is a notion=
 of both:<br><br>1. Default Roles -&gt=3B a role which is activated at logi=
n time. Oracle imposes a limit on the number of default roles which any giv=
en user can have.<br><br>2. Non-default role -&gt=3B a role which has to be=
 explicitly activated during the lifecycle of an application in order to ga=
in access to database resources. There are no limits on the number of non-d=
efault roles. This type of role helps us to only provide a user with the mi=
nimal set of privileges that they require at any given time=2C and minimise=
 their access to database resources.<br><br>I have looked through the Postg=
reSQL documentation=2C and cannot find anything analogous to the 'non-defau=
lt role' which I have outlined above - although obviously it does support r=
oles.<br><br>I just want to confirm that all roles in postgreSQL are activa=
ted at login time?<br><br>Secondly=2C is there a limit on the number of rol=
es which can be assigned to a user (or more accurately a 'login role') in p=
ostgreSQL?<br><br>Many thanks=2C<br><br>Andrew.<br><br> 		 	   		  <br /><h=
r />New Windows 7: Find the right PC for you. <a href=3D'http://www.microso=
ft.com/uk/windows/buy/' target=3D'_new'>Learn more.</a></body>
</html>=

--_af92c9f9-e037-4da7-8ff5-8f8a6f0666ef_--