Thread: linking postgre to AD

linking postgre to AD

From
"Jason Tesser"
Date:
I am looking into using PostgreSQL on the backend here.  I work for a Bible College.  We are putting together a
new software package.  The database would be on a LInux box but our domain is M$ so we use active
directory.  I saw that there is a PAM module for Postgre.  My question is with applications that will both be
standalone and web what is teh best way for me to authenticate to active directory.  C

Is there a Postgre function that will allow me to do this?
Does anyone have any other ideas that will work?

Thank you in advance for any feedback.

Jason Tesser
Web/Multimedia Programmer
Northland Ministries Inc.
(715)324-6900 x3050


Re: linking postgre to AD

From
Alex Page
Date:
On Fri, Nov 21, 2003 at 06:27:01AM -0600, Jason Tesser wrote:

> I am looking into using PostgreSQL on the backend here.  I work for a Bible College.  We are putting together a
> new software package.  The database would be on a LInux box but our domain is M$ so we use active
> directory.  I saw that there is a PAM module for Postgre.  My question is with applications that will both be
> standalone and web what is teh best way for me to authenticate to active directory.  C

> Is there a Postgre function that will allow me to do this?
> Does anyone have any other ideas that will work?

I'm not quite clear what you want to do. If you want to authenticate
against an Active Directory server, then you need to talk LDAP to it,
probably using the PAM LDAP module. There's a fair amount of
documentation on this out on the interweb.

If you want to use AD for client authentication in Postgres, section
6.2.5 of the Administrator's Guide details how to use PAM for Postgres
client authentication.

Then again, I've not tried this myself...

Dave
--
Mail: Alex Page <alex.page@cancer.org.uk>
Real: Systems/Network Assistant, Epidemiology Unit, Oxford
Tel:  01865 302 223 (external) / 223 (internal)
PGP:  8868 21D7 3D35 DD77 9D06  BF0A 0746 2DE6 55EA 367E

Attachment

Re: linking postgre to AD

From
"Jason Tesser"
Date:
Hi,

On Fri, Nov 21, 2003 at 06:27:01AM -0600, Jason Tesser wrote:

> I am looking into using PostgreSQL on the backend here.  I work for a Bible College.  We are putting together a
> new software package.  The database would be on a LInux box but our domain is M$ so we use active
> directory.  I saw that there is a PAM module for Postgre.  My question is with applications that will both be
> standalone and web what is teh best way for me to authenticate to active directory.  C

> Is there a Postgre function that will allow me to do this?
> Does anyone have any other ideas that will work?

> I'm not quite clear what you want to do. If you want to authenticate
> against an Active Directory server, then you need to talk LDAP to it,
> probably using the PAM LDAP module. There's a fair amount of
> documentation on this out on the interweb.

I want to make my standalone apps and my web apps authenticate through AD, but
keep any access levels (groups) in Postgre

> If you want to use AD for client authentication in Postgres, section
> 6.2.5 of the Administrator's Guide details how to use PAM for Postgres
> client authentication.

If I did that could I use a table in Postgre as a groups table so I can control
who can go where or who can see what in a web app?  I was wondering if Postgre
had some built in functions that could help me out here?

Re: linking postgre to AD

From
Bruno Wolff III
Date:
On Fri, Nov 21, 2003 at 07:13:46 -0600,
  Jason Tesser <JTesser@nbbc.edu> wrote:
>
> If I did that could I use a table in Postgre as a groups table so I can control
> who can go where or who can see what in a web app?  I was wondering if Postgre
> had some built in functions that could help me out here?

You can use groups to control access to objects in postgres.
For role level security you probably want to make your own group
tables and use views joining the groups table for rows matching the current
user, with the tables containing the tuples of interest to control access.

Re: linking postgre to AD

From
Mike Mascari
Date:
Jason Tesser wrote:

> I saw that there is a PAM module for Postgre.

Just out of curiosity, where did you get the notion that the shorthand
for PostgreSQL is "Postgre" and not "Postgres" ?

Marc, would it be possible to have a TIP that reads:

"Postgres + SQL = PostgreSQL"

In fact, could you use the above in replacement of this one:

---|
   V

> TIP 4: Don't 'kill -9' the postmaster

Mike Mascari
mascarm@mascari.com






Re: linking postgre to AD

From
"Jason Tesser"
Date:
Sorry guys about leaving the s off but what I am really curious about is the
AD stuff :-)

-----Original Message-----
From: Mike Mascari [mailto:mascarm@mascari.com]
Sent: Friday, November 21, 2003 11:08 AM
To: Jason Tesser
Cc: Pgsql (E-mail); scrappy@postgresql.org
Subject: Re: [GENERAL] linking postgre to AD


Jason Tesser wrote:

