Andreas wrote:
> who should own the db objects?
> I once read one should not let postgres or any other superuser own the
> tables and what not.
> Instead one should better create a separate user role with little
> privileges to be the owner.
> I'm not quite sure why this was abvised. Maybe like not using root for
> everyday work.
>
> What is commonly regarded as good design in this issue?
It is good practice to assign as few privileges as possible to
an application user.
As a consequence, it is a good idea not to have the application
user own any database objects (because the owner can drop or
modify them).
The same principle can be applied to the owner of the database objects:
That user need not have more privileges than necessary for creating
and maintaining database objects.
Your point of not doing everyday work as root is one good reason.
Another is functions that are defined as SECURITY DEFINER.
These functions will run with the permissions of the owner, no
matter who calls them. Consequently the owner of such a function
should also not have more privileges than necessary, so that
if the function contains a security hole, the damage will be
limited.
We routinely use a setup with two or more users:
One "admin" user owns the database objects. Only our DBAs
have the password of this user, and no application is allowed
to connect with this user.
Then there are one or more "normal" users who are granted the
necessary rights on the objects. These users are used by the
application to connect to the database.
Yours,
Laurenz Albe
