On Fri, 2009-07-24 at 14:14 +0200, Vivien Malerba wrote: > > > > 2009/7/24 Bas Driessen <[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. > > Ok, then I'll do this ASAP, unless you want to do it yourself (all the > statements are located in the beginning of the gda-postgres-meta.c > file)?
OK, I will give it a go over the weekend.
_______________________________________________ gnome-db-list mailing list [email protected] http://mail.gnome.org/mailman/listinfo/gnome-db-list
