Is it possible to set PostgreSQL roles with privileges this way?

Question

I'm trying to develop a system, and for the first time I'm using PostgreSQL. I'm trying to organize and separate roles properly, but I'm losing my mind with it (about a week on this now).

Following the title of this question, is it possible to set the following(?):

• A user who can do everything with all tables, except perform CRUD operations on the records of the same. In addition, this user can not create, delete, modify or insert roles.
• Another user who can perform all CRUD operations on table records, but can not perform any structural change operation in database objects. This guy cannot create/D/M/I roles as well.
• A third user who behaves like DBA, able to do anything.

The first user serves as a limited DBA who can make structural changes, but can not snoop data. The second is a system using the database. The third and last is a DBA with full control.

This question comes from another one previously made by me, similar, but not equal. I do not want to know what's wrong with that code anymore. Also, I believe that here is the most appropriate place to ask things like that.

I want to know if this is possible. If so, I would like to see how. If not, I want an explanation of why.

Thank you for your attention in advance.

Answer 1

What is possible is what's specified in the documentation for GRANT (http://www.postgresql.org/docs/current/static/sql-grant.html).

To make modifications on tables, a user must own the object. When a user owns an object, a user can do whatever they want with it (including restricting themselves any kind of access, along with the ability to grant themselves any access again).

For your limited DBA, there are non-standard ways in which your requirements can be implemented, such as a DBA creating a function with SECURITY DEFINER that validates the executing user, and the command to execute, as something allowed, like the following example:

CREATE OR REPLACE FUNCTION limited_dba_action (p_action text) RETURNS void AS
$body$
BEGIN
  IF p_action !~* '^\s*(CREATE|ALTER)\M' THEN -- add any other valid operations, such as TRUNCATE, DELETE, etc
    RAISE EXCEPTION 'Valid operations through this function are limited to creating and altering objects';
  END IF;

  IF p_action ~ ';\s*[^\s]' THEN
    RAISE EXCEPTION 'Please include only one operation';
  END IF;

  EXECUTE p_action;
END;
$body$ LANGUAGE plpgsql SECURITY DEFINER RETURNS NULL ON NULL INPUT;

GRANT EXECUTE ON FUNCTION limited_dba_action TO your_limited_dba_user;

For your system using the database, this is the standard for non-object-owners (i.e. they can do whatever is being granted to them, but not alter/drop the object).

Finally your DBA is simply a standard superuser.

So yes, what you require being able to do is perfectly possible.