Webappication and PostgreSQL login roles

No idea??

Thorsten Kraus schrieb:

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

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 //

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:

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...

This would be a possible way. Now the question is which algorithm
implementation of md5 PostgreSQL uses...

Bye,
Thorsten

Ben Trewern schrieb:

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 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

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.
***********************************************************************