Paraccel (Actian Matrix) security
Posted by Diego em Junho 24, 2014
Couple of quick notes \ tricks when it comes to working with security on a ParAccel database. All this was done on version 5 of the tool. I understand that Amazon Redshift uses ParAccel technology but I can’t tell which of the statements bellow are true or not on it.
· Edit the pg_hba.conf located at /home/paraccel/padb/rel/etc
· PADB reads the pg_hba.conf file on startup. After any edits, the cluster must be restarted with xstop and cqi xstart or use the pg_ctl utility to reload the file:
o $ pg_ctl reload
· A valid userid and password with rights granted to access the database is not sufficient to access a PADB database. The pga_hba.conf file must also allow access for the user.
If you want to expose your data trough views, hiding the tables, I suggest creating all the tables on schemas different than public and the views on the public schema (everything owned by a super user). Then you will need to create a “read only user” (let’s call it user1) and grant select permissions on all the views and usage permission on all the schemas the tables belong to.
I also suggest:
· Prevent users from creating objects on the public schema by running:
revoke create on schema public from public;
· Explicitly REVOKE execute permission on all functions on a particular schema since that by GRANTING usage on the schema you allowed the users to execute the function.
o FYI, I believe there is a minor bug on paraccel here where the function:
select has_function_privilege(‘user’, ‘schema.function’,‘execute’)
o returns true even though the user doesn’t have permissions to execute it;
Here follows a few tips\tricks\”things I’ve learned” while trying to accomplish that:
· It’s not enough to grant select permission on a view, you also need to select USAGE permission on all the schemas that contain tables the view reads from
· PUBLIC represents a group that always includes all users. An individual users privileges consist of the sum of privileges granted to PUBLIC, privileges granted to any groups that the user belongs to, and any privileges granted to the user individually;
· PUBLIC is also a schema;
· Database user’s accounts are global across a PADB instance (not per individual database).
· By default, all users have CREATE and USAGE privileges on the PUBLIC schema. To disallow users from creating objects in the PUBLIC schema of a database, use the REVOKE command to remove that privilege.
o revoke create on schema public from public;
· The only privilege that is directly relevant to functions is EXECUTE. When you create a new UDF, the default behaviour is that EXECUTE privilege accrues to all users (PUBLIC). PUBLIC permissions are granted in addition to individual user or group permissions; therefore, if you want to revoke function privileges for any user or group, you need to revoke from PUBLIC, then grant privileges individually to those users and groups. Revoking privileges from a specific user or group is not sufficient to prevent users from running UDFs.
o revoke execute on function schema.function() from public;
· By default, all system tables are granted PUBLIC SELECT access, but system views are not granted PUBLIC SELECT access.
· Having ownership of a view, or having privileges granted on a view, does not imply access to the underlying tables. You need to grant access to the underlying tables explicitly.
· By default, users are granted permission to create temporary tables by their automatic membership in the PUBLIC group. To remove the privilege for any users to create temporary tables, revoke the TEMP permission from the PUBLIC group and then explicitly grant the permission to create temporary tables to specific users or groups of users.
o revoke temporary on database <db> from public;
o Nothing stops you from dropping a group that contains users;
o It’s possible to drop a user that owns object. If you do so, the object will be left without an owner;
· Useful Commands:
\du: show users (select * from pg_user)
\dg: show groups
\dn: show schemas
\d: show all tables (SELECT * from pg_class c WHERE relkind=‘r’😉
Print – Grant Select on All views:
SELECT ‘GRANT SELECT ON ‘ || relname || ‘ TO group readonlygroup;’
from pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE nspname = ‘public’ and relkind=‘v’;
Print – Grant USAGE on all schemas:
SELECT ‘GRANT USAGE ON schema ‘ || nspname || ‘ TO group readonlygroup;’
Functions to test privileges:
select has_function_privilege(‘user’, ‘schema.function()’,‘execute’)
select has_table_privilege(‘user’, ‘schema.table’, ‘select’)