Re: [GENERAL] REASSIGN OWNED simply doesn't work - Mailing list pgsql-general

And for what it is worth, postgresql doesn't require superuser privilege to drop a role. It only requires superuser to drop another superuser, according to the documentation. And the documentation and every reference to best practices specifically recommends NOT setting up production databases so that ownership is by a superuser, and especially don't use superuser for application and ad-hoc access to the db, so it seems entirely likely that anyone following best practices would NOT be using REASSIGN OWNED on a superuser except in the specific case of converting a db which was originally owned and accessed by superuser to a db that has no superuser requirements at all - that's actually what I am trying to do, but in the specific use case of an RDS database, so even my 'master user' isn't a postgres superuser. And if the owner isn't superuser, any user with createrole can drop it - unless they need to reassign privileges, first, since they'll have to reassign ownership item by item to do that unless they want DROP OWNED to actually drop the objects rather than just removing permissions, since drop owned actually drops the object if it is executed on behalf of the owner. In short, it may meet your needs, but the documentation is incorrect and the functionality is very much incomplete regardless of your own needs.

Fundamentally, the REASSIGNED OWNED command is very useful, but only if it works in contexts other than reassigning away from a superuser.  Anyone wanting to manage a database while providing minimal privileges to individual users is likely to require its use, eventually.  And if you do decide to address that, there would be a very useful extension of existing privilege assignment commands which would allow me to assign a privilege to a role on every object for which some other role already has a privilege. That would allow me to much more easily add a group of users to an existing database - give the new group role all the same privileges as some other group, then just modify the few spots where that role requires different access.  I'm not sure what to call such a command, but I do know I'd find it useful.  If a developer on my team has create access (in staging and test, at least, if not in production), it is entirely possible that they could accidentally create tables without remembering to elevate their role first, in which case reassign owned will come in handy for non-superuser roles - cleaning up their mistake. 

--sam