What's the best way to handle privileges when the application account needs to maintain the database objects?

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

Responses

Browse pgsql-novice by date

  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