[SQL] Permission denied for language pltclu
Hi, I am using Postgres-.8.0.1. I am creating a function with ‘pltclu’ language. I have already created database with ‘pltclu’ language. But on creation this function I am getting this error and failed to create this function - ERROR: Permission denied for language pltclu - What is the cause? Regards Dinesh Pandey -- Dinesh Pandey Sr. Software Engineer Second Foundation (India) Pvt. Ltd. Plot# 52 Industrial Area, Phase II Chandigarh. (India) PH: (O) 0172-2639202, Extn: 233
Re: [SQL] Permission denied for language pltclu
On Jun 10, 2005, at 4:10 PM, Dinesh Pandey wrote: I am using Postgres-.8.0.1. I am creating a function with ‘pltclu’ language. I have already created database with ‘pltclu’language. But on creation this function I am getting this error and failed to create this function - ERROR: Permission denied for language pltclu - What is the cause? It means that the user you are creating the function as does not have permission to use pltclu. Only superusers can create functions using untrusted languages. http://www.postgresql.org/docs/8.0/interactive/pltcl.html Hope this helps. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Permission denied for language pltclu
I have installed the Postgres from “postgres” user with pltcl option and able to create these function with another dbUSER successfully and never get this error. But our client is getting this error, How to solve it now? Any Idea? Thanks Dinesh Pandey -Original Message- From: Michael Glaesemann [mailto:[EMAIL PROTECTED] Sent: Friday, June 10, 2005 12:58 PM To: [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org; 'PostgreSQL' Subject: Re: [SQL] Permission denied for language pltclu On Jun 10, 2005, at 4:10 PM, Dinesh Pandey wrote: > I am using Postgres-.8.0.1. > > > > I am creating a function with ‘pltclu’ language. I have already > created database with ‘pltclu’language. But on creation this > function I am getting this error and failed to create this function > > - > > ERROR: Permission denied for language pltclu > > - > > What is the cause? It means that the user you are creating the function as does not have permission to use pltclu. Only superusers can create functions using untrusted languages. http://www.postgresql.org/docs/8.0/interactive/pltcl.html Hope this helps. Michael Glaesemann grzm myrealbox com
Re: [SQL] Permission denied for language pltclu
Dinesh Pandey wrote: I have installed the Postgres from "postgres" user with pltcl option and able to create these function with another dbUSER successfully and never get this error. But our client is getting this error, How to solve it now? Any Idea? If you created an untrusted function as user "dbUSER" then it was a superuser too. Honest. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Permission denied for language pltclu
Sorry I didn't get it exactly. Because the same function (send e-mail) I am able to create at my end, but our client is not able to create it at their end. 1. Is there some problem in installation? Or 2. Problem with system user permission executing that database? OR 3. Problem With Database user permission? Now how to change permission of that user to be able to create this function? If you created an untrusted function as user "dbUSER" then it was a superuser too. Honest. Thanks Dinesh ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Permission denied for language pltclu
Dinesh Pandey wrote: Sorry I didn't get it exactly. Because the same function (send e-mail) I am able to create at my end, but our client is not able to create it at their end. 1. Is there some problem in installation? 2. Problem with system user permission executing that database? 3. Problem With Database user permission? Number 3 - it is to do with a PostgreSQL user account. That user needs to be a superuser. Now how to change permission of that user to be able to create this function? A good place to start with this sort of thing is the manuals. In the 7.4 manuals, I'd start with: Ch 36.1. Installing Procedural Languages Ch 17.2. User Attributes Reference I - the "ALTER USER" command Note that you may want to make the client's user a superuser just long enough to install the language and/or functions. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Permission denied for language pltclu
On Jun 10, 2005, at 5:38 PM, Dinesh Pandey wrote: Sorry I didn't get it exactly. Because the same function (send e- mail) I am able to create at my end, but our client is not able to create it at their end. 1. Is there some problem in installation? No. Or 2. Problem with system user permission executing that database? No. OR 3. Problem With Database user permission? Only a superuser can create a pltclu function. "dbUSER" must be a PostgreSQL superuser if it created the pltclu function. You client must use a PostgreSQL superuser to create a pltclu function. Now how to change permission of that user to be able to create this function? Make sure the user creating the function is a PostgreSQL superuser. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Permission denied for language pltclu
> OR > 3. Problem With Database user permission? Only a superuser can create a pltclu function. "dbUSER" must be a PostgreSQL superuser if it created the pltclu function. You client must use a PostgreSQL superuser to create a pltclu function. What do mean with super user. The user who has installed the Postgres (like I have installed it using "Postgres" user) and initialized the pgsql/data? I am able to create this pltcl function with another "dataman" user but the same getting failed at out client end. Now pls tell me how to fix it, so that I can tell our client. > Now how to change permission of that user to be able to create this > function? Make sure the user creating the function is a PostgreSQL superuser.
Re: [GENERAL] [SQL] Permission denied for language pltclu
Hi Richard/ Michael Thanks for your great help. I got the problem. Actually, I was not getting the cause of this problem, because it was working properly at our end. Actually this problem occurs when the function is being created by the user who has not created the current database. Solution: The database must be created by the user who is creating the pltcl function? Right Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Richard Huxton Sent: Friday, June 10, 2005 2:41 PM To: [EMAIL PROTECTED] Cc: 'Michael Glaesemann'; pgsql-general@postgresql.org; 'PostgreSQL' Subject: Re: [GENERAL] [SQL] Permission denied for language pltclu Dinesh Pandey wrote: > Sorry I didn't get it exactly. Because the same function (send e-mail) I am > able to create at my end, but our client is not able to create it at their > end. > > 1. Is there some problem in installation? > 2. Problem with system user permission executing that database? > 3. Problem With Database user permission? Number 3 - it is to do with a PostgreSQL user account. That user needs to be a superuser. > Now how to change permission of that user to be able to create this > function? A good place to start with this sort of thing is the manuals. In the 7.4 manuals, I'd start with: Ch 36.1. Installing Procedural Languages Ch 17.2. User Attributes Reference I - the "ALTER USER" command Note that you may want to make the client's user a superuser just long enough to install the language and/or functions. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] How-to suggestions to views
Hi, in the course of my investigation on how to agglomerate or concat several tables using a view or functions the following little HOW-TO felt out. It is kind of a full fledged example of how to coalesce two tables using a view. However it is as it is and I am more less new to writing rules and thus making faults. As the PG-doc says: There's a mega example but not some simple so I had a try in making something simple. So short story long: I want sou to have a look at my SQL-style example and either learn from it and/or give comments and suggestions (what didn't I see, since I am beginner and what hidden issues exist, what did I interpret wrong) textfile 8< HOW-TO concatinate two tables in Postgres using a view and rules This is a full-example of how to agglomerate two tables, connected via a 1:1 relation into one view on which DELETE, UPDATE and INSERT can be used. So our goal is to have two tables like id|a|b and id|x|y coalesced into one table or view id|a|b|x|y which by itself allows INSERT, UPDATE and DELETE. -- We start with creation of the tables -- In order to connect them afterwards, we need a primary key and some columns: CREATE TABLE tbla ( id int4 NOT NULL, a int4, b varchar(12), CONSTRAINT tbla_pk PRIMARY KEY (id) ) WITHOUT OIDS; -- One table is kind of master table, whilst the other(s) are/is -- slave table. This just means that the master table defines what -- (new) values are valid for the primary key and thus for the -- foreign keys of the slave tables. -- So a second table will at least have the same key as the master table. -- On the one hand as foreign key to allow just values in tbla and to -- retain referential integrity and on the other hand as primary key to -- keep values unique and thus make a 1:n relation become a 1:1 relation: CREATE TABLE tblb ( id int4 NOT NULL, x bool, y timestamp, CONSTRAINT tblb_pk PRIMARY KEY (id), CONSTRAINT tblb_fk FOREIGN KEY (id) REFERENCES tbla (id) ON UPDATE CASCADE ON DELETE CASCADE ) WITHOUT OIDS; -- Note that we specified ON DELETE CASCADE. This will get us handy, later -- Now let's already test our new tables plus their constraints by inserting some records: INSERT INTO tbla VALUES ( 3, 9034, 'F dabiu' ); INSERT INTO tbla VALUES ( 6, -23, 'Moosi llap' ); -- we have to use same keys and qty 0..1 INSERT INTO tblb VALUES ( 3, false, now() ); INSERT INTO tblb VALUES ( 6, true, now() ); -- Now we can create such a agglomerating view. However it will just allow -- data to be viewed - maybe thus the name. CREATE OR REPLACE VIEW a_and_b AS SELECT tbla.id, tbla.a, tbla.b, tblb.x, tblb.y FROM tbla NATURAL LEFT OUTER JOIN tblb; -- This creates a new 'table' (internally Postgres views are tables with no data -- but a bunch of rules) with all the columns we expected earlier. -- Note that since we used the name id in both tables, we could use a NATURAL -- JOIN. Depending on your column names and intentions, other joins are required. -- As you can see, this resembles just the concatenated table we had in mind: SELECT * FROM a_and_b; -- In order to make INSERT, UPDATE and DELETE work o the view, the next step -- is to define some rules. Note that there already exists one rule: The -- 'standard-rule' named _RETURN. This makes a table to a view in Postgres. -- Since the SELECT is covered by a rule, no actual data is required. -- Let's start with a rule for inserting records: CREATE OR REPLACE RULE a_b_insert AS ON INSERT TO a_and_b DO INSTEAD ( INSERT INTO tbla (id, a, b) VALUES (new.id, new.a, new.b); INSERT INTO tblb (id, x, y) VALUES (new.id, new.x, new.y); ); -- What we do here is: Instead of issuing the users insert, we issue two of -- our own inserts. Thereby reusing values we got from the users insert. -- This is the pseudo relation NEW. NEW has the same structure as the view -- the rule is written for. The two INSERTs (or whatever other SQL-statements) -- are coalesced by putting them into parentheses. Note that coming from our -- definition, we have to fill tbla before tblb. -- So it's now possible to insert records via tha view: INSERT INTO a_and_b VALUES (99, 123, 'text', false, now() ); SELECT * FROM a_and_b WHERE id=99; -- In order to be able to also delete records, a delete-rule is needed -- This is also a INSTEAD-rule, since it is intended to be a rather -- generic rule (any DELETE-WHERE-clause shall be possible), there are -- no restricting expressions and it is a INSTEAD but not a ALSO-rule. -- So in this case we want to delete records from just tbla. Since we enabled -- DELETE CASCADE Postgres will clean all dependent records in tblb as well. -- One could think that the WHERE clause of the view's DELETE will be applied -- to this rule as well, but that's not true. a 'DELETE FROM tbla' ends up -- in purging all records. So to restrict the rule's DELETE we can make use -- of the pseudo relation OLD. This relation has the same form
Re: [GENERAL] [SQL] Permission denied for language pltclu
Dinesh Pandey wrote: Hi Richard/ Michael Thanks for your great help. I got the problem. Actually, I was not getting the cause of this problem, because it was working properly at our end. Actually this problem occurs when the function is being created by the user who has not created the current database. Solution: The database must be created by the user who is creating the pltcl function? Right Not quite. Read the chapter on users I mentioned in the manuals. Then, try a "SELECT * FROM pg_user" and look at the "usesuper" column. Then, try "ALTER USER username CREATEUSER" and "ALTER USER username NOCREATEUSER" - see how these affect pg_user. In short, a "superuser" is a user who can create other users. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] [SQL] Permission denied for language pltclu
On Jun 10, 2005, at 6:51 PM, Dinesh Pandey wrote: Actually this problem occurs when the function is being created by the user who has not created the current database. Solution: The database must be created by the user who is creating the pltcl function? Right This is a coincidence. Only a PostgreSQL superuser can create a database, so a user who created the database will be a superuser. Only a superuser can create a function with an untrusted language. So, the same superuser can both create a database and create the function using pltclu. However, *any* PostgreSQL superuser should be able to create such a function, regardless of whether they created the database or not. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [SQL] Permission denied for language pltclu
On Jun 10, 2005, at 7:26 PM, Michael Glaesemann wrote: On Jun 10, 2005, at 6:51 PM, Dinesh Pandey wrote: Actually this problem occurs when the function is being created by the user who has not created the current database. Solution: The database must be created by the user who is creating the pltcl function? Right This is a coincidence. Only a PostgreSQL superuser can create a database, so a user who created the database will be a superuser. Only a superuser can create a function with an untrusted language. So, the same superuser can both create a database and create the function using pltclu. However, *any* PostgreSQL superuser should be able to create such a function, regardless of whether they created the database or not. Ach! Should have checked the docs before I mailed. I'm wrong about only superusers creating databases. Richard's got it all right. :) Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [SQL] Permission denied for language pltclu
In short, a "superuser" is a user who can create other users. But if the user is not super user, he is not allowed to install the language 'plpgsql' and 'pltcl' for database. But my problem was the language is already installed but getting error on creation of the function. And if any one is creating this function who is not owner of database, this problem occurs. Thanks Dinesh Pandey
Re: [GENERAL] [SQL] Permission denied for language pltclu
Tino Wildenhain wrote: No :-) But if you are able to create databases, you are a superuser :-) And as a superuser you can also create the untrusted functions. Not quite - if you can create USERS you are a superuser. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] How do write a query...
> |From: Alain Reymond [mailto:[EMAIL PROTECTED] > |Sent: Mittwoch, 01. Juni 2005 18:01 > |Subject: [SQL] How do write a query... > | > |I have a table like > |IdNum Date AValue > |1 10 01/01/2005 50 > |2 10 31/05/2005 60 > |3 25 02/02/2005 55 > |4 25 15/03/2005 43 > |5 25 28/05/2005 62 > |etc.. > | > |Id is unique, Num is an identification number with duplicates > possible, > |date is a ... date and Avalue... a value! > | > |If we have > |IdNum Date AValue > |Id1 Num1Date1 AValue1 > |Id2 Num1Date2 AValue2 > | > |The table is ordered on Num+Date. > |What I would like to calculate is (AValue2-AValue1) for a given Num > |(here num1). > | > |In this case, I would have to calculate > |60-50 for Num 10 > |and > |43-55, 62-43 for Num 25. Not sure if this covers all that you want, given your examples (what if there's only one row for a given Num value?), but ... select Num, AValue-AValue1 from Tafel join (select Num, min(AValue) as AValue1 from Tafel group by Num) as T using(Num) where AValue > AValue1 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
