Separate Sessions?? (View data <-> Query tool)

2008/11/25 Csaba Együd <csegyud@gmail.com>:

Yes, I do use CREATE TEMPORARAY TABLE statement and I know that temp table
lives only during the sessionor transaction. This is what I need.
I meant that I'd like pgAdmin3 to use the same session both in the query
tool and
the view data tool to be able to use the temp table while modifying the data
via view data tool.

That's simply not possible. It would blow up extremely quickly - as
soon as you set a GUC that the main browser wasn't expecting, or the
browser tried to run a query whilst you were (or vice-versa). You
could implement some kind of queuing mechanism of course, but that
would significantly reduce usability as everything would bottleneck
around a single connection.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

""Dave Page"" <dpage@pgadmin.org> a k�vetkezoket �rta �zenet�ben
news:937d27e10811251205k65418b5esc9398343aea59ffb@mail.gmail.com...
2008/11/25 Csaba Egy�d <csegyud@gmail.com>:

Yes, I do use CREATE TEMPORARAY TABLE statement and I know that temp table
lives only during the sessionor transaction. This is what I need.
I meant that I'd like pgAdmin3 to use the same session both in the query
tool and
the view data tool to be able to use the temp table while modifying the
data
via view data tool.

That's simply not possible. It would blow up extremely quickly - as
soon as you set a GUC that the main browser wasn't expecting, or the
browser tried to run a query whilst you were (or vice-versa). You
could implement some kind of queuing mechanism of course, but that
would significantly reduce usability as everything would bottleneck
around a single connection.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

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

Hi Dave,

so you say I have no chance to use a session wide temp table from triggers
because I can not create it before viewing data?
Isn't there a way to run something before starting querying the data for
table view? Imean it would be nice to be able to run a stored proc or any
valid SQL statement in the session of View Data tool. If there is not,
wouldn't it be good idea? It has minimal overhead and I think many people
use temporary tables or have other depenencies influencing a given query.
Hoping... :) Thank you.

--
Best Regards,
Csaba Egy�d

On Wed, Nov 26, 2008 at 5:37 AM, Csaba Együd <csegyud@gmail.com> wrote:

so you say I have no chance to use a session wide temp table from triggers
because I can not create it before viewing data?
Isn't there a way to run something before starting querying the data for
table view? Imean it would be nice to be able to run a stored proc or any
valid SQL statement in the session of View Data tool. If there is not,
wouldn't it be good idea? It has minimal overhead and I think many people
use temporary tables or have other depenencies influencing a given query.
Hoping... :) Thank you.

In the 10+ years of pgAdmin, no-one has ever asked to have additional
SQL run by the View Data tool - what would be the point? You wouldn't
be able to have it create the temp table you want to view because you
need to know what table to view before you run the tool.

If you need that level of flexibility, then use the query tool where
you can create and use the temp table in one session. Failing that, if
you really want to use multiple tools on the same temp table, you'll
need to get someone in the community to implement global temp tables -
though really they're exactly the same as any other table (pretty much
the whole point of the temp table is that it's session-specific, and
isn't accessed through shared memory like regular tables).

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

""Dave Page"" <dpage@pgadmin.org> a k�vetkezoket �rta �zenet�ben
news:937d27e10811260047k7c590625u1a540acff0e42b35@mail.gmail.com...
On Wed, Nov 26, 2008 at 5:37 AM, Csaba Egy�d <csegyud@gmail.com> wrote:

In the 10+ years of pgAdmin, no-one has ever asked to have additional
SQL run by the View Data tool - what would be the point? You wouldn't
be able to have it create the temp table you want to view because you
need to know what table to view before you run the tool.

If you need that level of flexibility, then use the query tool where
you can create and use the temp table in one session. Failing that, if
you really want to use multiple tools on the same temp table, you'll
need to get someone in the community to implement global temp tables -
though really they're exactly the same as any other table (pretty much
the whole point of the temp table is that it's session-specific, and
isn't accessed through shared memory like regular tables).

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

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

