I have a user who needs a PostgreSQL database in which the application will create all the database objects & periodically drop & recreate objects.
In the past we usually create two users - one for the dbowner & a hero acct for the application. We then create all the database objects under the dbowner acct & also create a default schema & execute the following command:
alter database :dbname set search_path=:dbschema;
We then grant the following privileges to the hero acct:
GRANT usage on SCHEMA :dbschema to :dbuser;
GRANT select, insert, update, delete on ALL TABLES IN SCHEMA :dbschema to :dbuser;
GRANT usage on ALL SEQUENCES IN SCHEMA :dbschema to :dbuser;
However, that configuration does not work for this case. It was recommended to me by colleagues that the hero account should not be the dbowner, but I don't see any other way to handle this situation.
This is a sample of the errors we get with our current configuration:
2017-01-12 15:42:21 EST [14309]: [203-1] db=dbname,user=dbuser LOG: statement: DROP TABLE IF EXISTS "audit_events" CASCADE
2017-01-12 15:42:21 EST [14309]: [204-1] db=dbname,user=dbuser ERROR: must be owner of relation audit_events
For now, I've told the developer that he should connect as the hero account normally & as the dbowner account for maintenance.
Is there a better way to handle this?
Thanks,
Karin Hilbert
