CODEWITHSUNDEEP
postgresql
Wolkenarchitekt
Wolkenarchitekt

Reputation: 21248

Postgres: Drop all tables that belong to a specific role (or force dropping a role, ignoring dependent objects)

In a Bash-script, i want to drop a Postgres user role. But Postgres does not let me to that, i'm getting Cannot drop table X because other objects depend on it.

So i want to drop all tables that depend on that role to be able to remove the role. Therefore i wrote a Postgres function (my first one, inspired by some posts) which should remove all tables that belong to a specific role.

This is the Bash script containing the function, and tries to apply that function to the role bob:

#!/bin/bash

sudo su - postgres -c "psql -d postgres -U postgres" << 'EOF'
CREATE LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$
DECLARE
    statements CURSOR FOR
        SELECT tablename FROM pg_tables
        WHERE tableowner = username;
BEGIN
    FOR stmt IN statements LOOP
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ';';
    END LOOP;
END;
$$
LANGUAGE plpgsql;
EOF

sudo su - postgres -c "psql -d postgres -U postgres -c \"SELECT truncate_tables('bob');\""

I don't get any errors, but the script has no effect - the tables that belong to the role are not dropped. This is the output:

CREATE FUNCTION
 truncate_tables 
-----------------

(1 row)

Where is the error in my function? Or are there other ways to force dropping a role, ignoring dependent objects?

EDIT:

I also tried to insert a DROP OWNED BY bob; before the removal, but there are still objects depending on that role which prevent the deletion of it.

Upvotes: 0

Views: 1875

Answers (1)

Magnus Hagander
Magnus Hagander

Reputation: 25118

You are running TRUNCATE TABLE - that will not remove the table. You need to run DROP TABLE for that.

I would guess that your problem is because the role owns objects in a different database. You need to run your function once in each database. Or better, DROP OWNED BY bob once in each database should work.

Upvotes: 4

Related Questions