I wasn't under the impression that open source meant "totally unsupported, undocumented, and fix it yourself if it's broken." If your attitude is going to be "it's good enough for my needs so I'll just rudely dismiss any problems with it," why merge it at all?
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
On Fri, Oct 13, 2017 at 12:39 PM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:
Sam Gendler wrote:
> psql 9.6.3 on OS X.
>
> I'm dealing with a production database in which all db access has been made
> by the same user - the db owner, which isn't actually a superuser because
> the db runs on amazon RDS - amazon retains the superuser privilege for its
> own users and makes non-superuser role with createrole and createdb
> privileges for use as the primary role by the AWS account.
It's true that REASSIGN OWNED is limited to a very particular scenario.
It was written to support the specific case of wanting to drop a role,
and that can only be done by a superuser, so why would it matter that
REASSIGN OWNED itself could not be run by a superuser?You could at least fix the documentation bug since this superuser-only restriction doesn't show up and is in fact contradicted by the sentence "REASSIGN OWNED requires privileges on both the source role(s) and the target role." The error message that comes back seems like it could be improved as well.The word "privileges" there seems odd too, wouldn't "membership" be more appropriate?David J.
