Unanswered: Database Security (Login) : Best Practice Guidance
I am in the early planning stages of a database and application for an engineering consultancy, and I have some questions regarding what is 'Best Practice' for the security side of the database particularly concerning the User Login Role / Group Role, and how that user information can be incorporated into record entries for change control [Inserted by & date / last modified by & date].
An initial Use Case assessment of the users of the final database would be:
Internal : Various levels of staff with access to all the records in any particular table, but restrictions as to tables they could access. eg. Associates: Access to most tables, except some financial records.
Engineers: Access to Project Tables and Technical Data tables.
Technicians: Access to Project tables only.
Accounting: Access to Financial tables, and read only on Project tables.
External: Clients and other consultants \ companies involved in the various projects. Access would only be to the particular project files which they are 'linked' to.
In addition, for auditing etc I want to add the ID\username of the person who Added & Last Modified the record.
As I understand it is best to use the in-built Roles [Login and Group roles] to assign privileges:
1) What is the system table\s that list the Roles?
2) Do these roles have primary keys that can be linked to the user tables for the change control?
3) If not, can primary keys be added to the system tables, or will this cause other issues?
4) When a User is disabled, as it would be necessary to maintain the record, are there any security concerns with leaving the User in the system, if the login and all other privileges are revoked?
5) Is the 'Connection' / 'Session' [currently planning on using Npgsql] username accessible through any function written in PL/pgSQL?
6) Can a view be setup so that the Username is incorporated into the view querry, and each external user would only see records relating to 'linked' project files?
Thank you for answering these questions, or referring me to a website with information on these sorts of issues.
As a general rule, direct table access should be severely limited.
Allow read access to data by implementing views, and write-access via stored functions(procedures,) and/or rules.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert
Thanks for the comments loquin. I was intending to use views for data reads and stored functions for data additions/modifications. I assume however that if the Role [User] permissions on a table prevented Create-Update, that when a Role calls a stored function writing to that table, that the write would fail.
Doing some further reading of the PostgreSQL 9.0 Manual, and a little bit of playing I have found a few answers to my questions, but not all.
1) Role [User] Tables: Information is stored in pg_catalog.pg_authid
There is also a pg_catalog view pg_user that exposes the same information.
2) Primary Key on pg_authid. The table does not have a primary key, but has an OID column. This is exposed in the pg_user view.
I have tried assigning a Foreign Key link from a User defined table to either pg_authid or pg_user, however I have not been able to. Is is possible to link a Foreign Key in a User Table to a system table?
1) Use the in-built PostgreSQL 'Group Roles' to set the access permissions to the various database objects.
2) Use the in-built PostgreSQL 'Login Roles' for user database access. These roles inherit their permissions from the assigned 'Group Role'.
Questions: Is is possible to link a Foreign Key in a User Table to a system table?
However, it is not possible to 'link' the user database information to the Login Role [ie. pg_Catalog.pg_authid].
eg. I have a 'Contact' table in my database that contains "Person" and "Company". I can not create an 'Access' table that contains the "ContactID" and the "Login Role OID" with a direct Foreign Key to pg_Catalog.pg_authid.oid.
Also, I can not reference a Foreign Key to a View.
If the above is correct, then it seems I have two options.
Step 1 : Create a 'copy' / 'mirror' of the pg_catalog.pg_authid table; that only contains the OID and rolname columns.
Step 2 : Create a 'Function' or 'Trigger' that adds new Login Roles, or that copies the added record to the mirrored table.
Step 3 : Link the 'Access' table to the mirrored table.
Use only Group Roles to control the access privileges to the database.
Create a 'UserLogin' table, and let the functions / application control the security.
The 'Access' table can then directly reference the 'UserLogin' table
Despite having to mirror a table which has issues if the trigger is not activated, I think Option 1 is the better of the two options.
Is this correct or is there an Option 3 that I have not thought of?