Postgresql database questions

[Maximilian]

I picked postgresql for my web application. Mainly because the pgadmin iii tool looks far less intimidating than the one that came with MySQL. I really like it so far. Anyways with the web application context in mind I have a few questions:

1. Should I create a regular user for my application to use and limit its permissions? As opposed to just using the "postgres" superuser account for everything?

2. Is there anything wrong with using the "public" schema? Its just there by default and seems to work well.

 

[Ken g6]

1. Looks like a good idea for security purposes.

2. Well, at that link, they don't use a different schema, but they do make changes to the public schema.

 

[Cogman]

1. Yes absolutely!

2. nope, nothing wrong with the public schema.

How you go about this is up to you. Some feel that the only way an application should interact with the DB is through stored procs. Others don't.

If you end up having multiple applications accessing the same DB, then it makes sense to constrain permissions as much as possible. That way, in case of breach, the damaged from leaked data will at least be somewhat contained. However, if it is just the one application that is using the whole db, then it is a bit more murky on how much benefit you can get from having very tight security around everything.

 

[Maximilian]

Cheers guys, I made a new account for my app and figured out permissions. Even if its just one application that will use the db it'll be good practice anyway.

Now ive got concurrency on my mind, im fine with synchronizing methods that will make changes to the db but im not sure if using the same object for everything would work. For example Connection, ResultSet and Statement. They all need to be opened/closed. I can declare/initialize all these things globally and access them from a single global class and just use the same object or I can just declare/initialize them where and when they are needed and close them there too.

Which is the best practice?

Here they say not to use a single connection for multiple threads which suggests to me one object for the whole app isnt a good plan:
http://stackoverflow.com/questions/1531073/is-java-sql-connection-thread-safe

 

[Cerb]

Which is the best practice?

Using a Pg-specific connection driver, and pooled connections. With a pool set up, each connection can have its own object/identifier.
I'm no Java person, but try this:
https://jdbc.postgresql.org/documentation/93/datasource.html

 

[Cogman]

Cheers guys, I made a new account for my app and figured out permissions. Even if its just one application that will use the db it'll be good practice anyway.

Now ive got concurrency on my mind, im fine with synchronizing methods that will make changes to the db but im not sure if using the same object for everything would work. For example Connection, ResultSet and Statement. They all need to be opened/closed. I can declare/initialize all these things globally and access them from a single global class and just use the same object or I can just declare/initialize them where and when they are needed and close them there too.

Which is the best practice?

Here they say not to use a single connection for multiple threads which suggests to me one object for the whole app isnt a good plan:
http://stackoverflow.com/questions/1531073/is-java-sql-connection-thread-safe

