Dropping everything in a database owned by a certain role — not working

Posted on

Question :

I want to delete everything in a certain database owned by a certain role. I’ve tried to do and nothing has been deleted.

sudo -u postgres psql
postgres=# l
                                      List of databases
          Name           |   Owner   | Encoding | Collate |  Ctype  |    Access privileges    
-------------------------+-----------+----------+---------+---------+-------------------------
 my_db123                | web_user1 | UTF8     | C       | C.UTF-8 | =Tc/web_user1          +
                         |           |          |         |         | web_user1=CTc/web_user1
 postgres                | postgres  | UTF8     | C       | C.UTF-8 | 
 template0               | postgres  | UTF8     | C       | C.UTF-8 | =c/postgres            +
                         |           |          |         |         | postgres=CTc/postgres
 template1               | postgres  | UTF8     | C       | C.UTF-8 | =c/postgres            +
                         |           |          |         |         | postgres=CTc/postgres

And

# drop owned by web_user1;

But then I connect to my_db123 and everything is it still remains – all the tables, all data in them.

Why have they not been deleted? And how to do it properly then?

I also want that after I delete everythin in a database, any newly object in that database to be owned the same user – web_user1. I used these commands to create a database initially:

CREATE DATABASE my_db123 WITH OWNER web_user1;
GRANT ALL PRIVILEGES ON DATABASE my_db123 TO web_user1;

Answer :

PostgreSQL documentation:

DROP OWNED drops all the objects within the current database that are owned by one of the specified roles.

You have to connect to my_db123 first.

Leave a Reply

Your email address will not be published.