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

On 2017-10-12 21:09, 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.
> 
> I am now tasked with securing the db, which means I want to create a
> role with reduced permissions that I can transfer ownership to, and then
> a bunch of roles for various types of access - developer ad-hoc access,
> application-specific roles, etc.
> 
> My first task was to simply create a role without createdb and
> createrole privilege which can be the owner of everything.
> 
> The original role was called 'stemadmin' and I have created a role
> called 'stem'
> 
> stem_local=> \du
> 
>                                         List of roles
> 
>   Role name  |                         Attributes                      
>   |    Member of     
> 
> -------------+------------------------------------------------------------+------------------
> 
>  sgendler    | Superuser, Create role, Create DB, Replication, Bypass
> RLS | {}
> 
>  stem        | No inheritance, Create role                             
>   | {}
> 
>  stemadmin   | No inheritance, Create role, Create DB                  
>   | {stem}
> 
>  stemdropper | No inheritance, Create role, Create DB                  
>   | {stemadmin,stem}
> 
> 
> I have a superuser called sgendler, but I cannot use it, because I do
> not have superuser access in my production environment (RDS).  Every
> object in the database is owned by stemadmin.
> 
> If I login to the database as stemadmin and attempt to execute 'REASSIGN
> OWNED BY stemadmin TO stem;' I receive the following requiring superuser
> or not being able to be the owning role directly when reassigning.  This
> seems like a documentation oversight.
> 
> stem_local=> reassign owned by stemadmin to stem;
> ERROR:  permission denied to reassign objects
> 
> So it won't allow me to give away my own permissions.  Obviously, I
> can't execute that statement as 'stem' since that would be stealing
> permissions. So my only remaining option was to create the 'stemdropper'
> role, which is a member of both 'stemadmin' and 'stem' so it should have
> permissions on objects owned by both stem and stemadmin.  Yet when I run
> the same statement as 'stemdropper' I still get the permission denied
> message.  So how am I supposed to reassign anything if I cannot become
> superuser?  Do I really have to dump the entire db without ownership
> info, then reimport it into a new db as the new owner?  That seems like
> a ridiculously slow and ineffective way to accomplish that.  And the
> error message is tremendously unhelpful, all things considered. 
> 
> It should be noted that if I alter all 3 roles with 'inherit' it still
> doesn't work.  It would appear that the only way to 'reassign owned' is
> as a superuser (which is contradicted by the documentation), which isn't
> accessible in RDS.
> 
> Meanwhile, the documentation merely says something about needing to have
> permissions to access both roles in a reassign command, but says nothing
> about 
> And while I have you, the 'new' page for subscribing to mailing lists
> just throws an error.  It took me way too long to become a member of
> this list because the instructions specifically sent me to an
> ineffective method.  Not exactly new-user friendly.  I've been using
> postgresql for more than a decade and have been a member of various
> lists for that long, but not this one.  Were I new to the postgresql
> ecosystem, I'd have probably quit in frustration when I couldn't log in
> after creating an account just to get on a mailing list so I can send an
> email.


One of the several reasons I do not like RDS...

I have not been able to figure it out either. Instead I used the
information_schema to generate a bunch of 'ALTER xxx OWNER TO yyy;'
statements, which when run as your current owner user will allow you to
give away your ownership to another user.

After that, make sure to only create objects using the "stepmadmin"
user, or you'll have to jump through hoops yet again.


-- Stephen





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general