On Tue, 2024-01-23 at 18:01 +0200, kaido vaikla wrote: > Yes, i have a real problem. To keep things clear in database, my design is: > - every application has own schema > - every schema has owner who is not a user postgres > - only schema owner can do DDL's on schema > > So if some application needs some extensions, then i give temporary suppersuser privilege to schema owner, > if "create extension" needs it, extensions are installed "with schema" And revoke suppersuser privilege after extension inatall. > I'm not sure, is it my design against postgres concept or not :(. > > But in same time, i expect that pg_dumpall will not change ownership or at least gives some error or something. I think your expectation is founded, and the current behavior is surprising. At first, I thought that would be a security problem in combination with trusted extensions, but then the objects from a trusted extension always belong to the bootstrap superuser. So, all extension objects always belong to a superuser, and a change in ownership to a different after a restore won't be a privilege escalation. In practice, it doesn't matter much to which superuser extension objects belong. Your usage pattern sort of breaks that. I think that your practice of temporarily elevating the prospective extension owner to superuser is a questionable one. You end up having extension objects owned by a non-superuser. That probably won't make much difference in most cases, but it could matter for SECURITY DEFINER functions or views that use restricted objects. And if the extension object owner changes after a restore, the behavior could change. I don't really see what you are expecting to gain from your design. Instead of temporarily elevating the schema owner's privileges, why don't you create the extension as superuser right away? (As an aside: you need a superuser to turn sumebody into a superuser. Don't tell me that your application is connecting as superuser...) So while I think that the current behavior is less than ideal, I don't see it as a massive problem that needs to be fixed. What is it that stops working for you after the restore? Perhaps some documentation for the surprising behavior might be in place. Yours, Laurenz Albe
