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

On Tue, Jan 17, 2017 at 10:02 AM, KARIN SUSANNE HILBERT <ioh1(at)psu(dot)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