[GENERAL] can select contents of view but not view itself, despite indirect membership
Hi everyone,
I'm having a strange issue on PostgreSQL 9.0.1 on Windows Server 2003
SP2.
I connect as a superuser and then SET SESSION AUTHORIZATION to user
"X" who is a member of group role "extranet_user" which inherits
membership from group role "user". "X", "extranet_user", and even
"user" are all INHERIT.
I have the following view:
CREATE OR REPLACE VIEW page_startup AS
SELECT contact.name, contact.nickname, COALESCE(
CASE
WHEN has_table_privilege('mandate'::text, 'select'::text)
THEN ( SELECT false AS bool
FROM mandate
NATURAL JOIN task
WHERE task.waiting_for = "session_user"()::text::integer AND
task.deadline < now()
LIMIT 1)
ELSE NULL::boolean
END, true) AS no_mandates
FROM contact
WHERE contact.id = "session_user"()::text::integer;
GRANT SELECT ON TABLE page_startup TO "user";
If I run this:
set session authorization "X";
select pg_has_role('user','member')
I get 't' as a result. Also, if I run this (just copying the
definition of the view):
set session authorization "X";
SELECT contact.name, contact.nickname, COALESCE(
CASE
WHEN has_table_privilege('mandate'::text, 'select'::text)
THEN ( SELECT false AS bool
FROM mandate
NATURAL JOIN task
WHERE task.waiting_for = "session_user"()::text::integer AND
task.deadline < now()
LIMIT 1)
ELSE NULL::boolean
END, true) AS no_mandates
FROM contact
WHERE contact.id = "session_user"()::text::integer;
I get the single row of data I'm looking for.
However, if I try to use the view instead of copying its definition:
set session authorization "X";
select * from page_startup
I get the following:
ERROR: permission denied for relation page_startup
** Error **
ERROR: permission denied for relation page_startup
SQL state: 42501
Strange, no? Anybody have any ideas why this might be?
Thanks,
Kev
--
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] can select contents of view but not view itself, despite indirect membership
On Nov 1, 4:39 pm, t...@sss.pgh.pa.us (Tom Lane) wrote: > Kevin Field writes: > > Strange, no? Anybody have any ideas why this might be? > > Worksforme: > > regression=# create group "user"; > CREATE ROLE > regression=# create group extranet_user in group "user"; > CREATE ROLE > regression=# create user x in group extranet_user; > CREATE ROLE > regression=# create view page_startup as select ... > CREATE VIEW > regression=# GRANT SELECT ON TABLE page_startup TO "user"; > GRANT > regression=# set session authorization x; > SET > regression=> select * from page_startup; > [ works ] > > I'm a bit suspicious of naming a group "user". I wonder whether you > outsmarted yourself somewhere along the line by failing to double-quote > that name, so that the command ended up doing something else than you > thought. > > regards, tom lane Good point about the naming (I was a bit wary of it myself but hadn't thought of the right thing yet); however, as you can see, the view grant is quoted, and also pgAdminIII shows this: GRANT "user" TO extranet_user; My guess is that it has something to do with the join to the table 'mandate'. If your view definition includes a CASE WHEN... that would potentially (but never actually, since it tests for permission first) select from a table that you don't have permission to select from...does it still work for you? (I'll try to build a generic example tomorrow to limit it to this specific test.) Thanks, Kev -- 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] can select contents of view but not view itself, despite indirect membership
> My guess is that it has something to do with the join to the table > 'mandate'. If your view definition includes a CASE WHEN... that would My new guess is something to do with permissions being cached somewhere, because this morning when I tried it (SET followed by SELECT from page_startup) from pgAdminIII, it worked. I had shut down pgAdminIII before leaving last night. Gah! Oh well, at least it's working now. I guess I should remember to always try restarting everything... Kev -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plperl booleans
When I use spi_prepare and spi_exec_prepared on a query that selects a boolean value, it comes back as the text 't' or 'f', which in perl both evaluate to true, so I have to either use perl to solve that or cast to integer in the query, both of which options seem less than elegant. Is this a bug or is it by design? -- 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] trigger functions with arguments
On Jun 8, 5:18 am, laurenz.a...@wien.gv.at ("Albe Laurenz") wrote:
> Kev wrote:
> > I came across this curious behaviour today, at least in the 2009-03-24
> > beta (I can't run a newer beta), that I couldn't find in the docs,
> > although maybe I missed it.
>
> [...]
>
> > So while that is the way to create the trigger itself, the
> > trigger procedure must be created like this:
>
> > CREATE OR REPLACE FUNCTION test() RETURNS trigger AS $BODY$
> > my ($a, $b) = @{$_TD->{args}}; # $a and/or $b may be null, depending on
> > how CREATE TRIGGER was used
> > ...
>
> Maybe it is hidden too well, but the documentation states
> inhttp://www.postgresql.org/docs/current/static/plpgsql-trigger.html
>
>A trigger procedure is created with the CREATE FUNCTION command, declaring
> it
>as a function with no arguments and a return type of trigger. Note that
> the function
>must be declared with no arguments even if it expects to receive arguments
>specified in CREATE TRIGGER - trigger arguments are passed via TG_ARGV,
>as described below.
>
> Yours,
> Laurenz Albe
Thanks, Laurenz, I had missed that. However, I don't think it's in
the best spot: since I was writing a plperl trigger function, it makes
sense for me to read the plperl trigger function docs, and the general
trigger and trigger function docs, but not necessarily the plpgsql
trigger function docs. IMO it should either be in the trigger
function docs for each language, or it should be in the general
trigger function docs, like maybe
http://www.postgresql.org/docs/8.3/static/trigger-definition.html
since it talks about return values and such there. That is, if it
actually applies to every language...
Kev
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] client_min_messages not suppressing messages in psql nor pgAdminIII
This is a cross-post from
http://stackoverflow.com/questions/21238209/info-output-despite-set-client-min-messages-to-warning-just-before
since I'm not getting any answers there yet.
With postgresql-9.0.15 on CentOS 6.5 (accessed via pgAdminIII 1.18.1 on
Win2003), I have a plperlu function that outputs an INFO message. I
want to suppress it during testing (using psql, which also behaves as
below), but I can't even seem to do it from a pgAdminIII query window:
SET client_min_messages TO WARNING;
select my_info_outputting_function('lalala')
I run that and look in the "messages" tab, and there's my INFO message.
(This may appear similar to
http://stackoverflow.com/questions/11404206/how-to-suppress-info-messages-when-running-psql-scripts
, but I don't want to disable INFO messages for my whole session, just
part of it and then set the minimum back to NOTICE.)
What am I doing wrong with the above code snippet? Does
client_min_messages not apply to pl/perlu functions?
UPDATE: upon further investigation, it seems to happen even with plpgsql
functions, not just plperlu functions:
create or replace function my_info_outputting_function() returns
void as $$
begin
raise INFO 'this should not appear...';
return;
end;
$$ language plpgsql;
SET client_min_messages TO WARNING;
select my_info_outputting_function();
I run the above snippet in a pgAdminIII query window and "this should
not appear" appears in the messages tab. Quoi?
Update 2: I also tried [log_min_messages][1] just in case. Same behaviour.
Any ideas?
Thanks,
Kev
[1]:
http://bytes.com/topic/postgresql/answers/423022-supressing-notice-messages-windows-cygwin-only-not-working
--
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] client_min_messages not suppressing messages in psql nor pgAdminIII
Why not use NOTICE? INFO is supposed to be used for things the user *requested* to see (for example, by supplying the "verbose" option to one of the commands which take that option). The documentation could be clearer on this, but it seems to suggest that there is no way to turn off INFO to the client. Yeah. Per elog.h: #define INFO17 /* Messages specifically requested by user (eg * VACUUM VERBOSE output); always sent to * client regardless of client_min_messages, * but by default not sent to server log. */ You should not be using level INFO unless you are responding to an explicit client request to get the output. If memory serves, we'd not even have invented that level except that VACUUM VERBOSE existed before we invented the elog levels, and we wanted to preserve its always-print-the-results behavior. Thank you very much Jeff and Tom for the clarification! This was bugging me, and I'm glad to know the right thing to do now. :) Kev -- 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] boolean short-circuiting in plpgsql
> Kev <[EMAIL PROTECTED]> writes:
> > ...because the case should force it to only evaluate 'old' when
> > TG_OP
> > = 'UPDATE' and otherwise ('INSERT') skip through to 't'. But this
> > causes the same error on insert. I suspect it's because the select
> > query gets parameterized and at that point the 'old' is missing,
> > before the case even gets to be parsed.
>
> Got it in one.
Thanks. Shouldn't there be some way around this then?
> > How do I get around this
> > without having two 'perform' statements?
>
> What you need is two nested IF statements. The PERFORM in your
> example
> is not relevant to the problem.
>
> regards, tom lane
Well, sure, in one sense, but I am actually trying to make it look
neater. Unless I'm missing something (quite possible...) the two
nested IF statements end up having two PERFORM statements:
if TG_OP = 'INSERT' then
perform recalc_sortnumpath(new.id);
else
if (new.sortnum != old.sortnum or new.parent != old.parent) then
perform recalc_sortnumpath(new.id);
end if;
end if;
...is there some way to boil this down using nested IF statements that
only has one PERFORM? (I mean, besides inverting it and having three
return statements and one perform.)
Thanks,
Kev
--
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] indirect membership in group roles
On Apr 2, 6:48 pm, t...@sss.pgh.pa.us (Tom Lane) wrote: > Kev writes: > > For some reason, which I couldn't see spelled out very well in the > > docs for GRANT ROLE and SET ROLE,indirectmembership in the group > > "user" doesn't give one its privileges unless you SET ROLE "user" > > first, even if all roles involved have INHERIT set. > > Really? Works for me: > > regression=# create group student inherit; > CREATE ROLE > regression=# create group employee inherit; > CREATE ROLE > regression=# create group "user"; > CREATE ROLE > regression=# grant "user" to student; > GRANT ROLE > regression=# grant "user" to employee; > GRANT ROLE > regression=# create user joe inherit; > CREATE ROLE > regression=# grant student to joe; > GRANT ROLE > regression=# create table mytable (f1 int); > CREATE TABLE > regression=# grant select on mytable to "user"; > GRANT > regression=# \c - joe > psql (8.4devel) > You are now connected to database "regression" as user "joe". > regression=> select * from mytable; > f1 > > (0 rows) > > I suspect you forgot to attach the "inherit" property to the > intermediate-level group. > > regards, tom lane That's interesting... This is what I'm showing in pgAdmin3: CREATE ROLE employee NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; ALTER ROLE employee SET search_path=public; GRANT "user" TO employee; CREATE ROLE "user" NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; ...you know, I wonder if it's only within the context of how I'm connecting, which is to connect as a superuser and then SET SESSION AUTHORIZATION to the selected user. Sorry, I should've mentioned that. Although, now it seems to be working. That makes my head hurt, because I have logs full of this: "DBD::Pg::db selectrow_array failed: ERROR: permission denied for relation my_table" ...and I remember going through and testing and reading up on it until I figured out the SET ROLE thing. Gosh. Well, sorry to waste your time, I have no idea how all this was possible. I guess I'll log my testing a lot more verbosely next time. Thanks for humouring me. Kev -- 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] OID Perfomance - Object-Relational databases
Hi everyone, I just came across a thread in the pgsql archives from October 2000, and found this post particularly interesting: http://archives.postgresql.org/pgsql-sql/2000-10/msg00044.php ...because I had already been designing something similar but on a larger scale, for an integrated information system with scheduling, accounting, internal messaging, inventory, safety management, and slew of other things. Initially when I read through the pgsql docs, I decided that I didn't ever want to deal with wraparound, and so I wouldn't use OIDs. But as I've been designing and implementing prototypes of this system, I've found it less than elegant to have universal references, because I always need to have a table name involved. Recently I clued in that OIDs could solve this, so I went looking through the archives and found that thread. I'm just wondering a few things. First, how would Michael/Tom's suggestion (to have a single sequence used by multiple tables) work exactly, i.e., if I had a number from that sequence, how would I know which table it belonged to without checking *all* of the tables for a row with that ID number? Josh said he would try the idea and report back on performance...how did this all turn out? If there's an answer to that question, then is it conceivable to use an int8 sequence across, say, 100 tables (which there may well be in the pool of things I would want to be able to arbitrarily reference by the time I'm done this project) as a substitute for OIDs until there are 64-bit OIDs? Either way, are 64-bit OIDs planned within the next couple years? I could only find discussion on this from 2000, and the announcement that OIDs were optional after a certain version. Thanks, Kev ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Carriage-Return in text field
I've run into this issue a bit... Do you mean, it's in the DB okay, but when it comes back to the client it breaks your transport mechanism (say, if you use JSON)? So far I've just been using a replace...if you find another way I'd love to hear it. (Because I also have to escape quotes and backslashes, being careful about order...) Kev > > > How do I insert text into a TEXT field that has a carriage-return in > it? > > I can obviously replace all CR with \n but isn't there an easier way to > do it? > > Thanks. > ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Transaction callback
http://archives.postgresql.org/pgsql-hackers/2004-01/msg00861.php (Just to dredge up another old thread...) While its true that a post-commit operation cannot modify a database, it might still for instance propagate the outcome of the transaction to another resource outside of the database, it might invalidate a transaction local in-memory cache, or it might clear resources occupied in memory for a rule system, etc. This is exactly what I'd like to do. Is there a way to do it nowadays? What I'm going for is an AJAX app that doesn't need to poll the server, but rather can have the server callback to it when a transaction messes with the data a user at which a user is looking. Any advice would be much appreciated. Thanks, Kev ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] getting status transaction error
This part... > repeated roughly once a minute. I've never seen this before. this is ...might mean some connection with autovacuum, maybe? I have no clue beyond that. Hope things go well for you. Kev ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] cutting out the middleperl
> On 22 Mar 2007 14:58:15 -0700, Kev <[EMAIL PROTECTED]> wrote: > > Hi everyone, > > > > I'm still in the design phase of a project. I was just wondering if > > anyone has any thoughts or experience on the idea of cutting the P out > > of the LAMP (or in my case, WAMP for now) stack. What I mean is > > having > > everything encapsulated into sql (or plpgsql or plperl where needed) > > functions stored in the pgsql server, and have Apache communicate with > > pgsql via a tiny C program that pretty much just checks whether the > > incoming function is on the allowed list and has the proper data > > types, > > then passes it straight in. Any errors are logged as potential > > security > > breaches. > > > > I'm really new to mod_perl too, so another question would be if this > > would be much faster than a simple perl script that did the same > > thing. > > > > I ask this because I realize I need to carefully check data coming > > into > > pgsql functions as well as at the client end. Why maintain a bunch of > > scripts with names similar to the functions they're calling and all > > performing similar checks anyway? > > > > I was kinda salivating at the thought of how fast things would be if > > you > > cut out the A as well, by using a Flash applet to give socket access > > to > > JavaScript. But then I guess you have to make your pgsql server > > itself > > publicly accessible on some port. Is that just asking for trouble? > > > > I appreciate any comments or thoughts anyone might have on this. > > IMO, I think 'thin middleware' approach is a great way to design > applications...so you are right on the money. The web server. IMO, > should be mostly concerned about rendering html. I don't think > eliminating the middleware is really practical. While you could use a > thick-client javascript framework like GWT and write your queries in > javascript (getting data back via json), I don't think it's really > possible to secure this properly without killing the 'ease of > implementation' factor. > > Then again, it's no worse then your typical old school visual basic or > delphi in-house application so common in the 90's. I really miss the > simplicity of Delphi. > > merlin Hi Merlin, Thanks for your reply, these are helpful comments. Just wondering about the security factor, though--is there something specific that would be impossible to lock down? I would think (but I'm no expert to be sure!) that a whitelist-only filter, I mean, if there weren't any buffer overflow vulnerabilities or anything like that, would be tough to sneak malicious SQL functions or other code through. Or did you mean in some other way? I don't think I would pass straight SQL queries anyway, if it makes a difference, but rather just the function name and the parameters separately, so that there's always an SQL function involved, but so that that's not obvious from the javascript end. All that's obvious is the thin-perlness of it: my perl script could be calling another script by the name we pass it, or a perl subroutine, or something else. Heh...Delphi was fun, except when I wanted to do some fancier things with it, even in high school...although our low-budget high school didn't exactly have the latest major version. Thanks, Kev ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] cutting out the middleperl
Hi Kenneth, This is wonderful news. I will definitely be checking into it as soon as I have a moment. Thanks! Kev > Kev, we have a GPL'd product targeting Postgres that has significant > overlap with what you want, though in other areas we went in another > direction. The site is www.andromeda-project.org, and I've put some > comments below: > > Kev wrote: > > Hi everyone, > > > > I'm still in the design phase of a project. I was just wondering if > > anyone has any thoughts or experience on the idea of cutting the P out > > of the LAMP (or in my case, WAMP for now) stack. What I mean is > > having > > everything encapsulated into sql (or plpgsql or plperl where needed) > > functions stored in the pgsql server, and have Apache communicate with > > pgsql via a tiny C program that pretty much just checks whether the > > incoming function is on the allowed list and has the proper data > > types, > > then passes it straight in. Any errors are logged as potential > > security > > breaches. > > > > Andromeda's goal is to implement all biz rules: constraints, automations > and security, in the server. > > This in effect makes the web server a proxy to the database, which > sounds like what you are after. The "P" portion for us is PHP, not > Perl, and it is small though non-zero. It has only two jobs really. In > the one direction it converts HTTP requests into SQL, and in the other > it converts SQL results into HTML. > > In terms of experience, I sat down to write the first code 33 months > ago, and it began to pay my bills about six months later. All of the > commercial bragging stuff is on the company website: http://www.secdat.com. > > > I'm really new to mod_perl too, so another question would be if this > > would be much faster than a simple perl script that did the same > > thing. > > > > Can't say there. My personal preference is for PHP because I can't > understand Perl five minutes after I've written it. > > > I ask this because I realize I need to carefully check data coming > > into > > pgsql functions as well as at the client end. Why maintain a bunch of > > scripts with names similar to the functions they're calling and all > > performing similar checks anyway? > > > > Well actually we tackled that problem by decided to *preserve* direct > table access through SQL as the standard API, which I realize is not the > standard, but for the life of me I can't understand why, since it is > such an amazingly simpler way to get what everyone says they are after. > > Here's what I mean. We write out a database spec in a plaintext file > that includes security, constraints, and automations. A "builder" > program then generates the DDL, encodes the biz logic in triggers, and > assigns table sel/ins/upd/del permissions to the tables. > > No messy API to remember or manage. Just specify the tables and > columns, who can do what, and what the formulas are, and its all automatic. > > A huge benefit to this is the basic ability to manipulate user's > databases through direct SQL. > > It's also IMHO the only way to ensure that you can accomplish the task > of having the web server be a proxy. Its easy to convert HTTP into > simple SQL insert/update etc., much harder to make it try to learn an API. > > > I was kinda salivating at the thought of how fast things would be if > > you > > cut out the A as well, by using a Flash applet to give socket access > > to > > JavaScript. But then I guess you have to make your pgsql server > > itself > > publicly accessible on some port. Is that just asking for trouble? > > > > I appreciate any comments or thoughts anyone might have on this. > > > > Thanks, > > Kev > > > > > > ---(end of broadcast)--- > > TIP 3: Have you checked our extensive FAQ? > > > >http://www.postgresql.org/docs/faq > > > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
