PostgreSQL Security/Roles/Grants

[Andrew Hall-6]

Some parts of this message have been removed. Learn more about Nabble's security policy.

Hi,

I come from an Oracle background and wonder if anyone could provide some background information on how best to implement an application security scheme 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 the lifecycle of an application in order to gain access to database resources. There are no limits on the number of non-default roles. This type of role 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 resources.

I have looked through the PostgreSQL documentation, and cannot find anything analogous to the 'non-default role' which I have outlined above - although obviously it does support roles.

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

Secondly, is there a limit on the number of roles which can be assigned to a user (or more accurately a 'login role') in postgreSQL?

Many thanks,

Andrew.

---

New Windows 7: Find the right PC for you. Learn more.

[Stephen Frost]

Andrew,

* Andrew Hall (andrewah@...) 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 resources. There are no limits on the number of non-default roles. This type of role 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 resources.
>
> I have looked through the PostgreSQL documentation, and cannot find anything analogous to the 'non-default role' which I have outlined above - although obviously it does support roles.
>
> I just want to confirm that all roles in postgreSQL are activated at login 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 to a user (or more accurately a 'login role') in postgreSQL?

No.

        Thanks,

                Stephen

[Jasen Betts-5]

On 2009-11-01, Andrew Hall <andrewah@...> 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.

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql