How Can I Run PostgreSQL's "dropdb" Command on Linux Without Sudo-ing to the Postgres User?

Question

I want to be able to run dropdb mydb. However, when I try to as my normal user I get:

dropdb: error: database removal failed: ERROR: must be owner of database mydb

Now I know that I can just do:

sudo -u postgres dropdb mydb

but that's annoying if I'm trying to script the dropping and re-creation of a DB, because I have to manually enter my sudo password.

I've mostly been able to avoid having to sudo to the postgres user by having a pg_hba.conf with:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

But for some reason dropdb doesn't seem to respect my pg_hba.conf. Is there some way to make it, so that I can just run dropdb as my regular user?

EDIT: And the same question applies with createdb. I can actually change the DB owner to be able to drop it (thanks stickybit!) ... but then I can't re-create it after.

Did you pass the `-U postgres` option? And restarted the service after changing the `pg_hba.conf`? — sticky bit, Aug 13 '20 at 02:22
I did restart postgres after (`sudo /etc/init.d/postgresql restart`). As for `-U`, as I understand it that should default to my user, and that ... is not the owner of the database (I thought it was but I just realized I was wrong). Let me try change that ownership. — machineghost, Aug 13 '20 at 02:30
Ok, changing the owner let me drop the DB ... but now I have the same problem with `createdb`. — machineghost, Aug 13 '20 at 02:34
You can get the owner's name from the catalog: `SELECT rol.rolname FROM pg_database dat LEFT JOIN pg_authid rol ON rol.oid = dat.datdba WHERE datname = '';` — sticky bit, Aug 13 '20 at 02:34
Thanks. I figured that out, and as I said above I changed the owner, but I still have this fundamental problem of not being the postgres user and therefore not being able to run createdb/dropdb ... unless I go manually change the owner (as the postgres user), in which case I dropdb once. — machineghost, Aug 13 '20 at 02:36
You may need to grant the user the privilege to create databases: `ALTER USER CREATEDB;` — sticky bit, Aug 13 '20 at 02:37
Thank you, that worked perfectly! I knew I was forgetting something, but I couldn't remember what. Would you care to put that in the form of an answer, so I can accept it? — machineghost, Aug 13 '20 at 02:41
" As for -U, as I understand it that should default to my user, and that ... is not the owner of the database" Exactly. That is why you need to use `-U postgres`, because without that it defaults to your user, and that default doesn't have the permissions. — jjanes, Aug 13 '20 at 03:34
What's wrong with using `psql -U postgres ...` (or `dropdb -U postgres ...`)? You will have to enable password authentication for the `postgres` user first. And if you don't want to enter that password, use the `.pgpass` file. See [here](https://dba.stackexchange.com/questions/14740/) or [here](https://stackoverflow.com/questions/6405127) — , Aug 13 '20 at 05:57
"But for some reason dropdb doesn't seem to respect my pg_hba.conf" That is difficult to believe, and you haven't shown us either what you did, or what error you got. — jjanes, Aug 13 '20 at 14:42
@jjanes see above: *just* `pg_hba.conf` allowed me to do record-level stuff, but it did not allow `dropdb` (without my user matching the DB owner) or `createdb`. Granting myself create database permissions with `ALTER USER CREATEDB` *did* allow creation. Changing DB ownership, or changing my Postgres user via `-U` *did* allow deletion ... but that proved unnecessary to my workflow, because after my normal user could create databases, they became the owner of them (and thus, could drop them without using `-U`, or manually changing DB ownership via the `postgres` user). — machineghost, Aug 13 '20 at 15:47

score 2 · Accepted Answer · edited Aug 13 '20 at 15:55

Unless you're the owner of the database try to pass the -U option with the database owner (or a superuser).

dropdb -U <the database owner> <the database name>

To get the owner of a database you can query the catalog:

SELECT rol.rolname
       FROM pg_database dat
            LEFT JOIN pg_authid rol
                      ON rol.oid = dat.datdba
       WHERE datname = '<your database name>';

(The above command can be run in psql or any other client, but must be run as the database superuser, e.g. postgres on most UNIX-based systems.)

To be able to create databases (with createdb or other means), you need to grant yourself the privilege to create databases.

ALTER USER <your user name> CREATEDB;

(Again, that can be run in psql or any other client, but must be run as database super user, e.g. postgres.)

You then should be the owner of the database automatically unless you specify otherwise and can therefore drop it again.

Of course you can also grant yourself superuser privileges analogously.

ALTER USER <your user name> SUPERUSER;

How Can I Run PostgreSQL's "dropdb" Command on Linux *Without* Sudo-ing to the Postgres User?

1 Answers1

How Can I Run PostgreSQL's "dropdb" Command on Linux Without Sudo-ing to the Postgres User?