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.