Ensure that per-service postgres users are not superusers

[djmitche]

In db:ugprade, we check that per-table permissions correspond to those in db/access.yml, but we don't look at whether a user is a superuser. We should.

[edunham]

This is because when users are created with gcp's command line tooling, they automatically get the superuser, createrole, and createdb roles assigned. I believe that alter role taskcluster_notify set nosuperuser nocreaterole nocreatedb (or whatever the correct perms are) will no-op if the service user's roles are correct, and fix them if they aren't.

I have verified that the credentials with which db:upgrade is called are able to make the above changes to users.

[sciurus]

If this can be done in such a way that

1. Any unexpected attributes are removed from each services role
2. Any unexpected role memberships are removed from each services role

then it sounds okay.

What will happen by default if we continue setting up the non-admin users the way edunham is doing it is

taskcluster-> \du
                                                List of roles
         Role name         |                         Attributes                         |      Member of      
---------------------------+------------------------------------------------------------+---------------------
 taskcluster               | Create role, Create DB                                     | {cloudsqlsuperuser}
 taskcluster_notify        | Create role, Create DB                                     | {cloudsqlsuperuser}

We can verify by testing after this command is run and seeing

                                                List of roles
         Role name         |                         Attributes                         |      Member of      
---------------------------+------------------------------------------------------------+---------------------
 taskcluster               | Create role, Create DB                                     | {cloudsqlsuperuser}
 taskcluster_notify        |                                                            | {}

[djmitche]

So far we've been checking but not enforcing permissions in db:upgrade. The idea is that any discrepancy is potentially an IOC and we don't want to sweep it under the rug. So I'll continue to do that here.

[djmitche]

postgres=# select
  r.rolname as role,
  u.rolname
from
  pg_catalog.pg_roles as r,
  pg_catalog.pg_roles as u,
  pg_catalog.pg_auth_members
where
 r.oid = roleid and
 u.oid = member and
 u.rolname like 'test\_%';                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
     role      |   rolname                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
---------------+-------------
 somefunkyrole | test_github

[djmitche]

postgres=# select
  rolname as user,
  rolsuper,
  rolcreaterole,
  rolcreatedb,
  rolreplication
from
  pg_catalog.pg_roles
where
  (rolsuper or rolcreaterole or rolcreatedb or rolreplication ) and
  rolname like 'test\_%';                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
   user    | rolsuper | rolcreaterole | rolcreatedb | rolreplication                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
-----------+----------+---------------+-------------+----------------
 test_auth | t        | f             | t           | f