From: | Craig Ringer <craig(at)2ndquadrant(dot)com> |
---|---|
To: | Praveen M <thrinz(at)gmail(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Identify user requested queries |
Date: | 2015-11-23 10:16:45 |
Message-ID: | CAMsr+YEKr84gSPhb=bs+vYQP-UkP7d_oA3CdLs84cu+ReKVCJw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 23 November 2015 at 13:27, Praveen M <thrinz(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Stark | 2015-11-23 12:14:33 | Re: onlyvalue aggregate (was: First Aggregate Funtion?) |
Previous Message | Dean Rasheed | 2015-11-23 09:29:39 | Re: onlyvalue aggregate (was: First Aggregate Funtion?) |