Thread: PostgreSQL Security/Roles/Grants
Hi,<br /><br />I come from an Oracle background and wonder if anyone could provide some background information on how bestto implement an application security scheme in PostgreSQL using roles / grants.<br /><br />I'd just like to outline firsthow I'd approach security in Oracle:<br /><br />There is a notion of both:<br /><br />1. Default Roles -> a rolewhich is activated at login time. Oracle imposes a limit on the number of default roles which any given user can have.<br/><br />2. Non-default role -> a role which has to be explicitly activated during the lifecycle of an applicationin order to gain access to database resources. There are no limits on the number of non-default roles. This typeof role helps us to only provide a user with the minimal set of privileges that they require at any given time, and minimisetheir access to database resources.<br /><br />I have looked through the PostgreSQL documentation, and cannot findanything analogous to the 'non-default role' which I have outlined above - although obviously it does support roles.<br/><br />I just want to confirm that all roles in postgreSQL are activated at login time?<br /><br />Secondly, isthere a limit on the number of roles which can be assigned to a user (or more accurately a 'login role') in postgreSQL?<br/><br />Many thanks,<br /><br />Andrew.<br /><br /><br /><hr />New Windows 7: Find the right PC for you. <ahref="http://www.microsoft.com/uk/windows/buy/" target="_new">Learn more.</a>
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 gainaccess to database resources. There are no limits on the number of non-default roles. This type of role helps us to onlyprovide a user with the minimal set of privileges that they require at any given time, and minimise their access to databaseresources. > > I have looked through the PostgreSQL documentation, and cannot find anything analogous to the 'non-default role' whichI 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') inpostgreSQL? No. Thanks, Stephen
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.