[Solved] phpPgAdmin - must be owner of relation

[cprn]

Hi, complete db administration newbie here.

 

I created a postgres db "cprn_luetest", created user "cprn_admin", added user to db, synced grants, set this user/pass in app.

App created a table "user" with owner "cprn_admin" but now I need to remove it so it could be recreated with updated schema.

 

The problem is I cannot remove this table from phpPgAdmin because it logs me in as "cprn" and refuses to execute DROP saying:

SQL error:
ERROR:  must be owner of relation user
In statement:
DROP TABLE "public"."user"

In table level privileges I only see default grants:

Role      CREATE  USAGE  Grantor
postgres  Yes     Yes    postgres
          Yes     Yes    postgres

In public privileges I tried to add something useful but it didn't help:

Role            CREATE  TEMPORARY  CONNECT  Grantor
                No      Yes        Yes      cprn_luetest
cprn_luetest    Yes*    Yes*       Yes*     cprn_luetest
cprn_admin      Yes     Yes        Yes      cprn_luetest
cprn            Yes*    Yes*       Yes*     cprn_luetest

What do I need to add and how to get god-like powers and be able to destroy everything (literally) in my db from phpPgAdmin?

Edited May 3, 2018 by cprn

[ziad87]

(not sure)

Can't you just drop it from cpanel?

[cprn]

The whole database? Yeah, that's what I ended up doing.

 

The problem is I don't really want to just drop data when this issue repeats itself on production and it might whenever I decide do change a table structure in future updates. Would much prefer to alter the schema instead but I couldn't. I couldn't touch a table created by `cprn_admin` when logged into phpPgAdmin as `cprn`. I thought `cprn` would be a root user of my database? Am I doing something wrong? Am I not supposed to create a dedicated user for the app?

[wolstech]

Lets see if Krydos knows. I don't know postgres very well...

[Krydos]

If you backup the database, drop it, recreate it, and restore the data you would be looking at maybe five minutes of downtime. The issue is cpanel creates databases and assigns permissions with root access via the cpanel button. Phppgadmin uses your cpanel username which doesn't have full root permissions. There's a lot of subtlety that is lost because cpanel didn't design their interface with root access for every possible thing you might want to do.

[cprn]

Ok, will have to prepare migration mechanism before continuing with other stuff. One of those unplanned gotchas, I suppose. Thanks for explaining.