When it comes the the DB. synchronizing methods is "doing it wrong" (I'm assuming java). The driver should take care of any sort of concurrency problems for you. (Unless sqlite is being used, but that is the exception not the rule).

You shouldn't really try to thread reading of db data because you won't really get a whole lot of benefit from it (your network is going to be slower than you object creation by a long shot).

You shouldn't reuse connections. Rather, each time you should hit the datasource to get a new connection (trust me on this, reusing a connection is bad evil juju). Most drivers (and postgres is one of them) have connection pooling which eliminates pretty much all of the overhead of establishing connections.

Raw jdbc kind of sucks. I would suggest at a minimum using something like jdbcTemplate however you may like and get a lot of milage out of several of the ORMs such as Hibernate, MyBatis, or JDBI.

 

[Cogman]

public class MyDAO {
	private final JdbcTemplate jdbcTemplate;

	public MyDAO(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public List<Thing> getThings() {
		return jdbcTemplate.query("SELECT * FROM Thing WHERE val=?", (ResultSet rs, int rowNum)-> {
			int id = rs.getInt("id");
			return new Thing(id);
		}, 7);
	}
}

Here is an example of how I would go about touching the database using jdbc.

Things to note here. I would have setup 1 jdbctemplate object. I didn't here, but I would probably use Injection (Dagger is currently my favorite) to pipe it everywhere. My DAOs are generally singletons and usually carry very little state (maybe a cache).

 

[Maximilian]

Cheers man, ive set up connection pooling to get a new connection each time, seems to work ok. I dunno I quite like the raw no framework approach so far 🙂 At least ive made more progress on the rest of this app without a framework than I did with a framework.

Iim a bit confused with regards to not reusing connections and the PreparedStatement class though. This is an initial method I wrote to get the users email and password from the database, I was using a Statement object but ive shoehorned in PreparedStatement because its its faster and protects from SQL injection:

private Map<String,String> getUserDetails(String loginAttemptEmail)
        {
            Map output = new HashMap<String,String>();
            ResultSet resultSet = null;
            String retrievedEmail = null;
            String retrievedPassword = null;
            PreparedStatement getEmailStatement = null;
            String getUserEmailString = "SELECT email,password FROM usertable WHERE email = ?";
            Connection databaseConnection = DatabaseUtils.getDatabaseConnection();
            try
            {
                getEmailStatement = databaseConnection.prepareStatement(getUserEmailString);
                getEmailStatement.setString(1,loginAttemptEmail);

                resultSet = getEmailStatement.executeQuery();
                resultSet.next();
                retrievedEmail = resultSet.getString("email");
                retrievedPassword = resultSet.getString("password");

            }
            catch (SQLException ex)
            {
                Logger.getLogger(AuthenticationServlet.class.getName()).log(Level.SEVERE, null, ex);
            }
            finally
            {
                DatabaseUtils.closeConnections(databaseConnection, resultSet, getEmailStatement);
            }
            
            if(retrievedEmail != null && retrievedPassword != null)
            {
                output.put(retrievedEmail, retrievedPassword);
                return output;
            }
            else
            {
                return null;
            }

        }

It works but if I understand this all correctly then PreparedStatement is being kinda wasted here because its being recreated each method call with a different connection. Dosent this negate the idea of having a PreparedStatement? Is this a case where reusing the same connection would be a good plan?

EDIT: Aha here we go!
http://stackoverflow.com/questions/11889401/preparedstatement-pool-with-connection-pool
http://stackoverflow.com/questions/...ement-preparedstatement-and-resultset-in-jdbc

Seems that even though im closing the resources they're not actually gone they're just returned to the pool. And the database server stores the PreparedStatement in cache or something, so it should be fine.

 

[Cogman]

Cheers man, ive set up connection pooling to get a new connection each time, seems to work ok. I dunno I quite like the raw no framework approach so far 🙂 At least ive made more progress on the rest of this app without a framework than I did with a framework.

Iim a bit confused with regards to not reusing connections and the PreparedStatement class though. This is an initial method I wrote to get the users email and password from the database, I was using a Statement object but ive shoehorned in PreparedStatement because its its faster and protects from SQL injection:

private Map<String,String> getUserDetails(String loginAttemptEmail)
        {
            Map output = new HashMap<String,String>();
            ResultSet resultSet = null;
            String retrievedEmail = null;
            String retrievedPassword = null;
            PreparedStatement getEmailStatement = null;
            String getUserEmailString = "SELECT email,password FROM usertable WHERE email = ?";
            Connection databaseConnection = DatabaseUtils.getDatabaseConnection();
            try
            {
                getEmailStatement = databaseConnection.prepareStatement(getUserEmailString);
                getEmailStatement.setString(1,loginAttemptEmail);

                resultSet = getEmailStatement.executeQuery();
                resultSet.next();
                retrievedEmail = resultSet.getString("email");
                retrievedPassword = resultSet.getString("password");

            }
            catch (SQLException ex)
            {
                Logger.getLogger(AuthenticationServlet.class.getName()).log(Level.SEVERE, null, ex);
            }
            finally
            {
                DatabaseUtils.closeConnections(databaseConnection, resultSet, getEmailStatement);
            }
            
            if(retrievedEmail != null && retrievedPassword != null)
            {
                output.put(retrievedEmail, retrievedPassword);
                return output;
            }
            else
            {
                return null;
            }

        }

It works but if I understand this all correctly then PreparedStatement is being kinda wasted here because its being recreated each method call with a different connection. Dosent this negate the idea of having a PreparedStatement? Is this a case where reusing the same connection would be a good plan?

EDIT: Aha here we go!
http://stackoverflow.com/questions/11889401/preparedstatement-pool-with-connection-pool
http://stackoverflow.com/questions/...ement-preparedstatement-and-resultset-in-jdbc

Seems that even though im closing the resources they're not actually gone they're just returned to the pool. And the database server stores the PreparedStatement in cache or something, so it should be fine.

You got it.

Connections are cached, so are PreparedStatements. But beyond that, the cost of recomputing them is pretty minimal in the grand scheme of things (Though, establishing new connections can be expensive. Hence connection pooling datasources).

If you want the low level stuff, JdbcTemplate really is quite low level, it just handles a ton of the boilerplate for you.

Here is your method doing the exact same thing (java 8, but you can used java 6 and 7)

private Map<String, String> getUserDetails(String loginAttemptEmail)
{
	String getUserEmailString = "SELECT email,password FROM usertable WHERE email = ?";
	try {
		return jdbcTemplate.query(getUserEmailString, (ResultSet rs)-> {
			Map<String, String> output = new HashMap<>();
			rs.next();
			String retrievedEmail = rs.getString("email");
			String retrievedPassword = rs.getString("password");
			if (retrievedEmail != null && retrievedPassword != null) {
				output.put(retrievedEmail, retrievedPassword);
				return output;
			}
			else {
				return null;
			}
		});
	}
	catch (DataAccessException ex)
	{
		Logger.getLogger(AuthenticationServlet.class.getName()).log(Level.SEVERE, null, ex);
	}
	return null;
}