HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Can not drop a PostgreSQL database using dropdb?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
postgresqldropdbcandatabasedropusingnot

Problem

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?

Solution

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:

  • PostgreSQL error: Fatal: role "username" does not exist



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

  • Force drop db while others may be connected

Code Snippets

C -- CREATE
c -- CONNECT
T -- TEMPORARY
sudo -u postgres dropdb my_database_name_here

Context

StackExchange Database Administrators Q#185472, answer score: 9

Revisions (0)

No revisions yet.