[GENERAL] postgresql jsonb processing with c api
Hi, I am developing a postgres extension. The extension gets json data as a string from external source and is supposed to be able to store this string in a Jsonb type. I am working with C API for postgres-9.4 installed from ubuntu trusty main repo. I would like to use a function that converts a cstring to Jsonb* structure defined in http://doxygen.postgresql.org/jsonb_8h.html There are functions doing exactly this already defined in http://doxygen.postgresql.org/jsonb_8c.html Namely, the function Datum jsonb_in ( PG_FUNCTION_ARGS ), however I am not sure if I can call this function from C API in a portable and safe manner. As it seems it is intended for being called by postgres from first glance. I could also use the function jsonb_from_cstring http://doxygen.postgresql.org/jsonb_8c.html#ab23eca28d5880f86a0943d71c90d6654 but it is declared and defined in jsonb.c and not declared in json.h, and hence linking with this function is not a very clean solution. I tried finding the symbols for jsonb_from_cstring in libpq.so, however there are none. I am guessing I need a non-standard build of postgres? So the question is, what is the best way to convert a cstring to a Jsonb* structure from within C API? Thanks, Igor
[GENERAL] EINTR causes panic (data dir on btrfs)
Every few weeks, I'm getting a error like this:
> 2015-02-11 15:31:00 CET PANIC: could not write to log file
> 00010007007D at offset 1335296, length 8192: Interrupted system
> call
> 2015-02-11 15:31:00 CET STATEMENT: COMMIT
> 2015-02-11 15:31:17 CET LOG: server process (PID 8390) was terminated by
> signal 6: Aborted
> 2015-02-11 15:31:17 CET DETAIL: Failed process was running: COMMIT
> 2015-02-11 15:31:17 CET LOG: terminating any other active server processes
> 2015-02-11 15:31:17 CET WARNING: terminating connection because of crash of
> another server proces
I'm running the Ubuntu 9.3.4-1 package on a 3.2.13 kernel.
Is there any solution for this? The code generating the error seems to
be this:
> if (write(openLogFile, from, nbytes) != nbytes)
> {
> /* if write didn't set errno, assume no disk space */
> if (errno == 0)
> errno = ENOSPC;
> ereport(PANIC,
> (errcode_for_file_access(),
> errmsg("could not write to log file %s "
> "at offset %u, length %lu: %m",
> XLogFileNameP(ThisTimeLineID, openLogSegNo),
> openLogOff, (unsigned long) nbytes)));
> }
which strikes me as a bit strange (but there may be data consistency
issues I'm not aware of). Why wouldn't postgres retry on EINTR or even
allow return values of write() lower than nbytes (and then continue in a
loop).
--
Gustavo Lopes
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hardware requirements for a PostGIS server
On 12 February 2015 at 00:38, Mathieu Basille wrote: > Platform > > > Linux is the platform of choice: > * Easier administration (install/configuration/upgrade), which is also true > for addons/dependencies (starting with PostGIS, but also GEOS, GDAL, PL/R); > * Better performance [4]; > * More tuning options (limited with MS systems); It has to be said that Linux isn't the only choice there. For example, FreeBSD (or NetBSD/OpenBSD) are popular choices for Postgres database servers as well and they perform great and have splendid documentation (an area where I often find Linux a little lacking). They might even be a bit more stable. There are also still several commercial UNIX flavours. I can't say how any of these alternatives (in combination with PostGIS) compare to Linux though, nor whether PostGIS is even available on all of these, but I suspect they're at least on par for performance and stability. Of all of these, Windows is probably the least suitable OS for the job. Which is the most suitable depends on quite a few things, not in the least how likely you'll be able to get experienced support for them. If you're new to the OS and you have to support the system yourself for any length of time, I think that good documentation is a factor to take into account. Am I biased? Definitely. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] checking if jsonb was detoasted and releasing memory
Let us say that the datum of type Datum contains a Jsonb* type. Then after the call Jsonb *jb = DatumGetJsonb(datum); the jb might point to a palloc'ed structure, in case detoasting took place. So the question is if this is the right way to free up the memory after checking that the jb was detoasted? if ((void *)jb != DatumGetPointer(datum)) pfree(jb); // free if detoasted
Re: [GENERAL] checking if jsonb was detoasted and releasing memory
Igor Stassiy writes: > Let us say that the datum of type Datum contains a Jsonb* type. > Then after the call > Jsonb *jb = DatumGetJsonb(datum); > the jb might point to a palloc'ed structure, in case detoasting took place. > So the question is if this is the right way to free up the memory after > checking that the jb was detoasted? > if ((void *)jb != DatumGetPointer(datum)) > pfree(jb); // free if detoasted For the most part the answer is "don't bother". Except in very narrow cases, it's not worth it to clean up such values retail as opposed to letting the next evaluation context reset get rid of them in bulk. The code you proposed will work, but it's ugly and usually a net waste of cycles. It also embeds assumptions we might want to change someday. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 9.5 RLS 'check policy' function arguments
I'm contemplating writing a function for use with the CHECK POLICY statement. Where can I find documentation describing the arguments that will be passed to the function? Ted -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] infinite recursion detected in rules for relation
I wanted to set a rule: CREATE RULE "_RETURN" ASON SELECT * from backend.testDO INSTEAD SELECT * FROM backend.test WHERE who='Me'; When I'm trying to do anything on the table I get following error: ERROR: infinite recursion detected in rules for relation "backend.test"** Błąd **ERROR: infinite recursion detected in rules for relation "backend.test"Stan SQL: 42P17 Is there any way to avoid that? Maybe there exist some other approaches that could be useful ? -- View this message in context: http://postgresql.nabble.com/infinite-recursion-detected-in-rules-for-relation-tp5837697.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] infinite recursion detected in rules for relation
On Thu, Feb 12, 2015 at 10:48 AM, pinker wrote: > I wanted to set a rule: > > CREATE RULE "_RETURN" AS > ON SELECT * from backend.test > DO INSTEAD > SELECT * FROM backend.test WHERE who='Me'; > > When I'm trying to do anything on the table I get following error: > > ERROR: infinite recursion detected in rules for relation "backend.test" > > ** Błąd ** > > ERROR: infinite recursion detected in rules for relation "backend.test" > Stan SQL: 42P17 > > Is there any way to avoid that? Maybe there exist some other approaches > that could be useful ? I'm not totally sure why you want to do the above. If I needed such a thing and "backend.test" already exists, I would rename "backend.test" to something like "backend.test__x" and then create a view like: CREATE VIEW backend.test AS SELECT * FROM bachend.test__x WHERE who='Me' WITH CHECK OPTION. The WITH CHECK OPTION will stop people from doing a INSERT or UPDATE which did not have "who" equal to 'Me'. I don't know if you would need this. I would then GRANT appropriate authority to the VIEW and remove it from "backend.test__x". -- He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
Re: [GENERAL] infinite recursion detected in rules for relation
pinker wrote > I wanted to set a rule: > > CREATE RULE "_RETURN" AS > ON SELECT * from backend.test > DO INSTEAD > SELECT * FROM backend.test WHERE who='Me'; > > When I'm trying to do anything on the table I get following error: > > ERROR: infinite recursion detected in rules for relation "backend.test" Not surprising... > Is there any way to avoid that? Maybe there exist some other approaches > that could be useful ? CREATE VIEW test_me AS SELECT * FROM backend.test WHERE who = 'Me' ; David J. -- View this message in context: http://postgresql.nabble.com/infinite-recursion-detected-in-rules-for-relation-tp5837697p5837700.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] segmentation fault postgres 9.3.5 core dump perlu related ?
Update/Information sharing on my pursuit of segmentation faults FreeBSD 10.0-RELEASE-p12 amd64 Postgres version 9.3.5 Below are three postgres core files generated from two different machine ( Georgia and Alabama ) on Feb 11. These cores would not be caused from an environment update issue that I last suspected might be causing the segfaults So I am kind of back to square one in terms of thinking what is occurring. ? I am not sure that I understand the associated time events in the postgres log file output. Is this whatever happens to be running on the other postgress forked process when the cored process was detected ? If this is the case then I have probably been reading to much from the content of the postgres log file at the time of core. This probably just represents collateral damage of routine transactions that were in other forked processes at the time one of the processes cored ? Therefore I would now just assert that postgres has a sporadic segmentation problem, no known way to reliably cause it and am uncertain as to how to proceed to resolve it. Georgia 8:38 Georgia 17:55 Alabama: 15:30 -- If someone sees something suggesting a direction to pursue from these core file back traces much appreciated. Thanks Dave Georgia - Core 17:55 - Feb 11 (gdb) bt #0 0x006f8670 in SearchCatCache () #1 0x00672537 in enum_in () #2 0x0071375b in InputFunctionCall () #3 0x00713b7e in OidInputFunctionCall () #4 0x00509a3d in coerce_type () #5 0x00511af3 in make_fn_arguments () #6 0x00513fed in make_op () #7 0x0050f53b in ?? () #8 0x0050d706 in transformExpr () #9 0x00518333 in transformTargetList () #10 0x004f02bc in transformStmt () #11 0x0064109d in pg_analyze_and_rewrite_params () #12 0x006fbc6b in ?? () #13 0x006fb6f5 in GetCachedPlan () #14 0x0059597a in SPI_plan_get_cached_plan () #15 0x0008024ed34d in ?? () from /usr/local/lib/postgresql/plpgsql.so #16 0x0008024f2590 in ?? () from /usr/local/lib/postgresql/plpgsql.so #17 0x0008024ee0d0 in ?? () from /usr/local/lib/postgresql/plpgsql.so #18 0x0008024eaf3b in ?? () from /usr/local/lib/postgresql/plpgsql.so #19 0x0008024ea243 in plpgsql_exec_function () from /usr/local/lib/postgresql/plpgsql.so #20 0x0008024e6551 in plpgsql_call_handler () from /usr/local/lib/postgresql/plpgsql.so #21 0x0057611f in ExecMakeTableFunctionResult () #22 0x0058b6c7 in ?? () #23 0x0057bab2 in ExecScan () #24 0x005756b8 in ExecProcNode () #25 0x00573630 in standard_ExecutorRun () #26 0x00645b0a in ?? () #27 0x00645719 in PortalRun () #28 0x006438ea in PostgresMain () #29 0x005ff267 in PostmasterMain () #30 0x005a31ba in main () (gdb) info threads Id Target Id Frame * 2Thread 802c06400 (LWP 100070) 0x006f8670 in SearchCatCache () * 1Thread 802c06400 (LWP 100070) 0x006f8670 in SearchCatCache () ? The gdb info threads response is still an annoying piece of information. Connecting gdb to a healthy running postmaster gives the same thread count as the core file. (2) However, other system system tools (top ps ) which indicate number of threads for the process only indicate one thread on the healty process. So I think this is a debugger bug. 2015-02-11T17:55:13.732147-05:00 georgia local0 info postgres[38321]: [7236-1] user=ace_db_client, db=ace_db, proc=38321, audit=dbm_client9, LOG: du ration: 4.384 ms statement: COMMIT 2015-02-11T17:55:13.743399-05:00 georgia local0 info postgres[86738]: [12-1] user=redcom, db=ace_db, proc=86738, audit=[unknown], LOG: duration: 14. 581 ms statement: SELECT database, COALESCE(max(extract(epoch FROM CURRENT_TIMESTAMP-prepared)),0) FROM pg_prepared_xacts JOIN pg_database ON datnam e=database WHERE datname='ace_db' GROUP BY database ORDER BY 1 2015-02-11T17:55:13.833624-05:00 georgia local0 info postgres[1018]: [11-1] user=, db=, proc=1018, audit=, LOG: server process (PID 38319) was termi nated by signal 11: Segmentation fault 2015-02-11T17:55:13.833669-05:00 georgia local0 info postgres[1018]: [11-2] user=, db=, proc=1018, audit=, DETAIL: Failed process was running: SELEC T * FROM cc.register_port_sip_user($1, $2, $3, $4, $5, $6, $7, $8, $9, $10 ) 2015-02-11T17:55:13.833701-05:00 georgia local0 info postgres[1018]: [12-1] user=, db=, proc=1018, audit=, LOG: terminating any other active server processes 2015-02-11T17:55:13.833896-05:00 georgia local0 notice postgres[38321]: [7237-1] user=ace_db_client, db=ace_db, proc=38321, audit=dbm_client9, WARNIN G: terminating connection because of crash of another server process 2015-02-11T17:55:13.833923-05:00 georgia local0 notice postgres[38321]: [7237-2] user=ace_db_client, db=ace_db, proc=38321, audit=dbm_client9, DETAIL : The postmaster has commanded this server process to roll back t
[GENERAL] How to hide stored procedure's bodies from specific user
Hi I want to hide my own stored procedures' bodies from the specific user. As far as I know, procedure's body is visible in the pg_catalog.pg_proc table. So, I did the following: REVOKE ALL ON pg_catalog.pg_proc FROM PUBLIC; And after it, when user tries: SELECT * from pg_proc; The following error occurs: ERROR: permission denied for relation pg_proc It turns out that user don't have access to the body of the procedure. But I still can get stored procedure's body using \sf function_name or with \ef function_name So, how can I completely hide my own stored procedure's bodies from this user? Thanks in advance Saimon
[GENERAL] EINTR causes panic (data dir on btrfs)
Every few weeks, I'm getting a error like this:
> 2015-02-11 15:31:00 CET PANIC: could not write to log file
> 00010007007D at offset 1335296, length 8192: Interrupted system
> call
> 2015-02-11 15:31:00 CET STATEMENT: COMMIT
> 2015-02-11 15:31:17 CET LOG: server process (PID 8390) was terminated by
> signal 6: Aborted
> 2015-02-11 15:31:17 CET DETAIL: Failed process was running: COMMIT
> 2015-02-11 15:31:17 CET LOG: terminating any other active server processes
> 2015-02-11 15:31:17 CET WARNING: terminating connection because of crash of
> another server proces
I'm running the Ubuntu 9.3.4-1 package on a 3.2.13 kernel.
Is there any solution for this? The code generating the error seems to
be this:
> if (write(openLogFile, from, nbytes) != nbytes)
> {
> /* if write didn't set errno, assume no disk space */
> if (errno == 0)
> errno = ENOSPC;
> ereport(PANIC,
> (errcode_for_file_access(),
> errmsg("could not write to log file %s "
> "at offset %u, length %lu: %m",
> XLogFileNameP(ThisTimeLineID, openLogSegNo),
> openLogOff, (unsigned long) nbytes)));
> }
which strikes me as a bit strange (but there may be data consistency
issues I'm not aware of). Why wouldn't postgres retry on EINTR or even
allow return values of write() lower than nbytes (and then continue in a
loop).
--
Gustavo Lopes
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Issue dumping schema using readonly user
Here’s the situation:
% psql --version
psql (PostgreSQL) 9.3.5
% postgres --version
postgres (PostgreSQL) 9.3.5
% psql mydatabase
create table mytable_is_readonly (id uuid primary key, text text not null);
create table mytable_is_not_readonly (id uuid primary key, text
text not null);
create user readonly with password 'readonly';
grant select on mytable_is_readonly to readonly;
% psql mydatabase readonly
\d mytable_is_readonly
Table "public.mytable_is_readonly"
Column │ Type │Modifiers
┼─┼──
id │ integer │ not null default
nextval('mytable_is_readonly_id_seq'::regclass)
text │ text│ not null
Indexes:
"mytable_is_readonly_pkey" PRIMARY KEY, btree (id)
\d mytable_is_not_readonly
Table "public.mytable_is_not_readonly"
Column │ Type │ Modifiers
┼─┼──
id │ integer │ not null default
nextval('mytable_is_not_readonly_id_seq'::regclass)
text │ text│ not null
Indexes:
"mytable_is_not_readonly_pkey" PRIMARY KEY, btree (id)
% pg_dump -U readonly mydatabase --schema-only --table=mytable_is_readonly
... this outputs and works
% pg_dump -U readonly mydatabase --schema-only
--table=mytable_is_not_readonly
pg_dump: [archiver (db)] query failed: ERROR: permission denied
for relation mytable_is_not_readonly
pg_dump: [archiver (db)] query was: LOCK TABLE
public.mytable_is_not_readonly IN ACCESS SHARE MODE
Is this a bug? Or defined behavior that is expected? My use case is that I
have some tables that I don’t want to allow the readonly account to access
data in but want to allow it to see the schema of that table. My guess was
that since it could see the schema interactively in psql, that it should be
allowed to pg_dump the table with schema only no problem.
Thanks for the help!
- Dan
Re: [GENERAL] How to hide stored procedure's bodies from specific user
For clarification - I run the commands using psql program. 2015-02-11 12:54 GMT+03:00 Saimon Lim : > Hi > I want to hide my own stored procedures' bodies from the specific user. > As far as I know, procedure's body is visible in the pg_catalog.pg_proc > table. > > So, I did the following: > REVOKE ALL ON pg_catalog.pg_proc FROM PUBLIC; > > And after it, when user tries: > SELECT * from pg_proc; > > The following error occurs: > ERROR: permission denied for relation pg_proc > > It turns out that user don't have access to the body of the procedure. > But I still can get stored procedure's body using > \sf function_name > or with > \ef function_name > > So, how can I completely hide my own stored procedure's bodies from this > user? > > Thanks in advance > Saimon >
Re: [GENERAL] Issue dumping schema using readonly user
On 02/11/2015 01:47 PM, Daniel LaMotte wrote:
Here’s the situation:
| % psql --version
psql (PostgreSQL) 9.3.5
% postgres --version
postgres (PostgreSQL) 9.3.5
% psql mydatabase
create table mytable_is_readonly (id uuid primary key, text text not null);
create table mytable_is_not_readonly (id uuid primary key, text text not
null);
create user readonly with password 'readonly';
grant select on mytable_is_readonly to readonly;
% psql mydatabase readonly
\d mytable_is_readonly
Table "public.mytable_is_readonly"
Column │ Type │Modifiers
┼─┼──
id │ integer │ not null default
nextval('mytable_is_readonly_id_seq'::regclass)
text │ text│ not null
Indexes:
"mytable_is_readonly_pkey" PRIMARY KEY, btree (id)
\d mytable_is_not_readonly
Table "public.mytable_is_not_readonly"
Column │ Type │ Modifiers
┼─┼──
id │ integer │ not null default
nextval('mytable_is_not_readonly_id_seq'::regclass)
text │ text│ not null
Indexes:
"mytable_is_not_readonly_pkey" PRIMARY KEY, btree (id)
% pg_dump -U readonly mydatabase --schema-only --table=mytable_is_readonly
... this outputs and works
% pg_dump -U readonly mydatabase --schema-only
--table=mytable_is_not_readonly
pg_dump: [archiver (db)] query failed: ERROR: permission denied for
relation mytable_is_not_readonly
pg_dump: [archiver (db)] query was: LOCK TABLE
public.mytable_is_not_readonly IN ACCESS SHARE MODE
|
Is this a bug? Or defined behavior that is expected? My use case is that
I have some tables that I don’t want to allow the readonly account to
access data in but want to allow it to see the schema of that table.
To me at least SELECT is accessing the data, so I am not sure that the
above meets your criteria in any case. I would do \dt+
mytable_is_not_readonly to see who has permissions on the table.
My
guess was that since it could see the schema interactively in psql, that
it should be allowed to pg_dump the table with schema only no problem.
Thanks for the help!
- Dan
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to hide stored procedure's bodies from specific user
On Thu, Feb 12, 2015 at 2:53 AM, Saimon Lim wrote: > For clarification - I run the commands using psql program. > > 2015-02-11 12:54 GMT+03:00 Saimon Lim : >> >> Hi >> I want to hide my own stored procedures' bodies from the specific user. >> As far as I know, procedure's body is visible in the pg_catalog.pg_proc >> table. >> >> So, I did the following: >> REVOKE ALL ON pg_catalog.pg_proc FROM PUBLIC; >> >> And after it, when user tries: >> SELECT * from pg_proc; >> >> The following error occurs: >> ERROR: permission denied for relation pg_proc >> >> It turns out that user don't have access to the body of the procedure. >> But I still can get stored procedure's body using >> \sf function_name >> or with >> \ef function_name >> >> So, how can I completely hide my own stored procedure's bodies from this >> user? oddly, psql does not echo (via -E switch) \sf and \ef which is small bug IMO. however, it's pretty easy to see what's going on via tailing the server log. What's happening here is psql is calling the function pg_catalog.pg_get_functiondef...you can revoke execute on that in a similar way. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to hide stored procedure's bodies from specific user
On 2/11/2015 1:54 AM, Saimon Lim wrote: I want to hide my own stored procedures' bodies from the specific user. As far as I know, procedure's body is visible in the pg_catalog.pg_proc table. only good way I know of to do that is to write the procedures in C so they are binary .so/.dll files. -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to hide stored procedure's bodies from specific user
Hi It is currently impossible on unpatched postgres. I am selling a patch to postgres that does a obfuscation of procedure body Regards Pavel Stehule 2015-02-11 10:54 GMT+01:00 Saimon Lim : > Hi > I want to hide my own stored procedures' bodies from the specific user. > As far as I know, procedure's body is visible in the pg_catalog.pg_proc > table. > > So, I did the following: > REVOKE ALL ON pg_catalog.pg_proc FROM PUBLIC; > > And after it, when user tries: > SELECT * from pg_proc; > > The following error occurs: > ERROR: permission denied for relation pg_proc > > It turns out that user don't have access to the body of the procedure. > But I still can get stored procedure's body using > \sf function_name > or with > \ef function_name > > So, how can I completely hide my own stored procedure's bodies from this > user? > > Thanks in advance > Saimon >
[GENERAL] What's a reasonable maximum number for table partitions?
The documentation says having too many partitions will end up being unproductive as it will cause the optimizer to examine all the tables for query planning. So I am wondering what's a reasonable upper limit? If I was to partition a table by day I would have 365 tables per year. Is that too many? What if I used a different criteria that would cause a thousand tables? Does anybody have experience with huge number of partitions if so where did you start running into trouble? Thanks.
Re: [GENERAL] How to hide stored procedure's bodies from specific user
Hello, Saimon,
I propose the following (ugly) solution.
--
/*as some privileged user: */
begin;
create table hidden_function_foo as select $code$
create function pg_temp.foo(p_input text) returns text as $$
select /*nodoby knows we are using
md5*/md5('the_salt_nobody_can_see' || p_input ||
'one_more_salt_nobody_can_see');
$$ language sql;
$code$::text code;
revoke all on table hidden_function_foo from ro;
create function foo(p_input text) returns text as $$
declare
l_res text;
begin
drop function if exists pg_temp.foo(text);
execute (select code from hidden_function_foo);
l_res := (select pg_temp.foo(p_input));
drop function pg_temp.foo(text);
return l_res;
end;
$$ language plpgsql security definer set search_path to pg_catalog,
public, pg_temp;
grant execute on function foo(text) to ro;
end;
--
/*as unprivileged ro user*/
select foo('bar');
select * from hidden_function_foo; -- fails
--
Maybe the solution is still unsafe, it is sufficient to make the inner
function produce some error to get a part of its body as a stacktrace.
BTW Do you believe hiding procedure bodies greatly improves security?
isn't it easier to hide salts, keys etc only?
Regards, Alexey
On 11.02.2015 12:54, Saimon Lim wrote:
Hi
I want to hide my own stored procedures' bodies from the specific user.
As far as I know, procedure's body is visible in the
pg_catalog.pg_proc table.
So, I did the following:
REVOKE ALL ON pg_catalog.pg_proc FROM PUBLIC;
And after it, when user tries:
SELECT * from pg_proc;
The following error occurs:
ERROR: permission denied for relation pg_proc
It turns out that user don't have access to the body of the procedure.
But I still can get stored procedure's body using
\sf function_name
or with
\ef function_name
So, how can I completely hide my own stored procedure's bodies from
this user?
Thanks in advance
Saimon
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
