Postgres Schemas

4 posts, 0 answers
  1. Jon
    Jon avatar
    2 posts
    Member since:
    Oct 2012

    Posted 11 Oct 2012 Link to this post

    Hi,

    I just installed OpenAcess ORM to create my first model, but in the Update From Database wizard many of my schemas are missing. 
    In the attached screenshot, the window on the left shows a partial schema list compared to the schemas displayed in the ORM wizard.
    http://grab.by/gHCc

    I can also send you the results of a query against pg_namespace or other info if that would help.

    Thanks
    Jon
  2.
  3. Ralph Waldenmaier
    Admin
    Ralph Waldenmaier avatar
    202 posts

    Posted 12 Oct 2012 Link to this post

    Hello Jon,

    We are obtaining the schemas that the connected user has access to. See the following sql that is used to get the schemas.

    /* owned schemas */
    select
    schemas.nspname
    from
    (
        select
        nspname,
        nspowner
        from pg_namespace
    ) schemas,
    (  
        SELECT usesysid FROM pg_user WHERE usename = current_user
    ) owner
    where
    schemas.nspowner = owner.usesysid  
    union
    /*granted schemas*/
    select
    s.nspname
    from
    (
    select
    nspname,
    cast(unnest(nspacl) as name) membership
    from pg_namespace
    ) s,
    (
        select
        *
        from
        (
            select
            groname,
            unnest(grolist) usesysid
            from
            pg_group
        ) groups
        join
        (
            SELECT usesysid FROM pg_user WHERE usename = current_user
        ) users on
        groups.usesysid = users.usesysid
    ) roles
    where
    strpos(s.membership, roles.groname) > 0
    union
    /* no nspacl defined => access to public */
    select
    nspname
    from pg_namespace
    where
    nspacl is null
    union
    /* public defined explicitly */
    select
    nspname
    from
    (
        select
        nspname,
        cast(unnest(nspacl) as name) membership
        from pg_namespace
    ) schemas
    where
    substring(schemas.membership, 1, 1) = '='

    Can you please check if you see the respective schemas when you execute this query against your database with the user that you use in Telerik OpenAccess ORM? Can you also please check if the user has the appropriate rights, to access these schemas.

    I hope this information is helpful for you.
    Feel free to ask if you have any other question. 

    All the best,
    Ralph
    the Telerik team
    Telerik OpenAccess ORM Meets ASP.NET Web API. Read more.
  5. Jon
    Jon avatar
    2 posts
    Member since:
    Oct 2012

    Posted 12 Oct 2012 Link to this post

    Hi,

    The SQL you provided looks good and is returning all 27 schemas in my database, including the schemas not displayed by the wizard. Are you sure there's no additional .NET code that might affect the number of schemas shown?
    Also, are there any limitations on the number of schemas displayed by the wizard?  Otherwise, I'm not sure what's going on.

    One note: you can and should ignore the 'pg_temp*' and pg_toast_temp*' schemas as these are temporary, system-created schemas that should never be included in a model. See this thread answer from Tom Lane: 
    http://archives.postgresql.org/pgsql-bugs/2012-04/msg00156.php

    Here's your SQL with the temp schemas excluded:
    http://pastebin.com/Z0aFk7UQ

    Thanks
    jon
  7. Ralph Waldenmaier
    Admin
    Ralph Waldenmaier avatar
    202 posts

    Posted 15 Oct 2012 Link to this post

    Hello Jon,

    Thank you for providing the hint regarding the pg_temp* schemas. We will improve our filter logic accordingly.
    As you guessed, we are indeed filtering the following schemas in our code  "pg_catalog", "pg_toast", "pg_temp_1", "pg_toast_temp_1" and "information_schema".
    The wizards are not filtering any schema information.

    Can you please confirm that you do get different results when you connect to your postgres database via an arbitrary sql editor and our wizards, using exactly the same user to connect to the database?

    All the best,
    Ralph
    the Telerik team
    Telerik OpenAccess ORM Meets ASP.NET Web API. Read more.
Back to Top