Re: linking postgre to AD

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(at)mascari(dot)com

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