aggregate
aggregate copied to clipboard
getodk
Aggregate 1.4.13 fails to start with PostgreSQL 9.3
When installing Aggregate 1.4.13 with a PostgreSQL 9.3 backend, the application fails to initialise. With PostgreSQL 9.5 it works fine.
Configuration
WAR deployment on Tomcat 8.5.11 on Ubuntu 14.04 LTS with default JRE (OpenJDK 7u121) and default PostgreSQL (9.3). Freshly created database using create_db_and_user.sql.
Problem description
The application fails to start. SQL exceptions are logged in catalina.out. The innermost exception thrown is java.sql.SQLException: validationQuery didn't return a row.
Diagnosis
The validation query referenced in the root exception is set in the validationQuery property on the dataSource bean in WEB-INF/lib/ODKAggregate-settings.jar:odk-settings.xml to be select schema_name from information_schema.schemata limit 1.
When executing that query as the odk database user against its database on PostgreSQL 9.3, the query returns 0 records. On PostgreSQL 9.5 it returns 1 record.
The issue seems specific to PostgreSQL 9.3, not to the odk database user. Any regular database user executing that query gets an empty result on 9.3, and 1 record on 9.5. The postgres superuser does get the expected 1 record on both.
AFAIK my 9.3 and 9.5 database instances are configured identically. Maybe there was a default permissions change between PostgreSQL versions?
Workaround
My (quick & dirty) workaround was to replace the validationQuery by select 1:
jar xf ODKAggregate.war WEB-INF/lib/ODKAggregate-settings.jar
jar xf WEB-INF/lib/ODKAggregate-settings.jar odk-settings.xml
sed -i -e 's/schema_name from information_schema.schemata limit//' odk-settings.xml
jar uf WEB-INF/lib/ODKAggregate-settings.jar odk-settings.xml
jar uf ODKAggregate.war WEB-INF/lib/ODKAggregate-settings.jar
I've updated the Aggregate 1.4.13 upgrade steps to direct users to upgrade to PostgreSQL 9.4 or newer. This appears to be an issue with permissions required to access to the information_schema content. That appears to be resolved in 9.4 and higher.
Thanks @mitchellsundt but wouldn't it be better to change the validation query so as to make this work irrespective of PostgreSQL version? PostgreSQL 9.3 will be around for another year and a half, and in many places won't/can't be upgraded to 9.4.
In applications that don't create their own tables, the validation query usually checks that some core table is found in the database, so that the application can "fail fast" when connected wrongly. In our case, where there may or may not be ODK tables yet in the database, the purpose of the validation query is really only to check that there is a PostgreSQL at the other end of the connection. For this, a SELECT 1 would suffice.
Using SELECT schema_name FROM information_schema.schemata WHERE schema_name = 'my-expected-schema-name' would add some value to the validation. However SELECT schema_name FROM information_schema.schemata LIMIT 1 adds none relative to SELECT 1. It only proves that some schemata (and not necessarily the one for the application) exist.
Yes, the validation query could be changed BUT, within ODK Aggregate, we MUSTbe able to run select statements against the information_schema. If that fails, then ODK Aggregate will never function correctly. So in this case, having a validation query that confirms you can access the information_schema is actually beneficial, as it catches this failure very early.
e.g.,
https://github.com/opendatakit/aggregate/blob/master/src/main/java/org/opendatakit/common/persistence/engine/pgres/DatastoreImpl.java#L180
The remedy is to add a grant to that schema when running against older versions.
e.g., something like the following as part of the create_db_and_user.sql content:
grant all privileges on schema "information_schema" to "your_odk__user";
Or perhaps just "usage" ? I am unclear whether usage would be sufficient for running select queries against the tables within the schema.
This is a puzzling failure that seems to be an issue with how 9.3 does grants, as everything was working on 9.1.x, and then everything is working on 9.4 (the version I used when updating everything to use JDBC 4 and Tomcat 8).
GRANT either USAGE or ALL on information_schema did not help so I looked a bit deeper. I've spotted the issue, and think I've got the clean solution.
Strangely, SELECT * FROM information_schema.columns works fine, which probably explains why my ODK instance is actually working on 9.3 ... until it breaks on another possible information_schema.* table permissions issue :-/
So what is the issue with information_schema.schemata? Looking at its definition (using \d+ information_schema.schemata in psql) gave a clue. It is defined as a view, with one significant difference between 9.3 and 9.5:
SELECT ... FROM pg_namespace n, pg_authid u ...
-- 9.3
WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'USAGE'::text);
-- 9.5
WHERE n.nspowner = u.oid AND (pg_has_role(n.nspowner, 'USAGE'::text)
OR has_schema_privilege(n.oid, 'CREATE, USAGE'::text));
Note that n aliases pg_namespace which is the 'real' table of schemata, and u aliases pg_authid, the table of user accounts (needed only to display the schema owner names).
The WHERE clause filters the rows the user gets to see. The check pg_has_role checks that the current user has the USAGE permission for the role n.nspowner. In other words, can the ODK user assume the role of the schema owner? Since the schema owner of the ODK schema is postgres this is false.
On 9.5, things work out because of the disjunct: the ODK user does have USAGE (and CREATE) privilege for the ODK schema (even if postgres is the schema owner) presumably because the schema sits in the ODK database it owns, and for which it is granted ALL.
The obvious solution then is to make the ODK user the owner of its own schema.
In create_db_and_user.sql, add the statement alter schema "odk_schema" owner to "odk_user"(in fact, this could probably replace the current grant all privileges on schema "odk_schema" to "odk_user").
Can you confirm that the additional grant on information_schema is not required?
I will add the alter-owner to the script. Update will be in the next ODK Aggregate release (no ETA).
I have just tested it on 9.3 with a freshly created user and database, and no grants (either on information_schema or the odk_schema) are needed:
create database testdb;
create user testuser with unencrypted password 'secret';
alter database testdb owner to testuser;
\c testdb
You are now connected to database "testdb" as user "postgres".
create schema testschema;
alter schema testschema owner to testuser;
\c testdb testuser
You are now connected to database "testdb" as user "testuser".
select count(*) from information_schema.schemata;
1
(1 row)
This message was created automatically by mail delivery software.
A message that you sent could not be delivered to one or more of its recipients. This is a temporary error. The following address(es) deferred:
[email protected] Domain beorse.net has exceeded the max emails per hour (305/250 (122%)) allowed. Message will be reattempted later
------- This is a copy of the message, including all the headers. ------ ------ The body of the message is 7067 characters long; only the first ------ 5000 or so are included here. Received: from github-smtp2-ext4.iad.github.net ([192.30.252.195]:42491 helo=github-smtp2a-ext-cp1-prd.iad.github.net) by hp159.hostpapa.com with esmtps (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256) (Exim 4.89) (envelope-from [email protected]) id 1dgMDn-000f6X-HR for [email protected]; Fri, 11 Aug 2017 22:32:15 -0400 Date: Fri, 11 Aug 2017 19:31:35 -0700 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=github.com; s=pf2014; t=1502505095; bh=gQ8vO+NQgWb9HgqbuswLpqf0flJaaJLNCEL0LvM9ENU=; h=From:Reply-To:To:Cc:Subject:List-ID:List-Archive:List-Post: List-Unsubscribe:From; b=YU1XKy2CLdHDdOnhMjjWzUI2aW+dMkKVlhbQJd51k6VdveZ/OPnpMDJx/4kJspQXD wrhZ18C1K41cFqstgXQeycIDTboGpTUWGNt6qqKNV0NCKBnvZuo7IGhPVztbaOf9Xu NSSc2tDjW6YSQSq4Qoi/9dwi301vlFZhGaN4DzJ0= From: Open Data Kit [email protected] Reply-To: opendatakit/aggregate [email protected] To: opendatakit/aggregate [email protected] Cc: Subscribed [email protected] Message-ID: opendatakit/aggregate/issues/[email protected] Subject: [opendatakit/aggregate] Aggregate 1.4.13 fails to start with PostgreSQL 9.3 (#86) Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="--==_mimepart_598e6887daf05_52f33f88a8ed7c3414627"; charset=UTF-8 Content-Transfer-Encoding: 7bit Precedence: list X-GitHub-Sender: opendatakit-bot X-GitHub-Recipient: jbeorse X-GitHub-Reason: subscribed List-ID: opendatakit/aggregate <aggregate.opendatakit.github.com> List-Archive: https://github.com/opendatakit/aggregate List-Post: mailto:[email protected] List-Unsubscribe: mailto:unsub+000519af33ebff97a363d1e77ffc27290d1a629a016cc54892cf0000000115a62a8792a169ce0ee362cd@reply.github.com, https://github.com/notifications/unsubscribe/AAUZr7DZ4nsbwC-HECUXgfy0h35ZyNzxks5sXQ6HgaJpZM4O1SSK X-Auto-Response-Suppress: All X-GitHub-Recipient-Address: [email protected]
----==_mimepart_598e6887daf05_52f33f88a8ed7c3414627 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit
When installing Aggregate 1.4.13 with a PostgreSQL 9.3 backend, the application fails to initialise. With PostgreSQL 9.5 it works fine.
Configuration
WAR deployment on Tomcat 8.5.11 on Ubuntu 14.04 LTS with default JRE (OpenJDK 7u121) and default PostgreSQL (9.3). Freshly created database using create_db_and_user.sql.
Problem description
The application fails to start. SQL exceptions are logged in catalina.out. The innermost exception thrown is java.sql.SQLException: validationQuery didn't return a row.
Diagnosis
The validation query referenced in the root exception is set in the validationQuery property on the dataSource bean in WEB-INF/lib/ODKAggregate-settings.jar:odk-settings.xml to be select schema_name from information_schema.schemata limit 1.
When executing that query as the odk database user against its database on PostgreSQL 9.3, the query returns 0 records. On PostgreSQL 9.5 it returns 1 record.
The issue seems specific to PostgreSQL 9.3, not to the odk database user. Any regular database user executing that query gets an empty result on 9.3, and 1 record on 9.5. The postgres superuser does get the expected 1 record on both.
AFAIK my 9.3 and 9.5 database instances are configured identically. Maybe there was a default permissions change between PostgreSQL versions?
Workaround
My (quick & dirty) workaround was to replace the validationQuery by select 1:
jar xf ODKAggregate.war WEB-INF/lib/ODKAggregate-settings.jar
jar xf WEB-INF/lib/ODKAggregate-settings.jar odk-settings.xml
sed -i -e 's/schema_name from information_schema.schemata limit//' odk-settings.xml
jar uf WEB-INF/lib/ODKAggregate-settings.jar odk-settings.xml
jar uf ODKAggregate.war WEB-INF/lib/ODKAggregate-settings.jar
-- You are receiving this because you are subscribed to this thread. Reply to this email directly or view it on GitHub: https://github.com/opendatakit/aggregate/issues/86 ----==_mimepart_598e6887daf05_52f33f88a8ed7c3414627 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit
Wednesday Feb 08, 2017 at 11:10 GMT
Originally opened as opendatakit/opendatakit#1270 (7 comment(s))
When installing Aggregate 1.4.13 with a PostgreSQL 9.3 backend, the application fails to initialise. With PostgreSQL 9.5 it works fine.
Configuration
WAR deployment on Tomcat 8.5.11 on Ubuntu 14.04 LTS with default JRE (OpenJDK 7u121) and default PostgreSQL (9.3). Freshly created database using create_db_and_user.sql.
Problem description
The application fails to start. SQL exceptions are logged in catalina.out. The innermost exception thrown is java.sql.SQLException: validationQuery didn't return a row.
Diagnosis
The validation query referenced in the root exception is set in the validationQuery property on the dataSource bean in WEB-INF/lib/ODKAggregate-settings.jar:odk-settings.xml to be select schema_name from information_schema.schemata limit 1.
When executing that query as the odk database user against its database on PostgreSQL 9.3, the query returns 0 records. On PostgreSQL 9.5 it returns 1 record.
The issue seems specific to PostgreSQL 9.3, not to the odk database user. Any regular database user executing that query gets an empty result on 9.3, and 1 record on 9.5. The postgres superuser does get the expected 1 record on both.
AFAIK my 9.3 and 9.5 database instances are configured identically. Maybe there was a default permissions change between PostgreSQL versions?
Workaround
My (quick & dirty) workaround was to replace the validationQuery by select 1:
jar xf ODKAggregate.war WEB-INF/lib/ODKAggregate-settings.jar
jar xf WEB-INF/lib/ODKAggregate-settings.jar odk-settings.xml
sed -i -e 
This message was created automatically by mail delivery software.
A message that you sent could not be delivered to one or more of its recipients. This is a temporary error. The following address(es) deferred:
[email protected] Domain beorse.net has exceeded the max emails per hour (311/250 (124%)) allowed. Message will be reattempted later
------- This is a copy of the message, including all the headers. ------ Received: from o4.sgmail.github.com ([192.254.112.99]:27538) by hp159.hostpapa.com with esmtps (TLSv1.2:ECDHE-RSA-AES128-GCM-SHA256:128) (Exim 4.89) (envelope-from [email protected]) id 1dgMDs-000f8D-Fz for [email protected]; Fri, 11 Aug 2017 22:32:20 -0400 DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d=github.com; h=from:reply-to:to:cc:in-reply-to:references:subject:mime-version:content-type:content-transfer-encoding:list-id:list-archive:list-post:list-unsubscribe; s=s20150108; bh=ZhaxnoTY3wo9vaSDhYpVPVvlZyI=; b=U8655O7e0+f6k7RN Q1Ykf2l6Hg6pnVxlTpEVDmJpPT665lscaKQn87Uobnn/n/uAfRNJYhuLnpe54NQt cmN/v+lNksJ5gh1puI8ONlgC7tqum+47XnexhqPnjoMHA83ZU8zlPV21nuJRNMS0 urdXPjJb2VMnKqAyEggfGNpPA8s= Received: by filter0910p1mdw1.sendgrid.net with SMTP id filter0910p1mdw1-10504-598E688C-35 2017-08-12 02:31:40.700702254 +0000 UTC Received: from github-smtp2b-ext-cp1-prd.iad.github.net (github-smtp2b-ext-cp1-prd.iad.github.net [192.30.253.17]) by ismtpd0002p1iad1.sendgrid.net (SG) with ESMTP id jObJBdZWQmyX0EoPXGXZNA for [email protected]; Sat, 12 Aug 2017 02:31:40.766 +0000 (UTC) Date: Sat, 12 Aug 2017 02:31:40 +0000 (UTC) From: Open Data Kit [email protected] Reply-To: opendatakit/aggregate [email protected] To: opendatakit/aggregate [email protected] Cc: Subscribed [email protected] Message-ID: opendatakit/aggregate/issues/86/[email protected] In-Reply-To: opendatakit/aggregate/issues/[email protected] References: opendatakit/aggregate/issues/[email protected] Subject: Re: [opendatakit/aggregate] Aggregate 1.4.13 fails to start with PostgreSQL 9.3 (#86) Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="--==_mimepart_598e688c9c105_d8b3f8425457c3c671fa"; charset=UTF-8 Content-Transfer-Encoding: 7bit Precedence: list X-GitHub-Sender: opendatakit-bot X-GitHub-Recipient: jbeorse X-GitHub-Reason: subscribed List-ID: opendatakit/aggregate <aggregate.opendatakit.github.com> List-Archive: https://github.com/opendatakit/aggregate List-Post: mailto:[email protected] List-Unsubscribe: mailto:unsub+000519afbed0530d680d82d4c26ae3ae6592a69be5753d4d92cf0000000115a62a8c92a169ce0ee362cd@reply.github.com, https://github.com/notifications/unsubscribe/AAUZr3q9wxsf14DbP17mEgh1xi1yTJ3Sks5sXQ6MgaJpZM4O1SSK X-Auto-Response-Suppress: All X-GitHub-Recipient-Address: [email protected] X-SG-EID: JVRD81wLmvjBgNlAmNtibjEbEEhcPGKEESixoxP2c46x0nc/uxuJc5RKwPLoKnYY9iKXdxkLT4Mgbv MCkwIkQExlylwRRsTcKTD+bwGUaSQHu/Jd1aKP6SvhYJzdRzjzW3Q2BPraXs5jvHY7zRqmGvnzPVGK msh5j0IFKxOE31oM2o7aOEwEXS551N1iReY6rHeWtCVwkj600LmOv88RMyNvWEs32gAoJFplH1Qs5N Y=
----==_mimepart_598e688c9c105_d8b3f8425457c3c671fa Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit
This is a puzzling failure that seems to be an issue with how 9.3 does grants, as everything was working on 9.1.x, and then everything is working on 9.4 (the version I used when updating everything to use JDBC 4 and Tomcat 8).
-- You are receiving this because you are subscribed to this thread. Reply to this email directly or view it on GitHub: https://github.com/opendatakit/aggregate/issues/86#issuecomment-321952125 ----==_mimepart_598e688c9c105_d8b3f8425457c3c671fa Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Comment by mitchellsundt
Tuesday Feb 14, 2017 at 18:47 GMT
This is a puzzling failure that seems to be an issue with how 9.3 does g= rants, as everything was working on 9.1.x, and then everything is working o= n 9.4 (the version I used when updating everything to use JDBC 4 and Tomcat= 8).
&mda=
sh;
You are receiving this because you are subscribed to this thread.Reply to this email directly, view it on GitHub, or mute the thread.
----==_mimepart_598e688c9c105_d8b3f8425457c3c671fa--
This message was created automatically by mail delivery software.
A message that you sent could not be delivered to one or more of its recipients. This is a temporary error. The following address(es) deferred:
[email protected] Domain beorse.net has exceeded the max emails per hour (310/250 (124%)) allowed. Message will be reattempted later
------- This is a copy of the message, including all the headers. ------ ------ The body of the message is 6328 characters long; only the first ------ 5000 or so are included here. Received: from o10.sgmail.github.com ([167.89.101.201]:64157) by hp159.hostpapa.com with esmtps (TLSv1.2:ECDHE-RSA-AES128-GCM-SHA256:128) (Exim 4.89) (envelope-from [email protected]) id 1dgMDs-000f83-3y for [email protected]; Fri, 11 Aug 2017 22:32:20 -0400 DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d=github.com; h=from:reply-to:to:cc:in-reply-to:references:subject:mime-version:content-type:content-transfer-encoding:list-id:list-archive:list-post:list-unsubscribe; s=s20150108; bh=JMCl36HEFaIfGIpRmw7qK/eYxyA=; b=i26TDLgr9riiHNeN NS/yAFfoIGw1VTdn60duWi6sgldueLXVWtFiG2rpS+WJmEnefVTaEGczogE847c8 mRT3U6inNqHn83IumrqxeetM2lnPMjHC5puPMn8D7doGmMFhN2QsjvGidufcdCaG DKdBUhd4WyeEjJDux0pEDpTxulY= Received: by filter0429p1mdw1.sendgrid.net with SMTP id filter0429p1mdw1-27035-598E688B-65 2017-08-12 02:31:39.835519285 +0000 UTC Received: from github-smtp2b-ext-cp1-prd.iad.github.net (github-smtp2b-ext-cp1-prd.iad.github.net [192.30.253.17]) by ismtpd0004p1iad1.sendgrid.net (SG) with ESMTP id RI22QbUjQZ-PjZVJu--bWg for [email protected]; Sat, 12 Aug 2017 02:31:39.757 +0000 (UTC) Date: Sat, 12 Aug 2017 02:31:40 +0000 (UTC) From: Open Data Kit [email protected] Reply-To: opendatakit/aggregate [email protected] To: opendatakit/aggregate [email protected] Cc: Subscribed [email protected] Message-ID: opendatakit/aggregate/issues/86/[email protected] In-Reply-To: opendatakit/aggregate/issues/[email protected] References: opendatakit/aggregate/issues/[email protected] Subject: Re: [opendatakit/aggregate] Aggregate 1.4.13 fails to start with PostgreSQL 9.3 (#86) Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="--==_mimepart_598e688b90e5e_602e3f82a7db5c2c742d5"; charset=UTF-8 Content-Transfer-Encoding: 7bit Precedence: list X-GitHub-Sender: opendatakit-bot X-GitHub-Recipient: jbeorse X-GitHub-Reason: subscribed List-ID: opendatakit/aggregate <aggregate.opendatakit.github.com> List-Archive: https://github.com/opendatakit/aggregate List-Post: mailto:[email protected] List-Unsubscribe: mailto:unsub+000519afeedac8bd7baeb148a66131f9f399dd5384e9695192cf0000000115a62a8b92a169ce0ee362cd@reply.github.com, https://github.com/notifications/unsubscribe/AAUZr-zrEGfiLDdWidxC1NHqZgI9UoLzks5sXQ6LgaJpZM4O1SSK X-Auto-Response-Suppress: All X-GitHub-Recipient-Address: [email protected] X-SG-EID: JVRD81wLmvjBgNlAmNtibjEbEEhcPGKEESixoxP2c455har2kpXABESYjobVxaaCbExIAmSNiF3i+C NRzdIebJvrjRJDhPN/q4jUZFxAAqZM2IFbrOuSdRf1+s0HrOoyfUQHYr0dB8HOdPgbLx6Hw05Aba5I lAYQ87yI4UaYHJi07bjP1YDpvsosFI76DNDjlml9kigPHEyM1Yav/MLWKuHG/bhj/oHr50EJbULaF7 g=
----==_mimepart_598e688b90e5e_602e3f82a7db5c2c742d5 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit
Yes, the validation query could be changed BUT, within ODK Aggregate, we MUSTbe able to run select statements against the information_schema. If that fails, then ODK Aggregate will never function correctly. So in this case, having a validation query that confirms you can access the information_schema is actually beneficial, as it catches this failure very early.
e.g.,
https://github.com/opendatakit/aggregate/blob/master/src/main/java/org/opendatakit/common/persistence/engine/pgres/DatastoreImpl.java#L180
The remedy is to add a grant to that schema when running against older versions.
e.g., something like the following as part of the create_db_and_user.sql content:
grant all privileges on schema "information_schema" to "your_odk__user";
Or perhaps just "usage" ? I am unclear whether usage would be sufficient for running select queries against the tables within the schema.
-- You are receiving this because you are subscribed to this thread. Reply to this email directly or view it on GitHub: https://github.com/opendatakit/aggregate/issues/86#issuecomment-321952124 ----==_mimepart_598e688b90e5e_602e3f82a7db5c2c742d5 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Comment by mitchellsundt
Tuesday Feb 14, 2017 at 18:44 GMT
Yes, the validation query could be changed BUT, within ODK Aggregate, we= ***MUST***be able to run select statements against the information_schema.= If that fails, then ODK Aggregate will never function correctly. So in th= is case, having a validation query that confirms you can access the informa= tion_schema is actually beneficial, as it catches this failure very early.
e.g.,
The remedy is to add a grant to that schema when running against older v= ersions.
e.g., something like the following as part of the create_db_and_user.sql= content:
grant all privileges on schema "information_schema" to "your_odk__user";=
Or perhaps just "usage" ? I am unclear whether usage would be sufficien= t for running select queries against the tables within the schema.
&mda=
sh;
You are receiving this because you are subscribed to this thread.Reply to this email directly, view it on GitHub, or mute the thread.
This message was created automatically by mail delivery software.
A message that you sent could not be delivered to one or more of its recipients. This is a temporary error. The following address(es) deferred:
[email protected] Domain beorse.net has exceeded the max emails per hour (308/250 (123%)) allowed. Message will be reattempted later
------- This is a copy of the message, including all the headers. ------ Received: from o11.sgmail.github.com ([167.89.101.202]:17738) by hp159.hostpapa.com with esmtps (TLSv1.2:ECDHE-RSA-AES128-GCM-SHA256:128) (Exim 4.89) (envelope-from [email protected]) id 1dgMDq-000f7f-9k for [email protected]; Fri, 11 Aug 2017 22:32:18 -0400 DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d=github.com; h=from:reply-to:to:cc:in-reply-to:references:subject:mime-version:content-type:content-transfer-encoding:list-id:list-archive:list-post:list-unsubscribe; s=s20150108; bh=7YksVX85xfyGM38Sm/htZF1jb1o=; b=FOsAktLkWxsBEARe yFfn5dck+HItb06pMuL6BQlmabMD/Hgq9mU4XgnfSAoC/BqZyRy6Bu1bC479JKNo OKoCeC+6VBAxRVjcPhnTMaSpU0Xxt0Hdwacj+eKYHKEmoj1ljBbdBpijDveyyCnu u71C14rnD3+MSQ/jG13mfbKzmTk= Received: by filter1091p1mdw1.sendgrid.net with SMTP id filter1091p1mdw1-19634-598E688A-1C 2017-08-12 02:31:38.575320279 +0000 UTC Received: from github-smtp2b-ext-cp1-prd.iad.github.net (github-smtp2b-ext-cp1-prd.iad.github.net [192.30.253.17]) by ismtpd0030p1mdw1.sendgrid.net (SG) with ESMTP id xmZ5SorTQo68IxmVjsG7Sw for [email protected]; Sat, 12 Aug 2017 02:31:38.331 +0000 (UTC) Date: Sat, 12 Aug 2017 02:31:38 +0000 (UTC) From: Open Data Kit [email protected] Reply-To: opendatakit/aggregate [email protected] To: opendatakit/aggregate [email protected] Cc: Subscribed [email protected] Message-ID: opendatakit/aggregate/issues/86/[email protected] In-Reply-To: opendatakit/aggregate/issues/[email protected] References: opendatakit/aggregate/issues/[email protected] Subject: Re: [opendatakit/aggregate] Aggregate 1.4.13 fails to start with PostgreSQL 9.3 (#86) Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="--==_mimepart_598e68897e066_75433fb432425c2c47852"; charset=UTF-8 Content-Transfer-Encoding: 7bit Precedence: list X-GitHub-Sender: opendatakit-bot X-GitHub-Recipient: jbeorse X-GitHub-Reason: subscribed List-ID: opendatakit/aggregate <aggregate.opendatakit.github.com> List-Archive: https://github.com/opendatakit/aggregate List-Post: mailto:[email protected] List-Unsubscribe: mailto:unsub+000519af443958ae82081adca0db490118e9ebf28d87790d92cf0000000115a62a8992a169ce0ee362cd@reply.github.com, https://github.com/notifications/unsubscribe/AAUZryi9SfsaQISMx4iBozz4RmT_5jf1ks5sXQ6JgaJpZM4O1SSK X-Auto-Response-Suppress: All X-GitHub-Recipient-Address: [email protected] X-SG-EID: JVRD81wLmvjBgNlAmNtibjEbEEhcPGKEESixoxP2c46tAMjpXwrdfuxFs30XHkgg/TF4aOx7mELuFL KlkA1uJmc/3TrKrDDpARsAQkKB00o1MSQd1rN68kydUPM8CUNyQO31ivjzwJ0NptWbKa8CBzE80hom fWRJlk6z65LM6P4jU0i525IEkMJaxD+tvQaSM0zN6dYlFaXMbUB0Z05CYOIB49eVhnA32MDnk880uh s=
----==_mimepart_598e68897e066_75433fb432425c2c47852 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit
I've updated the Aggregate 1.4.13 upgrade steps to direct users to upgrade to PostgreSQL 9.4 or newer. This appears to be an issue with permissions required to access to the information_schema content. That appears to be resolved in 9.4 and higher.
-- You are receiving this because you are subscribed to this thread. Reply to this email directly or view it on GitHub: https://github.com/opendatakit/aggregate/issues/86#issuecomment-321952120 ----==_mimepart_598e68897e066_75433fb432425c2c47852 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Comment by mitchellsundt
Monday Feb 13, 2017 at 18:46 GMT
I've updated the Aggregate 1.4.13 upgrade steps to direct users to upgra= de to PostgreSQL 9.4 or newer. This appears to be an issue with permissions= required to access to the information_schema content. That appears to be r= esolved in 9.4 and higher.
&mda=
sh;
You are receiving this because you are subscribed to this thread.Reply to this email directly, view it on GitHub, or mute the thread.
----==_mimepart_598e68897e066_75433fb432425c2c47852--
This message was created automatically by mail delivery software.
A message that you sent could not be delivered to one or more of its recipients. This is a temporary error. The following address(es) deferred:
[email protected] Domain beorse.net has exceeded the max emails per hour (312/250 (124%)) allowed. Message will be reattempted later
------- This is a copy of the message, including all the headers. ------ ------ The body of the message is 10829 characters long; only the first ------ 5000 or so are included here. Received: from o11.sgmail.github.com ([167.89.101.202]:20901) by hp159.hostpapa.com with esmtps (TLSv1.2:ECDHE-RSA-AES128-GCM-SHA256:128) (Exim 4.89) (envelope-from [email protected]) id 1dgMDu-000f8t-1P for [email protected]; Fri, 11 Aug 2017 22:32:22 -0400 DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d=github.com; h=from:reply-to:to:cc:in-reply-to:references:subject:mime-version:content-type:content-transfer-encoding:list-id:list-archive:list-post:list-unsubscribe; s=s20150108; bh=AI3XIrI2WQs86L4VeEUio8tH/v4=; b=W4KgKsNiCNsZE28/ QdcCaDgtXglUTe7kIMYlI4lyiKDX9SqEj5APoGWgRSSNtKBwC61Y5bWsFA3AYKnD BdKhkc8OPWJX+Ib1Ucp31A5uQW5514Bf2wB598rxOpdI5jpzJXAa52fjpteVCLVK 1FweZQKT8xTSgfNRZ8lBZratZZE= Received: by filter0999p1mdw1.sendgrid.net with SMTP id filter0999p1mdw1-17045-598E688E-12 2017-08-12 02:31:42.223507348 +0000 UTC Received: from github-smtp2b-ext-cp1-prd.iad.github.net (github-smtp2b-ext-cp1-prd.iad.github.net [192.30.253.17]) by ismtpd0026p1mdw1.sendgrid.net (SG) with ESMTP id lwJ-5OxtTMu8nzLtvx2Lvg for [email protected]; Sat, 12 Aug 2017 02:31:42.183 +0000 (UTC) Date: Sat, 12 Aug 2017 02:31:42 +0000 (UTC) From: Open Data Kit [email protected] Reply-To: opendatakit/aggregate [email protected] To: opendatakit/aggregate [email protected] Cc: Subscribed [email protected] Message-ID: opendatakit/aggregate/issues/86/[email protected] In-Reply-To: opendatakit/aggregate/issues/[email protected] References: opendatakit/aggregate/issues/[email protected] Subject: Re: [opendatakit/aggregate] Aggregate 1.4.13 fails to start with PostgreSQL 9.3 (#86) Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="--==_mimepart_598e688ddb563_a5323f8e3e325c38923fa"; charset=UTF-8 Content-Transfer-Encoding: 7bit Precedence: list X-GitHub-Sender: opendatakit-bot X-GitHub-Recipient: jbeorse X-GitHub-Reason: subscribed List-ID: opendatakit/aggregate <aggregate.opendatakit.github.com> List-Archive: https://github.com/opendatakit/aggregate List-Post: mailto:[email protected] List-Unsubscribe: mailto:unsub+000519afe730d2ab1e8952e7026eba8a7eef7297cc63cbb792cf0000000115a62a8d92a169ce0ee362cd@reply.github.com, https://github.com/notifications/unsubscribe/AAUZr5avlfzSo5nCA_Brq_1O_emo_4s5ks5sXQ6NgaJpZM4O1SSK X-Auto-Response-Suppress: All X-GitHub-Recipient-Address: [email protected] X-SG-EID: JVRD81wLmvjBgNlAmNtibjEbEEhcPGKEESixoxP2c47ISBc05I1ne53C9ekrgISqGUvHOnclkNKWtQ R1Er+Hy7PyeMhiwP/bnkCmHbOzcg4I7qSuNyF5lIIBK493RsTrKgUhnSE1DpTEbdswV1VhQbqV+EnI aW0EtQWNCrjkyYNJMXWqAu7+NoNqVpXN+7XiLkAJu4uwlMYgrSfsUkRkl0xDtozh8/dqWvt0cWo8+r Y=
----==_mimepart_598e688ddb563_a5323f8e3e325c38923fa Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit
GRANT either USAGE or ALL on information_schema did not help so I looked a bit deeper. I've spotted the issue, and think I've got the clean solution.
Strangely, SELECT * FROM information_schema.columns works fine, which probably explains why my ODK instance is actually working on 9.3 ... until it breaks on another possible information_schema.* table permissions issue :-/
So what is the issue with information_schema.schemata? Looking at its definition (using \d+ information_schema.schemata in psql) gave a clue. It is defined as a view, with one significant difference between 9.3 and 9.5:
SELECT ... FROM pg_namespace n, pg_authid u ...
-- 9.3
WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'USAGE'::text);
-- 9.5
WHERE n.nspowner = u.oid AND (pg_has_role(n.nspowner, 'USAGE'::text)
OR has_schema_privilege(n.oid, 'CREATE, USAGE'::text));
Note that n aliases pg_namespace which is the 'real' table of schemata, and u aliases pg_authid, the table of user accounts (needed only to display the schema owner names).
The WHERE clause filters the rows the user gets to see. The check pg_has_role checks that the current user has the USAGE permission for the role n.nspowner. In other words, can the ODK user assume the role of the schema owner? Since the schema owner of the ODK schema is postgres this is false.
On 9.5, things work out because of the disjunct: the ODK user does have USAGE (and CREATE) privilege for the ODK schema (even if postgres is the schema owner) presumably because the schema sits in the ODK database it owns, and for which it is granted ALL.
The obvious solution then is to make the ODK user the owner of its own schema.
In create_db_and_user.sql, add the statement alter schema "odk_schema" owner to "odk_user"(in fact, this could probably replace the current grant all privileges on schema "odk_schema" to "odk_user").
-- You are receiving this because you are subscribed to this thread. Reply to this email directly or view it on GitHub: https://github.com/opendatakit/aggregate/issues/86#issuecomment-321952131 ----==_mimepart_598e688ddb563_a5323f8e3e325c38923fa Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Wednesday Feb 15, 2017 at 13:28 GMT
GRANT either USAGE or ALL on
Strangely, SELECT * FROM information_schema.columns works f=
ine, which probably explains why my ODK instance is actually working on 9.3=
... until it breaks on another possible information_schema.* =
table permissions issue :-/
So what is the issue with information_schema.schemata? Look=
ing at its definition (using \d+ information_schema.schemata i=
n psql) gave a clue. It is defined as a view, with one significant differen=
ce between 9.3 and 9.5:
SEL=
ECT ... FROM pg_namespace n, pg_authid u=
...
-- 9.3
WHERE n.nspowner =3D u.oid AND=
span> pg_has_role(n.nspo=
wner, 'USAGE'::text);
-- 9.5
WHERE n.nspowner =3D u.oid AND=
span> (pg_has_role(n.nsp=
owner, 'USAGE' ::text)
OR has_schema
This message was created automatically by mail delivery software.
A message that you sent could not be delivered to one or more of its recipients. This is a temporary error. The following address(es) deferred:
[email protected] Domain beorse.net has exceeded the max emails per hour (309/250 (123%)) allowed. Message will be reattempted later
------- This is a copy of the message, including all the headers. ------ ------ The body of the message is 6737 characters long; only the first ------ 5000 or so are included here. Received: from o11.sgmail.github.com ([167.89.101.202]:22317) by hp159.hostpapa.com with esmtps (TLSv1.2:ECDHE-RSA-AES128-GCM-SHA256:128) (Exim 4.89) (envelope-from [email protected]) id 1dgMDq-000f7l-NK for [email protected]; Fri, 11 Aug 2017 22:32:18 -0400 DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d=github.com; h=from:reply-to:to:cc:in-reply-to:references:subject:mime-version:content-type:content-transfer-encoding:list-id:list-archive:list-post:list-unsubscribe; s=s20150108; bh=fyftKK2po0p4eMdz3pmyTJSgdPA=; b=Y1273EzpbBYKS9hU RJoiD21NXoXhKkVenqctOFbd7OQ+nHCG20oQSduQi1xbXTB2PU83g053gjDgTMyq btMvDFASVrK8NiPwkkxsBCd/xOxSRemjwQLL3TjtCBGgC4mKrVn6BDNP/bh07cUM 8y9cn2iIKBgGesj5DHOkqpAqVSw= Received: by filter0935p1mdw1.sendgrid.net with SMTP id filter0935p1mdw1-27986-598E688B-1 2017-08-12 02:31:39.069850975 +0000 UTC Received: from github-smtp2b-ext-cp1-prd.iad.github.net (github-smtp2b-ext-cp1-prd.iad.github.net [192.30.253.17]) by ismtpd0029p1mdw1.sendgrid.net (SG) with ESMTP id L298m18mRPugcYQI2_VsNA for [email protected]; Sat, 12 Aug 2017 02:31:39.017 +0000 (UTC) Date: Sat, 12 Aug 2017 02:31:39 +0000 (UTC) From: Open Data Kit [email protected] Reply-To: opendatakit/aggregate [email protected] To: opendatakit/aggregate [email protected] Cc: Subscribed [email protected] Message-ID: opendatakit/aggregate/issues/86/[email protected] In-Reply-To: opendatakit/aggregate/issues/[email protected] References: opendatakit/aggregate/issues/[email protected] Subject: Re: [opendatakit/aggregate] Aggregate 1.4.13 fails to start with PostgreSQL 9.3 (#86) Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="--==_mimepart_598e688a99c54_67113fb432425c2c135787"; charset=UTF-8 Content-Transfer-Encoding: 7bit Precedence: list X-GitHub-Sender: opendatakit-bot X-GitHub-Recipient: jbeorse X-GitHub-Reason: subscribed List-ID: opendatakit/aggregate <aggregate.opendatakit.github.com> List-Archive: https://github.com/opendatakit/aggregate List-Post: mailto:[email protected] List-Unsubscribe: mailto:unsub+000519af36915df543a82e736e0daab5a16d91fa396e4f5f92cf0000000115a62a8a92a169ce0ee362cd@reply.github.com, https://github.com/notifications/unsubscribe/AAUZr47HtL9SpwtsaHN3Pdtha-lkq8K0ks5sXQ6KgaJpZM4O1SSK X-Auto-Response-Suppress: All X-GitHub-Recipient-Address: [email protected] X-SG-EID: JVRD81wLmvjBgNlAmNtibjEbEEhcPGKEESixoxP2c477EOqY6elQNmxDLMG++3ZF089mzRzYoPc7vC rESPDVHKf78/R7BHsvCKSl1YZTTKvdjnMhFIGBw/9kkg8dUOjE3v62lst5iH0DhaOjxC+05yJcWtiw Y7Eg/CUtBdbIswqkf5JG3RDq2jGDGhSI0oIineH57bkFUgQDv2wZApDEacKom3t4+q9DThJB/h9jgt I=
----==_mimepart_598e688a99c54_67113fb432425c2c135787 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit
Thanks @mitchellsundt but wouldn't it be better to change the validation query so as to make this work irrespective of PostgreSQL version? PostgreSQL 9.3 will be around for another year and a half, and in many places won't/can't be upgraded to 9.4.
In applications that don't create their own tables, the validation query usually checks that some core table is found in the database, so that the application can "fail fast" when connected wrongly. In our case, where there may or may not be ODK tables yet in the database, the purpose of the validation query is really only to check that there is a PostgreSQL at the other end of the connection. For this, a SELECT 1 would suffice.
Using SELECT schema_name FROM information_schema.schemata WHERE schema_name = 'my-expected-schema-name' would add some value to the validation. However SELECT schema_name FROM information_schema.schemata LIMIT 1 adds none relative to SELECT 1. It only proves that some schemata (and not necessarily the one for the application) exist.
-- You are receiving this because you are subscribed to this thread. Reply to this email directly or view it on GitHub: https://github.com/opendatakit/aggregate/issues/86#issuecomment-321952123 ----==_mimepart_598e688a99c54_67113fb432425c2c135787 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Tuesday Feb 14, 2017 at 07:31 GMT
Thanks @mitchellsundt but wouldn't it be better to change the validation qu= ery so as to make this work irrespective of PostgreSQL version? PostgreSQL = 9.3 will be around for another year and a half, and in many places won't/can't be upgra= ded to 9.4.
In applications that don't create their own tables, the validation query=
usually checks that some core table is found in the database, so that the =
application can "fail fast" when connected wrongly. In our case, where ther=
e may or may not be ODK tables yet in the database, the purpose of the vali=
dation query is really only to check that there is a PostgreSQL at the othe=
r end of the connection. For this, a SELECT 1 would suffice.=
p>
Using SELECT schema_name FROM information_schema.schemata WHERE sc=
hema_name =3D 'my-expected-schema-name' would add some value to the =
validation. However SELECT schema_name FROM information_schema.schema=
ta LIMIT 1 adds none relative to SELECT 1. It only prov=
es that some schemata (and not necessarily the one for the applica=
tion) exist.
&mda=
sh;
You are receiving this because you are subscribed to this thread.Reply to this email directly, view it on GitHub, or mute the thread.
Attention! We're housekeeping! This issue will automatically be closed if no feedback is received in one week.
If this issue is important to you or you can provide more information about it, please, do so as soon as possible :)
