Thanks for the input. I guess i need to read more code and see if it is achievable. I started looking into the code very recently. Your inputs is very valuable to me. Thanks.
Yes I am trying to do something similar to multi-tenancy. I will look at the row level security.
On 23 November 2015 at 13:27, Praveen M <thrinz@gmail.com> wrote:
Hi All,
When the user attempts to make a connection with the database , the code will look into various pg_catalog tables internally. However the user also can query the pg_catalog tables. Is there a way to identify the user requested (or typed query) vs the system requested (internal) queries?
As far as I know there is no simple and reliable method.... but I'm no expert.
Most system accesses to common catalogs use the syscache, which doesn't go through the SQL parse/bind/execute process. Or they construct simple scans directly, again bypassing the full parser. The system will run internal queries with the SPI though, and that's full-fledged SQL. Triggers, rules, views, etc, use the SPI, as does plpgsql, fulltext search, XML support, and a few other parts of the system. So you cannot assume that anything using SQL is user-originated.
Take a look at PostgresMain in src/backend/tcop/postgres.c for the top-level user query entry point. You'll see there that you cannot rely on testing isTopLevel because multiple statements sent as a single query string are treated as if they were a nested transaction block. (see exec_simple_query(), postgres.c around line 962). That'd also cause problems with use of PL/PgSQL.
You can't assume that all SPI queries are safe, because the user can run queries via the SPI using plpgsql etc.
I don't see any way to do this without introducing the concept of a "system query"... and in PostgreSQL that's not simple, because the system query could cause the invocation of user-defined operators, functions, triggers, etc, that then run user-defined code. You'd have to clear the "system query" flag whenever you entered user-defined code, then restore it on exit. That seems exceedingly hard to get right reliably.
Reading between the lines, it sounds like you are looking for a way to limit end-user access to system catalogs as part of a lockdown effort, perhaps related to multi-tenancy. Correct? If so, you may wish to look at the current work on supporting row security on system catalogs, as that is probably closer to what you will need.
Also what procedure or function in the code that indicates the user can write queries , something like I wanted to know the code where the connection is created and available for user to use.
