CODEWITHSUNDEEP
postgresqlpostgresql-11
Monku
Monku

Reputation: 2650

How to add admin user to existing postgres database?

We have an existing PostgreSQL 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 the 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 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.

Upvotes: 3

Views: 2497

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247235

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.

Upvotes: 0

Related Questions