Re: Sanitize schema name

On 05/09/2015 11:55 PM, Ludovic Gasc wrote:
> 2015-05-10 2:41 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>:
>
>     On 05/09/2015 01:03 PM, Ludovic Gasc wrote:
>
>         2015-05-08 0:12 GMT+02:00 Adrian Klaver
>         <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>         <mailto:adrian.klaver@aklaver.com
>         <mailto:adrian.klaver@aklaver.com>>>:
>
>
>              On 05/07/2015 01:06 PM, Ludovic Gasc wrote:
>
>                  Thanks all for your answers, you understand well my need.
>
>                  About PQescapeIdentifier:
>                  1. An idea of release date for the next version of
>         psycopg2 ?
>                  2. Are you sure it's enough to protect against SQL
>         injections,
>                  because
>                  you can read in the documentation: *Tip:* As with string
>                  literals, to
>                  prevent SQL injection attacks, SQL identifiers must be
>         escaped
>                  when they
>                  are received from an untrustworthy source.
>
>                  About format() it doesn't work for schema, example:
>                  SELECT format('SELECT * FROM %I WHERE id=1', 'lg.devices')
>                  => SELECT * FROM "lg.devices" WHERE id=1
>                  SELECT * FROM "lg.devices" WHERE id=1
>                  => ERROR:  relation "lg.devices" does not exist
>                  LIGNE 1 : SELECT * FROM "lg.devices" WHERE id=1
>                                            ^
>
>                  ********** Error **********
>
>                  ERROR: relation "lg.devices" does not exist
>
>
>              Try:
>
>              SELECT format('SELECT * FROM %I.%I WHERE id=1', 'lg',
>         'devices')
>
>
>         Ok, now, it works, but, I need to launch the query two times:
>         First time
>         with SELECT format(, a second time with the result of the first
>         query.
>         It should be possible to execute that only in one pass ?
>
>
>     As far as I know, only in plpgsql:
>
>     http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE
>
>     Hence the previous suggestion about creating a psycopg2 function
>     that you could use directly.
>
>
> Ok, at least to me, it's the ideal situation.
>
>
>
>
>
>
>              Still not sure why you cannot use search_path and avoid the
>         schema
>              qualification altogether?
>
>
>         Because I use a pool of pgsql sockets where no connexions are
>         dedicated
>         to one particular client.
>
>
>     So all the clients are connecting to a single database with many
>     schemas, each schema unique to a client?
>
>
> Exactly. With that, I can easily generate cross statistics between
> clients for billing, because I've a hierarchy of clients.
>
>
>
>         I could change that each time just before to execute each query,
>         but it
>         shouldn't be very efficient.
>
>
>
>     So is the login role for each client unique, where you could use
>     ALTER ROLE SET search_path to have it preset:
>
>     http://www.postgresql.org/docs/9.4/interactive/sql-alterrole.html
>
>
> Thanks for your help, but this suggestion doesn't fit with my need: If I
> do that, I need to have a dedicated connection for each client.
> The idea is to mutualize pgsql connections for all clients like an
> applicative "virtualization": for some big clients, it will use several
> connections from the aiopg's pool, for some others, it uses no connections.

You are braver then I, especially given this:

https://docs.python.org/3.4/library/asyncio.html
"

Note

The asyncio package has been included in the standard library on a
provisional basis. Backwards incompatible changes (up to and including
removal of the module) may occur if deemed necessary by the core
developers. "




--
Adrian Klaver
adrian.klaver@aklaver.com