Search Postgresql Archives

Roles with passwords; SET ROLE ... WITH PASSWORD ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



At present, a role may have a password but unless it's a login role the docs say that password doesn't get used.

I currently have an app where it'd be handy to be able to:

   SET ROLE rolename WITH PASSWORD 'blah';

to switch to role `rolename' only if the password `blah' is correct for that role. `rolename' might or might not be a login role, though in most of the cases I'm looking at it'd make more sense for it to be a login role.

Why would this be useful? The app relies on PostgreSQL for basic user management and authentication. User rights are controlled by role assignments, and are enforced at the database level by appropriate table, column and function permissions plus selective use of SECURITY DEFINER functions and triggers. The app only uses role memberships to decide what UI to hide to avoid confusing the user with permission errors from the DB.

This works extremely well, _except_ that occasionally it's desirable to override a user's rights by intervention of a supervisor user. Having a way to do this by switching to a role by providing a password to confirm access would be nice, especially if the user didn't have to be a member of that role already.

Right now I'm having to spawn a new connection with the supplied supervisor username & password, then do the work in that connection. This works OK, but:

(a) The switch can't be done mid-transaction so that priveleges are held for the minimum time possible. I'm looking at using `SET ROLE' to drop down to lower rights in the supervisor connection instead, but this could be clumsy when the main user has several roles significant for the operation(s) being performed.

(b) The new connection doesn't hold the advisory locks the first connection had, which is occasionally problematic.

(c) It's a PITA when working through an ORM like Hibernate, whereas a `SET ROLE' would be trivial and convenient.

(d) I'd rather not spawn the extra backend, though I guess it doesn't matter much with an event of this rarity.


Anyway ... I'm curious about whether `SET ROLE rolename WITH PASSWORD' is something that's technically practical to implement in PostgreSQL and what people think about the idea.

I don't see any obvious, gaping security issues with doing this, since anybody who can 'SET ROLE ... WITH PASSWORD ...' should be able to log in with those credentials too. However, there may be issues interacting with external auth systems like ldap or kerberos. Thoughts?

Also: I'm currently thinking of writing a `SECURITY DEFINER' function that tests a supplied password against a named role by direct access to pg_shadow, and if the password matches invokes SET ROLE with that role. Crazy?

--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux