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.
