Access NEW and OLD from function called by a rule

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Access NEW and OLD from function called by a rule

Frodo Larik
Hello,

I was wondering if it was possible to get a hold of the NEW and OLD
variables available in a Rule and pass them to a function? Maybe there
is another (better) way of accomplishing what I try to do, so I'll
sketch you my testing layout:


CREATE TABLE clients (
   id SERIAL PRIMARY KEY,
   name text
);


CREATE TABLE persons  (
   id SERIAL PRIMARY KEY,
   first_name text,
   last_name text
);

CREATE TABLE t_workers (
   id SERIAL PRIMARY KEY,
   person_id integer REFERENCES persons(id),
   client_id integer REFERENCES clients(id)
);


CREATE TABLE t_contacts (
   id SERIAL PRIMARY KEY,
   person_id integer REFERENCES persons(id),
   client_id integer REFERENCES clients(id)
);

-- view containing all worker data
CREATE VIEW workers
AS
   SELECT w.*, p.first_name, p.last_name FROM t_workers AS w
   INNER JOIN persons AS p ON ( w.person_id = p.id );

Now for inserting data in  the workers view I created a rule:

CREATE OR REPLACE RULE insert_worker AS ON INSERT TO workers DO INSTEAD (
   INSERT INTO persons ( first_name, last_name )
      VALUES ( NEW.first_name, NEW.last_name );
 
   INSERT INTO t_workers ( person_id, client_id )
      VALUES ( currval('persons_id_seq'), NEW.client_id );
);

This works. Then I also have a t_contacts table where I want do the same
with, I create a view called contacts and a rule called insert_contact.  
Later on I will be having more views containing data from persons. So I
thought I could make some kind of macro of the "INSERT INTO persons .."
part. I saw PostgreSQL has support for CREATE FUNCTION (something I'm
not really familiar with). This is what I wanted to do:

I create a FUNCTION to insert data into persons:

CREATE OR REPLACE FUNCTION insert_person() RETURNS OPAQUE AS '
   BEGIN
      INSERT INTO persons ( first_name, last_name )
         VALUES ( NEW.first_name, NEW.last_name );
      RETURN NULL;
   END
' LANGUAGE 'plpgsql';

And I will call the FUNCTION from the isnert_worker RULE

CREATE OR REPLACE RULE insert_worker AS ON INSERT TO workers DO INSTEAD (
  SELECT insert_person();
 
   INSERT INTO t_workers ( person_id, client_id )
      VALUES ( currval('persons_id_seq'), NEW.client_id );
);

If I try to insert data into workers, the following happens:

test_db=# insert into workers ( first_name, last_name ) VALUES ( 'John',
'Doe');
ERROR:  record "new" is not assigned yet
DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT:  PL/pgSQL function "insert_person" line 2 at SQL statement

PostgreSQL obviously complains about NEW not available, how can I make
it available? Is this the way to do it?


Sincerely,

Frodo Larik

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Linux Postgres authentication against active directory

Ronzani Dario
Hi to all,

Actually I try to authenticate my Linux Postgres installation against Active
Directory, I find 3 solution to use:

1) LDAP
2) Pam and Kerberos
3) Kerberos alone

The first require the modification of the active directory schema, and I
prefer to avoid such responsibility.

For the 2 kerberos solution I don't find to much documentation, I try to
compile postgres with kerberos using this configure flag:

        --with-krb5=/usr/
        --with-includes=/usr/include/
        --with-libraries=/usr/lib/
       
        My kerberos installation is in
        /usr/bin and /usr/sbin for the binary
        /usr/lib/ libkrb5.* e libk5crypto.so
and libkadm5
        /usr/include/ krb5.h heaser file

But seems that I miss something because when I put the krb5 word in the
pg_hba.conf and I try to connect the system give me the error:

psql -U postgres -d template1 -h 192.168.0.205

psql: Kerberos 5 authentication failed

--from the system log--
postgres[26793]: [2-1] LOG:  Kerberos recvauth returned error 103
postgres[26793]: [3-1] FATAL:  Kerberos5 authentication failed for user
"postgres"
postgres[26795]: [2-1] LOG:  Kerberos recvauth returned error 103
postgres[26795]: [3-1] FATAL:  Kerberos5 authentication failed for user
"[hidden email]"

--from the postgres log--
postgres: Software caused connection abort from krb5_recvauth


I also create with KTPASS a principal for the windows user POSTGRES and put
it in the keytab file that the configure script search for.

The kinit utility work well for any user I try to use.


