How to use postgresql roles to connect a database?

2
votes

We saw PostgreSQL roles in class this year. Our teacher told us that it is more secure to use different roles with custom rights for every table or even column if necessary.

We have a project in which we have to use PostgreSQL to build a website with restricted access for connected users, who can be of different types (admin, employee, client). To follow the teacher's recommendations, we created different roles with different rights (one for each type of user).

We decided to use Go for our back end (with token auth) but I can't figure it out how to use our roles, which are more groups than users. I read in the doc that you "open" the connection to the DB once for all but to do so you have to give a PostgreSQL role. I didn't find a way to change the connected role without closing and reopening the DB. If I run the application without changing connected roles, how can PostgreSQL control if a user has the right to access tables he needs for the requests.

2
postgresqlsecuritygoroles

2 Answers

2
votes

You can change roles on the fly in PostgreSQL. If you are logged in as nobody, and nobody is a member of role cleve, you can become role admins with

SET ROLE cleve;

But using that during authentication is problematic, because there is nothing that keeps the user from running the statement

RESET ROLE;

to become nobody again and then impersonating somebody else.

Typically, there are two ways how you can use the role system to leverage database permissions:

  1. You have a personalized database user for every user of the application.

    That is of course only feasible if the set of users is fairly constant and limited.

    Then the individual users have no permissions at all, and there are certain roles like admin, reader, accountant and so on. The login roles are assigned permissions by becoming members of one or more of these roles, and they inherit their permissions.

  2. You don't have personalized database users.

    Then you only have one login role per set of permissions, say accountant, admin, viewer and so on.

    The application has to decide as which user it should connect before establishing the database connection. If you need database queries for this decision, you perform those as a nobody database user with very limited permissions. For example, it may call a function that verifies a user-supplied password.

0
votes

You can use the set role command to change the role while the session is open.