1

We granted third-party software access to our postgresql database. After billing dispute we have now cut ties with this company but cannot delete the user. We need to delete this user soon but can't figure out how to do it. Here's some of what we're seeing when we try to do it:

prod=> drop user evil_user;
ERROR:  role "evil_user" cannot be dropped because some objects depend on it
DETAIL:  owner of default privileges on new relations belonging to role evil_user

prod=> reassign owned by evil_user to root;
ERROR:  permission denied to reassign objects

prod=> drop role evil_user;
ERROR:  role "evil_user" cannot be dropped because some objects depend on it
DETAIL:  owner of default privileges on new relations belonging to role evil_user
                         ^
prod=> REVOKE ALL ON ALL TABLES IN SCHEMA PUBLIC FROM evil_user;
REVOKE

prod=> drop role evil_user;
ERROR:  role "evil_user" cannot be dropped because some objects depend on it
DETAIL:  owner of default privileges on new relations belonging to role evil_user

prod=> REVOKE ALL ON SCHEMA public FROM evil_user;
REVOKE

prod=> REVOKE ALL ON DATABASE prod FROM evil_user;
REVOKE

prod=> reassign owned by evil_user to root;
ERROR:  permission denied to reassign objects

prod=> drop user evil_user;
ERROR:  role "evil_user" cannot be dropped because some objects depend on it
DETAIL:  owner of default privileges on new relations belonging to role evil_user
                                      ^
prod=> ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON TABLES     FROM evil_user;
ALTER DEFAULT PRIVILEGES

prod=> drop user evil_user;
ERROR:  role "evil_user" cannot be dropped because some objects depend on it
DETAIL:  owner of default privileges on new relations belonging to role evil_user

prod=> reassign owned by evil_user to root;
ERROR:  permission denied to reassign objects

We need to get these people out of our database. For a number of reason we cannot easily move to a new Postgres instance.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
Vicky Vargas
  • 53
  • 1
  • 6

2 Answers2

2

There are unintuitive permission requirements when using REASSIGN without a supserusesr account, such as on RDS and Cloud SQL, but it is possible as long as your current_user has permission to GRANT evil_user TO prod. See this other post where I answered the same question: https://stackoverflow.com/a/62557497/79079

mltsy
  • 6,598
  • 3
  • 38
  • 51
1
   prod=> reassign owned by evil_user to root;
   ERROR:  permission denied to reassign objects

You must perform this action as a postgres superuser account, usually the user postgres.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • I am logging in as my master user (called 'root'). How do I execute a command as the 'postgres' user? I tried googling but no luck. – Vicky Vargas Jun 23 '17 at 13:39
  • You're logged in as user `prod` in the above, not `root`. To see if user `root` has superuser rights, use `\du root`. To see what user you're logged in as use `SELECT current_user;` – Craig Ringer Jun 23 '17 at 14:10
  • I'm logged in a root. The database name is prod. Attributes are "Create role, Create DB, Password valid until infinity". Member of "{rds_superuser}" – Vicky Vargas Jun 23 '17 at 16:21
  • 1
    Aha, you're on RDS. You should've mentioned that! RDS users don't have superuser rights, you can't do this the normal postgres way. I have no idea how user management in RDS works, maybe it's done via the rds console? Ask their support. – Craig Ringer Jun 23 '17 at 16:29
  • 2
    It's really really annoying that these questions are even permitted the PostgreSQL tag. You're not on PostgreSQL. You're on a proprietary fork of an archaic version. – Evan Carroll Jun 23 '17 at 16:34
  • 1
    @EvanCarroll RDS postgres is up to date and not heavily patched, it's tolerable. Redshift however, I retag. – Craig Ringer Jun 24 '17 at 12:07