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:
(enterprisedb@[local]:5445) [postgres] > x Expanded display is on. (enterprisedb@[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:
(enterprisedb@[local]:5445) [postgres] > x Expanded display is off. (enterprisedb@[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:
(enterprisedb@[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:
(enterprisedb@[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:
(enterprisedb@[local]:5445) [postgres] > create user u1 login password 'u1'; CREATE ROLE (enterprisedb@[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:
(enterprisedb@[local]:5445) [postgres] > create profile my_profile; CREATE PROFILE Time: 18.296 ms (enterprisedb@[local]:5445) [postgres] > alter profile my_profile limit FAILED_LOGIN_ATTEMPTS 1; ALTER PROFILE Time: 1.799 ms (enterprisedb@[local]:5445) [postgres] > alter profile my_profile limit PASSWORD_LOCK_TIME 1; ALTER PROFILE Time: 0.979 ms (enterprisedb@[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:
(enterprisedb@[local]:5445) [postgres] > alter user u1 profile my_profile ; ALTER ROLE Time: 1.931 ms (enterprisedb@[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:
(enterprisedb@[local]:5445) [postgres] > c edb You are now connected to database "edb" as user "enterprisedb". (enterprisedb@[local]:5445) [edb] > select distinct profile from dba_profiles; profile ------------ DEFAULT MY_PROFILE (2 rows) Time: 63.333 ms (enterprisedb@[local]:5445) [edb] > c postgres You are now connected to database "postgres" as user "enterprisedb". (enterprisedb@[local]:5445) [postgres] > select distinct profile from dba_profiles; profile ------------ DEFAULT MY_PROFILE (2 rows) Time: 2.130 ms (enterprisedb@[local]:5445) [postgres] > create database db1; CREATE DATABASE Time: 1226.746 ms (enterprisedb@[local]:5445) [postgres] > c db1 You are now connected to database "db1" as user "enterprisedb". (enterprisedb@[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:
(enterprisedb@[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) (enterprisedb@[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
(enterprisedb@[local]:5445) [postgres] > alter function my_func (varchar2,varchar2,varchar2) owner to enterprisedb; ALTER FUNCTION Time: 0.311 ms
Do we succeed now?
(enterprisedb@[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:
(enterprisedb@[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?
(enterprisedb@[local]:5445) [postgres] > c edb You are now connected to database "edb" as user "enterprisedb". (enterprisedb@[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 (enterprisedb@[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.