Thread: linking postgre to AD
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
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
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?
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.
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
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
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
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
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
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.