From andrewah@hotmail.com Sun Nov  1 18:04:31 2009
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_--



From sfrost@snowman.net Sun Nov  1 19:13:08 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id EB022643E9D
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Sun,  1 Nov 2009 15:13:08 -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 48604-01-2 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Sun,  1 Nov 2009 19:13:01 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from tamriel.snowman.net (tamriel.snowman.net [72.66.115.51])
	by mail.postgresql.org (Postfix) with ESMTP id 744416387CF
	for <pgsql-sql@postgresql.org>; Sun,  1 Nov 2009 15:10:01 -0400 (AST)
Received: by tamriel.snowman.net (Postfix, from userid 1000)
	id A05D3222AD; Sun,  1 Nov 2009 14:09:59 -0500 (EST)
Date: Sun, 1 Nov 2009 14:09:59 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Andrew Hall <andrewah@hotmail.com>
Cc: PostgreSQL Mailing List <pgsql-sql@postgresql.org>
Subject: Re: PostgreSQL Security/Roles/Grants
Message-ID: <20091101190959.GD17756@tamriel.snowman.net>
References: <COL122-W3402F3A4C1F2DF3A50ECC5CDB40@phx.gbl>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="5asni2pzAWIbIUvo"
Content-Disposition: inline
In-Reply-To: <COL122-W3402F3A4C1F2DF3A50ECC5CDB40@phx.gbl>
X-Editor: Vim http://www.vim.org/
X-Info: http://www.snowman.net
X-Operating-System: Linux/2.6.26-1-amd64 (x86_64)
X-Uptime: 14:08:16 up 66 days,  6:33, 14 users,  load average: 0.10, 0.18,
 0.12
User-Agent: Mutt/1.5.18 (2008-05-17)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200911/2
X-Sequence-Number: 33353


--5asni2pzAWIbIUvo
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Andrew,

* Andrew Hall (andrewah@hotmail.com) wrote:
> 2. Non-default role -> a role which has to be explicitly activated during=
 the lifecycle of an application in order to gain access to database resour=
ces. There are no limits on the number of non-default roles. This type of r=
ole helps us to only provide a user with the minimal set of privileges that=
 they require at any given time, and minimise their access to database reso=
urces.
>=20
> I have looked through the PostgreSQL documentation, and cannot find anyth=
ing analogous to the 'non-default role' which I have outlined above - altho=
ugh obviously it does support roles.
>=20
> I just want to confirm that all roles in postgreSQL are activated at logi=
n time?

No.  You need to read the documentation on the 'noinherit' attribute of
roles.

See:

http://www.postgresql.org/docs/8.4/static/role-membership.html

> Secondly, 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?

No.

	Thanks,

		Stephen

--5asni2pzAWIbIUvo
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkrt3QcACgkQrzgMPqB3kijf9QCeLYBLMR5U2gRIsus8SmuDi2q9
NdEAn3ZS5PZ07J1uorZDR+Ss8m8ARu4c
=wm4i
-----END PGP SIGNATURE-----

--5asni2pzAWIbIUvo--



From jasen@xnet.co.nz Mon Nov  2 10:26:53 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id EAFCE63587A
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon,  2 Nov 2009 06:26:53 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 16714-04-2 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon,  2 Nov 2009 10:26:38 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from ananke.wxnz.net (ananke.wxnz.net [58.28.4.122])
	by mail.postgresql.org (Postfix) with ESMTP id AB915907CD5
	for <pgsql-sql@postgresql.org>; Mon,  2 Nov 2009 06:10:33 -0400 (AST)
Received: from gonzo (ip-118-90-134-179.xdsl.xnet.co.nz [118.90.134.179])
	by ananke.wxnz.net (Postfix) with ESMTP id 5957417051A
	for <pgsql-sql@postgresql.org>; Mon,  2 Nov 2009 23:10:30 +1300 (NZDT)
Received: from news by gonzo with local (Exim 4.69)
	(envelope-from <news@reversiblemaps.ath.cx>) id 1N4tsi-0001Dz-5j
	for pgsql-sql@postgresql.org; Mon, 02 Nov 2009 23:11:24 +1300
To: pgsql-sql@postgresql.org
Path: not-for-mail
From: Jasen Betts <jasen@xnet.co.nz>
Newsgroups: gmane.comp.db.postgresql.sql
Subject: Re: PostgreSQL Security/Roles/Grants
Date: 2 Nov 2009 10:11:23 GMT
Organization: Dis (not Dat) Organisation
Lines: 19
Message-ID: <hcmb8b$3rg$3@reversiblemaps.ath.cx>
References: <COL122-W3402F3A4C1F2DF3A50ECC5CDB40@phx.gbl>
NNTP-Posting-Host: localhost
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Trace: reversiblemaps.ath.cx 1257156683 3952 127.0.0.1 (2 Nov 2009 10:11:23
	GMT)
X-Face: ?)Aw4rXwN5u0~$nqKj`xPz>xHCwgi^q+^?Ri*+R(&uv2=E1Q0Zk(>h!~o2ID@6{uf8s; a
	+M[5[U[QT7xFN%^gR"=tuJw%TXXR'Fp~W;
	(T"1(739R%m0Yyyv*gkGoPA.$b,D.w:z+<'"=-lVT?6
	{T?=R^:W5g|E2#EhjKCa+nt":4b}dU7GYB*HBxn&Td$@f%.kl^:7X8rQWd[NTc"P"u6nkisze/Q;
	8 "9Z{peQF,w)7UjV$c|RO/mQW/NMgWfr5*$-Z%u46"/00mx-,\R'fLPe.)^
User-Agent: slrn/pre1.0.0-2 (Linux)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.518 tagged_above=-10 required=5 tests=AWL=-0.519,
	BAYES_50=0.001
X-Spam-Level: 
X-Archive-Number: 200911/3
X-Sequence-Number: 33354

On 2009-11-01, Andrew Hall <andrewah@hotmail.com> wrote:

> 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.

the only way I know of to provide anything like non-default roles is
via functions declared with "security definer"

> 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?

no (2^16 maybe??) IIRC you do hit an complexity limit, O(n^2) or worse.