Web Installer PubSub Postgres Schema Error (#412)

Eric Dziewa opened 10 years ago

Loading PubSub component schema	ERROR
Loading schema from file: database/postgresql-pubsub-schema-3.0.0.sql, URI: jdbc:postgresql://localhost/tdb?user=postgres&password=tpubxmpp777
Can't finalize: ERROR: return type mismatch in function declared to return record Detail: Final statement returns too many columns. Where: SQL function "tigpubsubgetnodeitemsmeta"

web-install-11.png tigase.log.0 tigase-console.log

Andrzej Wójcik (Tigase) commented 10 years ago

Fixed DB schema for PostgreSQL

Eric Dziewa commented 10 years ago

  ---------------------------------------------
  ERROR! Terminating the server process.
  PubSub Component is not compatible with
  database schema which exists in
  jdbc:postgresql://localhost/tdb2?user=tigase&password=xmpp3030
  This component uses newer schema. To continue
  use of currently deployed schema, please use
  older version of PubSub Component.
  To convert database to new schema please see:
  https://projects.tigase.org/projects/tigase-pubsub/wiki/PubSub_database_schema_conversion

Andrzej Wójcik (Tigase) commented 10 years ago

Is there anything else after this entry in log or this is all?

If there is more could you attach log to this issue?

If not then could you run this again with

--debug=server,pubsub

entry in etc/init.properties and attach newly created log file to issue so there will be more (exception with description what failed) as from what I see in code there should be more.

Eric Dziewa commented 10 years ago

There is now this:

2015-01-13 16:17:33.185 [main]             PubSubDAOJDBC.checkSchema()        FINEST:   Exception during checkSchema: org.postgresql.util.PSQLException: ERROR: permission denied for relation tig_pubsub_nodes
  Where: SQL function "tigpubsubgetnodeid" statement 1

logs attached.

Andrzej Wójcik (Tigase) commented 10 years ago

It looks like now problem is with permissions to access database tables, like there where created with other user than user who now tries to use procedures.

Eric Dziewa commented 10 years ago

Here's what I've found out.

First permissions were not right. tdb* are what I'm using here.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 tdb       | tigase   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/tigase           +
           |          |          |             |             | tigase=CTc/tigase
 tdb2      | tigase   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/tigase           +
           |          |          |             |             | tigase=CTc/tigase
 tdb3      | tigase   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =CTc/tigase          +
           |          |          |             |             | tigase=CTc/tigase
 tdb4      | tigase   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 tdb5      | tigase   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 tigase    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 tpub      | tigase   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(10 rows)

user tigase is only granted C -- CREATE, c -- CONNECT, and T -- TEMPORARY. Setting grant options are said to not work in postgres unless you are connected to a DB. Granting permissions to user tigase in database/postgresql-installer-create-db.sql, then changing DB ownership resulted in those permissions. Ownership implies all privileges (they can be revoked which seems to be happening), so I commented the grant statement in the file and the result was tdb5 above. User tigase has all permissions as owner.

Another note, I ran the scripts as user tigase and had no problems with the DB. Tigase ran with no problems. This was tdb4 above.

tigase@tpub.xmpp-test.net tigase-issue #7.0.0-SNAPSHOT-b3752/ $ createdb -U tigase tdb4
tigase@tpub.xmpp-test.net tigase-issue #7.0.0-SNAPSHOT-b3752/ $ psql -q -U tigase -d tdb4 -f database/postgresql-schema-5-1.sql  
tigase@tpub.xmpp-test.net tigase-issue #7.0.0-SNAPSHOT-b3752/ $ psql -q -U tigase -d tdb4 -f database/postgresql-pubsub-schema-3.0.0.sql

After commenting GRANT ALL ON database ${dbName} TO ${dbUser}; in database/postgresql-installer-create-db.sql, and having all privileges I was still getting the same error.

Tried

postgres=# grant select, insert, update, delete on all tables in schema public to tigase;

Didn't work. The following did work and tigase is running.

postgres=# \c tdb
You are now connected to database "tdb" as user "postgres".
tdb=# grant select on all tables in schema public to tigase;

I think easiest would be to create the DB as $user not $superuser. The alternative being to remove the grant statement, connect to the DB and issue the grant on schema public.

Andrzej Wójcik (Tigase) commented 10 years ago

%wojtek: could you look at this issue, as I have not idea what is wrong as on my installation all is working fine?

wojciech.kapcia@tigase.net commented 10 years ago

%andrzej.wojcik works OK on my local postgres (9.2.2.0), schema is created correctly except for the permissions. Do you process database/postgresql-installer-post.sql after the schema loading? Do you pass correct parameters to DBSchemaLoader while loading pubsub schema (i.e. correct root user and database user as parameters)? Running it from command line results in correct schema.

Andrzej Wójcik (Tigase) commented 10 years ago

I fixed this issue by change of order used by web installer in which stored procedures/functions are created and permissions are assigned to first create procedures and later to add permissions. This way it works fine on PostgreSQL as well as on other databases.

Next snapshot build will contain proper fix

wojciech.kapcia@tigase.net commented 10 years ago

Works now as expected.

Type	Bug
Priority	Normal
Assignee	Artur Hefczyc
RedmineID	2605
Spent time	24h

	Eric Dziewa opened 10 years ago `Loading PubSub component schema ERROR Loading schema from file: database/postgresql-pubsub-schema-3.0.0.sql, URI: jdbc:postgresql://localhost/tdb?user=postgres&password=tpubxmpp777 Can't finalize: ERROR: return type mismatch in function declared to return record Detail: Final statement returns too many columns. Where: SQL function "tigpubsubgetnodeitemsmeta"` web-install-11.png tigase.log.0 tigase-console.log
	Andrzej Wójcik (Tigase) commented 10 years ago Fixed DB schema for PostgreSQL
	Eric Dziewa commented 10 years ago `--------------------------------------------- ERROR! Terminating the server process. PubSub Component is not compatible with database schema which exists in jdbc:postgresql://localhost/tdb2?user=tigase&password=xmpp3030 This component uses newer schema. To continue use of currently deployed schema, please use older version of PubSub Component. To convert database to new schema please see: https://projects.tigase.org/projects/tigase-pubsub/wiki/PubSub_database_schema_conversion`
	Andrzej Wójcik (Tigase) commented 10 years ago Is there anything else after this entry in log or this is all? If there is more could you attach log to this issue? If not then could you run this again with `--debug=server,pubsub` entry in `etc/init.properties` and attach newly created log file to issue so there will be more (exception with description what failed) as from what I see in code there should be more.
	Eric Dziewa commented 10 years ago There is now this: `2015-01-13 16:17:33.185 [main] PubSubDAOJDBC.checkSchema() FINEST: Exception during checkSchema: org.postgresql.util.PSQLException: ERROR: permission denied for relation tig_pubsub_nodes Where: SQL function "tigpubsubgetnodeid" statement 1` logs attached.
	Andrzej Wójcik (Tigase) commented 10 years ago It looks like now problem is with permissions to access database tables, like there where created with other user than user who now tries to use procedures.
	Eric Dziewa commented 10 years ago Here's what I've found out. First permissions were not right. tdb* are what I'm using here. postgres=# \l List of databases Name \| Owner \| Encoding \| Collate \| Ctype \| Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres \| postgres \| UTF8 \| en_US.UTF-8 \| en_US.UTF-8 \| tdb \| tigase \| UTF8 \| en_US.UTF-8 \| en_US.UTF-8 \| =Tc/tigase + \| \| \| \| \| tigase=CTc/tigase tdb2 \| tigase \| UTF8 \| en_US.UTF-8 \| en_US.UTF-8 \| =Tc/tigase + \| \| \| \| \| tigase=CTc/tigase tdb3 \| tigase \| UTF8 \| en_US.UTF-8 \| en_US.UTF-8 \| =CTc/tigase + \| \| \| \| \| tigase=CTc/tigase tdb4 \| tigase \| UTF8 \| en_US.UTF-8 \| en_US.UTF-8 \| tdb5 \| tigase \| UTF8 \| en_US.UTF-8 \| en_US.UTF-8 \| template0 \| postgres \| UTF8 \| en_US.UTF-8 \| en_US.UTF-8 \| =c/postgres + \| \| \| \| \| postgres=CTc/postgres template1 \| postgres \| UTF8 \| en_US.UTF-8 \| en_US.UTF-8 \| =c/postgres + \| \| \| \| \| postgres=CTc/postgres tigase \| postgres \| UTF8 \| en_US.UTF-8 \| en_US.UTF-8 \| tpub \| tigase \| UTF8 \| en_US.UTF-8 \| en_US.UTF-8 \| (10 rows) user tigase is only granted C -- CREATE, c -- CONNECT, and T -- TEMPORARY. Setting grant options are said to not work in postgres unless you are connected to a DB. Granting permissions to user tigase in database/postgresql-installer-create-db.sql, then changing DB ownership resulted in those permissions. Ownership implies all privileges (they can be revoked which seems to be happening), so I commented the grant statement in the file and the result was tdb5 above. User tigase has all permissions as owner. Another note, I ran the scripts as user tigase and had no problems with the DB. Tigase ran with no problems. This was tdb4 above. `tigase@tpub.xmpp-test.net tigase-issue #7.0.0-SNAPSHOT-b3752/ $ createdb -U tigase tdb4 tigase@tpub.xmpp-test.net tigase-issue #7.0.0-SNAPSHOT-b3752/ $ psql -q -U tigase -d tdb4 -f database/postgresql-schema-5-1.sql tigase@tpub.xmpp-test.net tigase-issue #7.0.0-SNAPSHOT-b3752/ $ psql -q -U tigase -d tdb4 -f database/postgresql-pubsub-schema-3.0.0.sql` After commenting `GRANT ALL ON database ${dbName} TO ${dbUser};` in database/postgresql-installer-create-db.sql, and having all privileges I was still getting the same error. Tried `postgres=# grant select, insert, update, delete on all tables in schema public to tigase;` Didn't work. The following did work and tigase is running. `postgres=# \c tdb You are now connected to database "tdb" as user "postgres". tdb=# grant select on all tables in schema public to tigase;` I think easiest would be to create the DB as $user not $superuser. The alternative being to remove the grant statement, connect to the DB and issue the grant on schema public.
	Andrzej Wójcik (Tigase) commented 10 years ago %wojtek: could you look at this issue, as I have not idea what is wrong as on my installation all is working fine?
	wojciech.kapcia@tigase.net commented 10 years ago %andrzej.wojcik works OK on my local postgres (9.2.2.0), schema is created correctly except for the permissions. Do you process `database/postgresql-installer-post.sql` after the schema loading? Do you pass correct parameters to `DBSchemaLoader` while loading pubsub schema (i.e. correct root user and database user as parameters)? Running it from command line results in correct schema.
	Andrzej Wójcik (Tigase) commented 10 years ago I fixed this issue by change of order used by web installer in which stored procedures/functions are created and permissions are assigned to first create procedures and later to add permissions. This way it works fine on PostgreSQL as well as on other databases. Next snapshot build will contain proper fix
	wojciech.kapcia@tigase.net commented 10 years ago Works now as expected.