1. 13

I’ve done a fair amount of work with MS-SQL, PostgreSQL, and MySQL databases, and one of the things that I always thought was odd was the way that all 3 of them, and presumably Oracle too, have user and permission systems of moderate to high levels of sophistication, yet I’ve never heard of anyone using them. All I’ve developed have been desktop and web apps accessing database servers, and every one of them at every company I’ve worked at has used a single database user with full privileges to everything on the server.

So I’m curious - have any fellow lobsters actually set up a production database server with multiple user accounts with varying levels of permissions over the database tables? Have you ever used features like row and column level security in production? What did you use these features for, and how did it end up working out?

  1.  

  2. 12

    We have some users set up with read-only permissions, mostly for applications which are expected to do some data analysis or visualization but never make changes to the source data. We also restrict access on a table-by-table basis for applications with limited scope.

    If application scope changes it means you have to change the permissions, but that little bit of administrative overhead has seemed worthwhile to keep clear boundaries and reduce the scope for accidental breakage.

    1. 6

      We have several “services” (not the micro-variety) that use a shared database. Each service has its own user with limited access to the tables it needs, but more importantly it lets us use a tool like PgHero or PgAnalyze to look at database usage patterns on a per-service basis. This has helped a lot with identifying bad queries, application bugs, etc.

      1. 3

        A long time ago I built a system to manage (Unix) logins for undergraduate university students. Driven by a copy of course enrolment data, it knew when people were entitled to accounts due to what courses they were in, let students more or less pick their own login names, handled slowly suspending and expiring accounts for students who had moved on, and so on. The core of the system was a PostgreSQL database of enrolment data, what logins existed in what state, and so on. Because it involved creating and deleting Unix accounts, I did my best to be security-aware, and as part of this I strongly separated out various levels of database access by role. A number of things didn’t need write access to the database, and other things only needed very selective write access; for example, the ‘let students pick their login name’ portion only needed to create a new record in the logins table, with no need for the power to write to, say, the course data.

        This was a long time ago and I haven’t been responsible for the system for years, but I believe I only used table level permissions instead of trying to use column or row level permissions.

        1. 3

          I’ve been working on my latest project with PostgreSQL and PostgREST. No production experience yet, but it’s been a thorough (and ongoing) exercise in learning how to leverage roles for user-level authorization.

          I keep meaning to write this stuff down as a blog post, but for now, an obnoxiously long comment is what I’m going with :)

          Column security: I’m a fan. So far it has been easy to read, write, and reason about in SQL. For each table I create I’ve been doing pretty explicit column grants, like:

          /* restrict even yourself from seeing access tokens */
          GRANT SELECT (id, created, name, fb_id)
             ON TABLE api.members
             TO member;
          
          GRANT UPDATE (name)
            ON TABLE api.members
            TO member;
          
          /* let loggerinner do its thing */
          GRANT SELECT
             ON TABLE api.members
             TO loggerinner;
          
          GRANT INSERT (fb_id, name, fb_token, fb_expires)
             ON TABLE api.members
             TO loggerinner;
          
          GRANT UPDATE (name, fb_token, fb_expires)
             ON TABLE api.members
             TO loggerinner;
          

          Row level security: So far I’ve only used it for the member system, but upcoming features are going to have more interesting privacy rules that will make me start using it more:

          /* restrict members to only updating themselves */
          ALTER TABLE api.members ENABLE ROW LEVEL SECURITY;
          CREATE POLICY own_account_only ON api.members
                  USING (current_user <> 'anon')
             WITH CHECK (current_user = 'loggerinner'  -- loggerinner upserts
                     OR  id = auth.user_id());         -- update yourself
          

          It’s nice how the row and column rules combine, so the RLS allowing people to create/update their own rows only actually lets them update their name.

          However….

          Views are footguns

          In postgres, any data seen through a view is seen through the eyes of the role that created the view. Since my schema migrations are running as a superuser, that means that no rules are being applied at all, and if I’m not careful, a view can be exposing arbitrarily sensitive data.

          I can get column rules back for a single role by SET ROLEing to it before creating the view, or I can make copies of the view for each role I want to GRANT, doing SET ROLE to the right view before each. Even if I do that and don’t make mistakes, RLS is not applied.

          Instead, I revert to Just Being Careful with views, and thinking through all rules that “should” be applied to the rows and columns being returned, and trying to make sure that they are.

          Just as a word of caution :)

          1. 2

            A few notes from the office:

            • We use roles and grants quite extensively in our main PostgreSQL DB, and for at least 5 years or more. To some extent this has been a good thing. It’s also meant a lot of complications. The phrase “update the grants file” connotes an unpleasant and somewhat terrifying task, for instance. (And yet, update it we must.)
            • We’ve tended to use views instead of column level security.
            • Row level security in PostgreSQL seems to be pretty performance intensive. But, mostly we just haven’t needed it yet.

            Roles are also proving fairly useful for a data warehouse DB (which FDWs to other DBs, btw).