Обсуждение: How to made available for a database an already installed language on another database ?
How to made available for a database an already installed language on another database ?
От
Richard Bayet
Дата:
Here's the trick: I had to do a PostgreSQL install as a unix user. That's to say the postmaster process is ran under my login name, and all the database files belong to me. And so, my login name under this system was added (i presume when i launched initdb) in pg_user as a PostgreSQL superuser. I could therefore create my own database, access it, and add plpgsql language support with createlang on my database. I hadn't made any single change to pg_hba.conf, so the "trust" method was prevailing. Now here's the trick: I decided to allow people having their own database on my server. To do so, I create for them a database, and a password file for this database. In pg_hba.conf, I now use the "password passwordfile" authentification method. And the problem is that they can't add any language support with createlang. Anytime they tried (for example '$>createlang plpgsql theirdb'), the program seemed to "freeze" on authentification ... They got a : "$> createlang plpgsql theirdb Password: THEIRPASS Password: THEIRPASS Password: THEIRPASS" I don't think it was an authentification problem because whenever they (or I) put a wrong password, they (or I) got a "Password authentification failed for user 'them'" ... I added myself to the "theirdb" password file, and things got weirder and weirder: now my shell hanged up, and I had to kill. I changed authentification method for "theirdb" to "trust", and tried again. And now i get this: "$>createlang plpgsql theirdb createlang: A function named 'plpgsql_call_handler' already exists. Installation aborted." I tried to figure it out by myself, reading the docs for hours but pfffrrrtt, could'nt get anything valuable. I read that only database superusers can add language support for a database, but eh, aren't I one since i have the usesuper attribute to TRUE in pg_user (template1 database) ? Due to the error report described a few lines above (about the handler that already exists), I'm not sure managing to give a user the superuser status would help in any way ... Thanks for any piece of help ...
I'm under PostgreSQL 7.0.2, and the server is a Sun Enterprise 450 under Solaris 2.5.1 Richard Bayet wrote : Too much to be read a second time
Richard Bayet <bayet@enseirb.fr> writes: > And the problem is that they can't add any language support with > createlang. > Anytime they tried (for example '$>createlang plpgsql theirdb'), the > program seemed to "freeze" on authentification ... > They got a : > "$> createlang plpgsql theirdb > Password: THEIRPASS Password: THEIRPASS Password: THEIRPASS" They just needed to be a little more patient ;-) IIRC, the createlang script invokes psql four or five times, so if you are using password authentication you're going to have to enter the password four or five times. I agree, this isn't particularly user-friendly ... but then, password auth is inherently not user-friendly for something you invoke frequently. You might want to look at the other auth methods. regards, tom lane
Re: How to made available for a database an already installed language on another database ?
От
Richard Bayet
Дата:
Tom Lane a écrit : > Richard Bayet <bayet@enseirb.fr> writes: > > And the problem is that they can't add any language support with > > createlang. > > Anytime they tried (for example '$>createlang plpgsql theirdb'), the > > program seemed to "freeze" on authentification ... > > They got a : > > "$> createlang plpgsql theirdb > > Password: THEIRPASS Password: THEIRPASS Password: THEIRPASS" > > They just needed to be a little more patient ;-) IIRC, the createlang > script invokes psql four or five times, so if you are using password > authentication you're going to have to enter the password four or five > times. > > I agree, this isn't particularly user-friendly ... but then, password > auth is inherently not user-friendly for something you invoke > frequently. You might want to look at the other auth methods. > > regards, tom lane Thanks a lot Tom, that do solves the first part of my problem... Has anyone an idea about the second part, that is, one the authentification passed, this error message: "$>createlang plpgsql theirdb createlang: A function named 'plpgsql_call_handler' already exists. Installation aborted." ? Actually, here's the situation on the base (results from "select * from pg_language") For template1: lanname | lanispl | lanpltrusted | lanplcallfoid | lancompiler ----------+---------+--------------+---------------+------------- internal | f | f | 0 | n/a C | f | f | 0 | /bin/cc sql | f | f | 0 | postgres For theirdb: (the "user" db) lanname | lanispl | lanpltrusted | lanplcallfoid | lancompiler ----------+---------+--------------+---------------+------------- internal | f | f | 0 | n/a C | f | f | 0 | /bin/cc sql | f | f | 0 | postgres For myowndb: (the very first db i created an onto which i installed plpgsql without any trouble) lanname | lanispl | lanpltrusted | lanplcallfoid | lancompiler ----------+---------+--------------+---------------+------------- internal | f | f | 0 | n/a C | f | f | 0 | /bin/cc sql | f | f | 0 | postgres plpgsql | t | t | 56992 | PL/pgSQL Is there any way to "reference" (inherit ?) the plpgsql language installed for myowndb, and thus making it accessible for the other databases ? Isn't there a inherit procedure to make any database inherit template1's installed language ? (I would have to drop plpgsql from myowndb and reinstall it for template1, but there shouldn't be a problem as long as I know the right procedure) Any idea to do this, even with very non user-friendly SQL queries ?
Richard Bayet <bayet@enseirb.fr> writes: > Thanks a lot Tom, that do solves the first part of my problem... > Has anyone an idea about the second part, that is, one the > authentification passed, this error message: > "$>createlang plpgsql theirdb > createlang: A function named 'plpgsql_call_handler' already exists. > Installation aborted." ? Sounds like you had previously let createlang get as far as creating the handler function but not as far as making the pg_language entry. Just drop the function and try again --- or read the createlang script and do the same couple of commands manually, it's not *that* hard ;-) regards, tom lane