[GENERAL] 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 ---(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
Re: [GENERAL] Webappication and PostgreSQL login roles
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/
Re: [GENERAL] Webappication and PostgreSQL login roles
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 [EMAIL PROTECTED] 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
Re: [GENERAL] Webappication and PostgreSQL login roles
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 [EMAIL PROTECTED] To verify the digital signature, you need to load the following certificate: https://pki.pca.dfn.de/uh-ca/pub/cacert/rootcert.crt smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Webappication and PostgreSQL login roles
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
Re: [GENERAL] Webappication and PostgreSQL login roles
In response to Thorsten Kraus <[EMAIL PROTECTED]>: > 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
Re: [GENERAL] Webappication and PostgreSQL login roles
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" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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
Re: [GENERAL] Webappication and PostgreSQL login roles
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" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] 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
Re: [GENERAL] Webappication and PostgreSQL login roles
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
Re: [GENERAL] Webappication and PostgreSQL login roles
I think it's something like SELECT 'md5' + md5(password + username); Regards, Ben "Thorsten Kraus" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] 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
