Does anyone know how I can solve this problem in Postgres:
I am creating a table of user permissions. I want to represent each
permission as a bit location. Each new permission I add to my system
would be assigned to the next available bit (or column if you will)
That way I can simply turn on a particular bit location for a
particular user and then AND their permission level with a particular
permission bit.
Just as an example:
Access to financial data is the first bit and would be represented by
1.
Access to the admin section would be the 2nd bit, represented by 01.
Access to the reporting section would be the 3rd bit, represented by
001.
Thus a user with access to the admin section but nothing else would
have 010
and a user with access to everything would have 111.
All possible permissions are stored in one table and a users permission
level is stored in the user table, but both fields are integer data
types.
HERE IS WHAT I WANT TO DO:
How do I setup a postgres constraint that will not allow an insert on
the permission table with an integer value that has more than one bit
set. In other words you could insert 1, 2, 4, 8, 16, 32, etc. because
there is only a single bit on in all of those integers, but not insert
3, 5, 6, 7, 9, 15, 19, etc. because they have multiple???