Re: linking postgre to AD

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