PostgreSQL v9.4, ERROR: must be owner of database test_db - Mailing list pgsql-novice
| From | KARIN SUSANNE HILBERT |
|---|---|
| Subject | PostgreSQL v9.4, ERROR: must be owner of database test_db |
| Date | |
| Msg-id | 1712183849.8384006.1469043469472.JavaMail.zimbra@psu.edu Whole thread Raw |
| Responses |
Re: PostgreSQL v9.4, ERROR: must be owner of database test_db
|
| List | pgsql-novice |
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
Database Specialist
Administrative Information Services
Pennsylvania State University
pgsql-novice by date: