How to add admin user to existing postgres database?

Question

We have an existing postgres database gsrdb in production with superuser gsr. We have been doing all the database maintenance with that user gsr. Also, the same user gsr is used by our app service to do transactions on the database.

We want to change this now. We want a separate superuser gsr_admin(whose credentials are managed by Vault) that can do the dba maintenance but still have our app service use existing user gsr.

The problem I am facing is that all the database objects so far are owned by gsr user and if I run updates, as user gsr_admin, on the database w.r.t. either table constraints or sequences it fails saying error: must be owner of relation...blah blah

How can I fix this?

So I was thinking if I could create a superuser admin group role called admin_group and reassign all the ownerships of all the database objects to it from user gsr and then alter both users gsr and gsr_admin to belong to this admin group role. Wouldn't that way everything that has been created so far would be owned by role admin_group ? And whether I create new objects as a user gsr or as gsr_admin either of them can still update the objects?

I might be wrong. Would really appreciate some inputs.

I think I can do this by creating a superuser admin role say proxy_admin. And then I change the ownership of all the database objects to proxy_admin and then whenever gsr or gsr_admin logs in I set the role to proxy_admin. I don't know how to do this though. Would appreciate some inputs. — Monku

Laurenz Albe Laurenz Albe · Accepted Answer · 2020-02-21T07:10:03

Simply run

ALTER ROLE gsr NOSUPERUSER;
ALTER ROLE gsr RENAME TO gsr_admin;  -- needs a new password now
CREATE ROLE gsr LOGIN;
GRANT USAGE ON SCHEMA myschema TO gsr;
GRANT SELECT, INSERT, UPDATE, DELETE
   ON ALL TABLES IN SCHEMA myschema TO gsr;

Similarly, grant USAGE on sequences and other required privileges. You may want to run some ALTER DEFAULT PRIVILEGES for future objects as well.

How to add admin user to existing postgres database?

1 Answers