[GENERAL] linking postgre to AD

Jason Tesser JTesser at nbbc.edu
Fri Nov 21 15:00:44 EST 2003


sorry wrong list though if anyone has an answer feel free to give it :-)

-----Original Message-----
From: python-list-bounces+jtesser=nbbc.edu at python.org
[mailto:python-list-bounces+jtesser=nbbc.edu at python.org]On Behalf Of
Jason Tesser
Sent: Friday, November 21, 2003 1:59 PM
To: Python List (E-mail)
Subject: RE: [GENERAL] 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.com



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





More information about the Python-list mailing list