Web Installer PubSub Postgres Schema Error (#412)

Eric Dziewa opened 1 decade 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

Activities

Andrzej Wójcik (Tigase) commented 1 decade ago

Fixed DB schema for PostgreSQL

Eric Dziewa commented 1 decade 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 1 decade 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 1 decade 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 1 decade 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 1 decade 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 1 decade 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) commented 1 decade 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 1 decade 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) commented 1 decade ago

Works now as expected.
Login to comment

Type	Bug
Priority	Normal
Assignee	Artur Hefczyc
RedmineID	2605
Spent time	0

Issue Votes (0)

Watchers (0)

Reference

tigase/_server/server-core#412