CODEWITHSUNDEEP
postgresql
hassanbsalimi
hassanbsalimi

Reputation: 109

postgresql DROP DATABASE not working with user having role of owner

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;

Upvotes: 1

Views: 1741

Answers (1)

Pooya
Pooya

Reputation: 3183

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.

Upvotes: 1

Related Questions