Thread: Who should own database?

Hi.
If one have database web_site_data
And two users: php_script and boris_developer
Who should own database?

I have some point on that when using mysql, but PostgreSQL role
management looks much more versatile, so I am looking for opinion of
PostgreSQL users. What is your practice and why?

Thanks.

--As of February 10, 2015 4:04:55 PM +0300, petrov.boris.v@mail.ru is
alleged to have said:

> If one have database web_site_data
> And two users: php_script and boris_developer
> Who should own database?
>
> I have some point on that when using mysql, but PostgreSQL role
> management looks much more versatile, so I am looking for opinion of
> PostgreSQL users. What is your practice and why?

--As for the rest, it is mine.

Who needs to do what with the database?  The owner should be the user that
needs the permissions that come with being the owner.  (Or the owner should
be a specific account set up just to be the owner, and the others should
only have the permissions they need.)

From just the names, I'd suggest if you are picking one of the two, to have
boris_developer be the owner, as php_script sounds like a web interface,
and it's usually best not to give web interfaces any permission they don't
absolutely *need*.

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------

Daniel Staal wrote
> --As of February 10, 2015 4:04:55 PM +0300,

> petrov.boris.v@

>  is
> alleged to have said:
>
>> If one have database web_site_data
>> And two users: php_script and boris_developer
>> Who should own database?
>>
>> I have some point on that when using mysql, but PostgreSQL role
>> management looks much more versatile, so I am looking for opinion of
>> PostgreSQL users. What is your practice and why?
>
> --As for the rest, it is mine.
>
> Who needs to do what with the database?  The owner should be the user that
> needs the permissions that come with being the owner.  (Or the owner
> should
> be a specific account set up just to be the owner, and the others should
> only have the permissions they need.)
>
>From just the names, I'd suggest if you are picking one of the two, to
have
> boris_developer be the owner, as php_script sounds like a web interface,
> and it's usually best not to give web interfaces any permission they don't
> absolutely *need*.

I tend to use both user roles and group roles.  Schema object ownership is
given to group roles.  There is also a user role that is given membership in
this group role.  Only your schema loader code needs to use this user all
others get roles that do not inherit from this owner role.  The owner login
role should issue "set role [owner group role]" before issuing create
statements and the like.

Maybe a bit of over engineering initially but it's worth considering.
Slightly simpler is to make the owner role a login role.  In either case
client application users should never be able to get owner permissions.

David J.



--
View this message in context: http://postgresql.nabble.com/Who-should-own-database-tp5837354p5837380.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

On 10.02.2015 18:34, Daniel Staal wrote:
> boris_developer be the owner, as php_script sounds like a web interface,
> and it's usually best not to give web interfaces any permission they don't
> absolutely *need*
You kind of confirming the way I am doing it most of the time. Web is
limited to minimum permissions it particularly uses and the owner is
human user.
 > Or the owner should be a specific account set up just to be the owner
That option also come to mind, and seems logical.
Thank you for considerations.

On 10.02.2015 18:55, David G Johnston wrote:
 > Maybe a bit of over engineering initially but it's worth considering.
 > Slightly simpler is to make the owner role a login role.  In either
 > case client application users should never be able to get owner
 > permissions.
Making LOGIN-less group-role an owner looks complicated to me at least
for now (not even sure I get it completely), but definitely worth
considering. Thank you for sharing.