Thread: [GENERAL] REASSIGN OWNED simply doesn't work
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.
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
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? However, I do not apologize for not thinking about your particular scenario. I had my itch, and I scratched it; you have yours, yet you complain that I did not scratch it in advance? Feel free to submit a patch (or convince/hire someone to do it for you) to add the new feature of allowing it to work without superuser privs. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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.
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@gmail.com> 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.
David G. Johnston wrote: > 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. alvherre=# create role owner1; CREATE ROLE alvherre=# create role owner2; CREATE ROLE alvherre=# create role sam login; CREATE ROLE alvherre=# grant owner1 to sam; GRANT ROLE alvherre=# grant owner2 to sam; GRANT ROLE alvherre=# set session authorization owner1; SET alvherre=> create table owner1_table (); CREATE TABLE alvherre=> \q RESET $ psql alvherre -U sam alvherre=> reassign owned by owner1 to owner2; REASSIGN OWNED alvherre=> \d Listado de relacionesEsquema │ Nombre │ Tipo │ Dueño ─────────┼──────────────┼───────┼────────public │ owner1_table │ tabla │ owner2 > The word "privileges" there seems odd too, wouldn't "membership" be more > appropriate? > > https://www.postgresql.org/docs/10/static/sql-reassign-owned.html I can change that. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general