Announcement

Collapse
No announcement yet.

Changes Coming For PostgreSQL 9.5

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Changes Coming For PostgreSQL 9.5

    Phoronix: Changes Coming For PostgreSQL 9.5

    The PostgreSQL 9.5 release change-log was recently updated in Git to reflect all of the latest changes for this next version of this database server due out in 2016...

    http://www.phoronix.com/scan.php?pag...Changes-So-Far

  • #2
    Microsoft SQL Server can do data masking.

    You can set it on certain columns. Such as when you do a SELECT query on a table, you can get certain data in certain columns masked.
    Example you can prevent developers (or some group) to have full access to customer data.

    Example make all email addresses show up as "*@example.com" or "[email protected]*.com" with asterisk to mask the data.
    This can be set on PII fields such as phone number, email address, social security number, password hashes, etc.

    Comment


    • #3
      Originally posted by uid313 View Post
      Microsoft SQL Server can do data masking.

      You can set it on certain columns. Such as when you do a SELECT query on a table, you can get certain data in certain columns masked.
      Example you can prevent developers (or some group) to have full access to customer data.
      I'm not sure why that should be relevant, but you could do essentially the same thing with a view. Just create a view without the masked column and
      grant the appropriate permissions to the view and the original table.

      Comment


      • #4
        Lately I'm using SQL server. I'm just want some simple things out of a database server. The SQL server GUI (called SSMS) is great. Almost any new feature is also added to this GUI.

        That said, there's various things that postgres can do that I find lacking in SQL server. E.g. I'd like to have a copy of one table as temp table. In postgres you can have one table rely/extend another table. So you don't have to update multiple tables in case you refresh one. That's a lot cleaner IMO. I haven't found the same yet in SQL server.

        SQL server also seems slow to change a table containing loads of data. Seems related to the (IIRC) recovery options. It's much faster in postgres to do this.

        Comment


        • #5
          I've worked for a company with billions of rows sized tables that used MS SQL Server when I came in but was just trying to transition to a Linux server which became my sole responsibility.

          My predecessor has designed the whole thing with MongoDB in mind which proved to be a horrible idea so we backpedalled into using Linux with PostgreSQL instead.

          It proved to be a great success and while it took almost a month to get performance done right with indexes etc. it turned out PostgreSQL outperforms SQL Server in most areas when configured right. The only thing I missed were covered indexes (e.g. you got 5 columns you need to "get" with a specialized query from a huge table, but you only search on 2 of those, so you build an index on 2 columns but it contains the other 3 as "covered" so you don't have to access row data making into an index only search).

          One of the biggest boons of postgreSQL compared to at least the older version of SQL Server was the autovacuuming. Indexes also didn't deteriorate so much as with SQL Server.
          Last edited by Almindor; 16 December 2015, 12:14 PM. Reason: typos

          Comment


          • #6
            Originally posted by Archprogrammer View Post

            I'm not sure why that should be relevant, but you could do essentially the same thing with a view. Just create a view without the masked column and
            grant the appropriate permissions to the view and the original table.
            DB2 has this capability as well. The difference is that with a mask, the query will still return the column, but the data will be masked.

            Let's say you have an employee table that contains the employee's SSN. The only people who need to have access to the full SSN is people in payroll, but managers might need to know the last 4 digits. You can create a mask like so:
            Code:
            CREATE MASK SSN_MASK ON EMPLOYEE
               FOR COLUMN SSN RETURN
                  CASE
                     WHEN (VERIFY_GROUP_FOR_USER(SESSION_USER,'PAYROLL') = 1)
                        THEN SSN
                     WHEN (VERIFY_GROUP_FOR_USER(SESSION_USER,'MGR') = 1)
                        THEN 'XXX-XX-' || SUBSTR(SSN,8,4)
                     ELSE NULL
                  END
               ENABLE;
            If you query select SSN from EMPLOYEE you'll get either

            123-45-6789 if you're part of the PAYROLL group or
            XXX-XX-6789 if you're part of the MGR group or
            NULL if you're not in either group

            You could do this with views, but it would be more work:
            - create a view without the SSN column for users not in PAYROLL or MGR groups
            - create a scalar function to mask the data and then create a view passing the SSN column to the scalar function
            - update all your application code to determine which group the user is in and query the correct view/table appropriately


            DB2 also has PERMISSIONs which are row based access control. This prevents rows from being returned based on whatever criteria you decide, similar to the masks above. You can find out more here: https://www-01.ibm.com/support/knowl...fymaskperm.htm

            Comment


            • #7
              Yes! But my patches will only in 9.6

              Comment


              • #8
                Originally posted by Almindor View Post
                The only thing I missed were covered indexes (e.g. you got 5 columns you need to "get" with a specialized query from a huge table, but you only search on 2 of those, so you build an index on 2 columns but it contains the other 3 as "covered" so you don't have to access row data making into an index only search).
                Didn't postgres get covering indexes in 9.2? http://www.postgresql.org/about/news/1415/

                Comment


                • #9
                  Originally posted by fuzz View Post

                  Didn't postgres get covering indexes in 9.2? http://www.postgresql.org/about/news/1415/
                  Sorry about the late reply, I just noticed this post..

                  I think it was false advertizing. There's nothing in http://www.postgresql.org/docs/curre...eateindex.html for covered indexes and I can't find anything when googling either. I don't think postgres has them.

                  Comment

                  Working...
                  X