Identify user requested queries

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.

Start reading at src/backend/tcop/postgres.c .

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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 <craig@2ndquadrant.com> wrote: