Postgresql – Choice of authentication approach for financial app on PostgreSQL

database-designpostgresqlSecurity

First some background.

The LedgerSMB project is an open source financial accounting software project that runs on PostgreSQL. We implement a very large amount of business logic in user defined functions, which act as the main mapping tool between program object methods and database behavior. Currently we use database users as authentication users, partly by choice (this allows centralized security logic, so that other tools can be written and re-use permissions given to users), and partly by necessity (after we forked from SQL-Ledger, there weren't many options for retrofitting security onto that codebase).

This buys us access to a reasonable number of single signon options that PostgreSQL has access to, from LDAP to Kerberos 5. We can even use PAM where passwords are concerned. It also allows us to re-use permissions when integrating with other applications, or allowing other client interfaces. For a financial accounting application this seems like a net win.

There are obvious costs involved. For the web application, we are very limited to the types of http auth that can be supported. DIGEST for example is entirely out. BASIC works, and we could implement KRB5 easily enough (I plan on having this supported and working out of the box for 1.4). Very strong authentication measures cannot be properly managed on this directly although we could probably shim them in if necessary (for example BASIC + client-side SSL cert with a cn matching the user name and a specific root ca).

At the same time we've run into a fair amount of criticism mostly from the development crowd and more occasionally from dba's who tell me that the application should be the security barrier, not the database. My view is still that a smaller security perimeter is generally better, that the re-use of business logic and security logic go together, and that it strikes me as dangerous to re-use business logic without re-using security logic on the same level of the program.

Am I missing any major tradeoffs here? Are there gotchas that I am not considering?

Best Answer

I think you're conflating authentication and authorization.

I completely agree that keeping the security model in the DB is wise, especially as LedgerSMB is designed with access from multiple clients in mind. Unless you plan on going 3-tier with a middleware layer it makes perfect sense to have users as database roles, especially for something like an accounting app.

This does not mean you have to authenticate users against the database using a PostgreSQL-supported authentication method. Your database users, roles and grants can be used for authorization only if you like.

Here's how it works for a web ui for example:

  • jane connects to web ui server and authenticates using whatever method is desired, say HTTPS X.509 client certificate handshake and DIGEST auth. The server now has a connection from a user it accepts is really jane.

  • The server connects to PostgreSQL using a fixed username/password (or Kerberos or whatever you like), authenticating its self to the db server as the user webui. The db server trusts webui to authenticate its users so webui has been given appropriate GRANTs (see below).

  • On that connection the server uses SET ROLE jane; to assume the authorization level of the user jane. Until RESET ROLE; or another SET ROLE is run, the connection is operating with the same access rights as jane and SELECT current_user() etc will report jane.

  • The server maintains the association between the database connection on which it has SET ROLE to jane and the web session for user jane, not allowing that PostgreSQL connection to be used by other connections with other users without a new SET ROLE inbetween.

You are now authenticating outside the server, but maintaining authorisation in the server. Pg needs to know what users exist, but doesn't need passwords or authentication methods for them.

See:

Details

The webui server controls the queries run, and it's not going to let jane run raw SQL (I hope!) so jane can't RESET ROLE; SET ROLE special_admin_user; via the web ui. For added safety I'd add a statement filter to the server that rejected SET ROLE and RESET ROLE unless the connection was in or entering a pool of unassigned connections.

You're still free to use direct authentication to Pg in other clients; you can mix and match freely. You just have to GRANT the webui user the rights to SET ROLE to users who can log in via the web and then give those users any normal CONNECT rights, passwords, etc you want. If you want to make them web-only, REVOKE their CONNECT rights on the database (and from public).

To make such an authentication/authorisation split easy I have a special role assume_any_user that I GRANT every newly created user to. I then GRANT assume_any_user to the real username used by things like a trusted web front-end, giving them the rights to become any user they like.

It's important to make assume_any_user a NOINHERIT role, so the webui user or whatever has no privilges by its self and can only act on the database once it's SET ROLE to a real user. Under no circumstances should webui be a superuser or DB owner.

If you're connection pooling, you can use SET LOCAL ROLE to set role only within a transaction, so you can return connections to the pool after COMMIT or ROLLBACK. Beware that RESET ROLE still works, so it still isn't safe to let the client run whatever SQL they want.

SET SESSION AUTHORIZATION is the related but stronger version of this command. It doesn't require role membership, but it's a superuser only command. You don't want your web ui connecting as a superuser. It can be reversed with RESET SESSION AUTHORIZATION, SET SESSION AUTHORIZATION DEFAULT or SET SESSION AUTHORIZATION theusername to regain superuser rights so it isn't a privilege-dropping security barrier either.

A command that worked like SET SESSION AUTHORIZATION but was irreversible and would work if you were a role member but not superuser would be great. At this point there isn't one, but you can still separate authentication and authorisation pretty well if you're careful.

Example and explanation

CREATE ROLE dbowner NOLOGIN;
CREATE TABLE test_table(x text);
INSERT INTO test_table(x) VALUES ('bork');
ALTER TABLE test_table OWNER TO dbowner;

CREATE ROLE assume_any_user NOINHERIT NOLOGIN;
CREATE ROLE webui LOGIN PASSWORD 'somepw' IN ROLE assume_any_user;

CREATE ROLE jane LOGIN PASSWORD 'somepw';
GRANT jane TO assume_any_user;
GRANT ALL ON TABLE test_table TO jane;

CREATE ROLE jim LOGIN PASSWORD 'somepw';
GRANT jim TO assume_any_user;

Now connect as webui. Note that you can't do anything to test_table but you can SET ROLE to jane and then you can access test_table:

$ psql -h 127.0.0.1 -U webui regress
Password for user webui:

regress=> SELECT session_user, current_user;
 session_user | current_user 
--------------+--------------
 webui        | webui
(1 row)



regress=> SELECT * FROM test_table;
ERROR:  permission denied for relation test_table

regress=> SET ROLE jane;
SET

regress=> SELECT session_user, current_user;
 session_user | current_user 
--------------+--------------
 webui        | jane
(1 row)

regress=> SELECT * FROM test_table;
  x   
------
 bork
(1 row)

Note that webui can SET ROLE to jim, even when already SET ROLEd to jane and even though jane hasn't been GRANTed the right to assume the role jim. SET ROLE sets your effective user ID, but it doesn't remove your ability to SET ROLE to other roles, that's a property of the role you connected as, not your current effective role. Consequently you must carefully control access to the SET ROLE and RESET ROLE commands. There is, AFAIK, no way to permanently SET ROLE for a connection, truly becoming the target user, though it'd certainly be nice to have.

Compare:

$ psql -h 127.0.0.1 -U webui regress
Password for user webui:

regress=> SET ROLE jane;
SET

regress=> SET ROLE jim;
SET
regress=> SELECT session_user, current_user;
 session_user | current_user 
--------------+--------------
 webui        | jim
(1 row)

to:

$ psql -h 127.0.0.1 -U jane regress
Password for user jane:

regress=> SET ROLE webui;
ERROR:  permission denied to set role "webui"
regress=> SET ROLE jim;
ERROR:  permission denied to set role "jim"

This means that SET ROLE isn't exactly the same as logging in as a given role, something you must keep in mind.

webui can't SET ROLE to dbowner since it hasn't been GRANTed that right:

regress=> SET ROLE dbowner;
ERROR:  permission denied to set role "dbowner"

so by its self it's pretty powerless, it can only assume the rights of other users and only when those users have web access enabled.

Related Question