pt-privilege-autogen

Summary

Generates REVOKE/GRANT statements for giving minimum privileges to users and database objects. It uses PostgreSQL statistics to learn access policy of the application.

Usage

pt-privilege-autogen [option...] [ start | stop ]
pt-privilege-autogen [option...] generate <USERNAME>

Commands

start Starts collecting table access statistics of the database.
generate Generates REVOKE/GRANT statements from collected access statistics of the tables. Requires user name to be granted.
stop Stops collecting table access statistics of the database.

Options

-h, --host=HOSTNAME
-p, --port=PORT
-U, --username=USERNAME
-d, --dbname=DBNAME
--help

-h, --host Specifies the connecting PostgreSQL database server name or IP address. If not specified, to set the value of PGHOST. localhost will be used if no value has been defined in PGHOST.

-p, --port Specifies the port number of the connecting PostgreSQL database. If not specified, to set the value of PGPORT. 5432 will be used if no value has been defined in PGPORT.

-U, --username Specifies the user name of the PosgtgreSQL database. If not specified, to set the value of PGUSER. The value of USER will be used if no value has been defined in PGUSER.

-d, --dbname Specifies the connecting database name. If not specified, to set the value of PGDATABASE. It connect to the database same as the database username if no value has been defined in PGDATABASE.

Output Items

This command shows GRANT/REVOKE statements that can be applied to the PostgreSQL database.

Examples

This example shows how to generate REVOKE/GRANT statements for the user snaga to be allowed to run pgbench transactions with minimum privileges.

Following procedure needs to be done by the user who already has the several permissions, like super-user. This example uses postgres super-user.

First, prepare a database for pgbench with superuser, and make sure that a regular user can’t access to the tables.

$ createdb -U postgres mydb
$ pgbench -i -U postgres mydb
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
creating tables...
100000 of 100000 tuples (100%) done (elapsed 0.70 s, remaining 0.00 s).
vacuum...
set primary keys...
done.
$ pgbench -c 1 -t 1 -U snaga -n mydb
ERROR:  permission denied for relation pgbench_branches
$

Second, start collecting table access statistics, and run pgbench transactions.

$ pt-privilege-autogen -U postgres -d mydb start
[2015-08-04 04:40:45] INFO: Collecting access statistics started.
$ pgbench -c 1 -t 1 -U postgres -n mydb
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 1
number of transactions actually processed: 1/1
latency average: 0.000 ms
tps = 14.402581 (including connections establishing)
tps = 20.464964 (excluding connections establishing)
$

Then, generate an access policy file, and apply it to the database. A regular user snaga is granted least privileges for four tables here.

$ pt-privilege-autogen -U postgres -d mydb generate snaga

-- Database
REVOKE ALL ON DATABASE "mydb" FROM "public";
GRANT CONNECT,TEMP ON DATABASE "mydb" TO "snaga";

-- Schema
REVOKE ALL ON SCHEMA "public" FROM "public";
GRANT USAGE ON SCHEMA "public" TO "snaga";

-- Table
REVOKE ALL ON TABLE "public"."pgbench_accounts" FROM "snaga";
REVOKE ALL ON TABLE "public"."pgbench_branches" FROM "snaga";
REVOKE ALL ON TABLE "public"."pgbench_history" FROM "snaga";
REVOKE ALL ON TABLE "public"."pgbench_tellers" FROM "snaga";
GRANT SELECT,UPDATE ON TABLE "public"."pgbench_accounts" TO "snaga";
GRANT SELECT,UPDATE ON TABLE "public"."pgbench_branches" TO "snaga";
GRANT INSERT ON TABLE "public"."pgbench_history" TO "snaga";
GRANT SELECT,UPDATE ON TABLE "public"."pgbench_tellers" TO "snaga";


$ pt-privilege-autogen -U postgres -d mydb generate snaga > grant.sql
$ psql -f grant.sql -U postgres mydb
REVOKE
GRANT
REVOKE
GRANT
REVOKE
REVOKE
REVOKE
REVOKE
GRANT
GRANT
GRANT
GRANT
$

Finally, stop collecting access statistics, and make sure that the regular user snaga can now run pgbench transaction on the database with the least privileges.

$ pt-privilege-autogen -U postgres -d mydb stop
[2015-08-04 04:44:21] INFO: Collecting access statistics stopped.
$ pgbench -c 1 -t 1 -U snaga -n mydb
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 1
number of transactions actually processed: 1/1
latency average: 0.000 ms
tps = 33.598764 (including connections establishing)
tps = 82.182774 (excluding connections establishing)
$