OK, I understand now. :)

BTW, is it that weird way to define a session wide temp table storing some
session specific information to generate e.g. views based on that? The only
thing I wanted to have a more comfortable tool to modify a few fields in the
table then having to compose SQL statements in the query tool. The problem
is that my triggers refuse modifying a record if a given Temp table is not
created before, because one or more fields have their default values from
that temp table.

I agree that running an SQL before viewing data in table view would be a bit
silly. But I could think something like a new per server connection property
in which I could define a single SQL sentence which would be automatically
run after a connection is successfully established. It could be like modem
initialization commands or something like that. Of course it could be empty
by default...

Thank you,

-- Csaba Egy�d

On Wed, Nov 26, 2008 at 10:17 AM, Csaba Együd <csegyud@gmail.com> wrote:

BTW, is it that weird way to define a session wide temp table storing some
session specific information to generate e.g. views based on that? The only
thing I wanted to have a more comfortable tool to modify a few fields in the
table then having to compose SQL statements in the query tool. The problem
is that my triggers refuse modifying a record if a given Temp table is not
created before, because one or more fields have their default values from
that temp table.

The does sound odd.

I agree that running an SQL before viewing data in table view would be a bit
silly. But I could think something like a new per server connection property
in which I could define a single SQL sentence which would be automatically
run after a connection is successfully established. It could be like modem
initialization commands or something like that. Of course it could be empty
by default...

One of the problems is that pgAdmin sets up connections as it requires
to make things work correctly. Allowing arbitrary commands to be run
could easily break that - for example; if you changed the client
encoding. Additionally, it's not always obvious what connection will
be used unless you're pretty familiar with the way pgAdmin works
internally.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

""Dave Page"" <dpage@pgadmin.org> a k�vetkezoket �rta �zenet�ben
news:937d27e10811260310y61b2c739kc2ff06ff86218e95@mail.gmail.com...
On Wed, Nov 26, 2008 at 10:17 AM, Csaba Egy�d <csegyud@gmail.com> wrote:

BTW, is it that weird way to define a session wide temp table storing some
session specific information to generate e.g. views based on that? The
only
thing I wanted to have a more comfortable tool to modify a few fields in
the
table then having to compose SQL statements in the query tool. The problem
is that my triggers refuse modifying a record if a given Temp table is not
created before, because one or more fields have their default values from
that temp table.

The does sound odd.

I agree that running an SQL before viewing data in table view would be a
bit
silly. But I could think something like a new per server connection
property
in which I could define a single SQL sentence which would be automatically
run after a connection is successfully established. It could be like modem
initialization commands or something like that. Of course it could be
empty
by default...

One of the problems is that pgAdmin sets up connections as it requires
to make things work correctly. Allowing arbitrary commands to be run
could easily break that - for example; if you changed the client
encoding. Additionally, it's not always obvious what connection will
be used unless you're pretty familiar with the way pgAdmin works
internally.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

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

Unfortunately I'm not familiar with pgAdmin's internal connection handling.
So I will live with query tool.
Thank you for your assistance and also for this great tool.

-- Csaba

The problem is that my triggers refuse modifying a record if a given Temp

table is not created before, because one or more fields have their default
values from that temp table

So why don't you either use a normal table, or if you need simultaneous use
of the trigger with different values, use an array instead of a table. Or
you might create the temp table in the trigger function..
If you create a normal table you can still delete it afterwards.

hth
WBL
--
"Patriotism is the conviction that your country is superior to all others
because you were born in it." -- George Bernard Shaw

""Willy-Bas Loos"" <willybas@gmail.com> a k�vetkezoket �rta �zenet�ben
news:1dd6057e0811270036s67a6b8baqda17273b0484e53d@mail.gmail.com...

The problem is that my triggers refuse modifying a record if a given Temp
table is not created before, because one or more fields have their default
values from that temp table

