Re: [GENERAL] view permissions problem - featuer or bug?
Travis Bauer <[EMAIL PROTECTED]> writes: > That part works okay, but here is a more complicated situation that > doesn't: > create user user1; > create table t1 (x int, y int); > create table t2 (a int, y int); > create view v1 as select * from t1 where x in (select a from t2); > -- > create view v2 as select * from v1 where x>3; > revoke all on t1 from public; > revoke all on t2 from public; > revoke all on v1 from public; > revoke all on v2 from public; > grant select on v2 to user1; > \c - user1 > select * from v2; > > v1: Permission denied. > The problem occurs because of the nested select underlined above. I agree, this is a bug. The rewriter checks access permissions on the basis of the rule's creator, not of the rule's invoker --- but it only does so at the top level of the rule query. It forgets to recurse into subqueries :-(. Fairly easy fix, will work on it for 7.1. regards, tom lane
Re: [GENERAL] view permissions problem - featuer or bug?
That part works okay, but here is a more complicated situation that doesn't: create user user1; create table t1 (x int, y int); create table t2 (a int, y int); create view v1 as select * from t1 where x in (select a from t2); -- create view v2 as select * from v1 where x>3; revoke all on t1 from public; revoke all on t2 from public; revoke all on v1 from public; revoke all on v2 from public; grant select on v2 to user1; \c - user1 select * from v2; > v1: Permission denied. The problem occurs because of the nested select underlined above. Since user1 has permissions on v2, he should be able to execute v2's select statement. V2 accesses another view v1. V1 accesses t1, which causes no problems since t1 is a table. But if v1 has a nested select statement (or calls a function which executes a select statement), you get a permission denied error. The workaround I did was to create a bunch of extra subview which user1 has access to. This works, but seems like the above scenerio shouldn't be causes the permission violation. Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer > > table big_customer_db is owned by user "master" > you create a view "active_customer_list" also owned by "master" > now, if you grant someone access to the view, they will be > able to get the data in it, even though they can't query > big_customer_db directly. > > Is this what you want? > > HTH, > -- > Martijn van Oosterhout <[EMAIL PROTECTED]> > http://cupid.suninternet.com/~kleptog/ >
Re: [GENERAL] libpq connectivity
Someone mentioned that you had to include libpq, but you are also using the
wrong GCC command line. the -c switch tells gcc to just build an object
file, ignoring the main routine and not creating an actual executable
program. You want something like this:
$ gcc conn2.c -o conn2 -lpq
At 01:53 AM 7/7/00, anuj wrote:
>Hello,
>
>I am working on Linux RH 6.0 platform.
>I want use PostgreSQL as a backend. 'C' language as a front-end.
>I am not able to connect to each other.
>I am using libpq.
>The program is :-
>
>/*conn2.c*/
>#include
>#include "/usr/include/pgsql/libpq-fe.h"
> main()
> {
> char *pghost, *pgport, *pgoptions,*pgtty;
> char *dbName;
> PGconn *conn;
> pghost = NULL; /* host name of the backend server */
> pgport = NULL; /* port of the backend server */
> pgoptions = NULL; /* special options to start up the backend
> * server */
> pgtty = NULL; /* debugging tty for the backend server */
> dbName = "template1";
>
> /* make a connection to the database */
> conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);
>}
>
>The compiling is ok, but linking have error.
>$ gcc conn2.c -c -o conn2
>No error
>
>The program compile and linking result :-
>*
>$ gcc conn2.c -o conn2
>/tmp/cchKU26L.o: In function `main':
>/tmp/cchKU26L.o(.text+0x47): undefined reference to `PQsetdbLogin'
>collect2: ld returned 1 exit status
>*
>
>How to remove this linking error, or how to make link between PostgreSQL and
>'C'?
>Thanks in advance
>Anuj
Re: [GENERAL] Couple of design questions
Jesse Scott <[EMAIL PROTECTED]> writes: > Hello everyone, > > I'm just beginning a PHP / PostgreSQL project for someone and there are a > couple of things where I am not sure the best design for the database. > > The first is that we want to store kind of a history of values for the past > 3 days or so. We want to use this so that we can analyze how the values > have changed over the last few days. The solution I thought of was having > 4 columns in the table for each value, like this: > > somedata int, > somedata_24 int, > somedata_48 int, > somedata_72 int, > > There are 3 different variables that we want to keep a history for in each > row. So what I thought we could do is at some arbitrary time each day, > copy the values from somedata into somedata_24, from somedata_24 into > somedata_48, from somedata_48 into somedata_72, and just forget whatever > was in somedata_72. My question is, how long would something like this > take (relatively speaking, I don't know the hardware specs of the server > exactly, it will be professionally hosted I believe) if there were around > 20,000 rows? If it would take too long or be too taxing on resources, do > you have any other ideas on how to handle something like this? Would it not be better to have something like: uid int,-- identified piece of data log_datedate, -- day the data was logged dataint,-- data to be stored The primary key will be composed of the uid and the log_data together. Now there is not need to age the data. If you want data from 24 hours ago, you take the current date, subtract one day, then do select based on that. Same with 48 hours, etc. Now all you have to do is periodically delete data older than a few days. But this method lets you choose arbitrarily how many days to keep. And if you also index the data by the log_date, the deleting old data will be very fast. -- Prasanth Kumar [EMAIL PROTECTED]
Re: [GENERAL] view permissions problem - featuer or bug?
Travis Bauer wrote: > > I have a set of tables and some views which perform calculations on > those table to which no one except the group officestaff has > any permissions. No problem. > > I tried to create views to which some particular client would have > permissions. These views would be filtered versions of the private views. > Herein lies the problem. If any of those underlying veiws call functions > that access tables to which the user does not have permissions, or if any > of those underlying views have sub-select statements (such as "where x in > (select . . . )") the user gets access denied errors. > > Is this a feature or a bug? On the one hand, it certainly provides tight > security. However, it seems like if you give someone permissions on a > view, that view ought to be allows to perform whatever it needs to get the > data back out regardless of other underlying permissions. At least the way it's supposed ot work is that the view is executed as if by the owner of the view. For example: table big_customer_db is owned by user "master" you create a view "active_customer_list" also owned by "master" now, if you grant someone access to the view, they will be able to get the data in it, even though they can't query big_customer_db directly. Is this what you want? HTH, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://cupid.suninternet.com/~kleptog/
Re: [GENERAL] A Referntial integrity
> Hello, > > We are using a postgresql-7.0.2. Consider the following scenario > > we have created a table t1 with columns > > c1 : having referential integrity with t2.c1 > c2 : having referential integrity with t3.c2 > > where t2 and t3 are different tables > > Assume that t2 has also got a referential integrity with t4.c3 where c3 is = > a column in t2 as well. > > Now I want to drop a constraint of table t2 that is referring to t4.c3. As = > per the documentation one can not drop a constraint in alter table command.= > In this situation I need to drop the table t2. But I can not drop this tab= > le since it has got child in table t1. > Do I need to drop t1 as well ?? This one is a classical example of master d= > etail - detail relation ship with dependent details which is very trivial i= > n real world. Infect in more complex design such detail - detail relationsh= > ip can go upto several levels. Every time dropping a table is not good. Is = > there any other way to do this?? Please elaborate on this > > Regards > > Niraj Bhatt No, you don't. Referential integrity is maintained by means of triggers in postgresql, so you can perform query like that: SELECT t.tgname, c1.relname FROM pg_trigger t INNER JOIN pg_class c1 ON t.tgrelid = c1.oid INNER JOIN pg_class c2 ON t.tgconstrrelid = c2.oid WHERE (c1.relname = 't1' AND c2.relname = 't2') OR (c1.relname = 't2' AND c2.relname = 't1'); , where t1 references t2 (or vice versa), and you will get three rows (or a multiple of three, if there are more than one reference between these tables), which will contain the names of referential triggers, like that: tgname | relname + RI_ConstraintTrigger_22073 | t2 RI_ConstraintTrigger_22075 | t2 RI_ConstraintTrigger_22071 | t1 (3 rows) Drop these triggers (there is one on the referencing table, and two ones on the referenced table), and there will be no reference anymore. Alex Bolenok.
Re: [GENERAL] help -- cursor inside a function
> hello , > i have a procedure as below. > > -- --- > > create function retrec(integer) returns integer > as > 'declare > uid alias for $1; > begin > declare retrec_cur cursor for select * from jd_user_master where > > um_user_id>uid; > if found then > fetch 1 from retrec_cur; > move next from retrec_cur; > return 1; > end if; > end;' > language 'plpgsql'; > -- --- > > this gets created , but while running it i get the error below, > -- - > > NOTICE: plpgsql: ERROR during compile of retrec near line 5 > ERROR: parse error at or near "cursor" > -- > > why this is so ? can anyone help me out ? thanx in advance. You may use query loops, such as: DECLARE nextrow RECORD; BEGIN FOR nextrow IN SELECT * FROM WHERE ORDER BY LOOP ... END LOOP; END; See postgresql HTML documentation for further info. Alex Bolenok.
Re: [GENERAL] Couple of design questions
Hi, For the first question , having about 20,000 rows seems it will work fine. (I do have about 100,000 rows table working fine.) For the second, sure PG has enough locking some look for MVCC feature at the PG docs(chapter 10). >Will SELECT ... FOR UPDATE help me out here? "However, take into account that SELECT FOR UPDATE will modify selected rows to mark them and so will results in disk writes. Row-level locks don't affect data querying. They are used to block writers to the same row only. " >Is there a way to >check and see if a row is locked and ignore locked rows in another >SELECT... FOR UPDATE? I don't think this would be a safe way. ie: if user A made a select for update , and then forgot to submit (commit) what would happen? the selected rows would stay locked for ever !?! So IMO ,it would be safer if user A make a simple select ( showing it in an HTML form ) and then make a seperate update (using HTML input).At this case you will still have the risk of update error( if user B had made simultanious updates ), but still seems safer. Regards Omid Omoomi Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
[GENERAL] Backup of database with Large Object
Hello, I would like to make a backup of a postgresql database (version 6.5) with large objects. I tried pg_dump, but It didn't saved the content of large object even with the -o option. Is there an easy solution ? __ Boîte aux lettres - Caramail - http://www.caramail.com
