pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
PostgreSQL Security/Roles/Grants 3+ messages / 3 participants [nested] [flat]
* PostgreSQL Security/Roles/Grants @ 2009-11-01 18:04 Andrew Hall <andrewah@hotmail.com> 2009-11-01 19:09 ` Re: PostgreSQL Security/Roles/Grants Stephen Frost <sfrost@snowman.net> 2009-11-02 10:11 ` Re: PostgreSQL Security/Roles/Grants Jasen Betts <jasen@xnet.co.nz> 0 siblings, 2 replies; 3+ messages in thread From: Andrew Hall @ 2009-11-01 18:04 UTC (permalink / raw) To: pgsql-sql 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/= ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: PostgreSQL Security/Roles/Grants 2009-11-01 18:04 PostgreSQL Security/Roles/Grants Andrew Hall <andrewah@hotmail.com> @ 2009-11-01 19:09 ` Stephen Frost <sfrost@snowman.net> 1 sibling, 0 replies; 3+ messages in thread From: Stephen Frost @ 2009-11-01 19:09 UTC (permalink / raw) To: Andrew Hall <andrewah@hotmail.com>; +Cc: pgsql-sql 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 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 Attachments: signature.asc (application/pgp-signature, 196B) download ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: PostgreSQL Security/Roles/Grants 2009-11-01 18:04 PostgreSQL Security/Roles/Grants Andrew Hall <andrewah@hotmail.com> @ 2009-11-02 10:11 ` Jasen Betts <jasen@xnet.co.nz> 1 sibling, 0 replies; 3+ messages in thread From: Jasen Betts @ 2009-11-02 10:11 UTC (permalink / raw) To: pgsql-sql 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. ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2009-11-02 10:11 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2009-11-01 18:04 PostgreSQL Security/Roles/Grants Andrew Hall <andrewah@hotmail.com> 2009-11-01 19:09 ` Stephen Frost <sfrost@snowman.net> 2009-11-02 10:11 ` Jasen Betts <jasen@xnet.co.nz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox