From: | KARIN SUSANNE HILBERT <ioh1(at)psu(dot)edu> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | What's the best way to handle privileges when the application account needs to maintain the database objects? |
Date: | 2017-01-17 16:02:46 |
Message-ID: | 236888940.6653117.1484668966027.JavaMail.zimbra@psu.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Hursh Jain | 2017-01-17 16:11:36 | Re: Queries with Joins before filtering taking too much time! Filter (where clause) *first* -- suggestions ? |
Previous Message | Piyush Katariya | 2017-01-17 15:39:11 | (Stateless and Distributed) Transaction in Event Driven Architecture |