patternsqlMinor
Can not drop a PostgreSQL database using dropdb?
Viewed 0 times
postgresqldropdbcandatabasedropusingnot
Problem
I can not drop a database using
I get this error:
but with
besides the database I want to drop.
What is wrong in PostgreSQL 9.x in Linux?
dropdbI get this error:
ERROR: must be owner of databasebut with
psql \l I see user admin (the one dropping the db) with admin=CTc/postgresbesides the database I want to drop.
What is wrong in PostgreSQL 9.x in Linux?
Solution
What you see there with
Quoting the manual:
If a superuser chooses to issue a
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:
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
Or switch to the shell user
But you may have to disconnect other users first. See:
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 commandis 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 -- TEMPORARYCONNECT 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_hereOr 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 -- TEMPORARYsudo -u postgres dropdb my_database_name_hereContext
StackExchange Database Administrators Q#185472, answer score: 9
Revisions (0)
No revisions yet.