Some New Security Features in PG9.0

Some New Security Features in PG9.0

1. New Grant and Revoke in PG9.0

In Previous version of PG (7.x,8.x), all the DBAs and Users used to miss the GRANT and REVOKE command which can be use to give permissions on all the tables inside the Schema. Now, they don;t have to.

From PG9.0, user can execute single GRANT and REVOKE command to give the permission on all the tables in a SCHEMA.

GRANT SELECT ON ALL TABLES in SCHEMA TEST to test_user;

Here is output of query which shows that above command has given SELECT privileges on all the tables in SCHEMA Test.

postgres=# select * from information_schema.table_privileges  where grantee ='test_user';;
 grantor  |  grantee  | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
----------+-----------+---------------+--------------+------------+----------------+--------------+----------------
 postgres | test_user | postgres      | test         | test       | SELECT         | NO           | NO
 postgres | test_user | postgres      | test         | test2      | SELECT         | NO           | NO

Similarly user can execute single Revoke command to revoke a privilege from all the tables in Schema:

REVOKE SELECT ON ALL TABLES in SCHEMA test from test_user;

2. Assign Default privileges to a Role.

In PG9.0, managing role privileges is now more easy.
PG9.0 now supports ALTER DEFAULT PRIVILGES as given below:

postgres=# alter default privileges for role newrole GRANT SELECT ON TABLES to public;
ALTER DEFAULT PRIVILEGES

3. Now, user can put a check for verifying the strength of Password given by user. Module passwordcheck by default comes with the PG9.0.

To enable this module user has to add following in postgresql.conf
file and has to restart the PG instance:

shared_preload_libraries = '$libdir/passwordcheck'

Working example is given below:

postgres=# alter user test_user password 'test_user';
ERROR:  password must not contain user name
postgres=# alter user test_user password 'test123';
ERROR:  password is too short
postgres=# alter user test_user password 'test45678';
ALTER ROLE

This feature is also having some limitation. It does not work properly if somebody pass the encrypted password. This feature is not recommended if some security feature is already implemented i.e if pre-encrypted passwords are already passing to DB.