Re: [NOVICE] PostgreSQL v9.4, ERROR: must be owner of database test_db

[David G. Johnston]

On Wed, Jul 20, 2016 at 3:37 PM, KARIN SUSANNE HILBERT <io...@psu.edu> wrote:

2016-07-10 02:54:12 EDT [4415]: [5-1] db=test_db,user=test_user LOG: statement: alter database test_db set standard_conforming_strings='off';

2016-07-10 02:54:12 EDT [4415]: [6-1] db=test_db,user=test_user ERROR: must be owner of database test_db

T

​he reason this isn't a problem is that the ALTER DATABASE is executed in its own transaction so when it fails it doesn't impact any other (future) activity in the session.

​The short answer is to tell the developer to stop sending "ALTER DATABASE SET" statements as part of their connection startup routine.

If you want to spend the time to understand why the documentation is a very good reference.

https://www.postgresql.org/docs/9.6/static/config-setting.html

This isn't anything special about the choice of "standard_conforming_strings" here, though search_path is probably a more frequent used choice.

​I also noticed this:

​alter database :dbname set search_path=:dbuser;

On its face that just feels wrong, though there is nothing technically incorrect about it.  Personally I would name the application, and the schemas for the application, different than the name of the user that is going to be accessing those schemas.  To have the name of a schema and the name of a user match, when said schema is not a personal schema for the user, is unusual.

David J.

[KARIN SUSANNE HILBERT]

Thank you David.  

I had suggested to the developer that he stop sending those statements, but I guess that's not an option.  So I'll just change my script that checks the error logs to ignore those errors for that database.

Regarding the search_path, then you would recommend executing the following statements when the database is created?

\set schemaname 'xxxxxxxx'
CREATE SCHEMA IF NOT EXISTS :schemaname AUTHORIZATION :dbowner;
alter database :dbname set search_path=:schemaname;
GRANT usage on SCHEMA :schemaname to :dbuser;

Regards,

Karin

---

From: "David G. Johnston" <david.g....@gmail.com>
To: "KARIN SUSANNE HILBERT" <io...@psu.edu>
Cc: pgsql-...@postgresql.org
Sent: Wednesday, July 20, 2016 3:57:28 PM
Subject: Re: [NOVICE] PostgreSQL v9.4, ERROR: must be owner of database test_db