Infrastructure at your Service

Daniel Westermann

EDB Postgres Advanced Server 9.5 new features – Profiles

The just released version of EDB Postgres Advanced Server 9.5 introduces profiles very much the same as in Oracle. Lets have a look at it.

As in Oracle there is a default profile:

([email protected][local]:5445) [postgres] > \x
Expanded display is on.
([email protected][local]:5445) [postgres] > select * from edb_profile;
-[ RECORD 1 ]-----------+--------
prfname                 | default
prffailedloginattempts  | -2
prfpasswordlocktime     | -2
prfpasswordlifetime     | -2
prfpasswordgracetime    | -2
prfpasswordreusetime    | -2
prfpasswordreusemax     | -2
prfpasswordverifyfuncdb | NULL
prfpasswordverifyfunc   | NULL

You can also query the dba_profiles view exactly as in Oracle:

([email protected][local]:5445) [postgres] > \x
Expanded display is off.
([email protected][local]:5445) [postgres] > select * from dba_profiles;
 profile |      resource_name       | resource_type |   limit   | common 
---------+--------------------------+---------------+-----------+--------
 DEFAULT | FAILED_LOGIN_ATTEMPTS    | PASSWORD      | UNLIMITED | NO
 DEFAULT | PASSWORD_GRACE_TIME      | PASSWORD      | UNLIMITED | NO
 DEFAULT | PASSWORD_LIFE_TIME       | PASSWORD      | UNLIMITED | NO
 DEFAULT | PASSWORD_LOCK_TIME       | PASSWORD      | UNLIMITED | NO
 DEFAULT | PASSWORD_REUSE_MAX       | PASSWORD      | UNLIMITED | NO
 DEFAULT | PASSWORD_REUSE_TIME      | PASSWORD      | UNLIMITED | NO
 DEFAULT | PASSWORD_VERIFY_FUNCTION | PASSWORD      | NULL      | NO
(7 rows)

In fact the dba_profiles view is just a view on top of edb_profile. You can verify this by:

([email protected][local]:5445) [postgres] >  \d+ dba_profiles                                                        
                           View "sys.dba_profiles"
    Column     |          Type          | Modifiers | Storage  | Description 
---------------+------------------------+-----------+----------+-------------
 profile       | character varying(128) |           | extended | 
 resource_name | character varying(32)  |           | extended | 
 resource_type | character varying(8)   |           | extended | 
 limit         | character varying(128) |           | extended | 
 common        | character varying(3)   |           | extended | 
View definition:
 SELECT
        CASE
...

When there are profiles the “create user/role” syntax should have been extended, too:

([email protected][local]:5445) [postgres] > \h create user
Command:     CREATE USER
Description: define a new database role
Syntax:
CREATE USER name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | CREATEUSER | NOCREATEUSER
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'
    | PROFILE profile_name      <XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
    | ACCOUNT { LOCK | UNLOCK }
    | LOCK TIME 'timestamp'
    | PASSWORD EXPIRE [ AT 'timestamp' ]
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

I would expect that every new user gets the default profile if we do not specify one:

([email protected][local]:5445) [postgres] > create user u1 login password 'u1';
CREATE ROLE
([email protected][local]:5445) [postgres] > \du+ u1
                     List of roles
 Role name |   Attributes    | Member of | Description 
-----------+-----------------+-----------+-------------
 u1        | Profile default | {}        | 

As expected. So lets play with a new profile:

([email protected][local]:5445) [postgres] > create profile my_profile;
CREATE PROFILE
Time: 18.296 ms
([email protected][local]:5445) [postgres] > alter profile my_profile limit FAILED_LOGIN_ATTEMPTS 1;
ALTER PROFILE
Time: 1.799 ms
([email protected][local]:5445) [postgres] > alter profile my_profile limit PASSWORD_LOCK_TIME 1;   
ALTER PROFILE
Time: 0.979 ms
([email protected][local]:5445) [postgres] > alter profile my_profile limit PASSWORD_LIFE_TIME 1;

Pretty much the same as in Oracle. For a full list and a description of the limits check the documentation.

Lets attach the new profile to our user:

