as databases tend to hold sensitive information this information should be protected as much as possible. oracle provides various tools for securing and auditing the database: database firewall, audit vault, enterprise security, database vault to name a few of them ( and for most of them you’ll need a separate license ) and of course all the privileges you can assign and revoke inside the database.
Roles, Groups and Passwords
as oracle does, postgres bases it’s internal security mechanisms on users and roles. users created in postgres are valid globally, that is: not specific to a single database. this means the amount of users is the same for all databases. privileges can also be assigned to groups, which can be granted to users. if you are used to the oracle terms be aware that:
- users in oracle are called roles in postgres
- roles in oracle are called groups in postgres
- sometimes the word role is used for both, users and groups, in postgres ( that is login roles, which are users, and nologin roles, which are groups )
to create a new login role in the database the “create role” command is used:
CREATE ROLE "user1" LOGIN; CREATE ROLE "user2" LOGIN PASSWORD 'user2'; # create a superuser CREATE ROLE "user3" LOGIN PASSWORD 'user3' SUPERUSER; # create a user and grant the privilege to create roles CREATE ROLE "user4" LOGIN PASSWORD 'user4' CREATEROLE; # create a user allowed to create databases CREATE ROLE "user5" LOGIN PASSWORD 'user5' CREATEDB; # create a user allowed to create databases and password validity CREATE ROLE "user6" LOGIN PASSWORD 'user6' CREATEDB VALID UNTIL '2012-10-01'; # create a user and limit her amount of connections CREATE ROLE "user7" LOGIN PASSWORD 'user7' CONNECTION LIMIT 2;
be careful if you create users like above and provide the password as normal string. depending on your server configuration the passwords will be visible in the server’s logfile and the psql history:
LOG: statement: CREATE ROLE "user1" LOGIN; LOG: statement: CREATE ROLE "user2" LOGIN PASSWORD 'user2'; LOG: statement: CREATE ROLE "user3" LOGIN PASSWORD 'user2' SUPERUSER; LOG: statement: CREATE ROLE "role"; LOG: statement: CREATE ROLE "user4" LOGIN PASSWORD 'user4' CREATEROLE;
as postgres internally encrypts the passwords with md5 you can prevent this by providing the encrypted password when creating users:
CREATE USER someuser LOGIN PASSWORD 'md572947234907hfasf3';
to get the encrypted password out of the database use the pg_authid view:
SELECT rolname, rolpassword FROM pg_authid; rolname | rolpassword ----------+------------------------------------- sysdba | md5448a3ec0e7a2689f0866afca52f91e13 user1 | user2 | md572881e285cdb0f9370dcdf1db0d9a869 user3 | md53b24544e8f4b2a20f4bcca02a35df8fb user4 | md547e1c205dd73d4c06405bd08d255e320 user5 | md51dc34834df4da4804236eb250118fb41 user6 | md5bdf2912fce3ee3f6657bacc65527c7bd user7 | md5c5068c076d70d192c7f205a9bba4c469 role1 |
to create a group ( or role in oracle terms ) just skip the login attribute:
CREATE ROLE "role1";
granting groups to users:
GRANT ROLE1 TO USER1;
or
GRANT ROLE1 TO USER1 WITH ADMIN OPTION;
you can either use the psql shortcut to list the roles in the database server:
\du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
role1 | Cannot login | {}
sysdba | Superuser, Create role, Create DB, Replication | {}
user1 | | {role1}
user2 | | {}
user3 | Superuser, Replication | {}
user4 | Create role | {}
user5 | Create DB | {}
user6 | Create DB | {}
user7 | 2 connections | {}
… or you may use the pg_roles view:
SELECT rolname,rolsuper,rolcreatedb,rolconnlimit,rolvaliduntil FROM pg_roles; rolname | rolsuper | rolcreatedb | rolconnlimit | rolvaliduntil ----------+----------+-------------+--------------+------------------------ sysdba | t | t | -1 | user1 | f | f | -1 | user2 | f | f | -1 | user3 | t | f | -1 | user4 | f | f | -1 | user5 | f | t | -1 | user6 | f | t | -1 | 2012-10-01 00:00:00+02 user7 | f | f | 2 | role1 | f | f | -1 |
to delete a role, just drop it:
DROP ROLE ROLE1; # or to suppress error messages in case the role does not exist DROP ROLE IF EXISTS ROLE1;
to delete everything owner by a specific role:
DROP OWNED BY USER1;
you can even re-assign all objects from one role to another:
REASSIGN OWNED BY USER1 TO USER2;
granting / revoking privileges on objects is similar than in oracle with a few exceptions. if you want to grant execute on a function you’ll have to specify the parameters, too:
GRANT EXECUTE ON FUNCTION1 ( int, int ) TO USER1; (
you can grant a privilege on a whole schema ( tables, sequences and functions ) :
GRANT SELECT ON ALL TABLES IN SCHEMA A TO USER2;
you can grant privileges on a whole database:
GRANT ALL PRIVILEGES ON DATABASE DBS200 TO USER2;
you can change the owner of objects:
ALTER TABLE TEST1 OWNER TO USER2;
if you want to create objects in a separate schema ( public is the default ) you’ll have to create it first:
CREATE SCHEMA SCHEMA1; CREATE TABLE SCHEMA1.TABLE1 ( A INTEGER );
specify the search path to avoid the schema in your commands:
SHOW search_path; SET search_path TO schema1,public;
to display privileges either use the psql shortcut:
\z
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
---------+--------------------+-------+-----------------------+--------------------------
public | pg_stat_statements | view | sysdba=arwdDxt/sysdba+|
| | | =r/sysdba |
schema1 | table1 | table | |
(2 rows)
or query the information schema for a specific object:
SELECT * FROM information_schema.table_privileges WHERE table_name = 'TABLE1';
Client Connections
in postgres there is one file which controls if and how clients connect to the database server. the file is called “pg_hba.conf” and is located in the data area of the database server. initdb automatically creates this file when the cluster is initialized.
in my case the file looks like this:
# TYPE DATABASE USER ADDRESS METHOD local all all md5 host all all ::1/128 md5 local replication sysdba md5 host replication sysdba 127.0.0.1/32 md5
the first column is the type, which can be one of:
- local: this is for unix domain sockets
- host: this is for tcp/ip
- hostssl: this is for ssl over tcp/ip
- hostnossl: this is for tcp/ip connections which do not use ssl
the second and third columns specifies the database name and user the configuration is valid for. by specifying addresses you can enable individual hosts or networks to connect to the database server. the last column specifies the authentication method, which can be one of:
- trust: this effectively disables authentication and should not be used
- reject: rejects all connections which are valid for the entry
- md5: password authentication using md5
- password: password authentication ( clear text )
- ident: use the os to authenticate the user
additionally to the methods above postgres provides support for pam, kerberos, gssapi, sspi for windows, radius and ldap. all for free, in contrast to oracle.
in general one should at least use md5 to provide minimum security. trust and ident should not be used in production environments.
check the documentation for a more detailed description ….
Auditing
for auditing in postgresql you’ll need to create triggers and/or functions. there is no out of the box module which you can use. but you can use several server parameters to log information to the server’s logfile.
A quick check on pgfoundry listed a project called “audittrail” which is still in beta status and the last update was in 2007.
in the end, you’ll have to spend more work on auditing in postgresql than in oracle. this may be a drawback for enterprise installations …
Daniel Westermann's Blog 