Security

Let's talk a bit about security : how can Spgeed help you manage who can access and modify the data of your database ?

Well, PostgreSQL has everything we need to handle data permissions. Couldn't we use PostgreSQL to manage security concerns for us ? Handling the security directly into the database also has some benefits :

  • it avoids worring about security in your services code (with sometimes a lot of checks)
  • it avoids complexifing where clauses (adding security filters) when you just want to do a simple request (for instance, a pagination or a count(*))
  • it avoids returning too many objects from database to apply security filters afterwards

With the next sub-sections, we will see how we can use Spgeed to rely on PostgreSQL to manage database security. We will use two main concepts : grant and policy:

  • grant define the permissions at the table level
  • policy define the permisssions at the row level.

Using GRANT to define roles and users with specific access priviledges

GRANT allows you to define a role with specific access priviledges to the tables of your databases. Usually, you define this part in the creation script of your database.

Example:

CREATE ROLE role_manager;
GRANT SELECT, UPDATE ON journey TO role_manager;
GRANT SELECT, UPDATE ON hotel TO role_manager;

After you created roles, you can now create users, affecting them the roles you want. Using Spgeed for user management, you can do so by applying one of this two strategies:

  • Create a single user for each role
CREATE USER user_manager IN ROLE role_manager

However, when a user connects to your application, you need to map manually the rights of this application user with the rights of a database user:

@Update(sql = "SET LOCAL SESSION AUTHORIZATION user_manager")
int setManager();
  • Create a database user for each application user
@Update(sql = "CREATE USER \"${username}\" IN ROLE role_manager")
int createUser(String username);

Warning

CREATE USER doesn't support prepare statement, so we recommend to use UUID as username.

Now when a user connects to your application, you should indicate to the database local session which user is connected:

@Update(sql = "SET LOCAL SESSION AUTHORIZATION \"${username}\"")
int setUser(String username);

Warning

SET LOCAL doesn't support prepare statement, so we recommend to use UUID as username.

It's not over

This depends on your architecture. However, be aware that you should keep track of who is authentificated and be cautious to setup the according database user each time a user connects to the application. As an example, you can store this information in a user session or use jwt.

Using POLICY to define security at a row level

PostgreSQL allows you to really fine-tune the permissions you want to apply to your data. Thanks to the policy feature, you can apply specific permissions at the row level.

To do so, you first need to enable the row level security in the according table:

ALTER TABLE hotel ENABLE ROW LEVEL SECURITY;

Then, you can create a specific policy:

CREATE POLICY hotel_policy ON hotel TO role_manager USING (manager = current_user::TEXT);

In the previous example, we defined a specific policy hotel_policy which will be applied to all rows of the hotel table. This policy claims that you will only have access to the rows of hotel table where the manager column matches you database user name.

To get more information on row security with PostgreSQL, you can have a look here

Preventing concurrency issues

The concurrency issue can be easily resolved by the database itself. To do so, you can to deal with the transaction level:

  • Directly with Spgeed session:
session.getConnection().setTransactionIsolation(level);
  • Or with a statement:
@Update(sql = "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ")
int setRepeatableReadTransaction();

You can find more information on PostgreSQL site.