So why don't you either use a normal table, or if you need simultaneous use
of the trigger with different values, use an array instead of a table. Or
you might create the temp table in the trigger function..
If you create a normal table you can still delete it afterwards.

hth
WBL
--
"Patriotism is the conviction that your country is superior to all others
because you were born in it." -- George Bernard Shaw

Willy,
you are right but... this is a bit difficult but I try to explain. I develop
an office management software which uses Postgres as a RDB server. This
software has to handle many firms separately but by design it has to use
only one database (customer requirement) so I had to find out a solid way to
separate the firms form each other. I find out that I qualify every
firm-dependent record with a firmid.

After logging in the client software will create a TEMP table in which it
stores the selected firmid. This TEMP table will live durring the login
session. After this point every views will filter to this firmid and won't
give back other firms' data just this. I know that other client softwares
will be able to select data from the tables. (Unfortunatelly I don't know a
way to revoke select right from a table while a view can select from it...
Do you know such thing?) Because of this I know that the clean way would be
using separate databases for separate firms but this was not my decision.
:( If you know a setting which can improve the security pleas let me know.

Also I had to assure that the INSERT and UPDATE operations will only work
for the selected firm and the user won't be able to potter with other firms'
data even not accidentally. This is done in triggers and this causes my
"View data tool" problem because viewing is possible but modifying is
enabled only when a firmid is set in the TEMP table.

BTW, I can use script templates with Query tool (like INSERT or UPDATE
script) which are very handy.

Thx,

--
Best Regards,
Csaba Egy�d
IN-FO Studio

how about this:

create role firm1 nologin;
create role john password 'secret' login;
grant firm1 to john;
create role firm2 nologin;
create role amy password 'secret' login;
grant firm2 to amy;

create table table1 (id serial primary key,firm integer, val integer);
insert into table1 (firm, val) values (1, 101);
insert into table1 (firm, val) values (1, 102);
insert into table1 (firm, val) values (1, 103);
insert into table1 (firm, val) values (1, 104);
insert into table1 (firm, val) values (1, 105);
insert into table1 (firm, val) values (2, 206);
insert into table1 (firm, val) values (2, 207);
insert into table1 (firm, val) values (2, 208);
insert into table1 (firm, val) values (2, 209);
insert into table1 (firm, val) values (2, 210);

revoke all on table1 from john;
revoke all on table1 from amy;
revoke all on table1 from firm1;
revoke all on table1 from firm2;

create view view_firm1 as select * from table1 where firm =1;
create view view_firm2 as select * from table1 where firm =2;
grant select, update on view_firm1 to firm1;
grant select, update on view_firm2 to firm2;

create or replace rule _update as on update
to view_firm1 do instead
update table1 set val = NEW.val where id=old.id;

create or replace rule _update as on update
to view_firm2 do instead
update table1 set val = NEW.val where id=old.id;

On Thu, Nov 27, 2008 at 3:06 PM, Csaba Együd <csegyud@gmail.com> wrote:

""Willy-Bas Loos"" <willybas@gmail.com> a következoket írta üzenetében
news:1dd6057e0811270036s67a6b8baqda17273b0484e53d@mail.gmail.com...

The problem is that my triggers refuse modifying a record if a given Temp

table is not created before, because one or more fields have their default
values from that temp table

So why don't you either use a normal table, or if you need simultaneous use
of the trigger with different values, use an array instead of a table. Or
you might create the temp table in the trigger function..
If you create a normal table you can still delete it afterwards.

hth
WBL
--
"Patriotism is the conviction that your country is superior to all others
because you were born in it." -- George Bernard Shaw

Willy,
you are right but... this is a bit difficult but I try to explain. I
develop an office management software which uses Postgres as a RDB server.
This software has to handle many firms separately but by design it has to
use only one database (customer requirement) so I had to find out a solid
way to separate the firms form each other. I find out that I qualify every
firm-dependent record with a firmid.

After logging in the client software will create a TEMP table in which it
stores the selected firmid. This TEMP table will live durring the login
session. After this point every views will filter to this firmid and won't
give back other firms' data just this. I know that other client softwares
will be able to select data from the tables. (Unfortunatelly I don't know a
way to revoke select right from a table while a view can select from it...
Do you know such thing?) Because of this I know that the clean way would be
using separate databases for separate firms but this was not my decision. :(
If you know a setting which can improve the security pleas let me know.

Also I had to assure that the INSERT and UPDATE operations will only work
for the selected firm and the user won't be able to potter with other firms'
data even not accidentally. This is done in triggers and this causes my
"View data tool" problem because viewing is possible but modifying is
enabled only when a firmid is set in the TEMP table.

BTW, I can use script templates with Query tool (like INSERT or UPDATE
script) which are very handy.

Thx,

--
Best Regards,
Csaba Együd
IN-FO Studio

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

--
"Patriotism is the conviction that your country is superior to all others
because you were born in it." -- George Bernard Shaw

""Willy-Bas Loos"" <willybas@gmail.com> a k�vetkezoket �rta �zenet�ben
news:1dd6057e0811280030q6df9a8ebqf6a71ac28b16ea3c@mail.gmail.com...
how about this:

create role firm1 nologin;
create role john password 'secret' login;
grant firm1 to john;
create role firm2 nologin;
create role amy password 'secret' login;
grant firm2 to amy;

create table table1 (id serial primary key,firm integer, val integer);
insert into table1 (firm, val) values (1, 101);
insert into table1 (firm, val) values (1, 102);
insert into table1 (firm, val) values (1, 103);
insert into table1 (firm, val) values (1, 104);
insert into table1 (firm, val) values (1, 105);
insert into table1 (firm, val) values (2, 206);
insert into table1 (firm, val) values (2, 207);
insert into table1 (firm, val) values (2, 208);
insert into table1 (firm, val) values (2, 209);
insert into table1 (firm, val) values (2, 210);

revoke all on table1 from john;
revoke all on table1 from amy;
revoke all on table1 from firm1;
revoke all on table1 from firm2;

create view view_firm1 as select * from table1 where firm =1;
create view view_firm2 as select * from table1 where firm =2;
grant select, update on view_firm1 to firm1;
grant select, update on view_firm2 to firm2;

create or replace rule _update as on update
to view_firm1 do instead
update table1 set val = NEW.val where id=old.id;

create or replace rule _update as on update
to view_firm2 do instead
update table1 set val = NEW.val where id=old.id;
--------------------------

Willy,
THX! This sounds quite promissing. If I understand well you say that I could
isolate data by using separate group roles for each firms and defining
updatable views to work on. The most strange thing for me is revoking all
rights from table1 but I guess this is the heart of it... If I revoke all
rights on table1 from the roles how will the user be able to modify the data
of table1 trough the views? I read somewhere that when a user wants to
select rows from a view the user needs to have the select permission for the
undelaying table as well, and thats the case with updating or inserting too.
I just would like to know - but the fact is that it works!

Editing table1 in View data tool is also working! Great. This appetizing
news makes me ask :) that if it is possible to edit a view in the View data
tool? (I defined both _update and _insert rules) It is not that important
because I can edit the table directly but why not if it's possible.

Thank you very much for opening my eyes. To tell the truth I was afraid a
bit of using updatable views because of the above misunderstanding.

--
Best Regards,
Csaba Egy�d
IN-FO Studio

Only, it's too bad that you can't update the records from the "view data"
dialog.
It works in access2000, it doesn't know the difference between views and
tables (the difference is actually very small).
You probably get some annoying error when you try to edit a normal select
view from access, though.

I guess pg_admin assumes that views cannot be edited, which is incorrect in
this case.
Maybe this is something that could be enhanced in a future release?

oh, i see i forgot:
revoke all on view_firm1 from amy;
revoke all on view_firm1 from firm2;
revoke all on view_firm2 from john;
revoke all on view_firm2 from firm1;

Cheers,
WBL

--
"Patriotism is the conviction that your country is superior to all others
because you were born in it." -- George Bernard Shaw

On Fri, Nov 28, 2008 at 11:04 AM, Willy-Bas Loos <willybas@gmail.com> wrote:

Only, it's too bad that you can't update the records from the "view data"
dialog.
It works in access2000, it doesn't know the difference between views and
tables (the difference is actually very small).
You probably get some annoying error when you try to edit a normal select
view from access, though.

Yeah, it's actually pretty hard to do in pgAdmin, because we have no
easy way of telling what the effects of the update are.

*thinks* - I wonder if we can use RETURNING for that now...

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

oh, and :
revoke all on table1 from public;
revoke all on view_firm1 from public;
revoke all on view_firm2 from public;

duh

--
"Patriotism is the conviction that your country is superior to all others
because you were born in it." -- George Bernard Shaw

""Willy-Bas Loos"" <willybas@gmail.com> a k�vetkezoket �rta �zenet�ben
news:1dd6057e0811280304m2c8b6d24tc1fd283bf2a9de2d@mail.gmail.com...
Only, it's too bad that you can't update the records from the "view data"
dialog.
It works in access2000, it doesn't know the difference between views and
tables (the difference is actually very small).
You probably get some annoying error when you try to edit a normal select
view from access, though.

I guess pg_admin assumes that views cannot be edited, which is incorrect in
this case.
Maybe this is something that could be enhanced in a future release?

oh, i see i forgot:
revoke all on view_firm1 from amy;
revoke all on view_firm1 from firm2;
revoke all on view_firm2 from john;
revoke all on view_firm2 from firm1;

Cheers,
WBL

--
"Patriotism is the conviction that your country is superior to all others
because you were born in it." -- George Bernard Shaw

--------------------------------------------

Guys, you are great! Thank you!

View data: Yes I thought the same - maybe pgAdmin treats views as read only
by default.
BTW, I use Access only to visualize database structure (manually). If I knew
a product which can reverse engineering postgres database along with
constraints and other object and can visualize them I would use that instead
and would forget Access...

Many thanks!

-- Csaba

*thinks* - I wonder if we can use RETURNING for that now...

How? If there is no RETURNING present in the command...

I'm not sure if this is all possible, but you might:
o make all editable views editable in pgAdmin as an option (costs
performance i guess, maybe some unexpected results if the rules do strange
things)
o if there are on update rules present, allow editing the values. After
leaving the row refresh the row.
o if there are on delete rules present, allow deleting rows. After deleting
rows, check if they exist, if so refresh them.
o if there are on insert rules present.. dang, don't know how to do that.
Act the same way you do when inserting in a normal table i guess.

Of course the rule may do something completely unexpected, of which pgAdmin
would know nothing. In that case you might have to press the refresh button
a lot. Maybe make a guru hint out of that.
But i think that any sensible use would be enabled by this.

--
"Patriotism is the conviction that your country is superior to all others
because you were born in it." -- George Bernard Shaw

If I understand well you say that I could isolate data by using separate

group roles for each firms and defining updatable views to work on.
yes. it's one way to do it.
As simple as this example is, you might as well make a seperate table for
each firm. Then you wouldn't need to bother with views and rules.
The views get interesting when you want to allow one record to be selected
by more than one firm.
Or if you want to be able to do stuff with all of the data in table1.
The disadvantage is that each firm has to use diffent code, they select from
a different table. If you want to get really crazy you can add a username to
table1 and make just one view that filters on applicable_roles (all the
roles granted to the current_role). But that doesn't scale so well i guess.

This sounds quite promissing.

Well, it only works as long as you use the database authentication (and
authorisation) system. Meaning that your application logs in to the database
with a seperate account for each user.
Works great for database clients like pgadmin, but it doesn't make you happy
in PHP or standard webservice stuff.
In that case you don't need this kind of authorisation in the database
layer, but you do it in a different application.

The most strange thing for me is revoking all rights from table1 but I

guess this is the heart of it... If I revoke all rights on table1 from the
roles how will the user be able to modify the data of table1 trough the
views?

I read somewhere that when a user wants to select rows from a view the

user needs to have the select permission for the undelaying table as well,
and thats the case with updating or inserting too.
The view uses the privileges of its owner, not those of the current_user

So for example:
john has no rights on table1, but can make a view "create view bla as select
* from table1". There will be a "permission denied" error for anyone who
selects from the view, even superusers.
the other way around:
fred is a superuser. When he makes the same view, all records will be
visible to those that (may) select from it.

just try it:
--based on the code from before, you now have the right to select from
table1 since you created it and are the owner
set role john;
create view bla as select * from table1;
select * from bla;

reset role;
select current_role;

select * from bla;

cheers,

WBL

--
"Patriotism is the conviction that your country is superior to all others
because you were born in it." -- George Bernard Shaw

""Willy-Bas Loos"" <willybas@gmail.com> a k�vetkezoket �rta �zenet�ben
news:1dd6057e0811281320v4f2808fs69a96dcf01f272bb@mail.gmail.com...

If I understand well you say that I could isolate data by using separate
group roles for each firms and defining updatable views to work on.

yes. it's one way to do it.
As simple as this example is, you might as well make a seperate table for
each firm. Then you wouldn't need to bother with views and rules.
The views get interesting when you want to allow one record to be selected
by more than one firm.
Or if you want to be able to do stuff with all of the data in table1.
The disadvantage is that each firm has to use diffent code, they select from
a different table. If you want to get really crazy you can add a username to
table1 and make just one view that filters on applicable_roles (all the
roles granted to the current_role). But that doesn't scale so well i guess.

This sounds quite promissing.

Well, it only works as long as you use the database authentication (and
authorisation) system. Meaning that your application logs in to the database
with a seperate account for each user.
Works great for database clients like pgadmin, but it doesn't make you happy
in PHP or standard webservice stuff.
In that case you don't need this kind of authorisation in the database
layer, but you do it in a different application.

The most strange thing for me is revoking all rights from table1 but I
guess this is the heart of it... If I revoke all rights on table1 from the
roles how will the user be able to modify the data of table1 trough the
views?
I read somewhere that when a user wants to select rows from a view the
user needs to have the select permission for the undelaying table as well,
and thats the case with updating or inserting too.

The view uses the privileges of its owner, not those of the current_user

So for example:
john has no rights on table1, but can make a view "create view bla as select
* from table1". There will be a "permission denied" error for anyone who
selects from the view, even superusers.
the other way around:
fred is a superuser. When he makes the same view, all records will be
visible to those that (may) select from it.

just try it:
--based on the code from before, you now have the right to select from
table1 since you created it and are the owner
set role john;
create view bla as select * from table1;
select * from bla;

reset role;
select current_role;

select * from bla;

cheers,

WBL

--
"Patriotism is the conviction that your country is superior to all others
because you were born in it." -- George Bernard Shaw
------------------

Willy,

I do not want to make it so complicated. This level of complexity is far
enough... :)

For a long time, I have tried for always use database roles to log into the
application instead ot unsing fictive users logging in with a shared
privilege-packed database role. I'm that kind of paranoid developer who
thinks that bad guys will try to hack the system. On the other hand there
may also be bugs in the client software and the unprivileged user could
reach information which is not for her / him...

I usually develop desktop applications not web apps. So I need this level of
granularity of the permission system. PostgreSQL is really impressive on
this field.

Regarding the view rights: I did not know this piece of information and I'm
thankful for you to making it clear.

Many thanks!

Cheers,
-- Csaba