Sql-server – Known vulnerabilities for when a guest executes a stored procedure on SQL Server

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:

• SET SESSION AUTHORIZATION
• SET ROLE
• GRANT

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.

I'm not aware of this being possible with SSDT unfortunately.

Depending on how big the project is and how many procedures you intend to enhance with 2012 goodies, it may be manageable with Composite Projects.

SSDT can combine a database project with one or more referenced database projects or dacpacs to describe a single composite database schema. Using a composite project allows a large database to be broken down into more manageable chunks, allows different people or teams to have responsibility for different parts of the overall schema, and enables reuse of database object definitions in multiple databases.

The notion would be to have a base project, containing the common object definitions and version specific projects for procedures that used new features. The 2012 project would reference the base project and a compile/build would combine objects from both.

The PITA would be that you can't override an object in the base project with an object in a composite, so you would have to maintain base, 2008 & 2012 projects. When you wanted a 2012 version of a particular procedure, you would have to remove it from base and create a version in both 2008 & 2012 projects.