postgresql DROP DATABASE not working with user having role of owner

Question

I have a requirement where I need to have two users as database owner. I have created a role of db owner and assigned the role to two users. Now either of the users are able to drop the database. What is missing here?

 mydb=> SELECT d.datname as "Name",
 mydb-> pg_catalog.pg_get_userbyid(d.datdba) as "Owner"
 mydb-> FROM pg_catalog.pg_database d;
   Name    |   Owner   
-----------+-----------
  mydb     |  mydb_role
(1 row)

 mydb=> \du
                                                                  List of roles
      Role name       |                         Attributes                         |                          Member of                          
----------------------+------------------------------------------------------------+-------------------------------------------------------------
  mydb_role           | Create DB, Cannot login                                    | {}
  mydb_user           |                                                            | { mydb_role}
  mydb_user_clone     |                                                            | { mydb_role}

ubuntu@ip-10-69-163-164:~$ psql -U  mydb_user_clone -d postgres
psql (13.4 (Ubuntu 13.4-4.pgdg20.04+1), server 13.3)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> DROP DATABASE  mydb WITH (FORCE);
ERROR:  must be a member of the role whose process is being terminated or member of pg_signal_backend
postgres=>

Below commands are used to create user and role

CREATE ROLE mydb_role nologin;
ALTER DATABASE mydb OWNER TO mydb_role;
GRANT mydb_role TO mydb_user;
GRANT mydb_role TO mydb_user_clone;

it's not about the database ownership, but about the open connections to that database from other roles/users (btw: how did you manage to create the role mydb_role twice?) — user330315
– user330315, Commented Oct 20, 2021 at 7:08
Its the problem of db ownership as the force option also throws the same error. ubuntu@ip-10-69-163-164:~$ dropdb --if-exists --force mydb dropdb: error: database removal failed: ERROR: must be a member of the role whose process is being terminated or member of pg_signal_backend. There are no two mydb_role It was a problem of copy and paste — hassanbsalimi
– hassanbsalimi, Commented Oct 20, 2021 at 22:04
The error clearly says it's about not allowed to terminate open connections to the database. If there were no connections your user would be able to drop the database. — user330315
– user330315, Commented Oct 21, 2021 at 5:05
The error does not even reference open connections to the database. It references a "process" but it is not at all clear what process is being talked about. — aquirdturtle
– aquirdturtle, Commented Jul 28, 2022 at 0:12

Laurenz Albe · Accepted Answer · 2021-10-20 07:09:08Z

1

Because you have not enough permission to terminate a backend process.

Your db user must be a member of the pg_signal_backend role to terminate another database user's process, OR you have to use another user with the superuser privilege.

edited Oct 20, 2021 at 7:09

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

answered Oct 20, 2021 at 7:06

Pooya

3,2132 gold badges16 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

hassanbsalimi Over a year ago

But I am not trying to terminate another users process which is db in this case. I own the db and I am trying to drop my own db.

Pooya Over a year ago

In postgres 13 and above when use WITH (force) database terminate all existing connections and this command executed with your connected user

Pooya Over a year ago

See document: postgresql.org/docs/current/sql-dropdatabase.html

hassanbsalimi Over a year ago

Its not the problem of active connections. Its the problem of being db owner.

ubuntu@ip-10-69-163-164:~$ dropdb --if-exists --force mydb dropdb: error: database removal failed: ERROR:  must be a member of the role whose process is being terminated or member of pg_signal_backend

Collectives™ on Stack Overflow

postgresql DROP DATABASE not working with user having role of owner

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related