Redshift: cannot drop user owner default privileges

Question

I'm trying to delete an user from my database but I'm getting an error:

user "ted.mosby" cannot be dropped because some objects depend on it
Detail: owner of default privileges on new relations belonging to user ted.mosby in schema tv_shows

How can I fix this error and remove the user from my database?

I've changed owner and already revoked all permissions from ted.mosby.

Answer 1

Revoke the default privileges:

ALTER DEFAULT PRIVILEGES FOR USER "ted.mosby" IN SCHEMA tv_shows
   REVOKE ALL ON TABLES FROM "ted.mosby";

You can use \ddp in psql to see if any default privileges are left.

Answer 2

I had the same issue but @Laurenz answer only partially helped me - I ran into permission issues as I had already transfered ownership on the schema from ted.mosby to my master user before revoking default privileges. Long story short - revoke default privileges before transfering ownership.

To reproduce my issue (I have no idea if this is a bug in Redshift or expected behaviour?):

-- executed with master user redshift_master
CREATE USER anton_test_user PASSWORD '***' IN GROUP redshift_dev;

Then using anton_test_user

CREATE SCHEMA anton_test_schema;
CREATE TABLE anton_test_schema.anton_test_table AS SELECT 1 AS anton;
ALTER DEFAULT PRIVILEGES IN SCHEMA anton_test_schema 
GRANT SELECT ON TABLES TO GROUP redshift_readonly;

Again with redshift_master

ALTER SCHEMA anton_test_schema OWNER TO redshift_master;
ALTER TABLE anton_test_schema.anton_test_table OWNER TO redshift_master;

Now trying to drop the user it complains about default privileges:

DROP USER anton_test_user;

Result as expected:

owner of default privileges on new relations belonging to user 
anton_test_user in schema anton_test_schema;

Now to the weird part, still with redshift_master

ALTER DEFAULT PRIVILEGES FOR USER anton_test_user IN SCHEMA anton_test_schema
REVOKE ALL ON TABLES FROM redshift_readonly;

Gives Invalid operation: permission denied for schema anton_test_schema. What?

If running with anton_test_user

ALTER DEFAULT PRIVILEGES IN SCHEMA anton_test_schema
REVOKE ALL ON TABLES FROM redshift_readonly;

As well gives Invalid operation: permission denied for schema anton_test_schema.

The only way for me to solve this and being able to drop anton_test_user was to, with redshift_master drop the schema and table completely

DROP TABLE anton_test_schema.anton_test_table;
DROP SCHEMA anton_test_schema;
DROP USER anton_test_user; -- it works now

My completely uninformed guess is that anton_test_user had lost permissions to the schema, so no grants for the user could be applied or revoked in that schema.

Answer 3

After being in contact with AWS Support and querying the table pg_default_acl I realised that the user I wanted to drop had group default access privileges. So first I had to run:

alter default privileges for user "ted.mosby" grant EXECUTE on functions to public;
alter default privileges for user "ted.mosby" grant all on tables to "ted.mosby";

Then the following query:

select usename, acl.*
from pg_default_acl as acl join pg_user on usesysid = defacluser
where usename = 'ted.mosby';

showed me the groups in which the user was actually part of.

So if I tried the following

alter default privileges for user "ted.mosby" in schema my_schema 
revoke all on tables FROM group my_schema_group;

but I got a permission denied. So the workaround suggested from AWS Support was to temporary grant create permission for example, executing the above command and then removing the create permission, in this way:

grant create on schema my_schema to "ted.mosby";

alter default privileges for user "ted.mosby" in schema my_schema 
revoke all on tables FROM group my_schema_group;

revoke create on schema my_schema from "ted.mosby";

After the above queries, I queried again pg_default_acl with the same statement and I could see there was no more record for the removed group. As the user was part of different groups I had to execute it several times.

After removed from all groups I was able to successfully drop user "ted.mosby".