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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
