date:20090305

Re: [HACKERS] Use array in a dynamic statement

2009-03-05 Thread Pavel Stehule

Hello

you can't to use parameters inside literal. There hasn't any sense (to
8.3, 8.4 will support USING).

you have to use serialisation to string and quoting.

some like

CREATE  OR REPLACE FUNCTION foo(int[])
RETURNS SETOF int AS $$
DECLARE r record;
BEGIN
  FOR r IN EXECUTE
  'SELECT (' || quote_literal($1::text) ||
'::int[])[i] AS x
  FROM generate_series(1, array_upper(' ||
quote_literal($1::text) || '::int[],1)) g(i)'
  LOOP
RETURN NEXT r.x;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM foo(ARRAY[1,2,3]);


CREATE OR REPLACE FUNCTION foo84(int[])
RETURNS SETOF int AS $$
DECLARE r record;
BEGIN
  FOR r IN EXECUTE
  'SELECT $1[i] AS x FROM generate_series(1,
array_upper($1,1)) g(i)' USING $1
  LOOP
RETURN NEXT r.x;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM foo84(ARRAY[1,2,3]);

regards
Pavel Stehule

2009/3/5 Sophie Yang yangsop...@yahoo.com:

 Hi,

 I am trying to implement a PL/PgSQL function as following:
 CREATE OR REPLACE FUNCTION sort_by_d_idx (nids INT[][], tbl_name VARCHAR)
 RETURNS varchar[]
 AS $$
 DECLARE
  result varchar[];
 BEGIN

  EXECUTE 'SELECT ARRAY(SELECT t.idx FROM generate_series(array_lower($1,1), 
 array_upper($1,1)) AS s(i), '
        ||tbl_name||' t WHERE $1[s.i][1] = t.rid and $1[s.i][2] = t.rtid ORDER 
 BY length(t.idx))'
  INTO result;

  RETURN result;
 END;
 $$ LANGUAGE plpgsql;

 I got an error ERROR: there is no parameter $1 when I test the function 
 with:
 select sort_by_d_idx('{{8148, 1}, {8149, 1}, {300, 2}, {8163, 1}, {8170, 
 1}}'::int[][], 'd_tree_1');

 The error is understandable, but my question is how to supply the int[][] 
 array into the dynamic SQL?

 To help understand the dynamic statement, the structure of d_tree_1 is (rid, 
 rtid, idx). The PK is (rid, rtid) pair.

 If the tbl_name is fixed, the following function works well:
 CREATE OR REPLACE FUNCTION sort_by_d_idx2 (nids INT[][])
 RETURNS varchar[]
 LANGUAGE SQL
 AS $$
 SELECT ARRAY(
    SELECT t.idx
    FROM
        generate_series(array_lower($1,1), array_upper($1,1)) AS s(i), 
 d_tree_1 t
    WHERE $1[s.i][1] = t.rid and $1[s.i][2] = t.rtid
    ORDER BY length(t.idx)
 );
 $$;

 Unfortunately, the tbl_name is determined at query time.

 Please help.





 --
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1668)

2009-03-05 Thread KaiGai Kohei

Heikki Linnakangas wrote:

KaiGai Kohei wrote:
The other one is it has two kind of reader permissions (select and
use).

The select permission is applied when user tries to read tables/columns
and its contents are returned to the user.
The use permission is applied when user tries to read table/columns,
but its contents are consumed internally (not returned to user directly).

For example:
SELECT a, b FROM t WHERE b 10 and c = 'aaa';

In this case,
db_column:{select} permission is applied on t.a.
db_column:{select use} permission is applied on t.b.
db_column:{use} permission is applied on t.c.
db_table:{select use} permission is applied on t

However, I don't absolutely oppose to integrate them into a single
reader select permission, because it was originally a single
permission, then use is added.

If you have use permisson on c, you can easily use it to find out the
exact value. Just do queries like SELECT 'foo' FROM t WHERE b 10 AND
c = 'aaa' AND c BETWEEN 1 AND 1000 repeatedly with different ranges to
zoom into the exact value. So I think separating those two permissions
is a mistake,

Hmm.
At least, I can agree to integrate these two permissions into a single
select. It is originally upper compatible to use.

Please note that user's privileges are not limited to create/alter/drop
them. One sensitive permission is db_procedure:{install}.
It is checked when user defined functions are set up as a function
internally invoked.

For example, pg_conversion.conproc is internally invoked to translate
a text, but it does not check pg_proc_aclcheck() in runtime.
We consider all user defined functions should be checked either of:
- db_procedure:{execute} permission for the client in runtime
or
- db_procedure:{install} permission for the DBA on installation time

Needless to say, {install} is more sensitive permission because it
means anyones to invoke it implicitly. So, the default policy only
allows it on functions defined by DBA, but the execute permission
is allowed normal users to invoke functions defined by himself.

Hmm. We normally rely on the fact that a conversion function needs to be
a C-function, and because only superusers can create C-functions we have
assumed that they're safe to call. Which was actually not true until
recently, when we added checks into all the conversion functions to
check that the source and target encoding of the strings passed as
arguments match the ones specified in the CREATE CONVERSION command.

There has been talks of making CREATE CONVERSION superuser-only, so we
could easily just do that. Can you give some other examples of where the
install permission is used?

Because SE-PostgreSQL works orthogonally to the existing access controls,
so we need to consider two cases.
a) A superuser connected from unprivileged domain (like: user_t, httpd_t)
b) A superuser connected from privileged domain (like: sysadm_t, unconfined_t)

The superuser is a concept of PostgreSQL, and the domain is a concept
of SELinux. It seems to me you assumes the b) case.

The a) case should be focused on here.
In the a) case, SE-PostgreSQL does not prevent to create C-function
(as far as shared library has an appropriate label: lib_t), and newly
created functions are labeled as unpriv-user defined functions which
depends on the domain.

It allows unpriv-domains to invoke functions defined by himself,
but does not allow to install as a conversion and others, because
it can be implicitly used by other domains.

NOTE: unprivileged domain cannot create files labeled as lib_t on the
operating system, so all they can do is to load libraries already
set up.

Our assumption is a client connected from user_t or httpd_t is suspicious,
even if they logged in as a superuser, so SE-PostgreSQL applies additional
checks.

But if I've understood correctly, one goal is to restrict the actions of
superusers as well. Is there something to disallow superusers from
creating C-functions? If yes, isn't that enough protection from things
like the conversion functions?

Please note that SE-PostgreSQL focuses on the domain a client connected
from, not attributes of database user.
(Needless to say, vanilla PostgreSQL concurrently prevents C-functions
by normal database users.)

The conversion is an example of install permissions.
The list of functions to be checked are here.

http://code.google.com/p/sepgsql/source/browse/trunk/sepgsql/src/backend/security/sepgsql/hooks.c#446

Thanks,
--
OSS Platform Development Division, NEC
KaiGai Kohei kai...@ak.jp.nec.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

54 matches

Mail list logo