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

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);

Vivien

_______________________________________________
gnome-db-list mailing list
[email protected]
http://mail.gnome.org/mailman/listinfo/gnome-db-list