LLBLGen 3.5 - 3.5.12.123 Adapter - .NET 4.0 PostgreSQL 9.2.8

I've reviewed some existing threads similar and I know this is an issue with my database login role that I am using for my connection in the designer.

My account can run the following queries that I nabbed from another post:

SELECT t.table_name, obj_description(c.oid, 'pg_class') as table_comment 
FROM information_schema.tables t 
INNER JOIN pg_class c ON t.table_name = c.relname 
INNER JOIN pg_namespace s ON t.table_schema = s.nspname AND s.oid = c.relnamespace 
WHERE t.table_schema = 'public' AND t.table_type = 'BASE TABLE'

SELECT c.*, col_description(t.oid, c.ordinal_position) as column_comment 
FROM INFORMATION_SCHEMA.COLUMNS c 
INNER JOIN pg_class t ON c.table_name = t.relname 
INNER JOIN pg_namespace s ON c.table_schema = s.nspname AND s.oid = t.relnamespace 
WHERE table_schema='public' --AND table_name = 'TABLE NAME' 
ORDER BY ordinal_position asc

select schema_name
from information_schema.schemata

What other permissions does my role need to be able to refresh the catalog without being a superuser or owner?

So what exactly do you get when you run these commands? And what do you get when trying to refresh the schema?

It appears I was wrong. With the first two queries, I get results. The last query on schemata returns 0 rows for my login.

Postgres is installed on an ubuntu machine. I can cheat by running su as the postgres user, I can execute that query and see all the schemas.

I'm trying to GRANT the necessary privileges to my login role, but unless I GRANT postgres to my login role, I cannot retrieve data from the schemata table. And while I can technically grant postgres to my role, I'm not supposed to. Plus, it would be nice to know which privileges need to be granted to perform this operation with a non superuser role.

I'm looking into the postgres documentation for all the GRANT options. http://www.postgresql.org/docs/9.0/static/sql-grant.html

So far nothing is working. I'm assuming once I can get my user to be able to select from that table, I will be in business.

When I do the refresh, I just get an empty Schemas node in the catalog refresher.

Do a select to know what are the actual privileges of the user. Also, I think the problem is not that the query doesn't work with that query, but that the user is not granted to that schema (i.e. public).

So, try to grant access to that schema to your user:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO myUserOrRole;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO myUserOrRole;

I already had granted the select on all tables.

I did not try the functions until now. That didn't work either.

I tried a bunch of things, even the tablespaces, but still can't select from information_schema.schemata

schemata is a table ( I think it's a view, but pgadmin III doesn't tell me) in another catalog, namely information_schema. It's a special catalog. I don't know what ACLs are defined on this catalog in your system.

On other databases, information_schema elements are views which retrieve data from system tables. I think on postgres this is the same thing. It might very well be that the rights on those tables are not granted to your user but as I don't know what schemata actually does/reads I can't say which tables are affected.

schemata is the table/view that the schema information is stored. Since llblgen designer is not showing any schemas, and that is the table I cannot select from, I am assuming that is the problem here.

Other than telling me which system tables the designer queries to load the schemas into the catalog refresher, I guess I'm on my own.

usschad wrote:

schemata is the table/view that the schema information is stored. Since llblgen designer is not showing any schemas, and that is the table I cannot select from, I am assuming that is the problem here.

Correct.

I looked up some things about information_schema and postgres and ran into this: http://stackoverflow.com/questions/9447635/how-to-grant-select-on-information-schema-views-in-postgresql

So it's weird your user account can't read from these views...

Other than telling me which system tables the designer queries to load the schemas into the catalog refresher, I guess I'm on my own.

It reads from information_schema.tables/views and several pg* tables (see postgresql driver source for the exact queries). Select rights on pg* and information_schema tables/views is enough, but as said, it should be all users can read from these tables already so there might be something else not right in the system. Do you have a DBA? Did s/he limit rights somehow?

The strange thing is that it doesn't give me a permissions error. It just returns 0 results. But if I change the role to the postgres role, the schemas in the database are returned.

I'll download the source to get the queries and then present them to the dba to solve.

Thanks.

I'll close this thread for now. Please post back the DBA's findings.

I ran into this problem again and I remembered that I posted about it a while back and forgot to circle around and post the answer.

The user needs to either own the schema or be a member of a group that owns the schema to read from information_schema.schemata.

Even if the user has usage of the schema and owns tables in it, it is not enough.

Thanks for the feedback.