Re: [gnome-db] [Fwd: Re: Partial Meta Store update.]

[Bas Driessen <bas driessen xobas com>]

On Fri, 2009-07-24 at 13:55 +0200, Vivien Malerba wrote:
>     
>     
>     2009/7/24 Bas Driessen <bas driessen xobas com>
> >         
> >     
> >         On Fri, 2009-07-24 at 13:47 +0200, Vivien Malerba wrote:
> > >             
> > >             
> > >             2009/7/24 Bas Driessen <bas driessen xobas com>
> > > > > > > > > > > > > > > >                                                                 
> > > > > > > > > > > > > > > >                                                                 
> > > > > > > > > > > > > > > >                                                                 2009/7/19 Bas Driessen <bas driessen xobas com>
> > > > > > > > > > > > > > > > >                                                                     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.