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
– sticky bit, Commented Aug 13, 2020 at 2: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
– machineghost, Commented Aug 13, 2020 at 2:30
Ok, changing the owner let me drop the DB ... but now I have the same problem with createdb. — machineghost
– machineghost, Commented Aug 13, 2020 at 2: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 = '<your database name>'; — sticky bit
– sticky bit, Commented Aug 13, 2020 at 2:34
" 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
– jjanes, Commented Aug 13, 2020 at 3:34

machineghost · Accepted Answer · 2020-08-13 15:55:18Z

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;

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related