After this not enthusiastics result I try with PAM and postgres (I just have
another installation that work well with pam-ldap), and sounds good, now I'm
able to authenticate the postgres user but not my and other user.

--system log--
postgres[26991]: pam_krb5: pam_sm_authenticate(postgresql ronzanid): entry:
postgres[26991]: pam_krb5: pam_sm_authenticate(postgresql ronzanid):
get_user_info(): Conversation error
postgres[26991]: pam_krb5: pam_sm_authenticate(postgresql ronzanid): exit:
failure
postgres[26991]: [2-1] LOG:  pam_authenticate failed: Error in service
module
postgres[26991]: [3-1] FATAL:  PAM authentication failed for user "ronzanid"
postgres[26992]: pam_krb5: pam_sm_authenticate(postgresql ronzanid): entry:
postgres[26992]: pam_krb5: pam_sm_authenticate(postgresql ronzanid):
getpwnam():
postgres[26992]: pam_krb5: pam_sm_authenticate(postgresql ronzanid): exit:
failure
postgres[26992]: [2-1] LOG:  pam_authenticate failed: User not known to the
underlying authentication module
postgres[26992]: [3-1] FATAL:  PAM authentication failed for user "ronzanid"

I hope someone can help, any hint, useful web pages, or documentation is
very appreciate.

Thanks in advance
Dario



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: Linux Postgres authentication against active directory

Magnus Hagander
> Hi to all,
>
> Actually I try to authenticate my Linux Postgres installation
> against Active Directory, I find 3 solution to use:
>
> 1) LDAP
> 2) Pam and Kerberos
> 3) Kerberos alone

(3) is the one I've been using, and it works very well. I've been
working on a HOWTO, but it' snot done yet.

Note that if your clients are on win32, you need at least version 8.0.2.


> For the 2 kerberos solution I don't find to much
> documentation, I try to compile postgres with kerberos using
> this configure flag:
>
> --with-krb5=/usr/
> --with-includes=/usr/include/
> --with-libraries=/usr/lib/
>
> My kerberos installation is in
> /usr/bin and /usr/sbin for the binary
> /usr/lib/ libkrb5.* e
> libk5crypto.so
> and libkadm5
> /usr/include/ krb5.h heaser file
>
> But seems that I miss something because when I put the krb5
> word in the pg_hba.conf and I try to connect the system give
> me the error:

For AD to do proper interop, the SPN has to be in uppercase. For this
you need to recompile postgresql using --with-krbsrvnam=POSTGRES on both
client and server. (You will be able to change this at runtime in 8.1)

Naturally you will have to recreate the service account in AD with the
proper SPN.


//Magnus

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: Access NEW and OLD from function called by a rule

Tom Lane-2
In reply to this post by Frodo Larik
Frodo Larik <[hidden email]> writes:
> PostgreSQL obviously complains about NEW not available, how can I make
> it available? Is this the way to do it?

No.  You seem to have read something about trigger functions, but this
usage is not a trigger function.  You need to do it more like this:

