[SQL] PostgreSQL Security/Roles/Grants

[Andrew Hall]

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.
http://www.microsoft.com/uk/windows/buy/

Re: [SQL] PostgreSQL Security/Roles/Grants

[Stephen Frost]

Andrew,

* Andrew Hall (andre...@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 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


signature.asc
Description: Digital signature