Thread: Best way to manage users
I am developing a web application and I would like to manage my user accounts in my postgresql database. Is the ‘best’ way to do this to set up all of my user accounts in Postgres and then set up a table to store the additional user information that I want to keep (email address, fax number etc…)?
Any suggestions would be appreciated.
Tia,
kevin
--As of Wednesday, January 4, 2006 7:59 -0500, Kevin Crenshaw is alleged to have said: > I am developing a web application and I would like to manage my user > accounts in my postgresql database. Is the 'best' way to do this to set > up all of my user accounts in Postgres and then set up a table to store > the additional user information that I want to keep (email address, fax > number etc...)? --As for the rest, it is mine. It depends entirely on what you need to do with the data, and what data you are collecting. Daniel T. Staal --------------------------------------------------------------- This email copyright the author. Unless otherwise noted, you are expressly allowed to retransmit, quote, or otherwise use the contents for non-commercial purposes. This copyright will expire 5 years after the author's death, or in 30 years, whichever is longer, unless such a period is in excess of local copyright law. ---------------------------------------------------------------
On 1/4/06 7:59 AM, "Kevin Crenshaw" <kcrenshaw@viscient.com> wrote: > I am developing a web application and I would like to manage my user > accounts in my postgresql database. Is the 'best' way to do this to set up > all of my user accounts in Postgres and then set up a table to store the > additional user information that I want to keep (email address, fax number > etc.)? Are you talking about database users, or web app users? What language are you developing in? Sean
I am talking about web app users. I would like to store contact information as well as billing related info like billing address and payment history and status. I am developing in VB.Net. Thanks, Kevin -----Original Message----- From: Sean Davis [mailto:sdavis2@mail.nih.gov] Sent: Wednesday, January 04, 2006 8:20 AM To: Kevin Crenshaw; pgsql-novice@postgresql.org Subject: Re: [NOVICE] Best way to manage users On 1/4/06 7:59 AM, "Kevin Crenshaw" <kcrenshaw@viscient.com> wrote: > I am developing a web application and I would like to manage my user > accounts in my postgresql database. Is the 'best' way to do this to set up > all of my user accounts in Postgres and then set up a table to store the > additional user information that I want to keep (email address, fax number > etc.)? Are you talking about database users, or web app users? What language are you developing in? Sean
On 1/4/06 9:51 AM, "Kevin Crenshaw" <kcrenshaw@viscient.com> wrote: > I am talking about web app users. I would like to store contact information > as well as billing related info like billing address and payment history and > status. I am developing in VB.Net. Sean As another poster pointed out, there are a number of ways to do this--all depend on your data and needs for it. If you are familiar with database normalization, then determine what is the best normalization for your data. If that is a foreign concept, I would grab a book on SQL and database design and read a few chapters about how to best design a database. Alternatively, there are a number of nice SQL and database design tutorials available online--use Google judiciously. As for specifics of VB.Net, I don't use it, so I can't really tell you how to best use the database information from your code--sorry. Sean
Kevin.....
> I am talking about web app users. I would like to store contact
> information as well as billing related info like billing address and
> payment history and status.
If it's a web app the postgresql user will be only the web server.
That's separate from your web app users, you can set them up any way you
choose. Probably you'd use a table (perhaps called users) in your
database with a unique id (maybe user_id). Additional info (email_adr,
username, password, zip code, whatever) might be in other columns in that
table.
When those users get involved in data in other tables you'll use the
user_id as the foreign key to reference which user it is.
Sometimes novice posters have the postgresql user mixed up with the
website users.
Of course, they *might* be one and the same, in some cases, but *usually*
not in a web app.
What I did the first time I set up postgreSQL and a web app was read a lot
and set up a simple postgreSQL web app from a tutorial and play with it to
see how it worked.
What works for me is to learn by doing because I make mistakes, then learn
from them. (hopefully!)
brew
==========================================================================
Strange Brew (brew@theMode.com)
Check out my Stock Option Covered Call website http://www.callpix.com
and my Musician's Online Database Exchange http://www.TheMode.com
==========================================================================
Thanks for the replies. I appreciate the advice. However, I think that a
better way to pose my question is to ask - what are the pros and cons of
using Postgres to handle user authentication for my web app?
Thanks,
Kevin
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of brew@theMode.com
Sent: Wednesday, January 04, 2006 10:28 AM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Best way to manage users
Kevin.....
> I am talking about web app users. I would like to store contact
> information as well as billing related info like billing address and
> payment history and status.
If it's a web app the postgresql user will be only the web server.
That's separate from your web app users, you can set them up any way you
choose. Probably you'd use a table (perhaps called users) in your
database with a unique id (maybe user_id). Additional info (email_adr,
username, password, zip code, whatever) might be in other columns in that
table.
When those users get involved in data in other tables you'll use the
user_id as the foreign key to reference which user it is.
Sometimes novice posters have the postgresql user mixed up with the
website users.
Of course, they *might* be one and the same, in some cases, but *usually*
not in a web app.
What I did the first time I set up postgreSQL and a web app was read a lot
and set up a simple postgreSQL web app from a tutorial and play with it to
see how it worked.
What works for me is to learn by doing because I make mistakes, then learn
from them. (hopefully!)
brew
==========================================================================
Strange Brew (brew@theMode.com)
Check out my Stock Option Covered Call website http://www.callpix.com
and my Musician's Online Database Exchange http://www.TheMode.com
==========================================================================
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
On 1/4/06 12:20 PM, "Kevin Crenshaw" <kcrenshaw@viscient.com> wrote: > Thanks for the replies. I appreciate the advice. However, I think that a > better way to pose my question is to ask - what are the pros and cons of > using Postgres to handle user authentication for my web app? Postgres will not do your user authentication for you. It WILL store your user information. As for storing a table of usernames/passwords (that is all that authentication requires, at least for basic auth), even a text file will do. However, if you are talking about money and accounting where data integrity is important, then an ACID compliant database seems a good way to go. Postgres is one (of several) such database. So, for storing user accounts, etc., postgres is fine. But that is quite a different (but related) question than user authentication for a web app. If you haven't done authentication via VB.NET before, I suggest you make the simplest case first and then decide if postgres will suit your needs. Sean
Kevin Crenshaw wrote: > Thanks for the replies. I appreciate the advice. However, I > think that a better way to pose my question is to ask - what > are the pros and cons of using Postgres to handle user > authentication for my web app? Maybe to add to that question: Is there a way to directly authenticate a user as a database user when using a web app? In .net, this is handeled by a cobination of the OS (active directory - AD), the DB (SQL Server) which can authenticate against AD, and the browser. However, how can pg get or request the authentication from your webbrowser? Unless there is a pgsql function that allows login as a different user from a webapp from the webapp, I would think it's not possible to actually do this the way a statefull app does it (under windows for example) Some comments on this would be great, as I have a very similar requirement and was also toying with the idea of using the pg users, instead of creating my own tables and user authentication infrastructure. Thanks Roland Giesler
On 4/1/06 5:43 pm, "Roland Giesler" <roland@giesler.za.net> wrote: > Kevin Crenshaw wrote: >> Thanks for the replies. I appreciate the advice. However, I >> think that a better way to pose my question is to ask - what >> are the pros and cons of using Postgres to handle user >> authentication for my web app? > > Maybe to add to that question: Is there a way to directly authenticate a > user as a database user when using a web app? In .net, this is handeled by > a cobination of the OS (active directory - AD), the DB (SQL Server) which > can authenticate against AD, and the browser. However, how can pg get or > request the authentication from your webbrowser? Unless there is a pgsql > function that allows login as a different user from a webapp from the > webapp, I would think it's not possible to actually do this the way a > statefull app does it (under windows for example) > > Some comments on this would be great, as I have a very similar requirement > and was also toying with the idea of using the pg users, instead of creating > my own tables and user authentication infrastructure. If using PHP you could authenticate when you login to your web app and then set an 'authenticated' session variable or cookie (Perl also I suppose). You would have to store username/password information in the session variable/cookie as well for subsequent page/db requests. Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.
I think that I would have to disagree with the statement that 'Postgres will not do your user authentication for you'. If you have a pg user account for each of your web app users and they submit a username and password using your login form in the web app and the web app uses that information to access the database - isn't that 'authenticating' the user? So, I guess what I would like to know is - is it better to have the web app users be pg users too or is it a better idea to separate the two? The way that I was thinking of doing this is to have one pg user account that the web app uses to access the database, then set up a 'users' table to hold all of the web app user account info. What are your thoughts? Kevin -----Original Message----- From: Sean Davis [mailto:sdavis2@mail.nih.gov] Sent: Wednesday, January 04, 2006 12:37 PM To: Kevin Crenshaw; brew@theMode.com; pgsql-novice@postgresql.org Subject: Re: [NOVICE] Best way to manage users On 1/4/06 12:20 PM, "Kevin Crenshaw" <kcrenshaw@viscient.com> wrote: > Thanks for the replies. I appreciate the advice. However, I think that a > better way to pose my question is to ask - what are the pros and cons of > using Postgres to handle user authentication for my web app? Postgres will not do your user authentication for you. It WILL store your user information. As for storing a table of usernames/passwords (that is all that authentication requires, at least for basic auth), even a text file will do. However, if you are talking about money and accounting where data integrity is important, then an ACID compliant database seems a good way to go. Postgres is one (of several) such database. So, for storing user accounts, etc., postgres is fine. But that is quite a different (but related) question than user authentication for a web app. If you haven't done authentication via VB.NET before, I suggest you make the simplest case first and then decide if postgres will suit your needs. Sean
On 1/4/06 1:55 PM, "Kevin Crenshaw" <kcrenshaw@viscient.com> wrote: > I think that I would have to disagree with the statement that 'Postgres will > not do your user authentication for you'. If you have a pg user account for > each of your web app users and they submit a username and password using > your login form in the web app and the web app uses that information to > access the database - isn't that 'authenticating' the user? So, I guess > what I would like to know is - is it better to have the web app users be pg > users too or is it a better idea to separate the two? True enough. However, you still have to have a function at the application level that checks to see if the database authenticated the user, so your application STILL has to participate in the authentication. My point was only that the database server cannot "talk" directly to the browser. Unless you NEED the database users to be different, I wouldn't do this. > The way that I was thinking of doing this is to have one pg user account > that the web app uses to access the database, then set up a 'users' table to > hold all of the web app user account info. This is what I would do for most applications, but that is only my opinion. Sean
--- Kevin Crenshaw <kcrenshaw@viscient.com> wrote: > Thanks for the replies. I appreciate the advice. > However, I think that a > better way to pose my question is to ask - what are > the pros and cons of > using Postgres to handle user authentication for my > web app? > > Thanks, > Kevin on a side note, remember to encrypt confidential information (eg, passwords) as opposed to storing the information in human readable format. some pretty high profile companies have been "lost" hardware with unencrypted data... like names and social security numbers... i use php and i need a user authentication system. i've been stuck on this for a while, but i haven't gone at it 100% either. i'm going to use php to code the authentication logic and will draw on data from pgsql. __________________________________________ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com
Thank you all for your input. You have been very helpful. The point regarding sensitive information is a good one. I plan to use the cryptographic classes included with VB.Net to encrypt sensitive information before it is stored in the database. I don't know if php offers the same functionality. -----Original Message----- From: operationsengineer1@yahoo.com [mailto:operationsengineer1@yahoo.com] Sent: Wednesday, January 04, 2006 2:05 PM To: Kevin Crenshaw; brew@theMode.com; pgsql-novice@postgresql.org Subject: Re: [NOVICE] Best way to manage users --- Kevin Crenshaw <kcrenshaw@viscient.com> wrote: > Thanks for the replies. I appreciate the advice. > However, I think that a > better way to pose my question is to ask - what are > the pros and cons of > using Postgres to handle user authentication for my > web app? > > Thanks, > Kevin on a side note, remember to encrypt confidential information (eg, passwords) as opposed to storing the information in human readable format. some pretty high profile companies have been "lost" hardware with unencrypted data... like names and social security numbers... i use php and i need a user authentication system. i've been stuck on this for a while, but i haven't gone at it 100% either. i'm going to use php to code the authentication logic and will draw on data from pgsql. __________________________________________ Yahoo! DSL - Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com
Roland, I wanted to reply to your post separately. I toyed with using pg_user to store user information, but it did not work. I tried to use pg_user to store user acct info and then use a 'user_detail' table to store additional details but I got an error stating that pg_user is not a table (this occurred when I tried to use the 'usesysid' column as a foreign key in my user_detail table). I think that the best solution - given the discussion thus far - is to have a separate pg user that the web app will use to access the database, and create a 'users' table in the db to store the web app usernames and passwords etc... Hth, kevin -----Original Message----- From: Roland Giesler [mailto:roland@giesler.za.net] Sent: Wednesday, January 04, 2006 12:44 PM To: 'Kevin Crenshaw'; brew@theMode.com; pgsql-novice@postgresql.org Subject: RE: [NOVICE] Best way to manage users Kevin Crenshaw wrote: > Thanks for the replies. I appreciate the advice. However, I > think that a better way to pose my question is to ask - what > are the pros and cons of using Postgres to handle user > authentication for my web app? Maybe to add to that question: Is there a way to directly authenticate a user as a database user when using a web app? In .net, this is handeled by a cobination of the OS (active directory - AD), the DB (SQL Server) which can authenticate against AD, and the browser. However, how can pg get or request the authentication from your webbrowser? Unless there is a pgsql function that allows login as a different user from a webapp from the webapp, I would think it's not possible to actually do this the way a statefull app does it (under windows for example) Some comments on this would be great, as I have a very similar requirement and was also toying with the idea of using the pg users, instead of creating my own tables and user authentication infrastructure. Thanks Roland Giesler
On 1/4/06 2:40 PM, "Kevin Crenshaw" <kcrenshaw@viscient.com> wrote: > Roland, > > I wanted to reply to your post separately. I toyed with using pg_user to > store user information, but it did not work. I tried to use pg_user to > store user acct info and then use a 'user_detail' table to store additional > details but I got an error stating that pg_user is not a table (this > occurred when I tried to use the 'usesysid' column as a foreign key in my > user_detail table). > > I think that the best solution - given the discussion thus far - is to have > a separate pg user that the web app will use to access the database, and > create a 'users' table in the db to store the web app usernames and > passwords etc... This hasn't been mentioned yet (and probably isn't that important for all but the most demanding web apps), but one way to significantly speed up web apps is to use persistent database connections (they are not closed after each request). This is not really feasible with a multiple-db-user setup. Sean
At 02:53 PM 1/4/06, Sean Davis wrote: >On 1/4/06 2:40 PM, "Kevin Crenshaw" <kcrenshaw@viscient.com> wrote: > > > Roland, > > > > I wanted to reply to your post separately. I toyed with using pg_user to > > store user information, but it did not work. I tried to use pg_user to > > store user acct info and then use a 'user_detail' table to store additional > > details but I got an error stating that pg_user is not a table (this > > occurred when I tried to use the 'usesysid' column as a foreign key in my > > user_detail table). > > > > I think that the best solution - given the discussion thus far - is to > have > > a separate pg user that the web app will use to access the database, and > > create a 'users' table in the db to store the web app usernames and > > passwords etc... > >This hasn't been mentioned yet (and probably isn't that important for all >but the most demanding web apps), but one way to significantly speed up web >apps is to use persistent database connections (they are not closed after >each request). This is not really feasible with a multiple-db-user setup. NOT TRUE. To be correct, your statement must be a bit longer. The connections are not closed after each request within each child process of the web server. If you run apache and there are 100 child processes, there can be up to 100 open "persistent" database connections. In many cases where database and webserver are on the same box there is little time difference between persistent and non-persistent connections; but in general there are many variables and each host should do their own timing tests.
On 1/4/06 4:28 PM, "Frank Bax" <fbax@sympatico.ca> wrote: > At 02:53 PM 1/4/06, Sean Davis wrote: > > > > >> On 1/4/06 2:40 PM, "Kevin Crenshaw" <kcrenshaw@viscient.com> wrote: >> >>> Roland, >>> >>> I wanted to reply to your post separately. I toyed with using pg_user to >>> store user information, but it did not work. I tried to use pg_user to >>> store user acct info and then use a 'user_detail' table to store additional >>> details but I got an error stating that pg_user is not a table (this >>> occurred when I tried to use the 'usesysid' column as a foreign key in my >>> user_detail table). >>> >>> I think that the best solution - given the discussion thus far - is to >> have >>> a separate pg user that the web app will use to access the database, and >>> create a 'users' table in the db to store the web app usernames and >>> passwords etc... >> >> This hasn't been mentioned yet (and probably isn't that important for all >> but the most demanding web apps), but one way to significantly speed up web >> apps is to use persistent database connections (they are not closed after >> each request). This is not really feasible with a multiple-db-user setup. > > > NOT TRUE. To be correct, your statement must be a bit longer. The > connections are not closed after each request within each child process of > the web server. If you run apache and there are 100 child processes, there > can be up to 100 open "persistent" database connections. In many cases > where database and webserver are on the same box there is little time > difference between persistent and non-persistent connections; but in > general there are many variables and each host should do their own timing > tests. Thanks for clarifying. Sean
--- Frank Bax <fbax@sympatico.ca> wrote: > At 02:53 PM 1/4/06, Sean Davis wrote: > > > > > >On 1/4/06 2:40 PM, "Kevin Crenshaw" > <kcrenshaw@viscient.com> wrote: > > > > > Roland, > > > > > > I wanted to reply to your post separately. I > toyed with using pg_user to > > > store user information, but it did not work. I > tried to use pg_user to > > > store user acct info and then use a > 'user_detail' table to store additional > > > details but I got an error stating that pg_user > is not a table (this > > > occurred when I tried to use the 'usesysid' > column as a foreign key in my > > > user_detail table). > > > > > > I think that the best solution - given the > discussion thus far - is to > > have > > > a separate pg user that the web app will use to > access the database, and > > > create a 'users' table in the db to store the > web app usernames and > > > passwords etc... > > > >This hasn't been mentioned yet (and probably isn't > that important for all > >but the most demanding web apps), but one way to > significantly speed up web > >apps is to use persistent database connections > (they are not closed after > >each request). This is not really feasible with a > multiple-db-user setup. > > > NOT TRUE. To be correct, your statement must be a > bit longer. The > connections are not closed after each request within > each child process of > the web server. If you run apache and there are 100 > child processes, there > can be up to 100 open "persistent" database > connections. In many cases > where database and webserver are on the same box > there is little time > difference between persistent and non-persistent > connections; but in > general there are many variables and each host > should do their own timing > tests. Frank, am i correct to infer that "In many cases where database and webserver are on the same box there is little time difference..." means that there can be more speed improvement when the web server and the db are on different hardware? tia... __________________________________________ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com
At 05:32 PM 1/4/06, operationsengineer1@yahoo.com wrote: >Frank, am i correct to infer that "In many cases where >database and webserver are on the same box there is >little time difference..." means that there can be >more speed improvement when the web server and the db >are on different hardware? In some environments even that "little time difference" can be a big deal!! To answer your question ... I don't know - I haven't any experience in that environment; but I'm guessing that it's true. But there's also no free lunch. It should also be pointed out that a persistent connection can only be reused if all connect string parms are the same. If you use pg_users for web authentication (as was mentioned earlier in this thread), then each user will have their own pool of persistent connections. The number of persistent connections could be (at least) = number of databases * number of users * number of child processes. This can get to quite a large number even in a small hosting environment with multiple databases. In my case, I stopped using persistent connections the first time I hit the limit of max connections within postgresql - my php scripts were crashing because there was no available persistent connection in the current child process, and backend was refusing to create any more connections. So, as I said before, it's important to do your own tests to see what works for you.
Kevin Crenshaw wrote: > I think that the best solution - given the discussion thus > far - is to have a separate pg user that the web app will > use to access the database, and create a 'users' table in the > db to store the web app usernames and passwords etc... Well, using postgres users to authenticate web users has the advantage that one can set up access priviledges in the database and in doing so limit the bypassing of access security from other apps or the likes of PgAdmin. Defining group roles and simply adding or removing users to a group, makes the process relatively simple. Without this, one would have to define all these things (table and column level access) manually and test for it in your app, which makes things quite a bit more complex. Thinking this all through, it seems that using PG users is till a good option if you need to have different user profiles in an app. Comments? Roland
I considered that point as well, however, I still believe it is better not
to have my user accounts handled by the db - because:
1. Fewer database user accounts means fewer vectors for entry into the db.
2. Controlling access to the data via your app gives you more control over
what the end user can see and what they can do with the data.
3. You can still use the Postgres' built in access controls to limit what
your user can do in the db as an added layer of security.
- When I say 'your user' I mean the user you set up to give your web app
access to the database and not the individual web app users contained within
the database.
4. Correct me if I'm wrong, but I don't think that Postgres allows access
control at the column or row level, just at the table level. Controlling
access via your app will give you access control down to whatever level you
need.
5. As another poster mentioned, controlling access via your app allows you
the ability to move to other rdbms' more easily if you choose to do so at a
later date.
Anyway, that's my $.02.
Kevin
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Roland Giesler
Sent: Thursday, January 05, 2006 5:50 AM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Best way to manage users
Kevin Crenshaw wrote:
> I think that the best solution - given the discussion thus
> far - is to have a separate pg user that the web app will
> use to access the database, and create a 'users' table in the
> db to store the web app usernames and passwords etc...
Well, using postgres users to authenticate web users has the advantage that
one can set up access priviledges in the database and in doing so limit the
bypassing of access security from other apps or the likes of PgAdmin.
Defining group roles and simply adding or removing users to a group, makes
the process relatively simple. Without this, one would have to define all
these things (table and column level access) manually and test for it in
your app, which makes things quite a bit more complex. Thinking this all
through, it seems that using PG users is till a good option if you need to
have different user profiles in an app.
Comments?
Roland
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
#4 is not quite correct. If you need to control column or row level access, simply create a view that enforces those limits. With proper structure, that view can even use a permissions based paradigm where you pass in a value to the view and it returns only those rows that are available to the specific web user, giving only the columns you wish the user to be able to view. Charley On Jan 5, 2006, at 7:32 AM, Kevin Crenshaw wrote: > I considered that point as well, however, I still believe it is > better not > to have my user accounts handled by the db - because: > > 1. Fewer database user accounts means fewer vectors for entry into > the db. > 2. Controlling access to the data via your app gives you more > control over > what the end user can see and what they can do with the data. > 3. You can still use the Postgres' built in access controls to > limit what > your user can do in the db as an added layer of security. > - When I say 'your user' I mean the user you set up to give your > web app > access to the database and not the individual web app users > contained within > the database. > 4. Correct me if I'm wrong, but I don't think that Postgres allows > access > control at the column or row level, just at the table level. > Controlling > access via your app will give you access control down to whatever > level you > need. > 5. As another poster mentioned, controlling access via your app > allows you > the ability to move to other rdbms' more easily if you choose to do > so at a > later date. > > Anyway, that's my $.02. > > Kevin > > > > > -----Original Message----- > From: pgsql-novice-owner@postgresql.org > [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Roland Giesler > Sent: Thursday, January 05, 2006 5:50 AM > To: pgsql-novice@postgresql.org > Subject: Re: [NOVICE] Best way to manage users > > Kevin Crenshaw wrote: >> I think that the best solution - given the discussion thus >> far - is to have a separate pg user that the web app will >> use to access the database, and create a 'users' table in the >> db to store the web app usernames and passwords etc... > > Well, using postgres users to authenticate web users has the > advantage that > one can set up access priviledges in the database and in doing so > limit the > bypassing of access security from other apps or the likes of PgAdmin. > Defining group roles and simply adding or removing users to a > group, makes > the process relatively simple. Without this, one would have to > define all > these things (table and column level access) manually and test for > it in > your app, which makes things quite a bit more complex. Thinking > this all > through, it seems that using PG users is till a good option if you > need to > have different user profiles in an app. > > Comments? > > Roland > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org
Charly, I have to disagree. What you are describing is a workaround that allows you to use the security features of the db to control access to a view that you created - not a built-in feature. It's still a workable solution to the problem. Thanks for providing another way to attack this issue. Kevin -----Original Message----- From: Charley Tiggs [mailto:ctiggs@xpressdocs.com] Sent: Thursday, January 05, 2006 8:51 AM To: Kevin Crenshaw Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] Best way to manage users #4 is not quite correct. If you need to control column or row level access, simply create a view that enforces those limits. With proper structure, that view can even use a permissions based paradigm where you pass in a value to the view and it returns only those rows that are available to the specific web user, giving only the columns you wish the user to be able to view. Charley On Jan 5, 2006, at 7:32 AM, Kevin Crenshaw wrote: > I considered that point as well, however, I still believe it is > better not > to have my user accounts handled by the db - because: > > 1. Fewer database user accounts means fewer vectors for entry into > the db. > 2. Controlling access to the data via your app gives you more > control over > what the end user can see and what they can do with the data. > 3. You can still use the Postgres' built in access controls to > limit what > your user can do in the db as an added layer of security. > - When I say 'your user' I mean the user you set up to give your > web app > access to the database and not the individual web app users > contained within > the database. > 4. Correct me if I'm wrong, but I don't think that Postgres allows > access > control at the column or row level, just at the table level. > Controlling > access via your app will give you access control down to whatever > level you > need. > 5. As another poster mentioned, controlling access via your app > allows you > the ability to move to other rdbms' more easily if you choose to do > so at a > later date. > > Anyway, that's my $.02. > > Kevin > > > > > -----Original Message----- > From: pgsql-novice-owner@postgresql.org > [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Roland Giesler > Sent: Thursday, January 05, 2006 5:50 AM > To: pgsql-novice@postgresql.org > Subject: Re: [NOVICE] Best way to manage users > > Kevin Crenshaw wrote: >> I think that the best solution - given the discussion thus >> far - is to have a separate pg user that the web app will >> use to access the database, and create a 'users' table in the >> db to store the web app usernames and passwords etc... > > Well, using postgres users to authenticate web users has the > advantage that > one can set up access priviledges in the database and in doing so > limit the > bypassing of access security from other apps or the likes of PgAdmin. > Defining group roles and simply adding or removing users to a > group, makes > the process relatively simple. Without this, one would have to > define all > these things (table and column level access) manually and test for > it in > your app, which makes things quite a bit more complex. Thinking > this all > through, it seems that using PG users is till a good option if you > need to > have different user profiles in an app. > > Comments? > > Roland > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org
Kevin Crenshaw wrote: > I considered that point as well, however, I still believe it > is better not to have my user accounts handled by the db - because: > > 1. Fewer database user accounts means fewer vectors for entry > into the db. Ok, but that depends on your environment. In a corporate environment this may prove problematic. Only one user that has all priviledges, often causes someone to trust some user with the password and then there's trouble. And you don't even have an audit trail, since all users use the same username/password combo. I know, that's worst case, but the other side of the coin. I guess it's horses for courses. > 2. Controlling access to the data via your app gives you more > control over what the end user can see and what they can do > with the data. Of course, but it's also much more work to write the app. Again depends on what you need. I come from MS SQL server to PG and the security over there is quite granular. > 3. You can still use the Postgres' built in access controls > to limit what your user can do in the db as an added layer of > security. - When I say 'your user' I mean the user you set up to give > your web app access to the database and not the individual > web app users contained within the database. Agreed > 4. Correct me if I'm wrong, but I don't think that Postgres > allows access control at the column or row level, just at the > table level. Controlling access via your app will give you > access control down to whatever level you need. I just checked up on this, and you're right, it's not possible (yet, I hope). > 5. As another poster mentioned, controlling access via your > app allows you the ability to move to other rdbms' more > easily if you choose to do so at a later date. That is a definite advantage. I'll have to revert back to creating my own security and users infrastructure, I guess. Thanks for the comments Roland