regression=# CREATE OR REPLACE FUNCTION insert_person(workers) returns void as $$
regression$# begin
regression$#   INSERT INTO persons ( first_name, last_name )
regression$#      VALUES ( $1.first_name, $1.last_name );
regression$# end$$ language plpgsql;
CREATE FUNCTION
regression=# CREATE OR REPLACE RULE insert_worker AS ON INSERT TO workers DO INSTEAD (
regression(# SELECT insert_person(new.*);
regression(# INSERT INTO t_workers ( person_id, client_id )
regression(# VALUES ( currval('persons_id_seq'), NEW.client_id );
regression(# );
CREATE RULE
regression=# insert into workers ( first_name, last_name ) VALUES ( 'John', 'Doe');
 insert_person
---------------

(1 row)

regression=#

The extra SELECT result is a bit annoying --- you could maybe hide that
by invoking the function within the rule INSERT, say by having it return
the inserted persons id.

I think passing "new.*" to a function from a rule works since about 7.4
or so.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

R: Linux Postgres authentication against active directory

Ronzani Dario
In reply to this post by Magnus Hagander
Hi, thanks for the answer.

Below my comment

> -----Messaggio originale-----
> Da: Magnus Hagander [mailto:[hidden email]]
> Inviato: venerdì 12 agosto 2005 12.56
> A: Ronzani Dario; [hidden email]
> Oggetto: RE: [GENERAL] Linux Postgres authentication against
> active directory
>
>
> > Hi to all,
> >
> > Actually I try to authenticate my Linux Postgres installation
> > against Active Directory, I find 3 solution to use:
> >
> > 1) LDAP
> > 2) Pam and Kerberos
> > 3) Kerberos alone
>
> (3) is the one I've been using, and it works very well. I've
> been working on a HOWTO, but it' snot done yet.
>
> Note that if your clients are on win32, you need at least
> version 8.0.2.

Great to know that someone are able to use this solution, I don't have any
client my application is a web (php, java) application with a request for AD
(or ldap depend on the customer) users authentication.

>
>
> > For the 2 kerberos solution I don't find to much
> > documentation, I try to compile postgres with kerberos using
> > this configure flag:
> >
> > --with-krb5=/usr/
> > --with-includes=/usr/include/
> > --with-libraries=/usr/lib/
> >
> > My kerberos installation is in
> > /usr/bin and /usr/sbin for the binary
> > /usr/lib/ libkrb5.* e
> > libk5crypto.so
> > and libkadm5
> > /usr/include/ krb5.h heaser file
> >
> > But seems that I miss something because when I put the krb5
> > word in the pg_hba.conf and I try to connect the system give
> > me the error:
>
> For AD to do proper interop, the SPN has to be in uppercase.
> For this you need to recompile postgresql using
> --with-krbsrvnam=POSTGRES on both client and server. (You
> will be able to change this at runtime in 8.1)
>
> Naturally you will have to recreate the service account in AD
> with the proper SPN.

My fear is that I have misunderstood how kerberes work, I understand that I
must to kerberize the postgres application to give it the access to AD, then
I need to put a principal to any user that need to authenticate?

This is my principal on the linux box, as you say (I think) I create this
principal in uppercase:

        ktutil:  rkt /usr/etc/postgresql/krb5.keytab
        ktutil:  list
        slot KVNO Principal
        ---- ----
---------------------------------------------------------------------
           1    3             POSTGRES/[hidden email]
           2    3             RONZANID/[hidden email]


May be my problem was related with this compilation line?

        checking for library containing com_err... -lkrb5
        checking for library containing krb5_encrypt... none required
        checking for library containing krb5_sendauth... none required
        checking krb5.h usability... yes
        checking krb5.h presence... yes
        checking for krb5.h... yes
        checking for krb5_ticket.enc_part2... yes
        checking for krb5_error.text.data... yes

Particularly I refer to krb5_encrypt and krb5_sendauth.

With more debug I received this error when I try to authenticate.

        postgres: Bad application version was sent (via sendauth) from
krb5_recvauth

Thanks Dario

>
>
> //Magnus
>


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: Access NEW and OLD from function called by a rule

Frodo Larik
In reply to this post by Tom Lane-2
Hi Tom,

Tom Lane wrote:

>Frodo Larik <[hidden email]> writes:
>  
>
>>PostgreSQL obviously complains about NEW not available, how can I make
>>it available? Is this the way to do it?
>>    
>>
>
>No.  You seem to have read something about trigger functions, but this
>usage is not a trigger function.  You need to do it more like this:
>
>regression=# CREATE OR REPLACE FUNCTION insert_person(workers) returns void as $$
>regression$# begin
>regression$#   INSERT INTO persons ( first_name, last_name )
>regression$#      VALUES ( $1.first_name, $1.last_name );
>regression$# end$$ language plpgsql;
>CREATE FUNCTION
>regression=# CREATE OR REPLACE RULE insert_worker AS ON INSERT TO workers DO INSTEAD (
>regression(# SELECT insert_person(new.*);
>regression(# INSERT INTO t_workers ( person_id, client_id )
>regression(# VALUES ( currval('persons_id_seq'), NEW.client_id );
>regression(# );
>CREATE RULE
>regression=# insert into workers ( first_name, last_name ) VALUES ( 'John', 'Doe');
> insert_person
>---------------
>
>(1 row)
>
>regression=#
>  
>
Thanks for tips! It works, but it seems I have to rewrite this function
for every rule??

I wanted to make te function more generic,after doing this I understand
that the argument of insert_person(workers) is a table/view name:

test_db=# CREATE OR REPLACE FUNCTION insert_person(persons) RETURNS
integer AS '
test_db'#    BEGIN
test_db'#       INSERT INTO persons ( first_name, last_name )
test_db'#          VALUES ( $1.first_name, $1.last_name );
test_db'#       RETURN currval(''persons_id_seq'');
test_db'#    END
test_db'# ' LANGUAGE 'plpgsql';
CREATE FUNCTION
test_db=#
test_db=# CREATE OR REPLACE RULE insert_worker AS ON INSERT TO workers
DO INSTEAD (
test_db(#   SELECT insert_person(new.*) AS person_id;
test_db(#  
test_db(#    INSERT INTO t_workers ( person_id, client_id )
test_db(#       VALUES ( currval('persons_id_seq'), NEW.client_id );
test_db(# );
ERROR:  function insert_person(workers) does not exist
HINT:  No function matches the given name and argument types. You may
need to add explicit type casts.

That means I have to create functions like insert_person(workers) ,  
insert_person(othertable) and function insert_person(anothertable).

Suggestions?

Sincerely,

Frodo Larik


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: Access NEW and OLD from function called by a rule

Frodo Larik
Frodo Larik wrote:

> That means I have to create functions like insert_person(workers) ,  
> insert_person(othertable) and function insert_person(anothertable).

I found the solution to this "problem". Create a function with a
Polymorphic Type (notice the anyelement):

CREATE OR REPLACE FUNCTION insert_person(anyelement) RETURNS integer AS $$
   BEGIN
      INSERT INTO persons ( first_name, last_name )
         VALUES ( $1.first_name, $1.last_name );
      RETURN currval('persons_id_seq');
   END
$$ LANGUAGE 'plpgsql';


more infor here:
http://www.postgresql.org/docs/8.0/interactive/extend-type-system.html#EXTEND-TYPES-POLYMORPHIC

sincerely,

Frodo Larik

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

Re: Linux Postgres authentication against active directory

Magnus Hagander
In reply to this post by Ronzani Dario
> > > Actually I try to authenticate my Linux Postgres installation
> > > against Active Directory, I find 3 solution to use:
> > >
> > > 1) LDAP
> > > 2) Pam and Kerberos
> > > 3) Kerberos alone
> >
> > (3) is the one I've been using, and it works very well. I've been
> > working on a HOWTO, but it' snot done yet.
> >
> > Note that if your clients are on win32, you need at least version
> > 8.0.2.
>
> Great to know that someone are able to use this solution, I
> don't have any client my application is a web (php, java)
> application with a request for AD (or ldap depend on the
> customer) users authentication.

The easiest way around that is to authenticate the user to the
webserver, and then use a single account to connect to the database (or
a couple, depending on group membership, or whatever suits your app).


> > Naturally you will have to recreate the service account in
> AD with the
> > proper SPN.
>
> My fear is that I have misunderstood how kerberes work, I
> understand that I must to kerberize the postgres application
> to give it the access to AD, then I need to put a principal
> to any user that need to authenticate?

No, you do not need to kerberize postgres to access AD. If you want to
"check ad passwords", it's porbably best to use LDAP.

Kerberos is used to achieve *single sign on*. Meaning your app never
sees the password. If this is not what you need, it's overly complex.

Any user who needs to authenticate needs a principal, yes. That's their
normal Windows account. On top of that, every *service* the user should
authenticate *to* also needs a principal - for mutual authentication.

If your client connects to your wbserver only, your webserver needs this
principal. If the clients connects to the database server, the database
server needs it. There is apparantly some way if you use mod_perl to
forward kerberos credentials from the webserver to the database server,
but I don't know any details about that.

But again, if you intend to provide a userid/password box to the user
and then authenticate those credentials, it's going to be a lot easier
to use for example LDAP.

You can, of course, use kerberos between your webserver and the database
server, but that's most likely an overkill as you'll only be able to
access it as a single user anyway (I think).


> This is my principal on the linux box, as you say (I think) I
> create this principal in uppercase:
>
> ktutil:  rkt /usr/etc/postgresql/krb5.keytab
> ktutil:  list
> slot KVNO Principal
> ---- ----
> ---------------------------------------------------------------------
>   1    3             POSTGRES/[hidden email]
>   2    3             RONZANID/[hidden email]

The postgres keytab only needs the POSTGRES principal. The other is your
user (I assume), and it sohuld not be stored *anywhere* - only
transitent whlie you are logged in.


> May be my problem was related with this compilation line?
>
> checking for library containing com_err... -lkrb5
> checking for library containing krb5_encrypt... none required
> checking for library containing krb5_sendauth... none required
> checking krb5.h usability... yes
> checking krb5.h presence... yes
> checking for krb5.h... yes
> checking for krb5_ticket.enc_part2... yes
> checking for krb5_error.text.data... yes
>
> Particularly I refer to krb5_encrypt and krb5_sendauth.

No, that looks correct. It jus tmeans that they were found in the krb5
library, and no *additional* libraries are needed. If you were missing
the rquired libs, you'd get an error and not just a notice.


> With more debug I received this error when I try to authenticate.
>
> postgres: Bad application version was sent (via
> sendauth) from krb5_recvauth

That probably indicates that your server has a different principal name
than the client (libpq library).


//Magnus

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

R: Linux Postgres authentication against active directory

Ronzani Dario
Finaly I follow your suggestion, I use ldap and seems to work (for now).
Thanks for your help
dario

> -----Messaggio originale-----
> Da: [hidden email]
> [mailto:[hidden email]] Per conto di
> Magnus Hagander
> Inviato: lunedì 15 agosto 2005 21.01
> A: Ronzani Dario; [hidden email]
> Oggetto: Re: [GENERAL] Linux Postgres authentication against
> active directory
>
>
> > > > Actually I try to authenticate my Linux Postgres installation
> > > > against Active Directory, I find 3 solution to use:
> > > >
> > > > 1) LDAP
> > > > 2) Pam and Kerberos
> > > > 3) Kerberos alone
> > >
> > > (3) is the one I've been using, and it works very well. I've been
> > > working on a HOWTO, but it' snot done yet.
> > >
> > > Note that if your clients are on win32, you need at least version
> > > 8.0.2.
> >
> > Great to know that someone are able to use this solution, I
> > don't have any client my application is a web (php, java)
> > application with a request for AD (or ldap depend on the
> > customer) users authentication.
>
> The easiest way around that is to authenticate the user to
> the webserver, and then use a single account to connect to
> the database (or a couple, depending on group membership, or
> whatever suits your app).
>
>
> > > Naturally you will have to recreate the service account in
> > AD with the
> > > proper SPN.
> >
> > My fear is that I have misunderstood how kerberes work, I
> > understand that I must to kerberize the postgres application
> > to give it the access to AD, then I need to put a principal
> > to any user that need to authenticate?
>
> No, you do not need to kerberize postgres to access AD. If
> you want to "check ad passwords", it's porbably best to use LDAP.
>
> Kerberos is used to achieve *single sign on*. Meaning your
> app never sees the password. If this is not what you need,
> it's overly complex.
>
> Any user who needs to authenticate needs a principal, yes.
> That's their normal Windows account. On top of that, every
> *service* the user should authenticate *to* also needs a
> principal - for mutual authentication.
>
> If your client connects to your wbserver only, your webserver
> needs this principal. If the clients connects to the database
> server, the database server needs it. There is apparantly
> some way if you use mod_perl to forward kerberos credentials
> from the webserver to the database server, but I don't know
> any details about that.
>
> But again, if you intend to provide a userid/password box to
> the user and then authenticate those credentials, it's going
> to be a lot easier to use for example LDAP.
>
> You can, of course, use kerberos between your webserver and
> the database server, but that's most likely an overkill as
> you'll only be able to access it as a single user anyway (I think).
>
>
> > This is my principal on the linux box, as you say (I think) I
> > create this principal in uppercase:
> >
> > ktutil:  rkt /usr/etc/postgresql/krb5.keytab
> > ktutil:  list
> > slot KVNO Principal
> > ---- ----
> >
> ---------------------------------------------------------------------
> >   1    3             POSTGRES/[hidden email]
> >   2    3             RONZANID/[hidden email]
>
> The postgres keytab only needs the POSTGRES principal. The
> other is your user (I assume), and it sohuld not be stored
> *anywhere* - only transitent whlie you are logged in.
>
>
> > May be my problem was related with this compilation line?
> >
> > checking for library containing com_err... -lkrb5
> > checking for library containing krb5_encrypt... none required
> > checking for library containing krb5_sendauth... none required
> > checking krb5.h usability... yes
> > checking krb5.h presence... yes
> > checking for krb5.h... yes
> > checking for krb5_ticket.enc_part2... yes
> > checking for krb5_error.text.data... yes
> >
> > Particularly I refer to krb5_encrypt and krb5_sendauth.
>
> No, that looks correct. It jus tmeans that they were found in
> the krb5 library, and no *additional* libraries are needed.
> If you were missing the rquired libs, you'd get an error and
> not just a notice.
>
>
> > With more debug I received this error when I try to authenticate.
> >
> > postgres: Bad application version was sent (via
> > sendauth) from krb5_recvauth
>
> That probably indicates that your server has a different
> principal name than the client (libpq library).
>
>
> //Magnus
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq