Обсуждение: 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
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
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:grzm@myrealbox.com]
Sent: Friday, June 10, 2005 12:58 PM
To: dpandey@secf.com
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
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
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
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
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
> 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.
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: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Richard Huxton
Sent: Friday, June 10, 2005 2:41 PM
To: dpandey@secf.com
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
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
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
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
On Jun 10, 2005, at 5:51 AM, 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 Dinesh, The user creating the function must be a superuser. The question to ask is: How do I make a user a superuser? The answer is in the documentation at these two links. It suffices to ALTER USER to have CREATEUSER privileges to be a superuser. http://www.postgresql.org/docs/8.0/static/user-attributes.html http://www.postgresql.org/docs/8.0/static/sql-alteruser.html I hope this clarifies things a bit. Sean
Am Freitag, den 10.06.2005, 15:21 +0530 schrieb Dinesh Pandey: > 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 > No :-) But if you are able to create databases, you are a superuser :-) And as a superuser you can also create the untrusted functions. Please read a bit in the documentation - when you manage databases for a customer you have some responsibilities and should know your tools.
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
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
Am Freitag, den 10.06.2005, 11:51 +0100 schrieb Richard Huxton: > 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. Yes I finally found out ;) It was one of these 2 privilegues... -- Tino Wildenhain <tino@wildenhain.de>
Am Freitag, den 10.06.2005, 16:15 +0530 schrieb Dinesh Pandey: > 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. > No, thats only a coincidence. Check the docs pretty please :-) And try to send mails to mailinglist in plaintext. -- Tino Wildenhain <tino@wildenhain.de>
Hi All! I have table: CREATE TABLE table1 ( ip char(15) NOT NULL, hits integer NOT NULL default '1', PRIMARY KEY (ip) ); So it's counting hits per each IP for current day and every day trancated by cron: TRUNCATE TABLE table1; before inserting or updating this table there're some checkings, logs, etc., so I'm using PL/PgSQL for that after all checkings and logs I have: UPDATE table1 SET hits = hits + 1 WHERE ip = some_ip; IF NOT FOUND THEN INSERT INTO table1 (ip) VALUES (some_ip); END IF; when IP is not found in table it inserts new record into table but in logs i see error ERROR: duplicate key violates unique constraint "table1" CONTEXT: PL/pgSQL function "insert_table1" line 68 at SQL statement But record is inserted into table what may be the problem? i also tried before: SELECT INTO cnt hits FROM table1 WHERE ip = some_ip; IF FOUND THEN UPDATE table1 SET hits = hits + 1 WHERE ip = some_ip; ELSE INSERT INTO table1 (ip) VALUES (some_ip); END IF; But same error still appears Thank You
Your problem is that the trigger's "found" check will not see the row inserted by a concurrent transaction. In other words, your insert actually fails, the record what you see was inserted by another concurrent transaction, and the "found" check didn't work because the other transaction started after yours, but was quicker, and your transaction can't see it's results. What you try to do is similar to the "insert-or-update" thing, which cannot be done safely in the way you tried to do it. Don't even bother to try, there are lots of discussions on the list and the conclusion is you can't avoid a race condition between the concurrent inserts. There always will be a way one of them will fail with an error. You could actually ignore the error if it's not part of a bigger transaction, which would of course be broken by the error. Your only way to avoid the error completely is to place a save point before the insert, catch the error, and roll back to the save point, and then continue your transaction as you need. HTH, Csaba. On Mon, 2005-06-13 at 18:22, ON.KG wrote: > Hi All! > > I have table: > > CREATE TABLE table1 ( > ip char(15) NOT NULL, > hits integer NOT NULL default '1', > PRIMARY KEY (ip) > ); > > So it's counting hits per each IP for current day and every day > trancated by cron: > TRUNCATE TABLE table1; > > before inserting or updating this table there're some checkings, > logs, etc., so I'm using PL/PgSQL for that > > after all checkings and logs I have: > > UPDATE table1 > SET hits = hits + 1 > WHERE ip = some_ip; > > IF NOT FOUND THEN > INSERT INTO table1 > (ip) > VALUES > (some_ip); > END IF; > > when IP is not found in table it inserts new record into table > but in logs i see error > ERROR: duplicate key violates unique constraint "table1" > CONTEXT: PL/pgSQL function "insert_table1" line 68 at SQL statement > > But record is inserted into table > > what may be the problem? > > i also tried before: > SELECT INTO cnt hits > FROM table1 > WHERE ip = some_ip; > > IF FOUND THEN > UPDATE table1 > SET hits = hits + 1 > WHERE ip = some_ip; > ELSE > INSERT INTO table1 > (ip) > VALUES > (some_ip); > END IF; > > But same error still appears > > Thank You > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
ON.KG wrote: > > before inserting or updating this table there're some checkings, > logs, etc., so I'm using PL/PgSQL for that > > after all checkings and logs I have: > > UPDATE table1 > SET hits = hits + 1 > WHERE ip = some_ip; > > IF NOT FOUND THEN > INSERT INTO table1 > (ip) > VALUES > (some_ip); > END IF; > > when IP is not found in table it inserts new record into table > but in logs i see error > ERROR: duplicate key violates unique constraint "table1" > CONTEXT: PL/pgSQL function "insert_table1" line 68 at SQL statement If you can have more than one client running this at once you have a race condition here. The order runs something like: 1. client A tries to update SOME_IP, no rows affected 2. client B tries to update SOME_IP, no rows affected 3. client A tries the insert of SOME_IP 4. client B tries the insert of SOME_IP - fails! If you have more than one client, this can always happen. You have two choices: 1. Use a lock to stop two clients interacting like this 2. Catch the error on the insert and try the update again. This requires version 8.0 or higher. -- Richard Huxton Archonet Ltd
[snip] > If you have more than one client, this can always happen. You have two > choices: > 1. Use a lock to stop two clients interacting like this This won't work unless you make all the clients serialized, or you have all the ip's already inserted in the data base... you can't lock on an unknown key, otherwise the locking will also need to insert, and you're back to the same race condition ;-) Cheers, Csaba.
Csaba Nagy wrote: > [snip] > >>If you have more than one client, this can always happen. You have two >>choices: >> 1. Use a lock to stop two clients interacting like this > > > This won't work unless you make all the clients serialized, or you have > all the ip's already inserted in the data base... you can't lock on an > unknown key, otherwise the locking will also need to insert, and you're > back to the same race condition ;-) You can, however, have something more finely-grained than whole-table locking (assuming one IP updated/inserted at a time) by filling a dummy table with e.g. integers 0..255 and locking a row there based on (e.g.) the last octet of your target IP. -- Richard Huxton Archonet Ltd
That would work indeed. Bit I guess the savepoint solution will be the simplest and fastest if the OP has or can install 8.0 version. Cheers, Csaba. On Mon, 2005-06-13 at 17:49, Richard Huxton wrote: > Csaba Nagy wrote: > > [snip] > > > >>If you have more than one client, this can always happen. You have two > >>choices: > >> 1. Use a lock to stop two clients interacting like this > > > > > > This won't work unless you make all the clients serialized, or you have > > all the ip's already inserted in the data base... you can't lock on an > > unknown key, otherwise the locking will also need to insert, and you're > > back to the same race condition ;-) > > You can, however, have something more finely-grained than whole-table > locking (assuming one IP updated/inserted at a time) by filling a dummy > table with e.g. integers 0..255 and locking a row there based on (e.g.) > the last octet of your target IP. > > -- > Richard Huxton > Archonet Ltd
Csaba Nagy wrote: > That would work indeed. Bit I guess the savepoint solution will be the > simplest and fastest if the OP has or can install 8.0 version. I'd say so. Otherwise you'll just sit on the lock, and then still have to deal with an error later anyway when the lock times out. -- Richard Huxton Archonet Ltd