> I saw that there is a PAM module for Postgre.

Just out of curiosity, where did you get the notion that the shorthand
for PostgreSQL is "Postgre" and not "Postgres" ?

Marc, would it be possible to have a TIP that reads:

"Postgres + SQL = PostgreSQL"

In fact, could you use the above in replacement of this one:

---|
   V

> TIP 4: Don't 'kill -9' the postmaster

Mike Mascari
mascarm@mascari.com






Re: linking postgre to AD

From
Mike Mascari
Date:
Jason Tesser wrote:

> Sorry guys about leaving the s off but what I am really curious about is the
> AD stuff :-)

No problem. I was actually being serious with my inquiry though. Did
you happen to see it in a publication or hear people pronounce it that
way? We've seen it on the list growing in number. Apple's site
references 'postgre' instead of 'postgres'.

I just worry about the fact that there is some confusion out there on
just what the vernacular of "PostgreSQL" is. Confusion causes people
to simply not mention it in casual conversion. I remember how Linux
was in the early nineties. The PostgreSQL Propaganda Minister should
be on top of this issue...

As far as AD is concerned, I think Bruno Wolff described what to do best:

1) Configure PostgreSQL to authenticate via PAM (pam_ldap)

2) Create a table of appgroups & groupmembers that defines the
application groups and their members, respectively

3) Create views over the actual data that test for the appropriate
group membership.

You can write your own function to simply the task:

CREATE FUNCTION isMember(text, text) RETURNS bool AS '

 SELECT true
 FROM appgroups, groupmembers
 WHERE appgroups.name = $1 AND
 appgroups.appgroup = groupmembers.appgroup AND
 groupmembers.userid = $2;

' LANGUAGE 'sql';

4) Now, if you only want people in the 'Administration' group to view
salaries:

CREATE VIEW v_salaries AS
SELECT *
FROM salaries
WHERE isMember('Administration', CURRENT_USER);

Or you could do the join against the base table for row-level security:

CREATE VIEW v_salaries AS
SELECT *
FROM salaries, groupmembers
WHERE salaries.appgroup = groupmembers.appgroup AND
groupmembers.userid = CURRENT_USER;

5) REVOKE SELECT on salaries from the public and GRANT select on
v_salaries to the public.

HTH,

There is a security problem here if users are able to create their own
functions:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=3D02B372.B6A4EFB6%40mascari.com

HTH,

Mike Mascari
mascarm@mascari.com



Re: linking postgre to AD

From
"Jason Tesser"
Date:
ok so what would prevent users from using SQL tools to connect to the database
and browse data?

> As far as AD is concerned, I think Bruno Wolff described what to do best:

> 1) Configure PostgreSQL to authenticate via PAM (pam_ldap)

> 2) Create a table of appgroups & groupmembers that defines the
application groups and their members, respectively

> 3) Create views over the actual data that test for the appropriate
group membership.

> You can write your own function to simply the task:

> CREATE FUNCTION isMember(text, text) RETURNS bool AS '

 SELECT true
 FROM appgroups, groupmembers
 WHERE appgroups.name = $1 AND
 appgroups.appgroup = groupmembers.appgroup AND
 groupmembers.userid = $2;

' LANGUAGE 'sql';

> 4) Now, if you only want people in the 'Administration' group to view
salaries:

CREATE VIEW v_salaries AS
SELECT *
FROM salaries
WHERE isMember('Administration', CURRENT_USER);

> Or you could do the join against the base table for row-level security:

CREATE VIEW v_salaries AS
SELECT *
FROM salaries, groupmembers
WHERE salaries.appgroup = groupmembers.appgroup AND
groupmembers.userid = CURRENT_USER;

> 5) REVOKE SELECT on salaries from the public and GRANT select on
v_salaries to the public.

HTH,

There is a security problem here if users are able to create their own
functions:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=3D02B372.B6A4EFB6%40mascari.com

HTH,

Mike Mascari
mascarm@mascari.com



--
http://mail.python.org/mailman/listinfo/python-list

Re: linking postgre to AD

From
Mike Mascari
Date:
Jason Tesser wrote:

> ok so what would prevent users from using SQL tools to connect to the database
> and browse data?

#5:

"REVOKE SELECT on salaries from the public and GRANT select on
v_salaries to the public."

Since CURRENT_USER is set at connection time, and since they will only
be able to SELECT from the views, they can only see what they're
supposed to see...

Mike Mascari
mascarm@mascari.com



Re: linking postgre to AD

From
Bruno Wolff III
Date:
On Fri, Nov 21, 2003 at 14:01:08 -0600,
  Jason Tesser <JTesser@nbbc.edu> wrote:
> ok so what would prevent users from using SQL tools to connect to the database
> and browse data?

You only give them access to the views, not the base tables.