Hi Craig, 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. Thanks Praveen On Mon, Nov 23, 2015 at 2:16 AM, Craig Ringer <cr...@2ndquadrant.com> wrote: > On 23 November 2015 at 13:27, Praveen M <thr...@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. >> > > Start reading at src/backend/tcop/postgres.c . > > -- > Craig Ringer http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services >
