Re: [gnome-db] [Fwd: Re: Partial Meta Store update.]
Bas Driessen <[email protected]>
| Newsgroups | gmane.comp.gnome.db |
|---|---|
| Organization | Xobas Software |
| Message-ID | <[email protected]> |
On Fri, 2009-07-24 at 13:55 +0200, Vivien Malerba wrote: > > > > 2009/7/24 Bas Driessen <[email protected]> > > > On Fri, 2009-07-24 at 13:47 +0200, Vivien Malerba wrote: > > > > > > > 2009/7/24 Bas Driessen <[email protected]> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > 2009/7/19 Bas Driessen <[email protected]> > > > > > > > > > > > > > > > > > > Hello, > > > > > > > > > > > > > > > > > > Question regarding partial meta store update. I found that the following to update a single table does not appear to work: > > > > > > > > > > > > > > > > > > > > > > > > > > > g_print ("\nPartial metastore update for table '%s'...\n", TABLE_NAME); > > > > > > > > > > > > > > > > > > GdaMetaContext mcontext = {"_tables", 1, NULL, NULL}; > > > > > > > > > > > > > > > > > > mcontext.column_names = g_new (gchar *, 1); > > > > > > > > > > > > > > > > > > mcontext.column_names[0] = "table_name"; > > > > > > > > > > > > > > > > > > mcontext.column_values = g_new (GValue *, 1); > > > > > > > > > > > > > > > > > > g_value_set_string ((mcontext.column_values[0] = gda_value_new (G_TYPE_STRING)), TABLE_NAME); > > > > > > > > > > > > > > > > > > if (!gda_connection_update_meta_store (connection, &mcontext, &error)) > > > > > > > > > > > > > > > > > > return -1; > > > > > > > > > > > > > > > > > > After this, my meta store is NOT updated. > > > > > > > > > > > > > > > > > > > > > > > > > > > This may depend on the actual value of TABLE_NAME, specifically if it is a mix of upper and lower case, or if it's a reserved keyword. The proper way is: > > > > > > > > > tmp = gda_sql_identifier_quote (TABLE_NAME, cnc, NULL, TRUE, FALSE); // or with the last argument being TRUE, see the doc > > > > > > > > > g_value_set_string ((mcontext.column_values[0] = gda_value_new (G_TYPE_STRING)), tmp); > > > > > > > > > g_free (tmp); > > > > > > > > > > > > > > > > > > Note that this is brand new (just been sorted out) and will require the master or LIBGDA_4.0 branches up to date. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ... > > > > > > ... > > > > > > ... > > > > > > > > > > > > > > > > > > > > > > > > > Which version of > > > > > > > PostgreSQL and > > > > > > > libgda are you > > > > > > > using? > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > I am on the latest > > > > > > LIBGDA_4.0 git > > > > > > branch. (so I am NOT > > > > > > on master, perhaps > > > > > > that may be the > > > > > > issue?) > > > > > > > > > > > > I am using > > > > > > postgresql 8.3.7 > > > > > > (default with Fedora > > > > > > 11) > > > > > > > > > > > > $ rpm -qa | grep > > > > > > postgresql > > > > > > postgresql-devel-8.3.7-1.fc11.x86_64 > > > > > > postgresql-python-8.3.7-1.fc11.x86_64 > > > > > > postgresql-libs-8.3.7-1.fc11.x86_64 > > > > > > postgresql-server-8.3.7-1.fc11.x86_64 > > > > > > postgresql-8.3.7-1.fc11.x86_64 > > > > > > > > > > > > What additional > > > > > > permissions (grants) > > > > > > do I need in > > > > > > Postgresql to be > > > > > > able to retrieve the > > > > > > data that is used > > > > > > the build the meta > > > > > > store? For testing > > > > > > now I have assigned > > > > > > an administrator > > > > > > role, but that is > > > > > > probably not a good > > > > > > idea in production? > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > No specific permission is > > > > > > required, except that the > > > > > > pg_catalog and > > > > > > information_schema schemas > > > > > > need to be readable (but I > > > > > > think it's the case all the > > > > > > time). Moreover, the meta > > > > > > data retreival works when > > > > > > updating all (calling > > > > > > gda_connection_update_meta_store() with a NULL context). > > > > > > > > > > > > I don't see why it does not > > > > > > work... > > > > > > > > > > > > What you can do is uncomment > > > > > > the line 2248 of > > > > > > gda-meta-store.c to define > > > > > > DEBUG_STORE_MODIFY, > > > > > > recompile and run your code > > > > > > again. You'll then have a > > > > > > lot of information about the > > > > > > update process of the > > > > > > GdaMetaStore, and maybe > > > > > > you'll see what's wrong. Be > > > > > > aware that when running > > > > > > gda_connection_update_meta_store(), the whole process takes place in a transaction so if something fails, then the meta store is reverted to what it was before the call. > > > > > > > > > > > > If you want you can send me > > > > > > the output it produces so I > > > > > > can have a look. > > > > > > > > > > > > > > > > > > > > > > > > > > Output as follows: > > > > > > > > > > CURRENT: > > > > > catalog_name > > > > > ------------ > > > > > stock > > > > > (1 row) > > > > > ------- BEGIN > > > > > FIND row 0(/0) returned row 0 > > > > > (unchanged) > > > > > Suggest update data into table > > > > > '_schemata': [catalog_name => > > > > > stock] > > > > > CURRENT: > > > > > catalog_name | schema_name | > > > > > schema_owner | schema_internal > > > > > -------------+-------------+--------------+---------------- > > > > > (0 rows) > > > > > NEW for table _schemata: > > > > > catalog_name | schema_name | > > > > > schema_owner | case > > > > > -------------+-------------+--------------+----- > > > > > (0 rows) > > > > > wrapped as: > > > > > catalog_name | schema_name | > > > > > schema_owner | case > > > > > -------------+-------------+--------------+----- > > > > > (0 rows) > > > > > ------- COMMIT > > > > > > > > > > > > > > > This does not look healthy as > > > > > all is empty. Should this list > > > > > something from the current > > > > > Meta Store? > > > > > > > > > > > > > > > > > > > > > OK, found the issue. It appears > > > > to be a permission problem in > > > > Postgresql. If I configure the > > > > data source (cnc) to connect as > > > > user "postgres" (administrator > > > > account), then the partial > > > > update works OK. If I do it as > > > > the "normal" user, it does not > > > > work. This is strange as the > > > > FULL update does work for that > > > > user. What additional permission > > > > is required for the 'normal' > > > > user? This may be a little bit > > > > out of the libgda scope, but > > > > half kind of overlapping as > > > > libgda should either fail for > > > > all cases if it can not reach > > > > the system tables or work in all > > > > cases. > > > > > > > > > > > > > > > > > > > Further to this. Tried to work it > > > out from Postgres, but the only > > > way the libgda partial meta store > > > updates work is if I set the > > > normal user as superuser: > > > > > > ALTER USER bas with SUPERUSER > > > > > > then all works OK, however it is > > > not a good idea to have superuser > > > rights to a normal user for the > > > obvious reasons. When I switch > > > back to normal user mode: > > > > > > ALTER USER bas with NOSUPERUSER > > > > > > then all fails again with the > > > empty debug table output above. I > > > need to work out which Postgresql > > > system tables libgda tries to > > > access when doing a partial > > > update. Any pointers of how to get > > > this done quickly/which area to > > > look? Any other postgresql users > > > on this list who has come across > > > this? > > > > > > > > > > > > > > > > > > Thanks for the quick analysis, I'll look > > > at this problem ASAP. > > > > > > In the meanwhile, you can run the very > > > same SELECT statements which are being run > > > when doing a partial meta store update in > > > a psql console and see where the problem > > > comes from. For this, all you have to do > > > is set the GDA_CONNECTION_EVENTS_SHOW > > > environment variable to COMMAND, which > > > will show you all the statements run along > > > with the variables' values when run (the > > > variable's values come first in the dump). > > > > > > I think the culprit is the > > > I_STMT_TABLE_NAMED SQL statement which > > > you'll find at the beginning of the > > > gda-postgres-meta.c file. I seems the > > > problem is with the permission denied to > > > access the pg_authid catalog table. > > > > > > > > > > > > > > > Maybe replacing pg_authid with pg_roles is OK. > > > > > > > > > > 1 step closer. Yes, the pg_authid was a problem as > > well, but I bypassed that for now by giving it > > select access for the end-user (but yes this should > > be addressed as well) > > > > > > The following SQL: > > > > select * from information_schema.schemata; > > > > This returns nothing when running as normal user in > > the psql shell, but as a superuser returns the > > following: > > > > # select * from information_schema.schemata; > > catalog_name | schema_name | schema_owner | > > default_character_set_catalog | > > default_character_set_schema | > > default_character_set_name | sql_path > > --------------+--------------------+--------------+-------------------------------+------------------------------+----------------------------+---------- > > stock | pg_toast | postgres | > > | | > > | > > stock | pg_temp_1 | postgres | > > | | > > | > > stock | pg_toast_temp_1 | postgres | > > | | > > | > > stock | public | postgres | > > | | > > | > > stock | information_schema | postgres | > > | | > > | > > stock | pg_catalog | postgres | > > | | > > | > > (6 rows) > > > > > > This is correct from a Postgresql point of view. > > They are not "supposed to provide the same results". > > Per SQL99, the schemata view is supposed to identify > > the schemata in a catalog that is owned by a given > > user. The SQL definition in the spec makes it clear > > that it only shows schemas owned by CURRENT_USER or > > a role that CURRENT_USER is a member of. Can this be > > resolved in libgda not to use the schemata table? > > > > > > Yes, instead we could use the pg_catalog.pg_namespace > > table, along with the pg_catalog.pg_roles as a replacement > > for pg_authid. In fact the information_schema.schemata is > > defined as: > > > > SELECT > > current_database()::information_schema.sql_identifier AS > > catalog_name, n.nspname::information_schema.sql_identifier > > AS schema_name, u.rolname::information_schema.sql_identifier > > AS schema_owner, NULL::character > > varying::information_schema.sql_identifier AS > > default_character_set_catalog, NULL::character > > varying::information_schema.sql_identifier AS > > default_character_set_schema, NULL::character > > varying::information_schema.sql_identifier AS > > default_character_set_name, NULL::character > > varying::information_schema.character_data AS sql_path > > FROM pg_namespace n, pg_authid u > > WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, > > 'USAGE'::text); > > > > > Just did a quick test with your SQL statement (SELECT > current_database..... etc) , but same problem. postgres user > returns data. Normal user returns no data. > > This is expected as the SQL above is the definition of > information_schema.schemata. This was just to illustrate the > filtering on the current role. Understood. pg_catalog.pg_namespace table and pg_catalog.pg_roles are accessible to the normal user (just tested to confirm), so it has my thumbs up. Bas. _______________________________________________ gnome-db-list mailing list [email protected] http://mail.gnome.org/mailman/listinfo/gnome-db-list