Re: [GENERAL] LYDB: Feasible to use PG roles instead ofapplication-level security?

----- Original Message -----
> From: "Alban Hertroys" <haramrae@gmail.com>
> To: "Guyren Howe" <guyren@gmail.com>
> Cc: "pgsql-general" <pgsql-general@postgresql.org>
> Sent: Friday, December 30, 2016 6:23:27 AM
> Subject: Re: [GENERAL] LYDB: Feasible to use PG roles instead of application-level security?
>
>
> > On 30 Dec 2016, at 4:19, Guyren Howe <guyren@gmail.com> wrote:
> >
> >
> > ... wonder whether it is practical to use PG’s own roles and
> > security model in lieu of using an application-level one.
> >
> > It seems that the role system in PG is sufficient for most general
> > purposes. One could presumably also have a table with role names and
> > associated metainformation (email address etc) as needed.
> >
> > If I have a system with many thousands of users, is it practical to manage
> > these users’ authentication and authorization using *just* Postgres?
>
> Postgres roles are global to the cluster, so you would end up with multiple
> thousands of roles if you have multiple databases in your cluster with
> different users on each. Which roles each user is allowed to have becomes
> quite the nightmare for the administrators, I suspect.
>
> For a web-application facing the internet, I'd say no, don't do that. You're
> dealing with far too many users to be maintainable.
>
> For an intranet database in a not-too-large company with a fixed set of
> users, it could be a good solution, especially if those roles can be linked
> to the company's LDAP server (assuming that's possible, I don't know).
> Multiple intranet applications on that same database can use the same users
> and roles.
>
> Someone needs to do the administration though; with volumes (of users) like
> that and the database knowledge level of the average system administrator, a
> GUI seems preferable. IMHO, pgadmin provides too many features to be
> practical for someone like that, you would probably prefer something that
> only does user administration. I don't know of anything that does that
> though (not a GUI user myself)...
>
> > It occurs to me that some client frameworks might have issues with their
> > connection pools if those connections keep switching users, assuming they
> > even can, but let’s set that aside for now. Or perhaps every connection
> > could immediately do a SET USER before executing its connection?
> >
> > This seems an attractive proposition from a security standpoint: if I use
> > row-level security pervasively, I can have a security system that’s
> > nestled nice and close to the data and presumably tricky to work around
> > from a hacker given direct access only to the client application.
>
> With a few changes, that could work very well.
>
> First, create roles for the different types of users that you expect. In a
> company, that could be by division, distinguishing division-heads, interns,
> etc.
>
> Secondly, have a table with the users and their attributes like you describe.
> Include an attribute for their database role there. Only administrator users
> should have access to that table.
>
> Finally, create a stored procedure that looks up a user name in that table
> and sets the accompanying role. If a user is not found, set the role to some
> default 'unprivileged' user.
> Make that procedure a SECURITY DEFINER with according permissions. That role
> stays active the entire session, so unless you close the connection, create
> a new one or change the user's role, this procedure doesn't need calling
> again.
>
> > Is this practical? Has anyone here done it? What might the caveats be?
>
> It's a fairly common practice, the ML archives should contain plenty of
> examples.



I cannot speak to the thousands of users scenario in practice, but in principle it seems workable.

As a practical matter, I have implemented something along the lines of what Hertroys describes, and taken it a step
further,engaging schema privileges as well. 

The PUBLIC schema contains a single relation that exposes a view with three columns: username, password, and
email_address.You could expose more, but for my purposes minimal is best. This view serves the express purpose of
creatingnew user accounts and is, by means of the rule system, writeable. Technically it is readable, too, but a select
statementprovides no information about underlying data ... so stalkers can't get a user list. 

pg_hba.conf is configured to allow anonymous login which has usage permission on, and only on, the PUBLIC schema. So
anyonecan log in and see that one view and do an insert on that view. 

The rule system pushes down the INSERT onto a table in a private schema that has a trigger which creates the data base
rolecorresponding to the new user row inserted into the view. Thus, prospective users "self administer" and create
theirown accounts as needed. The trigger also grants the new role membership in a particular group role. 

The group role has usage privileges on a second schema that exposes broader visibility into the data base as well as
additionalinsert and update privileges on some of these other, newly exposed views. 

You can read about and actually try it yourself at https://fairwinds.btober.net. Read the first few sections of the
tutorial.Just be aware that that is a test environment, so it may or may not be available at any particular,
unannouncedtimes that I'm playing around with it, and will occasionally be re-initialized.