Re: linking postgre to AD - Mailing list pgsql-general

From Mike Mascari
Subject Re: linking postgre to AD
Date
Msg-id 3FBE5EEB.1010409@mascari.com
Whole thread Raw
In response to Re: linking postgre to AD  ("Jason Tesser" <JTesser@nbbc.edu>)
List pgsql-general
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



pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: core dump
Next
From: Tom Lane
Date:
Subject: Re: Psql bug?