Postgres Pro
Downloads
Home   >   mailing lists

Thread: PostgreSQL v9.4, ERROR: must be owner of database test_db

PostgreSQL v9.4, ERROR: must be owner of database test_db

From
KARIN SUSANNE HILBERT
Date:
I'm a new PostgreSQL admin. We have PostgreSQL v9.4.1 installed on a Linux server (Scientific Linux release 6.6). I'm getting the following error in my diagnostic log:

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

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

2016-07-10 02:54:12 EDT [4415]: [8-1] db=test_db,user=test_user LOG: statement: ROLLBACK


The background behind this error is as follows:

I have been told by some other PostgreSQL admins that we should create a user, other than postgres, to be the owner of the database & that the application user should not be the database owner. I have also read that you should create an application schema & drop the public schema. Based on these guidelines, I created a script to create the users, database, objects, & to grant permissions for a new test database. The following command was used to execute the script:


psql -U postgres -d postgres -a < sql_scripts/NewDBScript.sql > sql_scripts/NewDB_YYYYMMDD.log


-- NewDBScript.sql
-- Assign variables:

\set dbowner 'xxxxxxxx'
\set dbname 'xxxxxxxx'
\set dbuser 'xxxxxxxx'

-- Create the users:
CREATE USER :dbowner;
\password :dbowner

CREATE USER :dbuser;
\password :dbuser

-- Create the database:
CREATE DATABASE :dbname WITH OWNER :dbowner ENCODING 'UTF8' CONNECTION LIMIT -1;

-- Connect to  database using postgres
\c :dbname postgres


-- Create the application schema & drop the public schema
CREATE SCHEMA IF NOT EXISTS :dbuser AUTHORIZATION :dbowner;
DROP SCHEMA IF EXISTS public CASCADE;

-- Set the schema for the database to be the hero account schema
alter database :dbname set search_path=:dbuser;
show search_path;


-- Connect to the database using database owner
\c :dbname :dbowner
show search_path;

...

-- After all the objects were created, I granted the following permissions to the application user:

GRANT usage on SCHEMA :dbuser to :dbuser;
GRANT select, insert, update, delete on ALL TABLES IN SCHEMA :dbuser to :dbuser;
GRANT usage on ALL SEQUENCES IN SCHEMA :dbuser to :dbuser;

My questions are:

1) Was this a good procedure to follow?
2) Would you recommend anything different?


3) With this database/user configuration, the application has been able to successfully connect to the database & is able to access & update the data. It apparently connects once per day & when it connects, I see the following errors in the diagnostic log:

2016-07-10 02:54:12 EDT [4415]: [1-1] db=[unknown],user=[unknown] LOG: connection received: host=xxx_appServerName_xxx port=43120

2016-07-10 02:54:12 EDT [4415]: [2-1] db=test_db,user=test_user LOG: connection authorized: user=test_user database=test_db SSL enabled (protocol=TLSv1, cipher=ECDHE-RSA-AES256-SHA, compression=off)

2016-07-10 02:54:12 EDT [4415]: [3-1] db=test_db,user=test_user LOG: statement: SELECT oid, typname FROM pg_type

2016-07-10 02:54:12 EDT [4415]: [4-1] db=test_db,user=test_user LOG: duration: 1.065 ms

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

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

2016-07-10 02:54:12 EDT [4415]: [8-1] db=test_db,user=test_user LOG: statement: ROLLBACK

2016-07-10 02:54:12 EDT [4415]: [9-1] db=test_db,user=test_user WARNING: there is no transaction in progress

2016-07-10 02:54:12 EDT [4415]: [10-1] db=test_db,user=test_user LOG: duration: 0.149 ms

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

2016-07-10 02:54:12 EDT [4415]: [12-1] db=test_db,user=test_user LOG: duration: 0.153 ms


I have contacted the developer regarding this error & he said the following: This code is called each and every time it connects.

%% part of init/1
%% Open a database connection to PostgreSQL
pgsql_connect(Server, Port, DB, Username, Password) ->    case pgsql:connect([{host, Server},                        {database, DB},                        {user, Username},                        {password, Password},                        {port, Port},                        {as_binary, true}]) of        {ok, Ref} ->            pgsql:squery(Ref, [<<"alter database ">>, DB, <<" set ">>,                               <<"standard_conforming_strings='off';">>]),            pgsql:squery(Ref, [<<"set standard_conforming_strings to 
'off';">>]),            {ok, Ref};        Err ->            Err    end.

I don't know anything about the coding of his application (apparently it is an application used as is out of the box). He's not getting any errors back in his application. It looks to me like the first statement (alter database test_db set standard_conforming_strings='off';) is not really necessary since the 2nd statement works (set standard_conforming_strings to 'off';).


  • Am I looking at this correctly?
  • Is it safe to ignore these errors in the diagnostic log?
  • Or can the application hero account be given permissions to execute this command? (if so, what would the grant statment be?)
  • Or should the application user account actually be the database owner user to avoid these errors?

I apologize for the long-winded questions - I just wanted to provide the background information. Any help with this would be appreciated.

Regards, Karin


Karin Hilbert
Database Specialist
Administrative Information Services
Pennsylvania State University

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

From
"David G. Johnston"
Date:
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.

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

From
KARIN SUSANNE HILBERT
Date:
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.