SEO

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

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
>


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

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

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

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

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster