Thread: Webappication and PostgreSQL login roles
Hi, I designed a Java web application. The persistence layer is a PostgreSQL database. The application needs user authentication. I think it's a good choice to implement this authentication mechanism via PostgreSQL login roles. So I can create several database login roles and set the database permissions to this login roles. This is my first project with the postgres database, so I don't know how I can validate a login from the website. Is there a best practice to do this or does PostgreSQL offers a stored procedure like 'authenticateUser(String username, String password)'? Thanks for your help. Bye, Thorsten
No idea?? Thorsten Kraus schrieb: > Hi, > > I designed a Java web application. The persistence layer is a > PostgreSQL database. The application needs user authentication. > I think it's a good choice to implement this authentication mechanism > via PostgreSQL login roles. So I can create several database login > roles and set the database permissions to this login roles. This is my > first project with the postgres database, so I don't know how I can > validate a login from the website. Is there a best practice to do this > or does PostgreSQL offers a stored procedure like > 'authenticateUser(String username, String password)'? > > Thanks for your help. > > Bye, > Thorsten > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >
Thorsten Kraus wrote: > No idea?? You'd need an authenticated user to call that stored procedure in the first place. It is kind of a chicken-and-egg problem. Usually people create a user for the webapp. This user makes the first connection to the database. After that you probably could define a security-definer procedure that handles further authentication (to an actual schema, for example). I have to admit I have never done this myself; but this is what I recall from previous discussions on similar topics. > Thorsten Kraus schrieb: >> Hi, >> >> I designed a Java web application. The persistence layer is a >> PostgreSQL database. The application needs user authentication. >> I think it's a good choice to implement this authentication mechanism >> via PostgreSQL login roles. So I can create several database login >> roles and set the database permissions to this login roles. This is my >> first project with the postgres database, so I don't know how I can >> validate a login from the website. Is there a best practice to do this >> or does PostgreSQL offers a stored procedure like >> 'authenticateUser(String username, String password)'? >> >> Thanks for your help. >> >> Bye, >> Thorsten -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //
Thorsten Kraus wrote: > Hi, > > I designed a Java web application. The persistence layer is a PostgreSQL > database. The application needs user authentication. > I think it's a good choice to implement this authentication mechanism > via PostgreSQL login roles. So I can create several database login roles > and set the database permissions to this login roles. This is my first > project with the postgres database, so I don't know how I can validate a > login from the website. Is there a best practice to do this or does > PostgreSQL offers a stored procedure like 'authenticateUser(String > username, String password)'? > > Thanks for your help. > > Bye, > Thorsten > Can you not use the username/password as part of the DSN? Regards, Lutz Broedel -- Lutz Broedel Leibniz University of Hannover Institute for Water Quality & Waste Management / ISAH Division: Water Resources Management Am Kleinen Felde 30 D - 30167 Hannover, Germany phone +49 (0)511 762 5984 fax +49 (0)511 762 19 413 lb@fggm.uni-hannover.de To verify the digital signature, you need to load the following certificate: https://pki.pca.dfn.de/uh-ca/pub/cacert/rootcert.crt
Attachment
Hi, thanks for your answer. I cant use the username/password in my DSN because I don't connect directly via JDBC to the database. I use hibernate for all database actions. The username and password has to be stored in the hibernate configuration file... Bye, Thorsten Lutz Broedel schrieb: > > Can you not use the username/password as part of the DSN? > > Regards, > Lutz Broedel >
In response to Thorsten Kraus <TK-Spam@gmx.de>: > Hi, > > thanks for your answer. I cant use the username/password in my DSN > because I don't connect directly via JDBC to the database. I use > hibernate for all database actions. The username and password has to be > stored in the hibernate configuration file... I can't help but wonder what other poor programming practices hibernate encourages ... > Lutz Broedel schrieb: > > > > Can you not use the username/password as part of the DSN? > > > > Regards, > > Lutz Broedel > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend -- Bill Moran http://www.potentialtech.com
You could originally connect to the database as some kind of power user. Check the password against the pg_shadow view (you would need to md5 your password somehow) and then do a SET SESSION AUTHORIZATION (or SET ROLE) to change your permissions. Not sure how secure this would be but it's the way I would try. Regards, Ben "Thorsten Kraus" <TK-Spam@gmx.de> wrote in message news:46124F74.3000302@gmx.de... > Hi, > > thanks for your answer. I cant use the username/password in my DSN because > I don't connect directly via JDBC to the database. I use hibernate for all > database actions. The username and password has to be stored in the > hibernate configuration file... > > Bye, > Thorsten > > > Lutz Broedel schrieb: >> >> Can you not use the username/password as part of the DSN? >> >> Regards, >> Lutz Broedel >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >
This would be a possible way. Now the question is which algorithm implementation of md5 PostgreSQL uses...
Bye,
Thorsten
Ben Trewern schrieb:
Bye,
Thorsten
Ben Trewern schrieb:
You could originally connect to the database as some kind of power user. Check the password against the pg_shadow view (you would need to md5 your password somehow) and then do a SET SESSION AUTHORIZATION (or SET ROLE) to change your permissions. Not sure how secure this would be but it's the way I would try. Regards, Ben "Thorsten Kraus" <TK-Spam@gmx.de> wrote in message news:46124F74.3000302@gmx.de...Hi, thanks for your answer. I cant use the username/password in my DSN because I don't connect directly via JDBC to the database. I use hibernate for all database actions. The username and password has to be stored in the hibernate configuration file... Bye, Thorsten Lutz Broedel schrieb:Can you not use the username/password as part of the DSN? Regards, Lutz Broedel---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
I've written a web application where users can upload spreadsheets, instead of having to key in forms. The spreadsheets get parsed and INSERTED into a table, and with the INSERT gets added an identifier so that I can always trace back what a particular row in the table corresponds to. I'd like to use COPY - FROM to achieve the same thing, but a stopping point is that I don't see how to add the new spreadsheet with a particular identifier. I'd like to be able to do something like COPY mytable (field-1, .. field-n, id = my_id) FROM file; or COPY mytable FROM file WITH id = my_id; A very messy solution would be to create a temp table with a special name, COPY to it, then INSERT from it to the permanent table. However, I don't want a solution of that type. I assume many people have this same problem. Any elegant solutions here? Thanks Jaime *********************************************************************** Bear Stearns is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. Bear Stearns does not provide tax, legal or accounting advice. You should consult your own tax, legal and accounting advisors before engaging in any transaction. In order for Bear Stearns to comply with Internal Revenue Service Circular 230 (if applicable), you are notified that any discussion of U.S. federal tax issues contained or referred to herein is not intended or written to be used, and cannot be used, for the purpose of: (A) avoiding penalties that may be imposed under the Internal Revenue Code; nor (B) promoting, marketing or recommending to another party any transaction or matter addressed herein. ***********************************************************************
Jaime Silvela wrote: > I've written a web application where users can upload spreadsheets, > instead of having to key in forms. The spreadsheets get parsed and > INSERTED into a table, and with the INSERT gets added an identifier so > that I can always trace back what a particular row in the table > corresponds to. > I'd like to use COPY - FROM to achieve the same thing, but a stopping > point is that I don't see how to add the new spreadsheet with a > particular identifier. > > I'd like to be able to do something like > COPY mytable (field-1, .. field-n, id = my_id) FROM file; or > COPY mytable FROM file WITH id = my_id; > > A very messy solution would be to create a temp table with a special > name, COPY to it, then INSERT from it to the permanent table. However, I > don't want a solution of that type. > I may have completely misunderstood you, but i'd think that copying the data directly from an uploaded file would be more than a little insecure. But then, you also mentioned that you parse the uploaded file. I don't understand how these two statements can be compatible. Do you mean that you'd like to load the data into a table, then retrieve the sequence ID? Presumably, if your application is really parsing the data first, one could simply do an INSERT and then grab the last inserted ID. Look at nextval() & currval(). http://www.postgresql.org/docs/7.3/static/functions-sequence.html brian
>>> I designed a Java web application. The persistence layer is a
>>> PostgreSQL database. The application needs user authentication.
>>> I think it's a good choice to implement this authentication mechanism
>>> via PostgreSQL login roles. So I can create several database login
>>> roles and set the database permissions to this login roles. This is my
>>> first project with the postgres database, so I don't know how I can
>>> validate a login from the website. Is there a best practice to do this
>>> or does PostgreSQL offers a stored procedure like
>>> 'authenticateUser(String username, String password)'?
Keep in mind that this might interact badly with very desirable features
like :
- persistent connections
(opening a postgres connection takes a lot longer than a simple SELECT,
so if you must reopen connections all the time your performance will suck)
- connection pooling
(what happens when a user gets the admin's connection out of the pool ?)
Since you use an object-relational mapper I believe it is better, and
more flexible to have your objects handle their own operations.
On a very basic level your objects can have a .isReadOnly() method which
is checked in your application before any writing takes place, for
instance.
Brian, that's not what I meant. Parsing of the uploaded file is just for the purpose of extracting the components of each spreadsheet row and constructing the INSERTs. Actually, whenever I copy from a file, either using COPY or with a custom importer, I put the data into a staging table, so that I can pre-process before writing to the main table. But why would COPYing from a file be so insecure? nextval() and sequences are not what I'm looking for. I want to assign the same id to all the rows imported from the same file. Let's say user A is working on portfolio_id 3, and decides to upload a spreadsheet with new values. I want to be able to import the spreadsheet into the staging table, and assign a portfolio_id of 3 to all its entries. Of course, I can't just UPDATE the staging table to have portfolio_id = 3, because user B might also be uploading a sheet for portfolio_id = 9. Any ideas on this? Thanks Jaime brian wrote: > Jaime Silvela wrote: >> I've written a web application where users can upload spreadsheets, >> instead of having to key in forms. The spreadsheets get parsed and >> INSERTED into a table, and with the INSERT gets added an identifier >> so that I can always trace back what a particular row in the table >> corresponds to. >> I'd like to use COPY - FROM to achieve the same thing, but a stopping >> point is that I don't see how to add the new spreadsheet with a >> particular identifier. >> >> I'd like to be able to do something like >> COPY mytable (field-1, .. field-n, id = my_id) FROM file; or >> COPY mytable FROM file WITH id = my_id; >> >> A very messy solution would be to create a temp table with a special >> name, COPY to it, then INSERT from it to the permanent table. >> However, I don't want a solution of that type. >> > > I may have completely misunderstood you, but i'd think that copying > the data directly from an uploaded file would be more than a little > insecure. But then, you also mentioned that you parse the uploaded > file. I don't understand how these two statements can be compatible. > > Do you mean that you'd like to load the data into a table, then > retrieve the sequence ID? Presumably, if your application is really > parsing the data first, one could simply do an INSERT and then grab > the last inserted ID. Look at nextval() & currval(). > > http://www.postgresql.org/docs/7.3/static/functions-sequence.html > > brian > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > *********************************************************************** Bear Stearns is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. Bear Stearns does not provide tax, legal or accounting advice. You should consult your own tax, legal and accounting advisors before engaging in any transaction. In order for Bear Stearns to comply with Internal Revenue Service Circular 230 (if applicable), you are notified that any discussion of U.S. federal tax issues contained or referred to herein is not intended or written to be used, and cannot be used, for the purpose of: (A) avoiding penalties that may be imposed under the Internal Revenue Code; nor (B) promoting, marketing or recommending to another party any transaction or matter addressed herein. ***********************************************************************
Jaime Silvela wrote:
> Brian, that's not what I meant.
> Parsing of the uploaded file is just for the purpose of extracting the
> components of each spreadsheet row and constructing the INSERTs.
> Actually, whenever I copy from a file, either using COPY or with a
> custom importer, I put the data into a staging table, so that I can
> pre-process before writing to the main table. But why would COPYing from
> a file be so insecure?
>
I was under the impression that you were copying indiscriminately from
an uploaded CSV file ("spreadsheet" being ambiguous). Obviously, that
would be a Bad Thing to rely upon.
> nextval() and sequences are not what I'm looking for. I want to assign
> the same id to all the rows imported from the same file. Let's say user
> A is working on portfolio_id 3, and decides to upload a spreadsheet with
> new values. I want to be able to import the spreadsheet into the staging
> table, and assign a portfolio_id of 3 to all its entries.
> Of course, I can't just UPDATE the staging table to have portfolio_id =
> 3, because user B might also be uploading a sheet for portfolio_id = 9.
>
Seems like you need to adjust your schema to use a pivot table:
CREATE TABLE portfolio (
id SERIAL PRIMARY KEY,
...
CREATE TABLE portfolio_entries (
portfolio_id INT4 NOT NULL,
...
CONSTRAINT fk_portfolio_entries FOREIGN KEY (portfolio_id)
REFERENCES portfolio
ON DELETE CASCADE
Then you should be able to insert directly into the second table a row
for each entry (for want of a better word) that corresponds to a
particular portfolio.
brian
That's sort of what I have already, and my problem is that the portfolio_id field does not exist in the CSV files. I'd like to be able to assign a portfolio_id, for the current file's entries. Another person in the list suggested dynamically adding a column with the portfolio_id to the file, and that of course would work, but is kinda messy. The problem with the solution you suggest is that when doing COPY, I'll get a complaint because of trying to populate an entry with a null value for portfolio_id. Some sort of automatic population of the portfolio_id field wouldn't work either, since many different users and processes could be inserting data into the staging table simultaneously. > Seems like you need to adjust your schema to use a pivot table: > > CREATE TABLE portfolio ( > > id SERIAL PRIMARY KEY, > ... > > CREATE TABLE portfolio_entries ( > portfolio_id INT4 NOT NULL, > > ... > > CONSTRAINT fk_portfolio_entries FOREIGN KEY (portfolio_id) > REFERENCES portfolio > ON DELETE CASCADE > > Then you should be able to insert directly into the second table a row > for each entry (for want of a better word) that corresponds to a > particular portfolio. > > brian > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > *********************************************************************** Bear Stearns is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. Bear Stearns does not provide tax, legal or accounting advice. You should consult your own tax, legal and accounting advisors before engaging in any transaction. In order for Bear Stearns to comply with Internal Revenue Service Circular 230 (if applicable), you are notified that any discussion of U.S. federal tax issues contained or referred to herein is not intended or written to be used, and cannot be used, for the purpose of: (A) avoiding penalties that may be imposed under the Internal Revenue Code; nor (B) promoting, marketing or recommending to another party any transaction or matter addressed herein. ***********************************************************************
> nextval() and sequences are not what I'm looking for. I want to
> assign the same id to all the rows imported from the same file.
> Let's say user A is working on portfolio_id 3, and decides to
> upload a spreadsheet with new values. I want to be able to import
> the spreadsheet into the staging table, and assign a portfolio_id
> of 3 to all its entries.
> Of course, I can't just UPDATE the staging table to have
> portfolio_id = 3, because user B might also be uploading a sheet
> for portfolio_id = 9.
The first thing to occur to me is to make the staging table TEMP, so
every session its own copy. But the second thing is, do you really
need a portfolio_id column in the staging table? After you get the
data massaged correctly into the staging table, perhaps you could
load it into the main table thusly:
insert into main_table (portfolio_id, other_columns ...)
select 3, other_columns ... from staging_table;
where 3 is the portfolio_id you want to assign to all the data you're
currently loading. This may not work exactly for your situation, but
does some variant make sense?
- John Burger
MITRE
On Tue, 03 Apr 2007 12:45:54 -0400, Jaime Silvela <JSilvela@Bear.com> wrote: > I'd like to be able to do something like > COPY mytable (field-1, .. field-n, id = my_id) FROM file; How do you get my_id? Can you get it in a trigger? Triggers still fire with copy so if you can get a trigger to fill in the id column you can copy with just the field names. klint. +---------------------------------------+-----------------+ : Klint Gore : "Non rhyming : : EMail : kg@kgb.une.edu.au : slang - the : : Snail : A.B.R.I. : possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---------------------------------------+-----------------+
I agree that with a temp table, the portfolio_id could be cleanly inserted as you suggest, from the temp table into the staging table. The staging table would need a portfolio_id, since it could house data from several different spreadsheets at the same time. In fact, the staging table could be bypassed altogether, it would add little value to "copy" to "temp", "insert" to staging, "insert" to main. Having the "staging" table be a temp table would seem to be a general solution in these cases where several users could be uploading files. Barring a new version of COPY, I think this is the best solution. Thank you Jaime John D. Burger wrote: >> nextval() and sequences are not what I'm looking for. I want to >> assign the same id to all the rows imported from the same file. Let's >> say user A is working on portfolio_id 3, and decides to upload a >> spreadsheet with new values. I want to be able to import the >> spreadsheet into the staging table, and assign a portfolio_id of 3 to >> all its entries. >> Of course, I can't just UPDATE the staging table to have portfolio_id >> = 3, because user B might also be uploading a sheet for portfolio_id >> = 9. > > The first thing to occur to me is to make the staging table TEMP, so > every session its own copy. But the second thing is, do you really > need a portfolio_id column in the staging table? After you get the > data massaged correctly into the staging table, perhaps you could load > it into the main table thusly: > > insert into main_table (portfolio_id, other_columns ...) > select 3, other_columns ... from staging_table; > > where 3 is the portfolio_id you want to assign to all the data you're > currently loading. This may not work exactly for your situation, but > does some variant make sense? > > - John Burger > MITRE > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ > *********************************************************************** Bear Stearns is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. Bear Stearns does not provide tax, legal or accounting advice. You should consult your own tax, legal and accounting advisors before engaging in any transaction. In order for Bear Stearns to comply with Internal Revenue Service Circular 230 (if applicable), you are notified that any discussion of U.S. federal tax issues contained or referred to herein is not intended or written to be used, and cannot be used, for the purpose of: (A) avoiding penalties that may be imposed under the Internal Revenue Code; nor (B) promoting, marketing or recommending to another party any transaction or matter addressed herein. ***********************************************************************
I can't use triggers because I need a specific "my_id". Here's the scenario: a user is browsing the web page for portfolio_id = 3, and wants to update the values. He uploads a CSV file, and I need its entries to be tagged with portfolio_id = 3, as another user could be uploading a CSV file for portfolio_id = 9. As suggested by John Burger, I think the cleanest way to deal with this is to have the CSV file COPY'd to a TEMP table, local to the user's session, and from there inserted with INSERT INTO portfolio(field-1, .. field-n, portfolio_id) SELECT field-1, .. field-n, 3 FROM ....; Thank you, Jaime Klint Gore wrote: > On Tue, 03 Apr 2007 12:45:54 -0400, Jaime Silvela <JSilvela@Bear.com> wrote: > >> I'd like to be able to do something like >> COPY mytable (field-1, .. field-n, id = my_id) FROM file; >> > > How do you get my_id? Can you get it in a trigger? Triggers still fire > with copy so if you can get a trigger to fill in the id column you can > copy with just the field names. > > klint. > > +---------------------------------------+-----------------+ > : Klint Gore : "Non rhyming : > : EMail : kg@kgb.une.edu.au : slang - the : > : Snail : A.B.R.I. : possibilities : > : Mail University of New England : are useless" : > : Armidale NSW 2351 Australia : L.J.J. : > : Fax : +61 2 6772 5376 : : > +---------------------------------------+-----------------+ > > *********************************************************************** Bear Stearns is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. Bear Stearns does not provide tax, legal or accounting advice. You should consult your own tax, legal and accounting advisors before engaging in any transaction. In order for Bear Stearns to comply with Internal Revenue Service Circular 230 (if applicable), you are notified that any discussion of U.S. federal tax issues contained or referred to herein is not intended or written to be used, and cannot be used, for the purpose of: (A) avoiding penalties that may be imposed under the Internal Revenue Code; nor (B) promoting, marketing or recommending to another party any transaction or matter addressed herein. ***********************************************************************
I think it's something like SELECT 'md5' + md5(password + username);
Regards,
Ben
"Thorsten Kraus" <TK-Spam@gmx.de> wrote in message news:46127989.6000409@gmx.de...This would be a possible way. Now the question is which algorithm implementation of md5 PostgreSQL uses...
Bye,
Thorsten
Ben Trewern schrieb:You could originally connect to the database as some kind of power user. Check the password against the pg_shadow view (you would need to md5 your password somehow) and then do a SET SESSION AUTHORIZATION (or SET ROLE) to change your permissions. Not sure how secure this would be but it's the way I would try. Regards, Ben "Thorsten Kraus" <TK-Spam@gmx.de> wrote in message news:46124F74.3000302@gmx.de...Hi, thanks for your answer. I cant use the username/password in my DSN because I don't connect directly via JDBC to the database. I use hibernate for all database actions. The username and password has to be stored in the hibernate configuration file... Bye, Thorsten Lutz Broedel schrieb:Can you not use the username/password as part of the DSN? Regards, Lutz Broedel---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend