RSS

PostgreSQL mini cookbook: Controlling Access to your data

PostgreSQL mini cookbook: Controlling Access to your data, those tricks back to 2001 and still works today. 😉

Restricting users to certain records or certain fields with views

The accounts team don’t need to see unfilled orders and the sales team don’t need to see bank details. You want to enforce this.

Solution

Create one view for each group of users, and deny them access to the underlying table(s).

Discussion

You can create alternative views of your database with CREATE VIEW <viewname> AS SELECT....

To a user this appears to be a table, but you can control what underlying data shows through. The SELECT query can be anything you like - it can involve JOINs, UNIONs or GROUP BY if you want. So, if the accounts department only needs to see completed orders:

CREATE VIEW accts_orders
  AS SELECT * FROM orders
    WHERE ord_delivered IS NOT NULL;

You then need to make sure that accounts has access to this view and not to the underlying table:

GRANT ALL ON accts_orders TO accounts;
REVOKE ALL ON orders FROM accounts;

You will need to be a superuser (postgres) or the owner of the database objects to do this. Possible permissions are: SELECT, INSERT, UPDATE, DELETE, RULE, ALL.

Letting users update views

You want to allow users to make restricted updates using the view you have defined

Solution

Add rules to the view to redirect changes to the underlying table(s). Remember that Postgres won’t allow updates/inserts unless there is an unqualified rule that catches all changes.

Discussion

To let users update a view you need to provide appropriate rules to Postgres with the CREATE RULE construct.

In the example of we want the accounts department to be able to alter orders but not delete them (goods have been shipped but not necessarily paid for).

CREATE RULE accts_orders_upd_rule0
  AS ON UPDATE TO accts_orders
    DO INSTEAD NOTHING;

CREATE RULE accts_orders_upd_rule
  AS ON UPDATE TO accts_orders
    WHERE NEW.ord_paid != OLD.ord_paid
      OR OLD.ord_paid IS NULL
    DO INSTEAD UPDATE orders
      SET ord_paid=NEW.ord_paid
      WHERE ord_id=NEW.ord_id;

The first rule looks simple enough - instead of updating accts_orders do nothing instead. Why do we need it, since you can’t update rules anyway? Postgres won’t try to update a view unless there is a rule that covers all eventualities. Since the second rule has a WHERE clause we need an empty rule to cover all possible updates a user can attempt.

This second rule captures updates to the accts_orders view and translates them into update queries onto accts_orders. In addition it only updates the ord_paid field, ignoring others.

The NEW keyword represents the values the record is going to be updated with. The OLD keyword represents the record before the update takes place.

What about permissions? The rule is executed with the permissions of its owner not the person who ends up executing it. So - if accounts don’t have the permissions to access the orders table that’s alright, as long as the user who creates the rule does.

Grant privileges on all tables in one go

You want to do something like GRANT ALL ON 'foo%' TO webuser.

Solution

You can’t do this directly from psql - you’ll need to write a shell-script or plpgsql function to automate it for you.

Discussion

You can use the following shell script:

###### start of grantall.sh #######
#!/bin/ash
SQL="SELECT relname FROM pg_class WHERE (relkind = 'r' OR relkind = 'S')
AND relname !~ '^pg_' ORDER BY relname"
OBJ=`psql -t -c "${SQL}" $1`
# OBJ=`echo ${OBJ} | sed 's/EOF//g'`
OBJ=`echo ${OBJ} | sed 's/ /, /g'`
# SQL="REVOKE ALL ON ${OBJ} FROM PUBLIC"
SQL="GRANT ALL ON ${OBJ} TO PUBLIC"
echo ${SQL}
psql -c "${SQL}" $1
###### end of grantall.sh #######

Or you can use this plpgsql function:

SELECT pgx_grant('ALL','comp%','nobody');

-- pgx_grant(PRIVILEGE, TABLES, USER)
--  Grants PRIVILEGE to USER on objects like TABLES%
--  Grants to tables, views and sequences.
--
CREATE FUNCTION pgx_grant(text,text,text) returns int4 as '
DECLARE
  priv ALIAS FOR $1;
  patt ALIAS FOR $2;
  user ALIAS FOR $3;
  obj  record;
  num  integer;
BEGIN
  num:=0;
  FOR obj IN SELECT relname FROM pg_class
  WHERE relname LIKE patt || \'%\' AND relkind in (\'r\',\'v\',\'S\') LOOP
    EXECUTE \'GRANT \' || priv || \' ON \' || obj.relname || \' TO \' || user;
    num := num + 1;
  END LOOP;
  RETURN num;
END;
' language 'plpgsql';

References