Thread: [NOVICE] What's the best way to handle privileges when theapplication account needs to maintain the database objects?
[NOVICE] What's the best way to handle privileges when theapplication account needs to maintain the database objects?
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
Re: [NOVICE] What's the best way to handle privileges when theapplication account needs to maintain the database objects?
On Tue, Jan 17, 2017 at 10:02 AM, KARIN SUSANNE HILBERT <ioh1@psu.edu> wrote: > I have a user who needs a PostgreSQL database in which the application will > create all the database objects & periodically drop & recreate objects. Personally, I am uncomfortable with a login used within an application dropping tables or doing other DDL. That adds another layer of protection for things like this: https://xkcd.com/327/ > 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; Seems reasonable to me. > 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? I would look at whether I could provide SECURITY DEFINER functions to do the specific DDL needed, and grant execute rights to the hero account. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company