Postgres Pro
Downloads
Home   >   mailing lists

Re: PostgreSQL v9.4, ERROR: must be owner of database test_db - Mailing list pgsql-novice

From KARIN SUSANNE HILBERT
Subject Re: PostgreSQL v9.4, ERROR: must be owner of database test_db
Date
Msg-id 343433457.10679539.1469121892079.JavaMail.zimbra@psu.edu
Whole thread Raw
In response to Re: PostgreSQL v9.4, ERROR: must be owner of database test_db  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-novice
Tree view
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.johnston@gmail.com>
To: "KARIN SUSANNE HILBERT" <ioh1@psu.edu>
Cc: pgsql-novice@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

On Wed, Jul 20, 2016 at 3:37 PM, KARIN SUSANNE HILBERT <ioh1@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.


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.

pgsql-novice by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: PostgreSQL v9.4, ERROR: must be owner of database test_db
Next
From: Paul Linehan
Date:
Subject: Generate a series of single days from a table of intervals.