Access NEW and OLD from function called by a rule

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
"postgres@OWORD.LOCAL"

--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

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

Frodo Larik <lists@elasto.nl> 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

Hi, thanks for the answer.

Below my comment

-----Messaggio originale-----
Da: Magnus Hagander [mailto:mha@sollentuna.net]
Inviato: venerdì 12 agosto 2005 12.56
A: Ronzani Dario; pgsql-general@postgresql.org
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/onet003@OWORD.LOCAL
2 3 RONZANID/onet003@OWORD.LOCAL

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

Hi Tom,

Tom Lane wrote:

Frodo Larik <lists@elasto.nl> 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

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

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/onet003@OWORD.LOCAL
2 3 RONZANID/onet003@OWORD.LOCAL

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

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