([email protected][local]:5445) [postgres] > alter user u1 profile my_profile ;
ALTER ROLE
Time: 1.931 ms
([email protected][local]:5445) [postgres] > \du u1
               List of roles
 Role name |     Attributes     | Member of 
-----------+--------------------+-----------
 u1        | Profile my_profile | {}

An important point to understand is that profiles are not per database but per instance. Once a profile is created it is available in all databases:

([email protected][local]:5445) [postgres] > \c edb
You are now connected to database "edb" as user "enterprisedb".
([email protected][local]:5445) [edb] > select distinct profile from dba_profiles;
  profile   
------------
 DEFAULT
 MY_PROFILE
(2 rows)

Time: 63.333 ms
([email protected][local]:5445) [edb] > \c postgres                               
You are now connected to database "postgres" as user "enterprisedb".
([email protected][local]:5445) [postgres] > select distinct profile from dba_profiles;
  profile   
------------
 DEFAULT
 MY_PROFILE
(2 rows)

Time: 2.130 ms

([email protected][local]:5445) [postgres] > create database db1;
CREATE DATABASE
Time: 1226.746 ms
([email protected][local]:5445) [postgres] > \c db1
You are now connected to database "db1" as user "enterprisedb".
([email protected][local]:5445) [db1] > select distinct profile from dba_profiles;
  profile   
------------
 DEFAULT
 MY_PROFILE
(2 rows)

When profiles are global what about a password verify function you may attach to the profile? Functions are per database, aren’t they? Lets create a simple function:

create or replace function my_func ( password in varchar(30) ) return boolean
is
begin
  if ( length (password) < 5 )
  then
    raise_application_error ( -20001, 'to short');
  end if;
  return true;
end my_func;
/

And then assign this function to the profile:

([email protected][local]:5445) [postgres] > \df
                             List of functions
 Schema |  Name   | Result data type |    Argument data types     |  Type  
--------+---------+------------------+----------------------------+--------
 public | my_func | boolean          | password character varying | normal
(1 row)

([email protected][local]:5445) [postgres] > alter profile my_profile limit password_verify_function my_func;
ERROR:  function my_func does not exist
Time: 0.479 ms

Hm. The function clearly exists. The issue here is that there are some specific requirements for the password verify function:

  • It needs to be owned by a super user
  • It needs to be in the sys schema
  • It needs to have the following signature: function_name (varchar2, varchar2, varchar2) return boolean

Knowing this let’s try again with this function:

CREATE OR REPLACE FUNCTION sys.my_func (username varchar2, password varchar2, old_password varchar2)  
RETURN boolean IMMUTABLE  
IS  
BEGIN  
  if ( length (password) < 5 )  
  then  
    raise_application_error ( -20001, 'too short');  
  end if;  
  return true;  
END my_func;  

To be sure lets set the owner of this function to a super user

([email protected][local]:5445) [postgres] > alter function my_func (varchar2,varchar2,varchar2) owner to enterprisedb;
ALTER FUNCTION
Time: 0.311 ms

Do we succeed now?

([email protected][local]:5445) [postgres] > alter profile my_profile limit password_verify_function my_func;
ALTER PROFILE
Time: 21.048 ms

Yes, much better. Lets do a simple test:

([email protected][local]:5445) [postgres] > alter user u1 password 'u1';
ERROR:  EDB-20001: too short
CONTEXT:  edb-spl function my_func(character varying,character varying,character varying) line 5 at procedure/function invocation statement
Time: 67.705 ms

Ok, now it really works. But what does happen if I am connected to another database where the password verify function does not exist?

([email protected][local]:5445) [postgres] > \c edb
You are now connected to database "edb" as user "enterprisedb".
([email protected][local]:5445) [edb] > create user u2 password 'u2' profile my_profile;
ERROR:  password verify function is stored in database postgres
HINT:  You must connect to this database in order to assign a password.
Time: 0.644 ms
([email protected][local]:5445) [edb] > 

Ok, at least the server is telling me where I need to connect to.

This does not lock like a big new feature but it is another little piece that makes migrations from Oracle less problematic especially if there are requirements for password policies.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure