5

I can not drop a database using 

dropdb

I get this error:

 ERROR:  must be owner of database

but with psql \l I see user admin (the one dropping the db) with 

 admin=CTc/postgres

besides the database I want to drop.

What is wrong in PostgreSQL 9.x in Linux?

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
Codedit
  • 83
  • 1
  • 2
  • 4
  • 1
    BTW, version 9.x does not make sense for Postgres since major version numbers include the first number after the dot - before the upcoming version 10 where this changes. – Erwin Brandstetter Sep 09 '17 at 00:23

1 Answers1

9

What you see there with psql \l only means that the Postgres role admin has been granted a couple of privileges by the Postgres role postgres - which is the default superuser and probably the actual owner of the database.

Quoting the manual:

If a superuser chooses to issue a GRANT or REVOKE command, the command is performed as though it were issued by the owner of the affected object. In particular, privileges granted via such a command will appear to have been granted by the object owner.

The privileges granted are:

C -- CREATE
c -- CONNECT
T -- TEMPORARY

CONNECT and TEMPORARYon databases are also default default privileges for PUBLIC, but what we see here has been granted explicitly.

The privilege to drop the DB is not given, it cannot be. Only the owner of the database can drop it. (Or a superuser.) Hence the error message:

ERROR: must be owner of database

Typically, each shell user connects with a Postgres role of the same name. So, given you have the necessary sudo rights, this should do it:

sudo -u postgres dropdb my_database_name_here

Or switch to the shell user postgres like demonstrated here:

But you may have to disconnect other users first. See:

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600