Implement password profile feature in Cloudberry DB.
This commit mainly implements the password profile feature. The grammars
is compatible to ORACLE password profile.
A password profile is a named set of password attributes that allow a DBA
to easily manage a group of roles(or users) that share similar password
security requirements. Every profile can be attched to one or more users.
When a user login in, the server enforces the profile that is attached to
the user.
Now, profiles can support to several significant security practices, as
following:
1.specifying the number of failed login attempts.
2.locking a user when it exceeds failed login attempts.
3.defining lock time.
4.defining limit of password reuse time.
What's more, for database is always available, every super user is not
managed by profiles. Only when the normal user is enable to use profile,
the profile is effective.
Authored-by: Zhang Wenchao zwcpostgres@gmail.com
diff --git a/.gitignore b/.gitignore
index a2799fe..c5195b9 100644
--- a/.gitignore
+++ b/.gitignore
@@ -72,10 +72,5 @@
/CMakeLists.txt
/compile_commands.json
/tmp_install/
-<<<<<<< HEAD
/.cache/
/install/
-=======
-/install/
-/.cache/
->>>>>>> 77c11b4beab (update .gitignore.)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 688e778..dc050a7 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -6911,6 +6911,16 @@
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><symbol>SHARED_DEPENDENCY_PROFILE</symbol> (<literal>t</literal>)</term>
+ <listitem>
+ <para>
+ The referenced object (which must be a profile) is mentioned as
+ the profile for a role using.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
Other dependency flavors might be needed in future. Note in particular
diff --git a/gpMgmt/bin/gpcheckcat b/gpMgmt/bin/gpcheckcat
index 3df54de..4eb5a04 100755
--- a/gpMgmt/bin/gpcheckcat
+++ b/gpMgmt/bin/gpcheckcat
@@ -1568,6 +1568,12 @@
if catname == "pg_authid":
columns.remove("rolpassword")
castcols.remove("rolpassword")
+ columns.remove("rolpasswordsetat")
+ castcols.remove("rolpasswordsetat")
+ columns.remove("rollockdate")
+ castcols.remove("rollockdate")
+ columns.remove("rolpasswordexpire")
+ castcols.remove("rolpasswordexpire")
if cat.tableHasConsistentOids():
qry = inconsistentEntryQuery(GV.max_content, catname, ['oid'], columns, castcols)
@@ -2277,6 +2283,7 @@
TableMainColumn['pg_statistic_ext_data'] = ['stxoid', 'pg_statistic_ext']
TableMainColumn['pg_type_encoding'] = ['typid', 'pg_type']
TableMainColumn['pg_window'] = ['winfnoid', 'pg_proc']
+TableMainColumn['pg_password_history'] = ['passhistroleid', 'pg_authid']
# Table with OID (special case), these OIDs are known to be inconsistent
TableMainColumn['pg_attrdef'] = ['adrelid', 'pg_class']
@@ -2915,7 +2922,7 @@
# Report missing issues
if len(self.missingIssues):
- omitlist = ['pg_attribute', 'pg_attribute_encoding', 'pg_type', 'pg_appendonly', 'pg_index']
+ omitlist = ['pg_attribute', 'pg_attribute_encoding', 'pg_type', 'pg_appendonly', 'pg_index', 'pg_password_history']
if 'pg_class' in self.missingIssues:
myprint(' Name of test which found this issue: missing_extraneous_pg_class')
for name in omitlist:
diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile
index 6b80005..e8b1560 100644
--- a/src/backend/catalog/Makefile
+++ b/src/backend/catalog/Makefile
@@ -91,7 +91,8 @@
pg_default_acl.h pg_init_privs.h pg_seclabel.h pg_shseclabel.h \
pg_collation.h pg_partitioned_table.h pg_range.h pg_transform.h \
pg_sequence.h pg_publication.h pg_publication_rel.h pg_subscription.h \
- pg_subscription_rel.h gp_partition_template.h pg_task.h pg_task_run_history.h
+ pg_subscription_rel.h gp_partition_template.h pg_task.h pg_task_run_history.h \
+ pg_profile.h pg_password_history.h
USE_INTERNAL_FTS_FOUND := $(if $(findstring USE_INTERNAL_FTS,$(CFLAGS)),true,false)
@@ -117,7 +118,7 @@
pg_namespace.dat pg_opclass.dat pg_operator.dat pg_opfamily.dat \
pg_proc.dat pg_range.dat pg_tablespace.dat \
pg_ts_config.dat pg_ts_config_map.dat pg_ts_dict.dat pg_ts_parser.dat \
- pg_ts_template.dat pg_type.dat \
+ pg_ts_template.dat pg_type.dat pg_profile.dat \
)
POSTGRES_BKI_DATA += $(addprefix $(top_srcdir)/src/include/catalog/,\
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index 0250ff2..0f61d9d 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -3790,6 +3790,7 @@
case OBJECT_TSPARSER:
case OBJECT_TSTEMPLATE:
case OBJECT_USER_MAPPING:
+ case OBJECT_PROFILE:
elog(ERROR, "unsupported object type %d", objtype);
}
@@ -3931,6 +3932,7 @@
case OBJECT_TSPARSER:
case OBJECT_TSTEMPLATE:
case OBJECT_USER_MAPPING:
+ case OBJECT_PROFILE:
elog(ERROR, "unsupported object type %d", objtype);
}
diff --git a/src/backend/catalog/catalog.c b/src/backend/catalog/catalog.c
index 814188d..c5cd225 100644
--- a/src/backend/catalog/catalog.c
+++ b/src/backend/catalog/catalog.c
@@ -67,6 +67,8 @@
#include "catalog/pg_resqueuecapability.h"
#include "catalog/pg_resgroup.h"
#include "catalog/pg_resgroupcapability.h"
+#include "catalog/pg_profile.h"
+#include "catalog/pg_password_history.h"
#include "catalog/pg_rewrite.h"
#include "catalog/pg_stat_last_operation.h"
#include "catalog/pg_stat_last_shoperation.h"
@@ -419,7 +421,10 @@
#ifdef USE_INTERNAL_FTS
relationId == GpSegmentConfigRelationId ||
#endif
- relationId == AuthTimeConstraintRelationId)
+ relationId == AuthTimeConstraintRelationId ||
+
+ relationId == ProfileRelationId ||
+ relationId == PasswordHistoryRelationId)
return true;
/* These are their indexes */
@@ -463,7 +468,13 @@
relationId == GpSegmentConfigContentPreferred_roleWarehouseIndexId ||
relationId == GpSegmentConfigDbidWarehouseIndexId ||
#endif
- relationId == AuthTimeConstraintAuthIdIndexId)
+ relationId == AuthTimeConstraintAuthIdIndexId ||
+ relationId == AuthIdRolProfileIndexId ||
+ relationId == ProfilePrfnameIndexId ||
+ relationId == ProfileOidIndexId ||
+ relationId == ProfileVerifyFunctionIndexId ||
+ relationId == PasswordHistoryRolePasswordIndexId ||
+ relationId == PasswordHistoryRolePasswordsetatIndexId)
{
return true;
}
@@ -484,7 +495,9 @@
relationId == PgSubscriptionToastTable ||
relationId == PgSubscriptionToastIndex ||
relationId == PgTablespaceToastTable ||
- relationId == PgTablespaceToastIndex)
+ relationId == PgTablespaceToastIndex ||
+ relationId == PgPasswordHistoryToastTable ||
+ relationId == PgPasswordHistoryToastIndex)
return true;
#ifdef USE_INTERNAL_FTS
/* GPDB added toast tables and their indexes */
diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index d9b20c2..80a29c0 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -94,6 +94,8 @@
#include "commands/tablespace.h"
#include "cdb/cdbvars.h"
#include "commands/extprotocolcmds.h"
+#include "catalog/pg_profile.h"
+#include "catalog/pg_password_history.h"
#include "commands/tablecmds.h"
@@ -198,6 +200,8 @@
TransformRelationId, /* OCLASS_TRANSFORM */
/* GPDB additions */
+ ProfileRelationId, /* OCLASS_PROFILE */
+ PasswordHistoryRelationId, /* OCLASS_PASSWORDHISTORY */
ExtprotocolRelationId, /* OCLASS_EXTPROTOCOL */
TaskRelationId /* OCLASS_TASK */
};
@@ -1573,6 +1577,8 @@
case OCLASS_DATABASE:
case OCLASS_TBLSPACE:
case OCLASS_SUBSCRIPTION:
+ case OCLASS_PROFILE:
+ case OCLASS_PASSWORDHISTORY:
elog(ERROR, "global objects cannot be deleted by doDeletion");
break;
@@ -2931,6 +2937,12 @@
Assert(object->objectSubId == 0);
return OCLASS_EXTPROTOCOL;
+ case ProfileRelationId:
+ return OCLASS_PROFILE;
+
+ case PasswordHistoryRelationId:
+ return OCLASS_PASSWORDHISTORY;
+
case PolicyRelationId:
return OCLASS_POLICY;
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 7b45f5d..b369508 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -47,6 +47,8 @@
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
#include "catalog/pg_opfamily.h"
+#include "catalog/pg_password_history.h"
+#include "catalog/pg_profile.h"
#include "catalog/pg_policy.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_publication.h"
@@ -380,6 +382,20 @@
true
},
{
+ "profile",
+ ProfileRelationId,
+ ProfileOidIndexId,
+ PROFILEID,
+ PROFILENAME,
+ Anum_pg_profile_oid,
+ Anum_pg_profile_prfname,
+ InvalidAttrNumber,
+ InvalidAttrNumber,
+ InvalidAttrNumber,
+ OBJECT_PROFILE,
+ true
+ },
+ {
"role",
AuthIdRelationId,
AuthIdOidIndexId,
@@ -879,6 +895,10 @@
/* OCLASS_STATISTIC_EXT */
{
"statistics object", OBJECT_STATISTIC_EXT
+ },
+ /* OCLASS_PROFILE */
+ {
+ "profile", OBJECT_PROFILE
}
};
@@ -1052,6 +1072,7 @@
case OBJECT_SUBSCRIPTION:
case OBJECT_RESQUEUE:
case OBJECT_RESGROUP:
+ case OBJECT_PROFILE:
address = get_object_address_unqualified(objtype,
(Value *) object, missing_ok);
break;
@@ -1371,6 +1392,11 @@
address.objectId = get_resgroup_oid(name, missing_ok);
address.objectSubId = 0;
break;
+ case OBJECT_PROFILE:
+ address.classId = ProfileRelationId;
+ address.objectId = get_profile_oid(name, missing_ok);
+ address.objectSubId = 0;
+ break;
default:
elog(ERROR, "unrecognized objtype: %d", (int) objtype);
/* placate compiler, which doesn't know elog won't return */
@@ -2341,6 +2367,7 @@
case OBJECT_EXTPROTOCOL:
case OBJECT_RESGROUP:
case OBJECT_RESQUEUE:
+ case OBJECT_PROFILE:
if (list_length(name) != 1)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -2641,6 +2668,13 @@
aclcheck_error(ACLCHECK_NOT_OWNER, objtype,
NameListToString(castNode(List, object)));
break;
+ case OBJECT_PROFILE:
+ /* We treat these object types as being owned by superusers */
+ if (!superuser_arg(roleid))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser")));
+ break;
default:
elog(ERROR, "unrecognized object type: %d",
(int) objtype);
@@ -4026,6 +4060,25 @@
appendStringInfo(&buffer, _("task %s"), taskname);
break;
}
+
+ case OCLASS_PROFILE:
+ {
+ char *profilename = ProfileGetNameByOid(object->objectId,
+ missing_ok);
+
+ if (profilename)
+ appendStringInfo(&buffer, _("profile %s"), profilename);
+ break;
+ }
+ case OCLASS_PASSWORDHISTORY:
+ {
+ char *username = GetUserNameFromId(object->objectId,
+ missing_ok);
+
+ if (username)
+ appendStringInfo(&buffer, _("history password for role %s"), username);
+ break;
+ }
}
/* an empty buffer is equivalent to no object found */
@@ -4589,6 +4642,14 @@
appendStringInfoString(&buffer, "task");
break;
+ case OCLASS_PROFILE:
+ appendStringInfoString(&buffer, "profile");
+ break;
+
+ case OCLASS_PASSWORDHISTORY:
+ appendStringInfoString(&buffer, "password_history");
+ break;
+
/*
* There's intentionally no default: case here; we want the
* compiler to warn if a new OCLASS hasn't been handled above.
@@ -5897,6 +5958,35 @@
}
}
break;
+
+ case OCLASS_PROFILE:
+ {
+ char *prfname;
+
+ prfname = ProfileGetNameByOid(object->objectId, missing_ok);
+ if (!prfname)
+ break;
+ if (objname)
+ *objname = list_make1(prfname);
+ appendStringInfoString(&buffer,
+ quote_identifier(prfname));
+ break;
+ }
+
+ case OCLASS_PASSWORDHISTORY:
+ {
+ char *username;
+
+ username = GetUserNameFromId(object->objectId, missing_ok);
+ if (!username)
+ break;
+ if (objname)
+ *objname = list_make1(username);
+ appendStringInfo(&buffer,
+ "history password for role %s: ", quote_identifier(username));
+
+ break;
+ }
/*
* There's intentionally no default: case here; we want the
diff --git a/src/backend/catalog/oid_dispatch.c b/src/backend/catalog/oid_dispatch.c
index 2f49300..8147323 100644
--- a/src/backend/catalog/oid_dispatch.c
+++ b/src/backend/catalog/oid_dispatch.c
@@ -104,6 +104,7 @@
#include "catalog/pg_operator.h"
#include "catalog/pg_opfamily.h"
#include "catalog/pg_policy.h"
+#include "catalog/pg_profile.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_publication.h"
#include "catalog/pg_publication_rel.h"
@@ -728,6 +729,22 @@
}
Oid
+GetNewOidForProfile(Relation relation, Oid indexId, AttrNumber oidcolumn,
+ char *prfname)
+{
+ OidAssignment key;
+
+ Assert(RelationGetRelid(relation) == ProfileRelationId);
+ Assert(indexId == ProfileOidIndexId);
+ Assert(oidcolumn == Anum_pg_profile_oid);
+
+ memset(&key, 0, sizeof(OidAssignment));
+ key.type = T_OidAssignment;
+ key.objname = prfname;
+ return GetNewOrPreassignedOid(relation, indexId, oidcolumn, &key);
+}
+
+Oid
GetNewOidForForeignDataWrapper(Relation relation, Oid indexId, AttrNumber oidcolumn,
char *fdwname)
{
diff --git a/src/backend/catalog/pg_shdepend.c b/src/backend/catalog/pg_shdepend.c
index e9b3738..b7d9a69 100644
--- a/src/backend/catalog/pg_shdepend.c
+++ b/src/backend/catalog/pg_shdepend.c
@@ -38,6 +38,7 @@
#include "catalog/pg_operator.h"
#include "catalog/pg_opfamily.h"
#include "catalog/pg_proc.h"
+#include "catalog/pg_profile.h"
#include "catalog/pg_shdepend.h"
#include "catalog/pg_statistic_ext.h"
#include "catalog/pg_subscription.h"
@@ -1209,6 +1210,16 @@
break;
}
+ case ProfileRelationId:
+ {
+ if (!SearchSysCacheExists1(PROFILEID, ObjectIdGetDatum(objectId)))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("profile %u was concurrently dropped",
+ objectId)));
+ break;
+ }
+
default:
elog(ERROR, "unrecognized shared classId: %u", classId);
@@ -1261,6 +1272,8 @@
appendStringInfo(descs, _("target of %s"), objdesc);
else if (deptype == SHARED_DEPENDENCY_TABLESPACE)
appendStringInfo(descs, _("tablespace for %s"), objdesc);
+ else if (deptype == SHARED_DEPENDENCY_PROFILE)
+ appendStringInfo(descs, _("profile of %s"), objdesc);
else
elog(ERROR, "unrecognized dependency type: %d",
(int) deptype);
@@ -1683,3 +1696,50 @@
table_close(sdepRel, RowExclusiveLock);
}
+
+/*
+ * recordProfileDependency
+ *
+ * A convenient wrapper of recordSharedDependencyOn -- register the specified
+ * roles of attached to profile.
+ */
+void
+recordProfileDependency(Oid roleId, Oid profileId)
+{
+ ObjectAddress myself,
+ referenced;
+
+ myself.classId = AuthIdRelationId;
+ myself.objectId = roleId;
+ myself.objectSubId = 0;
+
+ referenced.classId = ProfileRelationId;
+ referenced.objectId = profileId;
+ referenced.objectSubId = 0;
+
+ recordSharedDependencyOn(&myself, &referenced, SHARED_DEPENDENCY_PROFILE);
+}
+
+/*
+ * changeProfileDependency
+ *
+ * Update the shared dependencies to account for the new profile.
+ *
+ * Note: we don't need an objsubid argument because only whole objects
+ * have owners.
+ */
+void
+changeProfileDependency(Oid roleId, Oid newprofileId)
+{
+ Relation sdepRel;
+
+ sdepRel = table_open(SharedDependRelationId, RowExclusiveLock);
+
+ /* Adjust the SHARED_DEPENDENCY_PROFILE entry */
+ shdepChangeDep(sdepRel,
+ AuthIdRelationId, roleId, 0,
+ ProfileRelationId, newprofileId,
+ SHARED_DEPENDENCY_PROFILE);
+
+ table_close(sdepRel, RowExclusiveLock);
+}
\ No newline at end of file
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 44dc6fe..ead1aac 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -26,8 +26,14 @@
rolcanlogin,
rolreplication,
rolconnlimit,
+ rolenableprofile,
+ pg_profile.prfname AS rolprofile,
+ rolaccountstatus,
+ rolfailedlogins,
'********'::text as rolpassword,
rolvaliduntil,
+ rollockdate,
+ rolpasswordexpire,
rolbypassrls,
setconfig as rolconfig,
rolresqueue,
@@ -36,8 +42,9 @@
rolcreaterexthttp,
rolcreatewextgpfd,
rolresgroup
- FROM pg_authid LEFT JOIN pg_db_role_setting s
- ON (pg_authid.oid = setrole AND setdatabase = 0);
+ FROM pg_profile, pg_authid LEFT JOIN pg_db_role_setting s
+ ON (pg_authid.oid = setrole AND setdatabase = 0)
+ WHERE pg_profile.oid = pg_authid.rolprofile;
CREATE VIEW pg_shadow AS
SELECT
diff --git a/src/backend/commands/Makefile b/src/backend/commands/Makefile
index 613886d..94e0485 100644
--- a/src/backend/commands/Makefile
+++ b/src/backend/commands/Makefile
@@ -63,7 +63,8 @@
user.o \
vacuum.o \
variable.o \
- view.o
+ view.o \
+ pg_profile.o
OBJS += analyzefuncs.o analyzeutils.o extprotocolcmds.o exttablecmds.o queue.o
OBJS += resgroupcmds.o tablecmds_gp.o vacuum_ao.o taskcmds.o
diff --git a/src/backend/commands/alter.c b/src/backend/commands/alter.c
index dad0bcf..cb6f6d6 100644
--- a/src/backend/commands/alter.c
+++ b/src/backend/commands/alter.c
@@ -33,6 +33,7 @@
#include "catalog/pg_namespace.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_opfamily.h"
+#include "catalog/pg_profile.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_statistic_ext.h"
#include "catalog/pg_subscription.h"
@@ -379,6 +380,9 @@
case OBJECT_POLICY:
return rename_policy(stmt);
+ case OBJECT_PROFILE:
+ return rename_profile(stmt->subname, stmt->newname);
+
case OBJECT_DOMAIN:
case OBJECT_TYPE:
return RenameType(stmt);
@@ -726,6 +730,8 @@
case OCLASS_TRANSFORM:
case OCLASS_EXTPROTOCOL:
case OCLASS_TASK:
+ case OCLASS_PROFILE:
+ case OCLASS_PASSWORDHISTORY:
/* ignore object types that don't have schema-qualified names */
break;
diff --git a/src/backend/commands/comment.c b/src/backend/commands/comment.c
index 50b9697..b8e5f14 100644
--- a/src/backend/commands/comment.c
+++ b/src/backend/commands/comment.c
@@ -131,6 +131,7 @@
case OBJECT_ROLE:
case OBJECT_RESQUEUE:
case OBJECT_RESGROUP:
+ case OBJECT_PROFILE:
CreateSharedComments(address.objectId, address.classId, stmt->comment);
break;
default:
diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c
index 72b3cb7..94c3124 100644
--- a/src/backend/commands/event_trigger.c
+++ b/src/backend/commands/event_trigger.c
@@ -940,6 +940,7 @@
case OBJECT_DATABASE:
case OBJECT_TABLESPACE:
case OBJECT_ROLE:
+ case OBJECT_PROFILE:
/* no support for global objects */
return false;
case OBJECT_EVENT_TRIGGER:
@@ -1021,6 +1022,8 @@
case OCLASS_DATABASE:
case OCLASS_TBLSPACE:
case OCLASS_ROLE:
+ case OCLASS_PROFILE:
+ case OCLASS_PASSWORDHISTORY:
/* no support for global objects */
return false;
case OCLASS_EVENT_TRIGGER:
@@ -2156,6 +2159,7 @@
case OBJECT_EXTPROTOCOL:
case OBJECT_RESQUEUE:
case OBJECT_RESGROUP:
+ case OBJECT_PROFILE:
elog(ERROR, "unsupported object type: %d", (int) objtype);
}
@@ -2241,6 +2245,7 @@
case OBJECT_EXTPROTOCOL:
case OBJECT_RESQUEUE:
case OBJECT_RESGROUP:
+ case OBJECT_PROFILE:
elog(ERROR, "unsupported object type: %d", (int) objtype);
}
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index 2f37cec..08c3a9b 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -3526,6 +3526,7 @@
case OBJECT_STATISTIC_EXT:
case OBJECT_SUBSCRIPTION:
case OBJECT_TABLESPACE:
+ case OBJECT_PROFILE:
ereport(ERROR,
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
errmsg("cannot add an object of this type to an extension")));
diff --git a/src/backend/commands/pg_profile.c b/src/backend/commands/pg_profile.c
new file mode 100644
index 0000000..4a88ba8
--- /dev/null
+++ b/src/backend/commands/pg_profile.c
@@ -0,0 +1,831 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_profile.c
+ * routines to support manipulation of the pg_profile relation
+ *
+ *
+ * Copyright (c) 2023, Cloudberry Database, HashData Technology Limited.
+ *
+ *
+ * IDENTIFICATION
+ * src/backend/command/pg_profile.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "access/genam.h"
+#include "access/heapam.h"
+#include "catalog/dependency.h"
+#include "catalog/heap.h"
+#include "catalog/oid_dispatch.h"
+#include "catalog/pg_authid.h"
+#include "catalog/pg_profile.h"
+#include "cdb/cdbdisp_query.h"
+#include "cdb/cdbvars.h"
+#include "catalog/objectaccess.h"
+#include "postmaster/postmaster.h"
+#include "storage/lmgr.h"
+#include "utils/builtins.h"
+#include "utils/syscache.h"
+
+PG_FUNCTION_INFO_V1(get_role_status);
+
+char *
+ProfileGetNameByOid(Oid profileOid, bool noerr)
+{
+ char *prfnamestr;
+ Relation rel;
+ ScanKeyData skey;
+ SysScanDesc scan;
+ HeapTuple tup;
+ Form_pg_profile profile;
+
+ rel = table_open(ProfileRelationId, AccessShareLock);
+
+ /*
+ * Search pg_profile.
+ */
+ ScanKeyInit(&skey,
+ Anum_pg_profile_oid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(profileOid));
+ scan = systable_beginscan(rel, ProfileOidIndexId, true,
+ NULL, 1, &skey);
+ tup = systable_getnext(scan);
+ if (!HeapTupleIsValid(tup))
+ {
+ if (!noerr)
+ elog(ERROR, "profile %u could not be found", profileOid);
+
+ prfnamestr = NULL;
+ }
+ else
+ {
+ profile = (Form_pg_profile) GETSTRUCT(tup);
+
+ prfnamestr = pstrdup(profile->prfname.data);
+ }
+
+ systable_endscan(scan);
+ table_close(rel, AccessShareLock);
+
+ return prfnamestr;
+}
+
+/*
+ * get_profile_oid - Given a profile name, look up the profile's OID.
+ *
+ * If missing_ok is false, throw an error if profile name not found. If
+ * true, just return InvalidOid.
+ */
+Oid get_profile_oid(const char* prfname, bool missing_ok)
+{
+ Oid oid;
+
+ oid = GetSysCacheOid1(PROFILENAME, Anum_pg_profile_oid,
+ CStringGetDatum(prfname));
+ if (!OidIsValid(oid) && !missing_ok)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("profile \"%s\" does not exist", prfname)));
+ return oid;
+}
+
+/*
+ * rename_profile -
+ * change the name of a profile
+ */
+ObjectAddress
+rename_profile(char *oldname, char *newname)
+{
+ HeapTuple oldtuple,
+ newtuple;
+ TupleDesc dsc;
+ Relation rel;
+ Datum repl_val[Natts_pg_profile];
+ bool repl_null[Natts_pg_profile];
+ bool repl_repl[Natts_pg_profile];
+ Oid profileid;
+ ObjectAddress address;
+ Form_pg_profile profileform;
+
+ /* Only when enable_password_profile is true, can RENAME PROFILE. */
+ if (!enable_password_profile)
+ ereport(ERROR,
+ (errcode(ERRCODE_GP_FEATURE_NOT_CONFIGURED),
+ errmsg("can't RENAME PROFILE for enable_password_profile is not open")));
+
+ /* Only super user can RENMAE PROFILE. */
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied to rename profile, must be superuser")));
+
+ /* pg_default profile can't be renamed */
+ if (strcmp(oldname, "pg_default") == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("can't RENAME \"pg_default\" profile")));
+
+ rel = table_open(ProfileRelationId, RowExclusiveLock);
+ dsc = RelationGetDescr(rel);
+
+ oldtuple = SearchSysCache1(PROFILENAME, CStringGetDatum(oldname));
+ if (!HeapTupleIsValid(oldtuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("profile \"%s\" does not exist", oldname)));
+
+ profileform = (Form_pg_profile) GETSTRUCT(oldtuple);
+ profileid = profileform->oid;
+
+ /* make sure the new name doesn't exist */
+ if (SearchSysCacheExists1(PROFILENAME, CStringGetDatum(newname)))
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("profile \"%s\" already exists", newname)));
+
+ /* Build up updated catalog tuple */
+ MemSet(repl_repl, false, sizeof(repl_repl));
+
+ repl_repl[Anum_pg_profile_prfname - 1] = true;
+ repl_val[Anum_pg_profile_prfname - 1] = DirectFunctionCall1(namein,CStringGetDatum(newname));
+ repl_null[Anum_pg_profile_prfname - 1] = false;
+
+ newtuple = heap_modify_tuple(oldtuple, dsc, repl_val, repl_null, repl_repl);
+ CatalogTupleUpdate(rel, &oldtuple->t_self, newtuple);
+
+ InvokeObjectPostAlterHook(ProfileRelationId, profileid, 0);
+
+ ObjectAddressSet(address, ProfileRelationId, profileid);
+
+ ReleaseSysCache(oldtuple);
+
+ /*
+ * Close pg_profile, keep lock until transaction commit.
+ */
+ table_close(rel, NoLock);
+
+ /* MPP-6929: metadata tracking */
+ if (Gp_role == GP_ROLE_DISPATCH)
+ MetaTrackUpdObject(ProfileRelationId,
+ profileid,
+ GetUserId(),
+ "ALTER", "RENAME"
+ );
+
+ return address;
+}
+
+/*
+ * CREATE PROFILE
+ */
+Oid
+CreateProfile(ParseState *pstate, CreateProfileStmt *stmt)
+{
+ Relation pg_profile_rel;
+ TupleDesc pg_profile_dsc;
+ HeapTuple tuple;
+ Datum new_record[Natts_pg_profile];
+ bool new_record_nulls[Natts_pg_profile];
+ Oid profileid;
+ ListCell *option;
+ int failed_login_attempts = -1;
+ int password_lock_time = -1;
+ int password_life_time = -1;
+ int password_grace_time = -1;
+ int password_reuse_time = -1;
+ int password_reuse_max = -1;
+ int password_allow_hashed = -1;
+ DefElem *dfailedloginattempts = NULL;
+ DefElem *dpasswordlocktime = NULL;
+ DefElem *dpasswordreusemax = NULL;
+
+ /* Only when enable_password_profile is true, can CREATE PROFILE. */
+ if (!enable_password_profile)
+ ereport(ERROR,
+ (errcode(ERRCODE_GP_FEATURE_NOT_CONFIGURED),
+ errmsg("can't CREATE PROFILE for enable_password_profile is not open")));
+
+ /* Only super user can CREATE PROFILE. */
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied to create profile, must be superuser")));
+
+ /* Extract options from the statement node tree */
+ foreach(option, stmt->options)
+ {
+ DefElem *defel = (DefElem *) lfirst(option);
+
+ if (strcmp(defel->defname, "failed_login_attempts") == 0)
+ {
+ if (dfailedloginattempts)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options"),
+ parser_errposition(pstate, defel->location)));
+ dfailedloginattempts = defel;
+ }
+ else if (strcmp(defel->defname, "password_lock_time") == 0)
+ {
+ if (dpasswordlocktime)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options"),
+ parser_errposition(pstate, defel->location)));
+ dpasswordlocktime = defel;
+ }
+ else if (strcmp(defel->defname, "password_reuse_max") == 0)
+ {
+ if (dpasswordreusemax)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options"),
+ parser_errposition(pstate, defel->location)));
+ dpasswordreusemax = defel;
+ }
+ }
+
+ if (dfailedloginattempts && dfailedloginattempts->arg)
+ {
+ failed_login_attempts = intVal(dfailedloginattempts->arg);
+ if (failed_login_attempts == 0 ||
+ failed_login_attempts < PROFILE_UNLIMITED ||
+ failed_login_attempts > PROFILE_MAX_VALID)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid failed login attempts: %d", failed_login_attempts)));
+ }
+
+ if (dpasswordlocktime && dpasswordlocktime->arg)
+ {
+ password_lock_time = intVal(dpasswordlocktime->arg);
+ if (password_lock_time < PROFILE_UNLIMITED ||
+ password_lock_time > PROFILE_MAX_VALID)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid password lock time: %d", password_lock_time)));
+ }
+
+ if (dpasswordreusemax && dpasswordreusemax->arg)
+ {
+ password_reuse_max = intVal(dpasswordreusemax->arg);
+ if (password_reuse_max < PROFILE_UNLIMITED ||
+ password_reuse_max > PROFILE_MAX_VALID)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid password reuse max: %d", password_reuse_max)));
+ }
+
+ /*
+ * Check the pg_profile relation to be certain the profile doesn't already
+ * exist.
+ */
+ pg_profile_rel = table_open(ProfileRelationId, RowExclusiveLock);
+ pg_profile_dsc = RelationGetDescr(pg_profile_rel);
+
+ if (OidIsValid(get_profile_oid(stmt->profile_name, true)))
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("profile \"%s\" already exists",
+ stmt->profile_name)));
+
+ /*
+ * Build a tuple to insert
+ */
+ MemSet(new_record, 0, sizeof(new_record));
+ MemSet(new_record_nulls, false, sizeof(new_record_nulls));
+
+ new_record[Anum_pg_profile_prfname - 1] =
+ DirectFunctionCall1(namein, CStringGetDatum(stmt->profile_name));
+
+ new_record[Anum_pg_profile_prffailedloginattempts - 1] =
+ Int32GetDatum(failed_login_attempts);
+ new_record[Anum_pg_profile_prfpasswordlocktime - 1] =
+ Int32GetDatum(password_lock_time);
+ new_record[Anum_pg_profile_prfpasswordlifetime - 1] =
+ Int32GetDatum(password_life_time);
+ new_record[Anum_pg_profile_prfpasswordgracetime - 1] =
+ Int32GetDatum(password_grace_time);
+ new_record[Anum_pg_profile_prfpasswordreusetime - 1] =
+ Int32GetDatum(password_reuse_time);
+ new_record[Anum_pg_profile_prfpasswordreusemax - 1] =
+ Int32GetDatum(password_reuse_max);
+ new_record[Anum_pg_profile_prfpasswordallowhashed - 1] =
+ Int32GetDatum(password_allow_hashed);
+ new_record_nulls[Anum_pg_profile_prfpasswordverifyfuncdb -1] =
+ true;
+ new_record_nulls[Anum_pg_profile_prfpasswordverifyfunc - 1] =
+ true;
+
+ /*
+ * GetNewOidForProfile() / GetNewOrPreassignedOid() will return the
+ * pre-assigned OID, if any, and error out if there was no pre-assigned
+ * values in binary upgrade mode.
+ */
+ profileid = GetNewOidForProfile(pg_profile_rel, ProfileOidIndexId,
+ Anum_pg_profile_oid,
+ stmt->profile_name);
+
+ new_record[Anum_pg_profile_oid - 1] =
+ ObjectIdGetDatum(profileid);
+
+ tuple = heap_form_tuple(pg_profile_dsc, new_record, new_record_nulls);
+
+ /*
+ * Insert new record in the pg_profile table
+ */
+ CatalogTupleInsert(pg_profile_rel, tuple);
+
+ InvokeObjectPostCreateHook(ProfileRelationId, profileid, 0);
+
+ heap_freetuple(tuple);
+
+ /*
+ * Close pg_profile, but keep lock till commit
+ */
+ table_close(pg_profile_rel, NoLock);
+
+ if (Gp_role == GP_ROLE_DISPATCH)
+ {
+ Assert(stmt->type == T_CreateProfileStmt);
+ CdbDispatchUtilityStatement((Node *) stmt,
+ DF_CANCEL_ON_ERROR|
+ DF_WITH_SNAPSHOT|
+ DF_NEED_TWO_PHASE,
+ GetAssignedOidsForDispatch(),
+ NULL);
+
+ /* MPP-6929: metadata tracking */
+ MetaTrackAddObject(ProfileRelationId,
+ profileid,
+ GetUserId(),
+ "CREATE", "PROFILE");
+ }
+
+ return profileid;
+}
+
+/*
+ * ALTER PROFILE
+ */
+Oid
+AlterProfile(AlterProfileStmt *stmt)
+{
+ Datum new_record[Natts_pg_profile];
+ bool new_record_nulls[Natts_pg_profile];
+ bool new_record_repl[Natts_pg_profile];
+ Relation pg_profile_rel;
+ TupleDesc pg_profile_dsc;
+ HeapTuple tuple,
+ new_tuple;
+ Form_pg_profile profileform;
+ Oid profileid;
+ char *profile_name;
+ ListCell *option;
+ int failed_login_attempts = -1;
+ int password_lock_time = -1;
+ int password_life_time = -1;
+ int password_grace_time = -1;
+ int password_reuse_time = -1;
+ int password_reuse_max = -1;
+ int password_allow_hashed = -1;
+ Oid password_verify_funcdb = 0;
+ Oid password_verify_func = 0;
+ DefElem *dfailedloginattempts = NULL;
+ DefElem *dpasswordlocktime = NULL;
+ DefElem *dpasswordreusemax = NULL;
+ int numopts = 0;
+
+ numopts = list_length(stmt->options);
+
+ /* Only when enable_password_profile is true, can ALTER PROFILE. */
+ if (!enable_password_profile)
+ ereport(ERROR,
+ (errcode(ERRCODE_GP_FEATURE_NOT_CONFIGURED),
+ errmsg("can't ALTER PROFILE for enable_password_profile is not open")));
+
+ /* Only super user can ALTER PROFILE */
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied to alter profile, must be superuser")));
+
+ profile_name = stmt->profile_name;
+ Assert(profile_name);
+
+ /* Extract options from the statement node tree */
+ foreach(option, stmt->options)
+ {
+ DefElem *defel = (DefElem *) lfirst(option);
+
+ if (strcmp(defel->defname, "failed_login_attempts") == 0)
+ {
+ if (dfailedloginattempts)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ dfailedloginattempts = defel;
+ }
+ else if (strcmp(defel->defname, "password_lock_time") == 0)
+ {
+ if (dpasswordlocktime)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ dpasswordlocktime = defel;
+ }
+ else if (strcmp(defel->defname, "password_reuse_max") == 0)
+ {
+ if (dpasswordreusemax)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ dpasswordreusemax = defel;
+ }
+ }
+
+ if (dfailedloginattempts && dfailedloginattempts->arg)
+ {
+ failed_login_attempts = intVal(dfailedloginattempts->arg);
+ if (failed_login_attempts == 0 ||
+ failed_login_attempts < PROFILE_UNLIMITED ||
+ failed_login_attempts > PROFILE_MAX_VALID)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid failed login attempts: %d", failed_login_attempts)));
+ else if (failed_login_attempts == PROFILE_DEFAULT &&
+ strcmp(profile_name, "pg_default") == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("can't set failed login attempts to -1(DEFAULT) of pg_default")));
+ }
+
+ if (dpasswordlocktime && dpasswordlocktime->arg)
+ {
+ password_lock_time = intVal(dpasswordlocktime->arg);
+ if (password_lock_time < PROFILE_UNLIMITED ||
+ password_lock_time > PROFILE_MAX_VALID)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid password lock time: %d", password_lock_time)));
+ else if (password_lock_time == PROFILE_DEFAULT &&
+ strcmp(profile_name, "pg_default") == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("can't set password lock time to -1(DEFAULT) of pg_default")));
+ }
+
+ if (dpasswordreusemax && dpasswordreusemax->arg)
+ {
+ password_reuse_max = intVal(dpasswordreusemax->arg);
+ if (password_reuse_max < PROFILE_UNLIMITED ||
+ password_reuse_max > PROFILE_MAX_VALID)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid password reuse max: %d", password_reuse_max)));
+ else if (password_reuse_max == PROFILE_DEFAULT &&
+ strcmp(profile_name, "pg_default") == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("can't set password reuse max to -1(DEFAULT) of pg_default")));
+ }
+
+ /*
+ * Scan the pg_profile relation to be certain the profile exists.
+ */
+ pg_profile_rel = table_open(ProfileRelationId, RowExclusiveLock);
+ pg_profile_dsc = RelationGetDescr(pg_profile_rel);
+
+ tuple = SearchSysCache1(PROFILENAME, CStringGetDatum(profile_name));
+ if (!HeapTupleIsValid(tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("profile \"%s\" does not exist", profile_name)));
+
+ profileform = (Form_pg_profile) GETSTRUCT(tuple);
+ profileid = profileform->oid;
+
+ /*
+ * If all altered profile parameters are same with existing parameters, don't need
+ * to update tuple and return directly.
+ */
+ if (failed_login_attempts == profileform->prffailedloginattempts &&
+ password_lock_time == profileform->prfpasswordlocktime &&
+ password_reuse_max == profileform->prfpasswordreusemax)
+ {
+ ReleaseSysCache(tuple);
+ table_close(pg_profile_rel, NoLock);
+
+ return profileid;
+ }
+
+ /*
+ * Build an updated tuple, perusing the information just obtained
+ */
+ MemSet(new_record, 0, sizeof(new_record));
+ MemSet(new_record_nulls, false, sizeof(new_record_nulls));
+ MemSet(new_record_repl, false, sizeof(new_record_repl));
+
+ if (failed_login_attempts != -1 &&
+ failed_login_attempts != profileform->prffailedloginattempts)
+ {
+ new_record[Anum_pg_profile_prffailedloginattempts - 1] =
+ Int32GetDatum(failed_login_attempts);
+ new_record_repl[Anum_pg_profile_prffailedloginattempts - 1] =
+ true;
+ }
+
+ if (password_lock_time != -1 &&
+ password_lock_time != profileform->prfpasswordlocktime)
+ {
+ new_record[Anum_pg_profile_prfpasswordlocktime - 1] =
+ Int32GetDatum(password_lock_time);
+ new_record_repl[Anum_pg_profile_prfpasswordlocktime - 1] =
+ true;
+ }
+
+ if (password_reuse_max != -1 &&
+ password_reuse_max != profileform->prfpasswordreusemax)
+ {
+ new_record[Anum_pg_profile_prfpasswordreusemax - 1] =
+ Int32GetDatum(password_reuse_max);
+ new_record_repl[Anum_pg_profile_prfpasswordreusemax - 1] =
+ true;
+ }
+
+ /*
+ * Now, we just support failed_login_attempts, password_lock_time and
+ * password_reuse_max, we need to set other fields' values to default.
+ */
+ new_record[Anum_pg_profile_prfpasswordlifetime - 1] =
+ Int32GetDatum(password_life_time);
+ new_record[Anum_pg_profile_prfpasswordgracetime - 1] =
+ Int32GetDatum(password_grace_time);
+ new_record[Anum_pg_profile_prfpasswordreusetime - 1] =
+ Int32GetDatum(password_reuse_time);
+ new_record[Anum_pg_profile_prfpasswordallowhashed - 1] =
+ Int32GetDatum(password_allow_hashed);
+ new_record[Anum_pg_profile_prfpasswordverifyfuncdb -1] =
+ Int32GetDatum(password_verify_funcdb);
+ new_record[Anum_pg_profile_prfpasswordverifyfunc - 1] =
+ Int32GetDatum(password_verify_func);
+
+ new_tuple = heap_modify_tuple(tuple, pg_profile_dsc,
+ new_record, new_record_nulls, new_record_repl);
+ CatalogTupleUpdate(pg_profile_rel, &tuple->t_self, new_tuple);
+
+ InvokeObjectPostAlterHook(ProfileRelationId, profileid, 0);
+
+ ReleaseSysCache(tuple);
+ heap_freetuple(new_tuple);
+
+ /* MPP-6929: metadata tracking */
+ if (Gp_role == GP_ROLE_DISPATCH)
+ MetaTrackUpdObject(ProfileRelationId,
+ profileid,
+ GetUserId(),
+ "ALTER", "PROFILE");
+
+ /*
+ * Close pg_profile, but keep lock untill commit.
+ */
+ table_close(pg_profile_rel, NoLock);
+
+ if (Gp_role == GP_ROLE_DISPATCH)
+ {
+ CdbDispatchUtilityStatement((Node *) stmt,
+ DF_CANCEL_ON_ERROR|
+ DF_WITH_SNAPSHOT|
+ DF_NEED_TWO_PHASE,
+ NIL,
+ NULL);
+ }
+
+ return profileid;
+}
+
+/*
+ * DROP PROFILE
+ */
+void
+DropProfile(DropProfileStmt *stmt)
+{
+ Relation pg_profile_rel;
+ ListCell *cell;
+
+ /* Only when enable_password_profile is true, can ALTER PROFILE. */
+ if (!enable_password_profile)
+ ereport(ERROR,
+ (errcode(ERRCODE_GP_FEATURE_NOT_CONFIGURED),
+ errmsg("can't DROP PROFILE for enable_password_profile is not open")));
+
+ /* Only super user can DROP PROFILE */
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied to drop profile, must be superuser")));
+
+ /*
+ * Scan the pg_profile relation to find the Oid of the Profile(s) to be
+ * deleted.
+ */
+ pg_profile_rel = table_open(ProfileRelationId, RowExclusiveLock);
+
+ foreach(cell, stmt->profiles)
+ {
+ HeapTuple tuple;
+ Form_pg_profile profileform;
+ char *detail;
+ char *detail_log;
+ Oid profileid;
+
+ char *profile_name = strVal(lfirst(cell));
+ if (strcmp(profile_name, "pg_default") == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("Disallow to drop default profile")));
+
+ tuple = SearchSysCache1(PROFILENAME, PointerGetDatum(profile_name));
+ if (!HeapTupleIsValid(tuple))
+ {
+ if (!stmt->missing_ok)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("profile \"%s\" does not exist", profile_name)));
+ }
+ if (Gp_role != GP_ROLE_EXECUTE)
+ {
+ ereport(NOTICE,
+ (errmsg("profile \"%s\" does not exist", profile_name)));
+ }
+
+ continue;
+ }
+
+ profileform = (Form_pg_profile) GETSTRUCT(tuple);
+ profileid = profileform->oid;
+
+ /* DROP hook for the profile being removed */
+ InvokeObjectDropHook(ProfileRelationId, profileid, 0);
+
+ /*
+ * Lock the profile, so nobody can add dependencies to her while we drop
+ * her. We keep the lock until the end of transaction.
+ */
+ LockSharedObject(ProfileRelationId, profileid, 0, AccessExclusiveLock);
+
+ /* Check for pg_shdepend entries depending on this profile */
+ if (checkSharedDependencies(ProfileRelationId, profileid,
+ &detail, &detail_log))
+ ereport(ERROR,
+ (errcode(ERRCODE_DEPENDENT_OBJECTS_STILL_EXIST),
+ errmsg("profile \"%s\" cannot be dropped because some objects depend on it",
+ profile_name),
+ errdetail_internal("%s", detail),
+ errdetail_log("%s", detail_log)));
+
+ /*
+ * Remove the profile from the pg_profile table
+ */
+ CatalogTupleDelete(pg_profile_rel, &tuple->t_self);
+
+ ReleaseSysCache(tuple);
+
+ /* metadata track */
+ if (Gp_role == GP_ROLE_DISPATCH)
+ MetaTrackDropObject(ProfileRelationId,
+ profileid);
+ }
+
+ /*
+ * Now we can clean up; but keep locks until commit.
+ */
+ table_close(pg_profile_rel, NoLock);
+
+ if (Gp_role == GP_ROLE_DISPATCH)
+ {
+ CdbDispatchUtilityStatement((Node *) stmt,
+ DF_CANCEL_ON_ERROR|
+ DF_WITH_SNAPSHOT|
+ DF_NEED_TWO_PHASE,
+ NIL,
+ NULL);
+ }
+}
+
+/*
+ * As pg_profile fields' values may be PROFILE_UNLIMITED(-2) or
+ * PROFILE_DEFAULT(-1), transform them to normal values.
+ */
+int32
+tranformProfileValueToNormal(int32 profile_val, int attoff)
+{
+ HeapTuple profile_tuple;
+ int32 normal_profile_val;
+ bool isnull;
+ Datum datum;
+ Relation rel;
+
+ /*
+ * If current value is PROFILE_DEFAULT(-1), we need to search
+ * pg_profile catalog to get default profile's value.
+ */
+ if (profile_val == PROFILE_DEFAULT)
+ {
+ rel = table_open(ProfileRelationId, AccessShareLock);
+
+ profile_tuple = SearchSysCache1(PROFILEID, ObjectIdGetDatum(DefaultProfileOID));
+ Assert(HeapTupleIsValid(profile_tuple));
+
+ datum = SysCacheGetAttr(PROFILEID, profile_tuple, attoff, &isnull);
+ Assert(!isnull);
+
+ normal_profile_val = DatumGetInt32(datum);
+
+ if (normal_profile_val == PROFILE_UNLIMITED)
+ normal_profile_val = PROFILE_MAX_VALID;
+
+ ReleaseSysCache(profile_tuple);
+ table_close(rel, AccessShareLock);
+ }
+ /*
+ * Currently, the profile field's max valid value is PROFILE_MAX_VALID(9999).
+ */
+ else if (profile_val == PROFILE_UNLIMITED)
+ normal_profile_val = PROFILE_MAX_VALID;
+ else
+ normal_profile_val = profile_val;
+
+ return normal_profile_val;
+}
+
+/*
+ * Function get_role_status()
+ */
+Datum
+get_role_status(PG_FUNCTION_ARGS)
+{
+ HeapTuple tuple;
+ char *user_name;
+ Datum datum;
+ bool isnull;
+ int16 account_status;
+ char *ret = NULL;
+
+ user_name = PG_GETARG_CSTRING(0);
+
+ /* Only when enable_password_profile is true, can ALTER PROFILE. */
+ if (!enable_password_profile)
+ ereport(ERROR,
+ (errcode(ERRCODE_GP_FEATURE_NOT_CONFIGURED),
+ errmsg("can't call get_role_status for enable_password_profile is not open")));
+
+ /* Only super users has the privilege */
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied to call get_role_status, must be superuser")));
+
+ tuple = SearchSysCache1(AUTHNAME, CStringGetDatum(user_name));
+ if (!HeapTupleIsValid(tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("user \"%s\" does not exist", user_name)));
+
+ datum = SysCacheGetAttr(AUTHNAME, tuple, Anum_pg_authid_rolaccountstatus, &isnull);
+ Assert(!isnull);
+
+ account_status = DatumGetInt16(datum);
+
+ ReleaseSysCache(tuple);
+
+ switch(account_status)
+ {
+ case ROLE_ACCOUNT_STATUS_OPEN:
+ ret = "OPEN";
+ break;
+ case ROLE_ACCOUNT_STATUS_LOCKED_TIMED:
+ ret = "LOCKED(TIMED)";
+ break;
+ case ROLE_ACCOUNT_STATUS_LOCKED:
+ ret = "LOCKED";
+ break;
+ case ROLE_ACCOUNT_STATUS_EXPIRED_GRACE:
+ ret = "EXPIRED(GRACE)";
+ break;
+ case ROLE_ACCOUNT_STATUS_EXPIRED:
+ ret = "EXPIRED";
+ break;
+ default:
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("user %s's account_status %d not recognized",
+ user_name, account_status)));
+ }
+
+ PG_RETURN_CSTRING(ret);
+}
\ No newline at end of file
diff --git a/src/backend/commands/seclabel.c b/src/backend/commands/seclabel.c
index c6adb01..487ef44 100644
--- a/src/backend/commands/seclabel.c
+++ b/src/backend/commands/seclabel.c
@@ -59,6 +59,7 @@
case OBJECT_TABLESPACE:
case OBJECT_TYPE:
case OBJECT_VIEW:
+ case OBJECT_PROFILE:
return true;
case OBJECT_ACCESS_METHOD:
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index ab15d32..8d796cc 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -14000,6 +14000,8 @@
case OCLASS_TRANSFORM:
case OCLASS_EXTPROTOCOL:
case OCLASS_TASK:
+ case OCLASS_PROFILE:
+ case OCLASS_PASSWORDHISTORY:
/*
* We don't expect any of these sorts of objects to depend on
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 5c3db97..69cf69f 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -28,12 +28,15 @@
#include "catalog/pg_authid.h"
#include "catalog/pg_database.h"
#include "catalog/pg_db_role_setting.h"
+#include "catalog/pg_password_history.h"
+#include "catalog/pg_profile.h"
#include "commands/comment.h"
#include "commands/dbcommands.h"
#include "commands/seclabel.h"
#include "commands/user.h"
#include "libpq/crypt.h"
#include "miscadmin.h"
+#include "postmaster/postmaster.h"
#include "storage/lmgr.h"
#include "utils/acl.h"
#include "utils/builtins.h"
@@ -112,6 +115,8 @@
ListCell *item;
ListCell *option;
char *password = NULL; /* user password */
+ char *profilename = NULL; /* profile name the role be attached */
+ Oid profileId = DefaultProfileOID; /* default profile oid */
bool issuper = false; /* Make the user a superuser? */
bool inherit = true; /* Auto inherit privileges? */
bool createrole = false; /* Can this user create roles? */
@@ -133,6 +138,10 @@
bool validUntil_null;
char *resqueue = NULL; /* resource queue for this role */
char *resgroup = NULL; /* resource group for this role */
+ bool account_is_lock = false; /* whether the account will be locked/unlocked */
+ bool enable_profile = false; /* whether user can use password profile */
+ int16 account_status = ROLE_ACCOUNT_STATUS_OPEN; /* default accountstatus is 'OPEN' */
+ TimestampTz now = 0; /* current timestamp with time zone */
List *addintervals = NIL; /* list of time intervals for which login should be denied */
DefElem *dpassword = NULL;
DefElem *dresqueue = NULL;
@@ -149,6 +158,11 @@
DefElem *dadminmembers = NULL;
DefElem *dvalidUntil = NULL;
DefElem *dbypassRLS = NULL;
+ DefElem *dprofile = NULL;
+ DefElem *daccountIsLock = NULL;
+ DefElem *denableProfile = NULL;
+
+ now = GetCurrentTimestamp();
/* The defaults can vary depending on the original statement type */
switch (stmt->stmt_type)
@@ -333,6 +347,31 @@
parser_errposition(pstate, defel->location)));
dbypassRLS = defel;
}
+
+ else if (strcmp(defel->defname, "profile") == 0)
+ {
+ if (dprofile)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ dprofile = defel;
+ }
+ else if (strcmp(defel->defname, "accountislock") == 0)
+ {
+ if (daccountIsLock)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ daccountIsLock = defel;
+ }
+ else if (strcmp(defel->defname, "enableProfile") == 0)
+ {
+ if (denableProfile)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ denableProfile = defel;
+ }
else
elog(ERROR, "option \"%s\" not recognized",
defel->defname);
@@ -374,6 +413,55 @@
resgroup = strVal(linitial((List *) dresgroup->arg));
if (dbypassRLS)
bypassrls = intVal(dbypassRLS->arg) != 0;
+ if (dprofile)
+ profilename = strVal(dprofile->arg);
+ if (daccountIsLock)
+ account_is_lock = intVal(daccountIsLock->arg) != 0;
+ if (denableProfile)
+ enable_profile = intVal(denableProfile->arg) != 0;
+
+ /*
+ * Only the super user has the privileges of profile.
+ */
+ if (dprofile)
+ {
+ if (!enable_password_profile)
+ ereport(ERROR,
+ (errcode(ERRCODE_GP_FEATURE_NOT_CONFIGURED),
+ errmsg("can't CREATE USER ... PROFILE for enable_password_profile is not open")));
+
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to create role attached to profile")));
+ }
+
+ if (daccountIsLock)
+ {
+ if (!enable_password_profile)
+ ereport(ERROR,
+ (errcode(ERRCODE_GP_FEATURE_NOT_CONFIGURED),
+ errmsg("can't CREATE USER ... ACCOUNT LOCK/UNLOCK for enable_password_profile is not open")));
+
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to create role account lock/unlock")));
+ }
+
+ if (denableProfile)
+ {
+ if (!enable_password_profile)
+ ereport(ERROR,
+ (errcode(ERRCODE_GP_FEATURE_NOT_CONFIGURED),
+ errmsg("can't CREATE USER ... ENABLE/DISABLE PROFILE for enable_password_profile is not open")));
+
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to create role enable/disable profile")));
+ }
+
/* Check some permissions first */
if (issuper)
@@ -479,6 +567,14 @@
new_record[Anum_pg_authid_rolcanlogin - 1] = BoolGetDatum(canlogin);
new_record[Anum_pg_authid_rolreplication - 1] = BoolGetDatum(isreplication);
new_record[Anum_pg_authid_rolconnlimit - 1] = Int32GetDatum(connlimit);
+ new_record[Anum_pg_authid_rolenableprofile - 1] = BoolGetDatum(enable_profile);
+
+ new_record[Anum_pg_authid_rolprofile - 1] = ObjectIdGetDatum(profileId);
+ new_record[Anum_pg_authid_rolaccountstatus - 1] = Int16GetDatum(account_status);
+ new_record[Anum_pg_authid_rolfailedlogins - 1] = Int32GetDatum(0);
+ new_record_nulls[Anum_pg_authid_rolpasswordsetat - 1] = true;
+ new_record_nulls[Anum_pg_authid_rollockdate - 1] = true;
+ new_record_nulls[Anum_pg_authid_rolpasswordexpire - 1] = true;
/* Set the CREATE EXTERNAL TABLE permissions for this role */
if (exttabcreate || exttabnocreate)
@@ -520,6 +616,10 @@
password);
new_record[Anum_pg_authid_rolpassword - 1] =
CStringGetTextDatum(shadow_pass);
+ new_record[Anum_pg_authid_rolpasswordsetat - 1] =
+ Int64GetDatum(now);
+ new_record_nulls[Anum_pg_authid_rolpasswordsetat - 1] =
+ false;
}
}
else
@@ -631,6 +731,60 @@
new_record[Anum_pg_authid_oid - 1] = ObjectIdGetDatum(roleid);
+ /*
+ * Change accountstatus and lockdate if lock account
+ */
+ if (account_is_lock)
+ {
+ new_record[Anum_pg_authid_rolaccountstatus - 1] =
+ Int16GetDatum(ROLE_ACCOUNT_STATUS_LOCKED);
+ new_record[Anum_pg_authid_rollockdate - 1] =
+ Int64GetDatum(now);
+ new_record_nulls[Anum_pg_authid_rollockdate - 1] =
+ false;
+ }
+
+ if (enable_profile)
+ {
+ new_record[Anum_pg_authid_rolenableprofile - 1] =
+ BoolGetDatum(enable_profile);
+ }
+
+ if (profilename)
+ {
+ /* Scan the pg_profile relation to be certain the profile exists. */
+ Relation pg_profile_rel;
+ TupleDesc pg_profile_dsc;
+ HeapTuple tuple;
+ Form_pg_profile profileform;
+ Oid profileid;
+
+ pg_profile_rel = table_open(ProfileRelationId, AccessShareLock);
+ pg_profile_dsc = RelationGetDescr(pg_profile_rel);
+
+ tuple = SearchSysCache1(PROFILENAME, CStringGetDatum(profilename));
+ if (!HeapTupleIsValid(tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("profile \"%s\" does not exist", profilename)));
+
+ profileform = (Form_pg_profile) GETSTRUCT(tuple);
+ profileid = profileform->oid;
+
+ new_record[Anum_pg_authid_rolprofile - 1] =
+ ObjectIdGetDatum(profileid);
+ new_record_nulls[Anum_pg_authid_rolprofile - 1] =
+ false;
+
+ ReleaseSysCache(tuple);
+ table_close(pg_profile_rel, NoLock);
+
+ /*
+ * Add profile dependency
+ */
+ recordProfileDependency(roleid, profileid);
+ }
+
tuple = heap_form_tuple(pg_authid_dsc, new_record, new_record_nulls);
/*
@@ -747,9 +901,15 @@
HeapTuple tuple,
new_tuple;
Form_pg_authid authform;
+ Relation pg_profile_rel;
+ TupleDesc pg_profile_dsc;
+ Form_pg_profile profileform;
+ Oid profileid;
+ HeapTuple profile_tuple;
ListCell *option;
char *rolename = NULL;
char *password = NULL; /* user password */
+ char *profilename = NULL; /* profile name the role be attached */
int issuper = -1; /* Make the user a superuser? */
int inherit = -1; /* Auto inherit privileges? */
int createrole = -1; /* Can this user create roles? */
@@ -757,6 +917,7 @@
int canlogin = -1; /* Can this user login? */
int isreplication = -1; /* Is this a replication role? */
int connlimit = -1; /* maximum connections allowed */
+ bool enable_profile = false; /* whether user can use password profile */
char *resqueue = NULL; /* resource queue for this role */
char *resgroup = NULL; /* resource group for this role */
List *exttabcreate = NIL; /* external table create privileges being added */
@@ -766,6 +927,8 @@
Datum validUntil_datum; /* same, as timestamptz Datum */
bool validUntil_null;
int bypassrls = -1;
+ int account_is_lock = -1; /* whether the account will be locked/unlocked */
+ TimestampTz now = 0; /* current timestamp with time zone */
DefElem *dpassword = NULL;
DefElem *dresqueue = NULL;
DefElem *dresgroup = NULL;
@@ -779,6 +942,9 @@
DefElem *drolemembers = NULL;
DefElem *dvalidUntil = NULL;
DefElem *dbypassRLS = NULL;
+ DefElem *dprofile = NULL;
+ DefElem *daccountIsLock = NULL;
+ DefElem *denableProfile = NULL;
Oid roleid;
bool bWas_super = false; /* Was the user a superuser? */
int numopts = 0;
@@ -809,6 +975,8 @@
check_rolespec_name(stmt->role,
"Cannot alter reserved roles.");
+ now = GetCurrentTimestamp();
+
/* Extract options from the statement node tree */
foreach(option, stmt->options)
{
@@ -968,6 +1136,30 @@
errmsg("conflicting or redundant options")));
dbypassRLS = defel;
}
+ else if (strcmp(defel->defname, "profile") == 0)
+ {
+ if (dprofile)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ dprofile = defel;
+ }
+ else if (strcmp(defel->defname, "accountislock") == 0)
+ {
+ if (daccountIsLock)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ daccountIsLock = defel;
+ }
+ else if (strcmp(defel->defname, "enableProfile") == 0)
+ {
+ if (denableProfile)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ denableProfile = defel;
+ }
else
elog(ERROR, "option \"%s\" not recognized",
defel->defname);
@@ -1005,6 +1197,54 @@
resgroup = strVal(linitial((List *) dresgroup->arg));
if (dbypassRLS)
bypassrls = intVal(dbypassRLS->arg);
+ if (dprofile)
+ profilename = strVal(dprofile->arg);
+ if (daccountIsLock)
+ account_is_lock = intVal(daccountIsLock->arg);
+ if (denableProfile)
+ enable_profile = intVal(denableProfile->arg) != 0;
+
+ /*
+ * Only the super user has the privileges of profile.
+ */
+ if (dprofile)
+ {
+ if (!enable_password_profile)
+ ereport(ERROR,
+ (errcode(ERRCODE_GP_FEATURE_NOT_CONFIGURED),
+ errmsg("can't ALTER USER ... PROFILE for enable_password_profile is not open")));
+
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to alter role attached to profile")));
+ }
+
+ if (daccountIsLock)
+ {
+ if (!enable_password_profile)
+ ereport(ERROR,
+ (errcode(ERRCODE_GP_FEATURE_NOT_CONFIGURED),
+ errmsg("can't ALTER USER ... ACCOUNT LOCK/UNLOCK for enable_password_profile is not open")));
+
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to alter role account lock/unlock")));
+ }
+
+ if (denableProfile)
+ {
+ if (!enable_password_profile)
+ ereport(ERROR,
+ (errcode(ERRCODE_GP_FEATURE_NOT_CONFIGURED),
+ errmsg("can't ALTER USER ... ENABLE/DISABLE PROFILE for enable_password_profile is not open")));
+
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to alter role enable/disable profile")));
+ }
/*
* Scan the pg_authid relation to be certain the user exists.
@@ -1171,11 +1411,85 @@
new_record_repl[Anum_pg_authid_rolconnlimit - 1] = true;
}
+ if (denableProfile)
+ {
+ new_record[Anum_pg_authid_rolenableprofile - 1] = BoolGetDatum(enable_profile);
+ new_record_repl[Anum_pg_authid_rolenableprofile - 1] = true;
+ }
+
+ /*
+ * Change accountstatus and lockdate when superuser alter user to lock/unlock
+ */
+ if (account_is_lock >= 0)
+ {
+ if (account_is_lock == 0)
+ {
+ new_record[Anum_pg_authid_rolaccountstatus - 1] =
+ Int16GetDatum(ROLE_ACCOUNT_STATUS_OPEN);
+ new_record_repl[Anum_pg_authid_rolaccountstatus - 1] = true;
+
+ new_record[Anum_pg_authid_rolfailedlogins - 1] =
+ Int32GetDatum(0);
+ new_record_repl[Anum_pg_authid_rolfailedlogins - 1] = true;
+
+ new_record_nulls[Anum_pg_authid_rollockdate - 1] = true;
+ new_record_repl[Anum_pg_authid_rollockdate - 1] = true;
+ }
+ else
+ {
+ new_record[Anum_pg_authid_rolaccountstatus - 1] =
+ Int16GetDatum(ROLE_ACCOUNT_STATUS_LOCKED);
+ new_record_repl[Anum_pg_authid_rolaccountstatus - 1] = true;
+
+ new_record[Anum_pg_authid_rollockdate - 1] = Int64GetDatum(now);
+ new_record_repl[Anum_pg_authid_rollockdate - 1] = true;
+ }
+ }
+
/* password */
if (password)
{
char *shadow_pass;
char *logdetail;
+ Datum datum;
+ bool isnull;
+ bool setat_isnull;
+ TimestampTz password_set_at = 0;
+ int32 profile_reuse_max = 0;
+ SysScanDesc password_history_scan;
+ HeapTuple profiletuple;
+
+ pg_profile_rel = table_open(ProfileRelationId, AccessShareLock);
+ pg_profile_dsc = RelationGetDescr(pg_profile_rel);
+
+ datum = SysCacheGetAttr(AUTHNAME, tuple,
+ Anum_pg_authid_rolprofile, &isnull);
+ Assert(!isnull);
+
+ profileid = DatumGetObjectId(datum);
+ profiletuple = SearchSysCache1(PROFILEID, ObjectIdGetDatum(profileid));
+ if (!HeapTupleIsValid(profiletuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("profile \"%d\" does not exist", profileid)));
+
+ /* Get PASSWORD_REUSE_MAX from profile tuple and transform it to normal value */
+ profileform = (Form_pg_profile) GETSTRUCT(profiletuple);
+ profile_reuse_max = tranformProfileValueToNormal(profileform->prfpasswordreusemax,
+ Anum_pg_profile_prfpasswordreusemax);
+
+ ReleaseSysCache(profiletuple);
+
+ if (profile_reuse_max == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PASSWORD),
+ errmsg("can't alter user password for profile's password_reuse_max is zero.")));
+
+ /*
+ * Get shadow password from pg_authid
+ */
+ datum = SysCacheGetAttr(AUTHNAME, tuple,
+ Anum_pg_authid_rolpassword, &isnull);
/* Like in CREATE USER, don't allow an empty password. */
if (password[0] == '\0' ||
@@ -1193,7 +1507,107 @@
new_record[Anum_pg_authid_rolpassword - 1] =
CStringGetTextDatum(shadow_pass);
}
+
+ /*
+ * Disallow to use recently passwords which controlled by
+ * profile's PASSWORD_REUSE_MAX.
+ */
+ if (!isnull)
+ {
+ Relation pg_password_history_rel;
+ Relation pg_password_history_passwordsetat_idx;
+ TupleDesc pg_password_history_dsc;
+ char *history_shadow_pass = NULL;
+ Datum password_history_record[Natts_pg_password_history];
+ bool password_nulls[Natts_pg_password_history];
+ TimestampTz history_password_set_at = 0;
+ HeapTuple password_history_tuple;
+ ScanKeyData skey;
+ int i;
+
+ pg_password_history_rel = table_open(PasswordHistoryRelationId, RowExclusiveLock);
+ pg_password_history_passwordsetat_idx = index_open(PasswordHistoryRolePasswordsetatIndexId, RowExclusiveLock);
+ pg_password_history_dsc = RelationGetDescr(pg_password_history_rel);
+
+ MemSet(password_history_record, 0, sizeof(password_history_record));
+ MemSet(password_nulls, false, sizeof(password_nulls));
+
+ history_shadow_pass = TextDatumGetCString(datum);
+
+ datum = SysCacheGetAttr(AUTHNAME, tuple,
+ Anum_pg_authid_rolpasswordsetat, &setat_isnull);
+ Assert(!setat_isnull);
+ history_password_set_at = DatumGetInt64(datum);
+
+ /*
+ * When current password is not null in pg_authid, we need to record
+ * it into pg_password_history table every time.
+ */
+ password_history_record[Anum_pg_password_history_passhistroleid - 1] =
+ ObjectIdGetDatum(roleid);
+ password_history_record[Anum_pg_password_history_passhistpasswordsetat - 1] =
+ Int64GetDatum(history_password_set_at);
+ password_history_record[Anum_pg_password_history_passhistpassword - 1] =
+ CStringGetTextDatum(history_shadow_pass);
+
+ /* Form the insert tuple */
+ password_history_tuple = heap_form_tuple(pg_password_history_dsc,
+ password_history_record, password_nulls);
+
+ /* Insert new record into the pg_password_history table */
+ CatalogTupleInsert(pg_password_history_rel, password_history_tuple);
+
+ /* Advance command counter so we can see new record */
+ CommandCounterIncrement();
+
+ /* form a scan key */
+ ScanKeyInit(&skey,
+ Anum_pg_password_history_passhistroleid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(roleid));
+
+ /*
+ * Get only recently PASSWORD_REUSE_MAX tuples.
+ */
+ password_history_scan = systable_beginscan_ordered(pg_password_history_rel,
+ pg_password_history_passwordsetat_idx,
+ NULL, 1, &skey);
+ for (i = 0; i < profile_reuse_max; i++)
+ {
+ password_history_tuple = systable_getnext_ordered(password_history_scan, BackwardScanDirection);
+
+ if (!HeapTupleIsValid(password_history_tuple))
+ break;
+
+ datum = heap_getattr(password_history_tuple, Anum_pg_password_history_passhistpassword,
+ pg_password_history_dsc, &isnull);
+ Assert(!isnull);
+ history_shadow_pass = text_to_cstring(DatumGetTextP(datum));
+
+ /*
+ * Use password verify function to check whether password
+ * has been recorded in pg_password_history.
+ */
+ if (plain_crypt_verify(rolename, history_shadow_pass, password, &logdetail) == STATUS_OK)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PASSWORD),
+ errmsg("The new password should not be the same with latest %d history password",
+ profile_reuse_max)));
+ }
+
+ systable_endscan_ordered(password_history_scan);
+
+ index_close(pg_password_history_passwordsetat_idx, NoLock);
+ table_close(pg_password_history_rel, NoLock);
+ }
+
+ password_set_at = now;
+ new_record[Anum_pg_authid_rolpasswordsetat - 1] =
+ Int64GetDatum(password_set_at);
+ new_record_repl[Anum_pg_authid_rolpasswordsetat - 1] = true;
new_record_repl[Anum_pg_authid_rolpassword - 1] = true;
+
+ table_close(pg_profile_rel, NoLock);
}
/* unset password */
@@ -1208,6 +1622,32 @@
new_record_nulls[Anum_pg_authid_rolvaliduntil - 1] = validUntil_null;
new_record_repl[Anum_pg_authid_rolvaliduntil - 1] = true;
+ /* profile name */
+ if (profilename)
+ {
+ /* Scan the pg_profile relation to be certain the profile exists. */
+ pg_profile_rel = table_open(ProfileRelationId, RowExclusiveLock);
+ pg_profile_dsc = RelationGetDescr(pg_profile_rel);
+
+ profile_tuple = SearchSysCache1(PROFILENAME, CStringGetDatum(profilename));
+ if (!HeapTupleIsValid(profile_tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("profile \"%s\" does not exist", profilename)));
+
+ profileform = (Form_pg_profile) GETSTRUCT(profile_tuple);
+ profileid = profileform->oid;
+
+ new_record[Anum_pg_authid_rolprofile - 1] = PointerGetDatum(profileid);
+ new_record_repl[Anum_pg_authid_rolprofile - 1] = true;
+
+ ReleaseSysCache(profile_tuple);
+ table_close(pg_profile_rel, NoLock);
+
+ /* set up dependencies for the new role */
+ changeProfileDependency(roleid, profileid);
+ }
+
/* Set the CREATE EXTERNAL TABLE permissions for this role, if specified in ALTER */
if (exttabcreate || exttabnocreate)
{
@@ -1514,7 +1954,8 @@
DropRole(DropRoleStmt *stmt)
{
Relation pg_authid_rel,
- pg_auth_members_rel;
+ pg_auth_members_rel,
+ pg_password_history_rel;
ListCell *item;
if (!have_createrole_privilege())
@@ -1528,6 +1969,7 @@
*/
pg_authid_rel = table_open(AuthIdRelationId, RowExclusiveLock);
pg_auth_members_rel = table_open(AuthMemRelationId, RowExclusiveLock);
+ pg_password_history_rel = table_open(PasswordHistoryRelationId, RowExclusiveLock);
foreach(item, stmt->roles)
{
@@ -1656,6 +2098,29 @@
systable_endscan(sscan);
/*
+ * Remove all role history passwords from pg_password_history.
+ */
+ ScanKeyInit(&scankey,
+ Anum_pg_password_history_passhistroleid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(roleid));
+
+ sscan = systable_beginscan(pg_password_history_rel, PasswordHistoryRolePasswordIndexId,
+ true, NULL, 1, &scankey);
+
+ while (HeapTupleIsValid(tmp_tuple = systable_getnext(sscan)))
+ {
+ CatalogTupleDelete(pg_password_history_rel, &tmp_tuple->t_self);
+ }
+
+ systable_endscan(sscan);
+
+ /*
+ * Delete shared dependency references related to this role object.
+ */
+ deleteSharedDependencyRecordsFor(AuthIdRelationId, roleid, 0);
+
+ /*
* Remove any time constraints on this role.
*/
DelRoleDenials(role, roleid, NIL);
@@ -1690,6 +2155,7 @@
/*
* Now we can clean up; but keep locks until commit.
*/
+ table_close(pg_password_history_rel, NoLock);
table_close(pg_auth_members_rel, NoLock);
table_close(pg_authid_rel, NoLock);
diff --git a/src/backend/libpq/auth.c b/src/backend/libpq/auth.c
index ae465b8..7937b05 100644
--- a/src/backend/libpq/auth.c
+++ b/src/backend/libpq/auth.c
@@ -45,11 +45,14 @@
#include "access/genam.h"
#include "access/heapam.h"
#include "catalog/indexing.h"
+#include "catalog/objectaccess.h"
#include "catalog/pg_authid.h"
#include "catalog/pg_auth_time_constraint.h"
+#include "catalog/pg_profile.h"
#include "cdb/cdbendpoint.h"
#include "cdb/cdbvars.h"
#include "pgtime.h"
+#include "postmaster/loginmonitor.h"
#include "postmaster/postmaster.h"
#include "utils/acl.h"
#include "utils/builtins.h"
@@ -286,6 +289,12 @@
const char *errstr;
char *cdetail;
int errcode_return = ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION;
+ Relation pg_authid_rel;
+ TupleDesc pg_authid_dsc;
+ HeapTuple auth_tuple;
+ Form_pg_authid authform;
+ bool account_status_isnull;
+ int16 account_status;
/*
* If we failed due to EOF from client, just quit; there's no point in
@@ -364,6 +373,32 @@
else
logdetail = cdetail;
+ /* Send signal to login monitor */
+ pg_authid_rel = table_open(AuthIdRelationId, AccessShareLock);
+ pg_authid_dsc = RelationGetDescr(pg_authid_rel);
+
+ auth_tuple = SearchSysCache1(AUTHNAME,
+ CStringGetDatum(port->user_name));
+
+ if (!HeapTupleIsValid(auth_tuple))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("user \"%s\" does not exist",
+ port->user_name)));
+ }
+
+ /* Send signal only when user is not a super user and
+ * user is enable to use profile.
+ */
+ authform = (Form_pg_authid) GETSTRUCT(auth_tuple);
+ account_status = DatumGetInt16(SysCacheGetAttr(AUTHNAME, auth_tuple,
+ Anum_pg_authid_rolaccountstatus, &account_status_isnull));
+ if (enable_password_profile && !authform->rolsuper && authform->rolenableprofile &&
+ (account_status != ROLE_ACCOUNT_STATUS_LOCKED ||
+ account_status != ROLE_ACCOUNT_STATUS_LOCKED_TIMED))
+ SendLoginFailedSignal(port->user_name);
+
ereport(FATAL,
(errcode(errcode_return),
errmsg(errstr, port->user_name),
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 755c64e..bfccb56 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -5155,6 +5155,17 @@
return newnode;
}
+static CreateProfileStmt *
+_copyCreateProfileStmt(const CreateProfileStmt *from)
+{
+ CreateProfileStmt *newnode = makeNode(CreateProfileStmt);
+
+ COPY_STRING_FIELD(profile_name);
+ COPY_NODE_FIELD(options);
+
+ return newnode;
+}
+
static DenyLoginInterval *
_copyDenyLoginInterval(const DenyLoginInterval *from)
{
@@ -5189,6 +5200,17 @@
return newnode;
}
+static AlterProfileStmt *
+_copyAlterProfileStmt(const AlterProfileStmt *from)
+{
+ AlterProfileStmt *newnode = makeNode(AlterProfileStmt);
+
+ COPY_STRING_FIELD(profile_name);
+ COPY_NODE_FIELD(options);
+
+ return newnode;
+}
+
static AlterRoleSetStmt *
_copyAlterRoleSetStmt(const AlterRoleSetStmt *from)
{
@@ -5212,6 +5234,17 @@
return newnode;
}
+static DropProfileStmt *
+_copyDropProfileStmt(const DropProfileStmt *from)
+{
+ DropProfileStmt *newnode = makeNode(DropProfileStmt);
+
+ COPY_NODE_FIELD(profiles);
+ COPY_SCALAR_FIELD(missing_ok);
+
+ return newnode;
+}
+
static LockStmt *
_copyLockStmt(const LockStmt *from)
{
@@ -6753,6 +6786,15 @@
case T_DropRoleStmt:
retval = _copyDropRoleStmt(from);
break;
+ case T_CreateProfileStmt:
+ retval = _copyCreateProfileStmt(from);
+ break;
+ case T_AlterProfileStmt:
+ retval = _copyAlterProfileStmt(from);
+ break;
+ case T_DropProfileStmt:
+ retval = _copyDropProfileStmt(from);
+ break;
case T_LockStmt:
retval = _copyLockStmt(from);
break;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 8568c69..3bb25eb 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2287,6 +2287,15 @@
}
static bool
+_equalCreateProfileStmt(const CreateProfileStmt *a, const CreateProfileStmt *b)
+{
+ COMPARE_STRING_FIELD(profile_name);
+ COMPARE_NODE_FIELD(options);
+
+ return true;
+}
+
+static bool
_equalDenyLoginInterval(const DenyLoginInterval *a, const DenyLoginInterval *b)
{
COMPARE_NODE_FIELD(start);
@@ -2325,6 +2334,15 @@
}
static bool
+_equalAlterProfileStmt(const AlterProfileStmt *a, const AlterProfileStmt *b)
+{
+ COMPARE_STRING_FIELD(profile_name);
+ COMPARE_NODE_FIELD(options);
+
+ return true;
+}
+
+static bool
_equalDropRoleStmt(const DropRoleStmt *a, const DropRoleStmt *b)
{
COMPARE_NODE_FIELD(roles);
@@ -2334,6 +2352,15 @@
}
static bool
+_equalDropProfileStmt(const DropProfileStmt *a, const DropProfileStmt *b)
+{
+ COMPARE_NODE_FIELD(profiles);
+ COMPARE_SCALAR_FIELD(missing_ok);
+
+ return true;
+}
+
+static bool
_equalLockStmt(const LockStmt *a, const LockStmt *b)
{
COMPARE_NODE_FIELD(relations);
@@ -3948,6 +3975,15 @@
case T_DropRoleStmt:
retval = _equalDropRoleStmt(a, b);
break;
+ case T_CreateProfileStmt:
+ retval = _equalCreateProfileStmt(a, b);
+ break;
+ case T_AlterProfileStmt:
+ retval = _equalAlterProfileStmt(a, b);
+ break;
+ case T_DropProfileStmt:
+ retval = _equalDropProfileStmt(a, b);
+ break;
case T_LockStmt:
retval = _equalLockStmt(a, b);
break;
diff --git a/src/backend/nodes/outfast.c b/src/backend/nodes/outfast.c
index 706d2f8..a8ab42e 100644
--- a/src/backend/nodes/outfast.c
+++ b/src/backend/nodes/outfast.c
@@ -1341,6 +1341,17 @@
case T_AlterRoleSetStmt:
_outAlterRoleSetStmt(str, obj);
break;
+
+ case T_CreateProfileStmt:
+ _outCreateProfileStmt(str, obj);
+ break;
+ case T_AlterProfileStmt:
+ _outAlterProfileStmt(str, obj);
+ break;
+ case T_DropProfileStmt:
+ _outDropProfileStmt(str, obj);
+ break;
+
case T_AlterSystemStmt:
_outAlterSystemStmt(str, obj);
break;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 43dd8d7..cfff625 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -3276,6 +3276,10 @@
case T_AlterRoleSetStmt:
case T_DropRoleStmt:
+ case T_CreateProfileStmt:
+ case T_AlterProfileStmt:
+ case T_DropProfileStmt:
+
case T_CreateSchemaStmt:
case T_CreatePLangStmt:
case T_AlterOwnerStmt:
@@ -4758,6 +4762,16 @@
_outAlterRoleSetStmt(str, obj);
break;
+ case T_CreateProfileStmt:
+ _outCreateProfileStmt(str, obj);
+ break;
+ case T_AlterProfileStmt:
+ _outAlterProfileStmt(str, obj);
+ break;
+ case T_DropProfileStmt:
+ _outDropProfileStmt(str, obj);
+ break;
+
case T_AlterSystemStmt:
_outAlterSystemStmt(str, obj);
break;
diff --git a/src/backend/nodes/outfuncs_common.c b/src/backend/nodes/outfuncs_common.c
index 9de97f1..f777d6d 100644
--- a/src/backend/nodes/outfuncs_common.c
+++ b/src/backend/nodes/outfuncs_common.c
@@ -837,6 +837,15 @@
}
static void
+_outCreateProfileStmt(StringInfo str, const CreateProfileStmt *node)
+{
+ WRITE_NODE_TYPE("CREATEPROFILESTMT");
+
+ WRITE_STRING_FIELD(profile_name);
+ WRITE_NODE_FIELD(options);
+}
+
+static void
_outDenyLoginInterval(StringInfo str, const DenyLoginInterval *node)
{
WRITE_NODE_TYPE("DENYLOGININTERVAL");
@@ -863,6 +872,15 @@
WRITE_BOOL_FIELD(missing_ok);
}
+static void
+_outDropProfileStmt(StringInfo str, const DropProfileStmt *node)
+{
+ WRITE_NODE_TYPE("DROPPROFILESTMT");
+
+ WRITE_NODE_FIELD(profiles);
+ WRITE_BOOL_FIELD(missing_ok);
+}
+
static void
_outAlterObjectSchemaStmt(StringInfo str, const AlterObjectSchemaStmt *node)
{
@@ -905,6 +923,15 @@
WRITE_INT_FIELD(action);
}
+static void
+_outAlterProfileStmt(StringInfo str, const AlterProfileStmt *node)
+{
+ WRITE_NODE_TYPE("ALTERPROFILESTMT");
+
+ WRITE_STRING_FIELD(profile_name);
+ WRITE_NODE_FIELD(options);
+}
+
static void
_outAlterSystemStmt(StringInfo str, const AlterSystemStmt *node)
{
diff --git a/src/backend/nodes/readfast.c b/src/backend/nodes/readfast.c
index 889ae04..d1efd23 100644
--- a/src/backend/nodes/readfast.c
+++ b/src/backend/nodes/readfast.c
@@ -2247,6 +2247,16 @@
return_value = _readAlterRoleSetStmt();
break;
+ case T_CreateProfileStmt:
+ return_value = _readCreateProfileStmt();
+ break;
+ case T_AlterProfileStmt:
+ return_value = _readAlterProfileStmt();
+ break;
+ case T_DropProfileStmt:
+ return_value = _readDropProfileStmt();
+ break;
+
case T_AlterObjectDependsStmt:
return_value = _readAlterObjectDependsStmt();
break;
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 028da20..7e8ead1 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -3124,6 +3124,8 @@
return_value = _readAlterSystemStmt();
else if (MATCHX("ALTERROLESTMT"))
return_value = _readAlterRoleStmt();
+ else if (MATCHX("ALTERPROFILESTMT"))
+ return_value = _readAlterProfileStmt();
else if (MATCHX("ALTERSEQSTMT"))
return_value = _readAlterSeqStmt();
else if (MATCHX("ALTERTABLECMD"))
@@ -3192,6 +3194,8 @@
return_value = _readCreatePolicyStmt();
else if (MATCHX("CREATEROLESTMT"))
return_value = _readCreateRoleStmt();
+ else if (MATCHX("CREATEPROFILESTMT"))
+ return_value = _readCreateProfileStmt();
else if (MATCHX("CREATESCHEMASTMT"))
return_value = _readCreateSchemaStmt();
else if (MATCHX("CREATESEQSTMT"))
@@ -3212,6 +3216,8 @@
return_value = _readDropdbStmt();
else if (MATCHX("DROPROLESTMT"))
return_value = _readDropRoleStmt();
+ else if (MATCHX("DROPPROFILESTMT"))
+ return_value = _readDropProfileStmt();
else if (MATCHX("DROPSTMT"))
return_value = _readDropStmt();
else if (MATCHX("DISTRIBUTIONKEYELEM"))
diff --git a/src/backend/nodes/readfuncs_common.c b/src/backend/nodes/readfuncs_common.c
index 43e02b4..f236871 100644
--- a/src/backend/nodes/readfuncs_common.c
+++ b/src/backend/nodes/readfuncs_common.c
@@ -389,6 +389,17 @@
READ_DONE();
}
+static AlterProfileStmt *
+_readAlterProfileStmt(void)
+{
+ READ_LOCALS(AlterProfileStmt);
+
+ READ_STRING_FIELD(profile_name);
+ READ_NODE_FIELD(options);
+
+ READ_DONE();
+}
+
static AlterSeqStmt *
_readAlterSeqStmt(void)
{
@@ -870,6 +881,17 @@
READ_DONE();
}
+static CreateProfileStmt *
+_readCreateProfileStmt(void)
+{
+ READ_LOCALS(CreateProfileStmt);
+
+ READ_STRING_FIELD(profile_name);
+ READ_NODE_FIELD(options);
+
+ READ_DONE();
+}
+
static CreateSchemaStmt *
_readCreateSchemaStmt(void)
{
@@ -1031,6 +1053,17 @@
READ_DONE();
}
+static DropProfileStmt *
+_readDropProfileStmt(void)
+{
+ READ_LOCALS(DropProfileStmt);
+
+ READ_NODE_FIELD(profiles);
+ READ_BOOL_FIELD(missing_ok);
+
+ READ_DONE();
+}
+
static DropStmt *
_readDropStmt(void)
{
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 45e22f7..b6ac3a5 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -318,10 +318,10 @@
RetrieveStmt CreateTaskStmt AlterTaskStmt DropTaskStmt
/* GPDB-specific commands */
-%type <node> AlterQueueStmt AlterResourceGroupStmt
+%type <node> AlterProfileStmt AlterQueueStmt AlterResourceGroupStmt
CreateExternalStmt
- CreateQueueStmt CreateResourceGroupStmt
- DropQueueStmt DropResourceGroupStmt
+ CreateProfileStmt CreateQueueStmt CreateResourceGroupStmt
+ DropProfileStmt DropQueueStmt DropResourceGroupStmt
ExtTypedesc OptSingleRowErrorHandling ExtSingleRowErrorHandling
%type <node> deny_login_role deny_interval deny_point deny_day_specifier
@@ -373,8 +373,10 @@
%type <ival> opt_nowait_or_skip
%type <list> OptRoleList AlterOptRoleList
+%type <list> OptProfileList
%type <defelt> CreateOptRoleElem AlterOptRoleElem
%type <defelt> AlterOnlyOptRoleElem
+%type <defelt> OptProfileElem
%type <str> opt_type
%type <str> foreign_server_version opt_foreign_server_version
@@ -831,7 +833,7 @@
/* GPDB-added keywords, in alphabetical order */
%token <keyword>
- ACTIVE
+ ACCOUNT ACTIVE
CONTAINS COORDINATOR CPUSET CPU_RATE_LIMIT
@@ -841,7 +843,7 @@
ERRORS EVERY EXCHANGE EXPAND
- FIELDS FILL FORMAT
+ FAILED_LOGIN_ATTEMPTS FIELDS FILL FORMAT
FULLSCAN
@@ -859,7 +861,7 @@
ORDERED OVERCOMMIT
- PARTITIONS PERCENT PERSISTENTLY PROTOCOL
+ PARTITIONS PASSWORD_LOCK_TIME PASSWORD_REUSE_MAX PERCENT PERSISTENTLY PROFILE PROTOCOL
QUEUE
@@ -872,6 +874,8 @@
THRESHOLD
+ UNLOCK_P
+
VALIDATION
WAREHOUSE
@@ -1191,6 +1195,7 @@
%nonassoc UNCOMMITTED
%nonassoc UNENCRYPTED
%nonassoc UNLISTEN
+ %nonassoc UNLOCK_P
%nonassoc UNTIL
%nonassoc UPDATE
%nonassoc VACUUM
@@ -1386,6 +1391,7 @@
| AlterTaskStmt
| AlterTypeStmt
| AlterPolicyStmt
+ | AlterProfileStmt
| AlterQueueStmt
| AlterResourceGroupStmt
| AlterSeqStmt
@@ -1429,6 +1435,7 @@
| CreateWarehouseStmt
| AlterOpFamilyStmt
| CreatePolicyStmt
+ | CreateProfileStmt
| CreatePLangStmt
| CreateQueueStmt
| CreateResourceGroupStmt
@@ -1456,6 +1463,7 @@
| DropOpClassStmt
| DropOpFamilyStmt
| DropOwnedStmt
+ | DropProfileStmt
| DropQueueStmt
| DropResourceGroupStmt
| DropStmt
@@ -1853,6 +1861,26 @@
{
$$ = makeDefElem("deny", (Node *) $1, @1);
}
+ | PROFILE name
+ {
+ $$ = makeDefElem("profile", (Node *)makeString($2), @1);
+ }
+ | ACCOUNT LOCK_P
+ {
+ $$ = makeDefElem("accountislock", (Node *) makeInteger(true), @1);
+ }
+ | ACCOUNT UNLOCK_P
+ {
+ $$ = makeDefElem("accountislock", (Node*) makeInteger(false), @1);
+ }
+ | ENABLE_P PROFILE
+ {
+ $$ = makeDefElem("enableProfile", (Node *) makeInteger(true), @1);
+ }
+ | DISABLE_P PROFILE
+ {
+ $$ = makeDefElem("enableProfile", (Node *) makeInteger(false), @1);
+ }
| IDENT
{
/*
@@ -2114,6 +2142,99 @@
/*****************************************************************************
*
+ * Create a new Postgres DBMS Profile
+ *
+ *****************************************************************************/
+
+CreateProfileStmt:
+ CREATE PROFILE name
+ {
+ CreateProfileStmt *n = makeNode(CreateProfileStmt);
+ n->profile_name = $3;
+ $$ = (Node *)n;
+ }
+ | CREATE PROFILE name LIMIT OptProfileList
+ {
+ CreateProfileStmt *n = makeNode(CreateProfileStmt);
+ n->profile_name = $3;
+ n->options = $5;
+ $$ = (Node *)n;
+ }
+ ;
+
+/*
+ * Options for CREATE PROFILE and ALTER PROFILE.
+ */
+OptProfileList:
+ OptProfileList OptProfileElem { $$ = lappend($1, $2); }
+ | /* EMPTY */ { $$ = NIL; }
+ ;
+
+OptProfileElem:
+ FAILED_LOGIN_ATTEMPTS SignedIconst
+ {
+ $$ = makeDefElem("failed_login_attempts",
+ (Node *)makeInteger($2), @1);
+ }
+ | PASSWORD_LOCK_TIME SignedIconst
+ {
+ $$ = makeDefElem("password_lock_time",
+ (Node *)makeInteger($2), @1);
+ }
+ | PASSWORD_REUSE_MAX SignedIconst
+ {
+ $$ = makeDefElem("password_reuse_max",
+ (Node *)makeInteger($2), @1);
+ }
+ ;
+
+
+/*****************************************************************************
+ *
+ * Alter a postgresql DBMS profile
+ *
+ *****************************************************************************/
+
+AlterProfileStmt:
+ ALTER PROFILE name LIMIT OptProfileList
+ {
+ AlterProfileStmt *n = makeNode(AlterProfileStmt);
+ n->profile_name = $3;
+ n->options = $5;
+ $$ = (Node *)n;
+ }
+ ;
+
+
+/*****************************************************************************
+ *
+ * Drop a postgresql DBMS profile
+ *
+ * XXX Ideally this would have CASCADE/RESTRICT options, but a profile
+ * might be attached by users in multiple databases, using CASCADE will drop
+ * users meanwhile which is unreasonable. So we always behave as RESTRICT.
+ *****************************************************************************/
+
+DropProfileStmt:
+ DROP PROFILE name_list
+ {
+ DropProfileStmt *n = makeNode(DropProfileStmt);
+ n->profiles = $3;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | DROP PROFILE IF_P EXISTS name_list
+ {
+ DropProfileStmt *n = makeNode(DropProfileStmt);
+ n->profiles = $5;
+ n->missing_ok = true;
+ $$ = (Node *)n;
+ }
+ ;
+
+
+/*****************************************************************************
+ *
* Create a postgresql group (role without login ability)
*
*****************************************************************************/
@@ -8969,6 +9090,7 @@
drop_type_name { $$ = $1; }
| DATABASE { $$ = OBJECT_DATABASE; }
| ROLE { $$ = OBJECT_ROLE; }
+ | PROFILE { $$ = OBJECT_PROFILE; }
| SUBSCRIPTION { $$ = OBJECT_SUBSCRIPTION; }
| TABLESPACE { $$ = OBJECT_TABLESPACE; }
| RESOURCE QUEUE { $$ = OBJECT_RESQUEUE; }
@@ -11698,6 +11820,15 @@
n->missing_ok = false;
$$ = (Node *)n;
}
+ | ALTER PROFILE name RENAME TO name
+ {
+ RenameStmt *n = makeNode(RenameStmt);
+ n->renameType = OBJECT_PROFILE;
+ n->subname = $3;
+ n->newname = $6;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
;
opt_column: COLUMN
@@ -18501,6 +18632,7 @@
ABORT_P
| ABSOLUTE_P
| ACCESS
+ | ACCOUNT
| ACTION
| ACTIVE
| ADD_P
@@ -18601,6 +18733,7 @@
| EXPRESSION
| EXTENSION
| EXTERNAL
+ | FAILED_LOGIN_ATTEMPTS
| FAMILY
| FIELDS
| FILL
@@ -18717,6 +18850,8 @@
| PARTITIONS
| PASSING
| PASSWORD
+ | PASSWORD_LOCK_TIME
+ | PASSWORD_REUSE_MAX
| PERCENT
| PERSISTENTLY
| PLANS
@@ -18729,6 +18864,7 @@
| PROCEDURAL
| PROCEDURE
| PROCEDURES
+ | PROFILE
| PROGRAM
| PROTOCOL
| PUBLICATION
@@ -18831,6 +18967,7 @@
| UNENCRYPTED
| UNKNOWN
| UNLISTEN
+ | UNLOCK_P
| UNLOGGED
| UNTIL
| UPDATE
@@ -19378,6 +19515,7 @@
ABORT_P
| ABSOLUTE_P
| ACCESS
+ | ACCOUNT
| ACTION
| ACTIVE
| ADD_P
@@ -19522,6 +19660,7 @@
| EXTENSION
| EXTERNAL
| EXTRACT
+ | FAILED_LOGIN_ATTEMPTS
| FALSE_P
| FAMILY
| FIELDS
@@ -19675,6 +19814,8 @@
| PARTITIONS
| PASSING
| PASSWORD
+ | PASSWORD_LOCK_TIME
+ | PASSWORD_REUSE_MAX
| PERCENT
| PERSISTENTLY
| PLACING
@@ -19692,6 +19833,7 @@
| PROCEDURAL
| PROCEDURE
| PROCEDURES
+ | PROFILE
| PROGRAM
| PROTOCOL
| PUBLICATION
@@ -19816,6 +19958,7 @@
| UNIQUE
| UNKNOWN
| UNLISTEN
+ | UNLOCK_P
| UNLOGGED
| UNTIL
| UPDATE
diff --git a/src/backend/postmaster/Makefile b/src/backend/postmaster/Makefile
index 917c605..5ba0602 100644
--- a/src/backend/postmaster/Makefile
+++ b/src/backend/postmaster/Makefile
@@ -25,7 +25,8 @@
postmaster.o \
startup.o \
syslogger.o \
- walwriter.o
+ walwriter.o \
+ loginmonitor.o
OBJS += backoff.o autostats.o
diff --git a/src/backend/postmaster/loginmonitor.c b/src/backend/postmaster/loginmonitor.c
new file mode 100644
index 0000000..d67fcc8
--- /dev/null
+++ b/src/backend/postmaster/loginmonitor.c
@@ -0,0 +1,893 @@
+/*-------------------------------------------------------------------------
+ *
+ * loginmonitor.c
+ *
+ * PostgreSQL Integrated Login Monitor Daemon
+ *
+ * Like autovacuum, the login monitor is structured in two different
+ * kinds of processes: the login monitor launcher and the login monitor
+ * worker. The launcher is an always-running process, started by the
+ * postmaster. It is mainly used to process user's failed login
+ * authentication. It will always running in loop waiting for failed
+ * login signal. The launcher will signal postmaster to fork the worker
+ * process when it receives failed authentication signal from the postgres
+ * process. The worker process is the process which doing the actual
+ * working; as the worker process is only forked when authenticate failed,
+ * one worker process is enough to finish that. It will be forked from
+ * the postmaster as needed. Like normal postgres process, login monitor
+ * worker is equipped with locks, transactions, read/write catalog table
+ * and other functionalities.
+ *
+ * The login monitor launcher cannot start the worker process by itself,
+ * as doing so would cause robustness issues (namely, failure to shut
+ * them down on exceptional conditions, and also, since the launcher is
+ * connected to shared memory and is thus subject to corruption there,
+ * it is not as robust as the postmaster). So it leaves that task to the
+ * postmaster.
+ *
+ * There is a login monitor shared memory area, where the launcher
+ * stores information about its pid and latch. What's more, the worker
+ * stores the current login user's name and it's latch to shared memory.
+ * There is also a flag between launcher and worker to indicate whether
+ * the failed login authentication signal has been processed.
+ *
+ * When there is a failed login authentication, the postgres process will
+ * send the signal to the postmaster and wait the shared memory latch.
+ * The launcher received signal from postgres process, it will set the
+ * flag to true to indicate the signal is processing. And it will resend
+ * a signal to postmaster and wait the latch of lm_latch in shared memory.
+ * After receiving the signal from the launcher, the postmaster will fork
+ * the worker to do the actual working. After the worker finishes work,
+ * it will notify postgres process and the launcher by setting the latch
+ * and the lm_latch in shared memory. Moreover, it will reset the flag to
+ * false to indicate the work is finished.
+ *
+ * Only when the user is able to use profile, process will send signal to
+ * login monitor and wait for the completion of worker process. Otherwise,
+ * the failed login authentication of the user will be ignored and doesn't
+ * need to send signal to the launcher.
+ *
+ * Copyright (c) 2023, Cloudberry Database, HashData Technology Limited.
+ *
+ *
+ * IDENTIFICATION
+ * src/backend/postmaster/loginmonitor.c
+ */
+
+#include "postgres.h"
+
+#include "access/heapam.h"
+#include "access/htup_details.h"
+#include "access/reloptions.h"
+#include "catalog/objectaccess.h"
+#include "catalog/pg_authid.h"
+#include "catalog/pg_profile.h"
+#include "libpq/pqsignal.h"
+#include "pgstat.h"
+#include "postmaster/fork_process.h"
+#include "postmaster/interrupt.h"
+#include "postmaster/loginmonitor.h"
+#include "postmaster/postmaster.h"
+#include "storage/ipc.h"
+#include "storage/pmsignal.h"
+#include "storage/proc.h"
+#include "storage/smgr.h"
+#include "tcop/tcopprot.h"
+#include "utils/ps_status.h"
+#include "utils/memutils.h"
+#include "utils/syscache.h"
+#include "utils/timeout.h"
+
+#include "cdb/cdbvars.h"
+
+/* Memory context for login monitor rewrites catalogs */
+static MemoryContext LoginMonitorMemCxt;
+static pid_t LoginMonitorPID;
+
+/*
+ * The main Login Monitor shmem struct. On shared memory we store it,
+ * which will be set by failed proc and reset by login monitor
+ * post-processing. This struct keeps:
+ *
+ * lm_pid Login Monitor Launcher Process pid
+ * lm_latch Login Monitor Launcher Latch pointer
+ * curr_user_name current failed user name
+ * latch pointer of current failed proc's latch
+ * login_failed_requested whether current is handling a failed login
+ *
+ * curr_user_name, latch and login_failed_requested are protected by LWLock LoginFailedSharedMemoryLock
+ */
+typedef struct {
+ pid_t lm_pid;
+ Latch *lm_latch;
+ char curr_user_name[NAMEDATALEN];
+ Latch *latch;
+ sig_atomic_t login_failed_requested;
+} LoginMonitorShmemStruct;
+
+static LoginMonitorShmemStruct *LoginMonitorShmem;
+
+/* Flags to tell if we are in an login monitor process */
+static bool am_login_monitor_launcher = false;
+static bool am_login_monitor_worker = false;
+
+static void LoginMonitorShutdown(void);
+
+static void HandleLoginMonitorInterrupts(void);
+
+static void LoginMonitorShmemReset(void);
+
+#ifdef EXEC_BACKEND
+static pid_t lmlauncher_forkexec(void);
+static pid_t lmworker_forkexec(void);
+#endif
+
+NON_EXEC_STATIC void LoginMonitorLauncherMain(int argc, char *argv[]);
+
+NON_EXEC_STATIC void LoginMonitorWorkerMain(int argc, char *argv[]) pg_attribute_noreturn();
+
+static void record_failed_login(void);
+
+
+/********************************************************************
+ * LOGIN MONITOR CODE
+ ********************************************************************/
+
+
+#ifdef EXEC_BACKEND
+/*
+ * forkexec routine for the login monitor launcher process.
+ *
+ * Format up the arglist, then fork and exec.
+ */
+static pid_t
+lmlauncher_forkexec(void)
+{
+ char *lm[10];
+ int lc = 0;
+
+ lm[lc++] = "postgres";
+ lm[lc++] = "--forkloginmonitor";
+ lm[lc++] = NULL;
+ lm[lc] = NULL;
+
+ Assert(lc < lengthof(lm));
+
+ return postmaster_forkexec(lc, lm);
+}
+#endif
+
+/*
+ * Main entry point for login monitor launcher process, to be called from
+ * the postmaster.
+ */
+int
+StartLoginMonitorLauncher(void) {
+#ifdef EXEC_BACKEND
+ switch ((LoginMonitorPID = lmlauncher_forkexec()))
+#else
+ switch ((LoginMonitorPID = fork_process()))
+#endif
+ {
+ case -1:
+ ereport(LOG,
+ (errmsg("could not fork login monitor process: %m")));
+ return 0;
+
+#ifndef EXEC_BACKEND
+ case 0:
+ /* in postgresmaster child ... */
+ InitPostmasterChild();
+
+ /* Close the postmaster's sockets */
+ ClosePostmasterPorts(false);
+
+ LoginMonitorLauncherMain(0, NULL);
+ break;
+#endif
+ default:
+ return (int) LoginMonitorPID;
+ }
+
+ /* shouldn't get here */
+ return 0;
+}
+
+/*
+ * Main loop for the login monitor process.
+ */
+NON_EXEC_STATIC void
+LoginMonitorLauncherMain(int argc, char *argv[]) {
+ sigjmp_buf local_sigjmp_buf;
+
+ am_login_monitor_launcher = true;
+
+ MyBackendType = B_LOGIN_MONITOR;
+ init_ps_display(NULL);
+
+ ereport(DEBUG1,
+ (errmsg_internal("login monitor launcher started")));
+
+ SetProcessingMode(InitProcessing);
+
+ /*
+ * Set up signal handler. We operate on databases much like a regular
+ * backend, so we use the same signal handling. See equivalent code in
+ * tcop/postgres.c.
+ */
+ pqsignal(SIGHUP, SignalHandlerForConfigReload);
+ pqsignal(SIGINT, StatementCancelHandler);
+ pqsignal(SIGTERM, SignalHandlerForShutdownRequest);
+ /* SIGQUIT handler was already set up by InitPostmasterChild */
+
+ InitializeTimeouts(); /* established SIGALRM handler */
+
+ pqsignal(SIGPIPE, SIG_IGN);
+ pqsignal(SIGUSR1, procsignal_sigusr1_handler);
+ pqsignal(SIGUSR2, SIG_IGN);
+ pqsignal(SIGFPE, FloatExceptionHandler);
+ pqsignal(SIGCHLD, SIG_DFL);
+
+ /* Early initialization */
+ BaseInit();
+
+ /*
+ * Create a per-backend PGPROC struct in shared memory, except in the
+ * EXEC_BACKEND case where this was done in SubPostmasterMain. We must do
+ * this before we can use LWLocks (and in the EXEC_BACKEND case we already
+ * had to do some stuff with LWLocks).
+ */
+#ifndef EXEC_BACKEND
+ InitProcess();
+#endif
+
+ InitPostgres(NULL, InvalidOid, NULL, InvalidOid, NULL, false);
+
+ SetProcessingMode(NormalProcessing);
+
+ /*
+ * Create a memory context that we will do all our work in. We do this so
+ * that we can reset the context during error recovery and thereby avoid
+ * possible memory leaks.
+ */
+ LoginMonitorMemCxt = AllocSetContextCreate(TopMemoryContext,
+ "Login Monitor",
+ ALLOCSET_DEFAULT_SIZES);
+ MemoryContextSwitchTo(LoginMonitorMemCxt);
+
+ /*
+ * If an exception is encountered, processing resumes here.
+ *
+ * This code is a stripped down version of PostgresMain error recovery.
+ *
+ * Note that we use sigsetjmp(..., 1), so that the prevailing signal mask
+ * (to wit, BlockSig) will be restored when longjmp'ing to here. Thus,
+ * signals other than SIGQUIT will be blocked until we complete error
+ * recovery. It might seem that this policy makes the HOLD_INTERRUPTS()
+ * call redundant, but it is not since InterruptPending might be set
+ * already.
+ */
+ if (sigsetjmp(local_sigjmp_buf, 1) != 0) {
+ /* since not using PG_TRY, must reset error stack by hand */
+ error_context_stack = NULL;
+
+ /* Prevents interrupts while cleaning up */
+ HOLD_INTERRUPTS();
+
+ /* Forget any pending QueryCancel or timeout request */
+ disable_all_timeouts(false);
+ QueryCancelPending = false; /* second to avoid race condition */
+
+ /* Report the error to the server log */
+ EmitErrorReport();
+
+ /* Abort the current transaction in order to recover */
+ AbortCurrentTransaction();
+
+ /*
+ * Release any other resources, for the case where we were not in a
+ * transaction.
+ */
+ LWLockReleaseAll();
+ AbortBufferIO();
+ UnlockBuffers();
+ /* this is probably dead code, but let's be safe: */
+ if (AuxProcessResourceOwner)
+ ReleaseAuxProcessResources(false);
+ AtEOXact_Buffers(false);
+ AtEOXact_SMgr();
+ AtEOXact_Files(false);
+ AtEOXact_HashTables(false);
+
+ /*
+ * Now return to normal top-level context and clear ErrorContext for
+ * next time.
+ */
+ MemoryContextSwitchTo(LoginMonitorMemCxt);
+ FlushErrorState();
+
+ /* Flush any leaked data in the top-level context */
+ MemoryContextResetAndDeleteChildren(LoginMonitorMemCxt);
+
+ /* Now we can allow interrupts again */
+ RESUME_INTERRUPTS();
+
+ /* reset and notify process by latch */
+ SetLatch(LoginMonitorShmem->latch);
+ ResetLatch(LoginMonitorShmem->lm_latch);
+ LoginMonitorShmemReset();
+
+ /* if in shutdown mode, no need for anything further; just go away */
+ if (ShutdownRequestPending)
+ LoginMonitorShutdown();
+
+ /*
+ * Sleep at least 1 milli second after any error. We don't want to be
+ * filling the error logs as fast as we can.
+ */
+ pg_usleep(1000L);
+ }
+
+ /* We can now handle ereport(ERROR) */
+ PG_exception_stack = &local_sigjmp_buf;
+
+ /* must unblock signals before calling rebuild */
+ PG_SETMASK(&UnBlockSig);
+
+ /*
+ * Set always-secure search path.
+ */
+ SetConfigOption("search_path", "", PGC_SUSET, PGC_S_OVERRIDE);
+
+ /*
+ * Force zero_damaged_pages OFF in the login monitor process, even if it is set`
+ * in postgresql.conf. We don't really want such a dangerous option being applied
+ * non-interactively.
+ */
+ SetConfigOption("zero_damaged_pages", "false", PGC_SUSET, PGC_S_OVERRIDE);
+
+ /*
+ * Force settable timeouts off to avoid letting these settings prevent
+ * regular maintenance from being executed.
+ */
+ SetConfigOption("statement_timeout", "0", PGC_SUSET, PGC_S_OVERRIDE);
+ SetConfigOption("lock_timeout", "0", PGC_SUSET, PGC_S_OVERRIDE);
+ SetConfigOption("idle_in_transaction_session_timeout", "0",
+ PGC_SUSET, PGC_S_OVERRIDE);
+
+ /*
+ * Force default_transaction_isolation to READ COMMITTED. We don't want
+ * to pay the overhead of serializable mode, nor add any risk of causing
+ * deadlocks or delaying other transactions.
+ */
+ SetConfigOption("default_transaction_isolation", "read committed",
+ PGC_SUSET, PGC_S_OVERRIDE);
+
+ LoginMonitorShmem->lm_pid = MyProcPid;
+ LoginMonitorShmem->lm_latch = MyLatch;
+
+ /*
+ * Main loop until shutdown request
+ */
+ while (!ShutdownRequestPending) {
+ /*
+ * Wait until naptime expires or we get some type of signal (all the
+ * signal handlers will wake us by calling SetLatch).
+ */
+ (void) WaitLatch(MyLatch,
+ WL_LATCH_SET | WL_TIMEOUT | WL_EXIT_ON_PM_DEATH,
+ 5 * 1000L,
+ WAIT_EVENT_LOGIN_MONITOR_LAUNCHER_MAIN);
+
+ /* Clear any already-pending wakeups */
+ ResetLatch(MyLatch);
+
+ HandleLoginMonitorInterrupts();
+
+ /*
+ * As only one worker can run at any time, before start a worker,
+ * we should check whether there is already one worker running.
+ */
+ if (LoginMonitorShmem->login_failed_requested)
+ {
+ SendPostmasterSignal(PMSIGNAL_START_LOGIN_MONITOR_WORKER);
+ elog(DEBUG1, "Login monitor launcher is processing uesr %s and has sent starting"
+ "worker signal to postmaster.", LoginMonitorShmem->curr_user_name);
+ WaitLatch(LoginMonitorShmem->lm_latch,
+ WL_LATCH_SET | WL_POSTMASTER_DEATH, 0,
+ WAIT_EVENT_LOGINMONITOR_FINISH);
+ /* Clear any already-pending wakeups */
+ ResetLatch(LoginMonitorShmem->lm_latch);
+ }
+ }
+
+ LoginMonitorShutdown();
+}
+
+/********************************************************************
+ * LOGIN MONITOR WORKER CODE
+ ********************************************************************/
+/*
+ * Main entry point for login monitor process.
+ *
+ * This code is heavily based on pgarch.c, q.v.
+ */
+int
+StartLoginMonitorWorker(void) {
+ pid_t worker_pid;
+
+#ifdef EXEC_BACKEND
+ switch ((worker_pid = lmworker_forkexec()))
+#else
+ switch ((worker_pid = fork_process()))
+#endif
+ {
+ case -1:
+ ereport(LOG,
+ (errmsg("could not fork login monitor worker process: %m")));
+ return 0;
+
+#ifndef EXEC_BACKEND
+ case 0:
+ /* in postmaster child ... */
+ InitPostmasterChild();
+
+ /* Close the postmaster's sockets */
+ ClosePostmasterPorts(false);
+
+ LoginMonitorWorkerMain(0, NULL);
+ break;
+#endif
+ default:
+ return (int) worker_pid;
+ }
+
+ /* shouldn't get here */
+ return 0;
+}
+
+/*
+ * LoginMonitorWorkerMain
+ */
+NON_EXEC_STATIC void
+LoginMonitorWorkerMain(int argc, char *argv[]) {
+ sigjmp_buf local_sigjmp_buf;
+
+ am_login_monitor_worker = true;
+
+ /* MPP-4990: LoginMonitor always runs as utility-mode */
+ if (IS_QUERY_DISPATCHER())
+ Gp_role = GP_ROLE_DISPATCH;
+ else
+ Gp_role = GP_ROLE_UTILITY;
+
+ MyBackendType = B_LOGIN_MONITOR_WORKER;
+ init_ps_display(NULL);
+
+ SetProcessingMode(InitProcessing);
+
+ /*
+ * Set up signal handlers. We operate on databases much like a regular
+ * backend, so we use the same signal handling. See equivalent code in
+ * tcop/postgres.c.
+ */
+ pqsignal(SIGHUP, SignalHandlerForConfigReload);
+ pqsignal(SIGINT, SIG_IGN);
+ pqsignal(SIGTERM, die);
+
+ /* SIGQUIT handler was already set up by InitPostmasterChild */
+ InitializeTimeouts(); /* establishes SIGALRM handler */
+
+ pqsignal(SIGPIPE, SIG_IGN);
+ pqsignal(SIGUSR1, procsignal_sigusr1_handler);
+ pqsignal(SIGUSR2, SIG_IGN);
+ pqsignal(SIGFPE, FloatExceptionHandler);
+ pqsignal(SIGCHLD, SIG_DFL);
+
+ /* Early initialization */
+ BaseInit();
+
+ /*
+ * Create a per-backend PGPROC struct in shared memory, except in the
+ * EXEC_BACKEND case where this was done in SubPostmasterMain. We must do
+ * this before we can use LWLocks (and in the EXEC_BACKEND case we already
+ * had to do some stuff with LWLocks).
+ */
+#ifndef EXEC_BACKEND
+ InitProcess();
+#endif
+
+ /*
+ * If an exception is encountered, processing resumes here.
+ *
+ * This code is a stripped down version of PostgresMain error recovery.
+ *
+ * Note that we use sigsetjmp(..., 1), so that the prevailing signal mask
+ * (to wit, BlockSig) will be restored when longjmp'ing to here. Thus,
+ * signals other than SIGQUIT will be blocked until we complete error
+ * recovery. It might seem that this policy makes the HOLD_INTERRUPTS()
+ * call redundant, but it is not since InterruptPending might be set
+ * already.
+ */
+ if (sigsetjmp(local_sigjmp_buf, 1) != 0) {
+ /* since not using PG_TRY, must reset error stack by hand */
+ error_context_stack = NULL;
+
+ /* Prevents interrupts while cleaning up */
+ HOLD_INTERRUPTS();
+
+ /* Report the error to the server log */
+ EmitErrorReport();
+
+ SetLatch(LoginMonitorShmem->lm_latch);
+ /*
+ * We can now go away. Note that because we called InitProcess, a
+ * callback was registered to do ProcKill, which will clean up
+ * necessary state.
+ */
+ proc_exit(0);
+ }
+
+ /* We can now handle ereport(ERROR) */
+ PG_exception_stack = &local_sigjmp_buf;
+
+ PG_SETMASK(&UnBlockSig);
+
+ /*
+ * Set always-secure search path.
+ */
+ SetConfigOption("search_path", "", PGC_SUSET, PGC_S_OVERRIDE);
+
+ /*
+ * Force zero_damaged_pages OFF in the login monitor process, even if it is set`
+ * in postgresql.conf. We don't really want such a dangerous option being applied
+ * non-interactively.
+ */
+ SetConfigOption("zero_damaged_pages", "false", PGC_SUSET, PGC_S_OVERRIDE);
+
+ /*
+ * Force settable timeouts off to avoid letting these settings prevent
+ * regular maintenance from being executed.
+ */
+ SetConfigOption("statement_timeout", "0", PGC_SUSET, PGC_S_OVERRIDE);
+ SetConfigOption("lock_timeout", "0", PGC_SUSET, PGC_S_OVERRIDE);
+ SetConfigOption("idle_in_transaction_session_timeout", "0",
+ PGC_SUSET, PGC_S_OVERRIDE);
+
+ /*
+ * Force default_transaction_isolation to READ COMMITTED. We don't want
+ * to pay the overhead of serializable mode, nor add any risk of causing
+ * deadlocks or delaying other transactions.
+ */
+ SetConfigOption("default_transaction_isolation", "read committed",
+ PGC_SUSET, PGC_S_OVERRIDE);
+
+ /*
+ * Force synchronous replication off to allow regular maintenance even if
+ * we are waiting for standbys to connect. This is important to ensure we
+ * aren't blocked from performing anti-wraparound tasks.
+ */
+ if (synchronous_commit > SYNCHRONOUS_COMMIT_LOCAL_FLUSH)
+ SetConfigOption("synchronous_commit", "local",
+ PGC_SUSET, PGC_S_OVERRIDE);
+
+ if (LoginMonitorShmem->curr_user_name) {
+ InitPostgres(DB_FOR_COMMON_ACCESS, InvalidOid, NULL, InvalidOid, NULL, false);
+ SetProcessingMode(NormalProcessing);
+ set_ps_display(LoginMonitorShmem->curr_user_name);
+ ereport(DEBUG1,
+ (errmsg_internal("login monitor: processing user \"%s\" failed",
+ LoginMonitorShmem->curr_user_name)));
+
+ record_failed_login();
+ }
+
+ proc_exit(0);
+}
+
+/*
+ * Process any new interrupts.
+ */
+static void
+HandleLoginMonitorInterrupts(void) {
+ /* the normal shutdown case */
+ if (ShutdownRequestPending)
+ LoginMonitorShutdown();
+
+ if (ConfigReloadPending) {
+ ConfigReloadPending = false;
+ ProcessConfigFile(PGC_SIGHUP);
+ }
+
+ /* Process barrier events */
+ if (ProcSignalBarrierPending)
+ ProcessProcSignalBarrier();
+
+ /* Perform logging of memory contexts of this process */
+ if (LogMemoryContextPending)
+ ProcessLogMemoryContextInterrupt();
+
+ /* Process sinval catchup interrupts that happened while sleeping */
+ ProcessCatchupInterrupt();
+}
+
+/*
+ * Process a failed login authentication
+ *
+ * As this will only record the current failed login, we don't need
+ * CHECK_FOR_INTERRUPTS during process.
+ */
+static void
+record_failed_login(void) {
+ Datum new_record[Natts_pg_authid];
+ bool new_record_nulls[Natts_pg_authid];
+ bool new_record_repl[Natts_pg_authid];
+ Relation pg_authid_rel;
+ Relation pg_profile_rel;
+ TupleDesc pg_authid_dsc;
+ HeapTuple profile_tuple;
+ HeapTuple auth_tuple;
+ HeapTuple new_tuple;
+ Form_pg_profile profileform;
+ int32 failed_login_attempts;
+ int32 profile_failed_login_attempts;
+ bool isnull;
+ int32 profileid;
+ TimestampTz now;
+
+ /* Start a transaction so our commands have one to play into. */
+ StartTransactionCommand();
+
+ /* Acquire LWLock */
+ LWLockAcquire(LoginFailedSharedMemoryLock, LW_EXCLUSIVE);
+ elog(DEBUG1, "Login monitor worker has acquired LoginFailedSharedMemoryLock for process user %s.",
+ LoginMonitorShmem->curr_user_name);
+
+ /*
+ * Update related catalog and check whether the account need to be locked
+ */
+ pg_authid_rel = table_open(AuthIdRelationId, RowExclusiveLock);
+ pg_authid_dsc = RelationGetDescr(pg_authid_rel);
+ pg_profile_rel = table_open(ProfileRelationId, AccessShareLock);
+
+ auth_tuple = SearchSysCache1(AUTHNAME,
+ CStringGetDatum(LoginMonitorShmem->curr_user_name));
+
+ Assert(HeapTupleIsValid(auth_tuple));
+
+ /* get current failed_login_attempts */
+ failed_login_attempts = SysCacheGetAttr(AUTHNAME, auth_tuple,
+ Anum_pg_authid_rolfailedlogins, &isnull);
+ Assert(!isnull);
+
+ /* increase failed_login_attempts by one */
+ failed_login_attempts++;
+ elog(DEBUG1, "User %s FAILED LOGIN ATTEMPTS is %d in Login Monitor worker",
+ LoginMonitorShmem->curr_user_name, failed_login_attempts);
+ /*
+ * Build an updated tuple, perusing the information just obtained
+ */
+ MemSet(new_record, 0, sizeof(new_record));
+ MemSet(new_record_nulls, true, sizeof(new_record_nulls));
+ MemSet(new_record_repl, false, sizeof(new_record_repl));
+
+ new_record[Anum_pg_authid_rolfailedlogins - 1] =
+ Int32GetDatum(failed_login_attempts);
+ new_record_nulls[Anum_pg_authid_rolfailedlogins - 1] =
+ false;
+ new_record_repl[Anum_pg_authid_rolfailedlogins - 1] =
+ true;
+
+ /* get the user's current profile oid */
+ profileid = SysCacheGetAttr(AUTHNAME, auth_tuple,
+ Anum_pg_authid_rolprofile, &isnull);
+ Assert(!isnull);
+
+ /* get user's current profile tuple */
+ profile_tuple = SearchSysCache1(PROFILEID, ObjectIdGetDatum(profileid));
+ Assert(HeapTupleIsValid(profile_tuple));
+ profileform = (Form_pg_profile) GETSTRUCT(profile_tuple);
+
+ /*
+ * Transform failed_login_attempts to normal value if it's
+ * PROFILE_DEFAULT or PROFILE_UNLIMITED.
+ */
+ profile_failed_login_attempts =
+ tranformProfileValueToNormal(profileform->prffailedloginattempts,
+ Anum_pg_profile_prffailedloginattempts);
+
+ /*
+ * If user's failed_login_attempts is bigger equal than current
+ * profile's failed_login_attempts, update account status to
+ * locked and lockdate to now.
+ */
+ if (failed_login_attempts >= profile_failed_login_attempts) {
+ new_record[Anum_pg_authid_rolaccountstatus - 1] =
+ Int32GetDatum(ROLE_ACCOUNT_STATUS_LOCKED_TIMED);
+ new_record_nulls[Anum_pg_authid_rolaccountstatus - 1] =
+ false;
+ new_record_repl[Anum_pg_authid_rolaccountstatus - 1] =
+ true;
+
+ now = GetCurrentTimestamp();
+ new_record[Anum_pg_authid_rollockdate - 1] =
+ Int64GetDatum(now);
+ new_record_nulls[Anum_pg_authid_rollockdate - 1] =
+ false;
+ new_record_repl[Anum_pg_authid_rollockdate - 1] =
+ true;
+ }
+
+ new_tuple = heap_modify_tuple(auth_tuple, pg_authid_dsc,
+ new_record, new_record_nulls, new_record_repl);
+ CatalogTupleUpdate(pg_authid_rel, &auth_tuple->t_self, new_tuple);
+
+ InvokeObjectPostAlterHook(AuthIdRelationId, profileid, 0);
+
+ ReleaseSysCache(auth_tuple);
+ ReleaseSysCache(profile_tuple);
+ table_close(pg_profile_rel, NoLock);
+ table_close(pg_authid_rel, NoLock);
+
+ /* reset login_failed_requested to false */
+ LoginMonitorShmem->login_failed_requested = false;
+
+ /* notify process by setting latch */
+ SetLatch(LoginMonitorShmem->lm_latch);
+ SetLatch(LoginMonitorShmem->latch);
+ LWLockRelease(LoginFailedSharedMemoryLock);
+ elog(DEBUG1, "Login monitor worker has released LoginFailedSharedMemoryLock for process user %s.",
+ LoginMonitorShmem->curr_user_name);
+
+ CommitTransactionCommand();
+}
+
+/*
+ * Notify Login Monitor Launcher by LoginMonitorShmem
+ */
+void
+LoginMonitorWorkerFailed(void) {
+ SetLatch(LoginMonitorShmem->lm_latch);
+}
+
+/*
+ * Perform a normal exit from the login monitor.
+ */
+static void
+LoginMonitorShutdown(void) {
+ ereport(DEBUG1,
+ (errmsg_internal("login monitor shutting down")));
+
+ LoginMonitorShmem->lm_pid = 0;
+ LoginMonitorShmem->lm_latch = NULL;
+
+ proc_exit(0);
+}
+
+/*
+ * SIGUSR1: a login password authentication failed
+ */
+void
+HandleLoginFailed(void) {
+ int save_errno = errno;
+
+ LoginMonitorShmem->login_failed_requested = true;
+ SetLatch(MyLatch);
+
+ errno = save_errno;
+}
+
+/*
+ * IsLoginMonitorLauncher functions
+ * Return whether this is a login monitor launcher process.
+ */
+bool
+IsLoginMonitorLauncherProcess(void) {
+ return am_login_monitor_launcher;
+}
+
+/*
+ * IsLoginMonitorWorker functions
+ * Return whether this is a login monitor worker process.
+ */
+bool
+IsLoginMonitorWorkerProcess(void) {
+ return am_login_monitor_worker;
+}
+
+/*
+ * LoginMonitorShmemSize
+ * Compute space needed for login monitor-related shared memory
+ */
+Size
+LoginMonitorShmemSize(void) {
+ Size size;
+
+ size = sizeof(LoginMonitorShmemStruct);
+
+ return size;
+}
+
+/*
+ * LoginMonitorShmemInit
+ * Allocate and initialize login monitor-related shared memory
+ */
+void
+LoginMonitorShmemInit(void) {
+ bool found;
+
+ LoginMonitorShmem = (LoginMonitorShmemStruct *)
+ ShmemInitStruct("Login Monitor Data",
+ LoginMonitorShmemSize(),
+ &found);
+
+ if (!IsUnderPostmaster) {
+ Assert(!found);
+ LoginMonitorShmem->lm_pid = 0;
+ LoginMonitorShmem->lm_latch = NULL;
+ memset(LoginMonitorShmem->curr_user_name, 0, NAMEDATALEN);
+ LoginMonitorShmem->latch = NULL;
+ LoginMonitorShmem->login_failed_requested = false;
+ } else
+ Assert(found);
+}
+
+/*
+ * SendLoginFailedSignal - signal the postmaster after failed authentication.
+ */
+void
+SendLoginFailedSignal(const char *curr_user_name) {
+ int rc;
+
+ /* If called in a standalone backend or Login Monitor PID is 0, do nothing */
+ if (!IsUnderPostmaster || !LoginMonitorPID)
+ return;
+ /*
+ * Before sending signal, we need to acquire lock in shared memory and set
+ * current user oid. Only when login monitor backend process has solved the
+ * current signal, the lock will be released. By this way, the login monitor
+ * solve the postgres signal serially which will avoid dead lock.
+ */
+ LWLockAcquire(LoginFailedControlLock, LW_EXCLUSIVE);
+ LWLockAcquire(LoginFailedSharedMemoryLock, LW_EXCLUSIVE);
+ elog(DEBUG1, "User %s has acquire LoginFailedControlLock and LoginFailedSharedMemoryLock", curr_user_name);
+
+ /* Reset login monitor shmem user name */
+ memset(LoginMonitorShmem->curr_user_name, 0, NAMEDATALEN);
+ /* Set current user name */
+ strcpy(LoginMonitorShmem->curr_user_name, curr_user_name);
+
+ /* Set latch to pointer to MyLatch */
+ LoginMonitorShmem->latch = &MyProc->procLatch;
+ ResetLatch(LoginMonitorShmem->latch);
+
+ /* Send signal to PostMaster */
+ SendPostmasterSignal(PMSIGNAL_FAILED_LOGIN);
+
+ elog(DEBUG1, "User %s has sent failed login signal to postmaster", curr_user_name);
+
+ LWLockRelease(LoginFailedSharedMemoryLock);
+ elog(DEBUG1, "User %s has released LoginFailedSharedMemoryLock and wait latch", curr_user_name);
+
+ rc = WaitLatch(LoginMonitorShmem->latch,
+ WL_LATCH_SET | WL_POSTMASTER_DEATH, 0,
+ WAIT_EVENT_LOGINMONITOR_FINISH);
+
+ LWLockRelease(LoginFailedControlLock);
+ elog(DEBUG1, "User %s has release LoginFailedControlLock", curr_user_name);
+}
+
+static void
+LoginMonitorShmemReset(void) {
+ Assert(LoginMonitorShmem);
+
+ LWLockAcquire(LoginFailedSharedMemoryLock, LW_EXCLUSIVE);
+
+ memset(LoginMonitorShmem->curr_user_name, 0, NAMEDATALEN);
+ LoginMonitorShmem->latch = NULL;
+ LoginMonitorShmem->login_failed_requested = false;
+
+ LWLockRelease(LoginFailedSharedMemoryLock);
+
+ return;
+}
diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c
index 3d9a199..c9c8b2d 100644
--- a/src/backend/postmaster/postmaster.c
+++ b/src/backend/postmaster/postmaster.c
@@ -130,6 +130,7 @@
#include "postmaster/syslogger.h"
#include "postmaster/backoff.h"
#include "postmaster/bgworker.h"
+#include "postmaster/loginmonitor.h"
#include "replication/logicallauncher.h"
#include "replication/walsender.h"
#include "storage/fd.h"
@@ -278,6 +279,7 @@
char *bonjour_name;
bool restart_after_crash = true;
bool remove_temp_files_after_crash = true;
+bool enable_password_profile = true;
/* Hook for plugins to start background workers */
start_bgworkers_hook_type start_bgworkers_hook = NULL;
@@ -295,7 +297,8 @@
AutoVacPID = 0,
PgArchPID = 0,
PgStatPID = 0,
- SysLoggerPID = 0;
+ SysLoggerPID = 0,
+ LoginMonitorPID = 0;
/* Startup process's status */
typedef enum
@@ -541,6 +544,7 @@
static bool do_start_bgworker(RegisteredBgWorker *rw);
static pid_t StartChildProcess(AuxProcType type);
static void StartAutovacuumWorker(void);
+static void StartLMWorker(void);
static void MaybeStartWalReceiver(void);
static void InitPostmasterDeathWatchHandle(void);
@@ -2089,6 +2093,9 @@
if (PgArchPID == 0 && PgArchStartupAllowed())
PgArchPID = StartArchiver();
+ if (enable_password_profile && LoginMonitorPID == 0 && pmState == PM_RUN)
+ LoginMonitorPID = StartLoginMonitorLauncher();
+
/* If we need to signal the autovacuum launcher, do so now */
if (avlauncher_needs_signal)
{
@@ -3192,6 +3199,8 @@
signal_child(SysLoggerPID, SIGHUP);
if (PgStatPID != 0)
signal_child(PgStatPID, SIGHUP);
+ if (enable_password_profile && LoginMonitorPID != 0)
+ signal_child(LoginMonitorPID, SIGHUP);
/* Reload authentication config files too */
if (!load_hba())
@@ -3522,6 +3531,8 @@
PgArchPID = StartArchiver();
if (PgStatPID == 0)
PgStatPID = pgstat_start();
+ if (enable_password_profile && LoginMonitorPID == 0)
+ LoginMonitorPID = StartLoginMonitorLauncher();
/* workers may be scheduled to start now */
maybe_start_bgworkers();
@@ -3669,6 +3680,21 @@
}
/*
+ * Was it the login monitor? If exit status is zero (normal) or one
+ * (FATAL exit), we assume everything is all right just like normal
+ * backends and just try to restart a new one. Any other exit condition
+ * is treated as a crash.
+ */
+ if (enable_password_profile && pid == LoginMonitorPID)
+ {
+ LoginMonitorPID = 0;
+ if (!EXIT_STATUS_0(exitstatus) && !EXIT_STATUS_1(exitstatus))
+ HandleChildCrash(pid, exitstatus,
+ _("login monitor process"));
+ continue;
+ }
+
+ /*
* Was it the archiver? If exit status is zero (normal) or one (FATAL
* exit), we assume everything is all right just like normal backends
* and just try to restart a new one so that we immediately retry
@@ -4138,6 +4164,21 @@
signal_child(AutoVacPID, (SendStop ? SIGSTOP : SIGQUIT));
}
+ /* Take care of the login monitor too */
+ if (enable_password_profile)
+ {
+ if (pid == LoginMonitorPID)
+ LoginMonitorPID = 0;
+ else if (LoginMonitorPID != 0 && take_action)
+ {
+ ereport(DEBUG2,
+ (errmsg_internal("sending %s to process %d",
+ (SendStop ? "SIGSTOP" : "SIGQUIT"),
+ (int) LoginMonitorPID)));
+ signal_child(LoginMonitorPID, (SendStop ? SIGSTOP : SIGQUIT));
+ }
+ }
+
/* Take care of the archiver too */
if (pid == PgArchPID)
PgArchPID = 0;
@@ -4313,6 +4354,9 @@
signal_child(StartupPID, SIGTERM);
if (WalReceiverPID != 0)
signal_child(WalReceiverPID, SIGTERM);
+ /* stop login monitor */
+ if (enable_password_profile && LoginMonitorPID != 0)
+ signal_child(LoginMonitorPID, SIGTERM);
/* checkpointer, archiver, stats, and syslogger may continue for now */
/* Now transition to PM_WAIT_BACKENDS state to wait for them to die */
@@ -4343,7 +4387,8 @@
(CheckpointerPID == 0 ||
(!FatalError && Shutdown < ImmediateShutdown)) &&
WalWriterPID == 0 &&
- AutoVacPID == 0)
+ AutoVacPID == 0 &&
+ LoginMonitorPID == 0)
{
if (Shutdown >= ImmediateShutdown || FatalError)
{
@@ -4437,6 +4482,7 @@
Assert(CheckpointerPID == 0);
Assert(WalWriterPID == 0);
Assert(AutoVacPID == 0);
+ Assert(LoginMonitorPID == 0);
/* syslogger is not considered here */
pmState = PM_NO_CHILDREN;
}
@@ -4658,6 +4704,8 @@
signal_child(PgArchPID, signal);
if (PgStatPID != 0)
signal_child(PgStatPID, signal);
+ if (enable_password_profile && LoginMonitorPID != 0)
+ signal_child(LoginMonitorPID, signal);
}
/*
@@ -5820,6 +5868,13 @@
StartAutovacuumWorker();
}
+ if (CheckPostmasterSignal(PMSIGNAL_START_LOGIN_MONITOR_WORKER) &&
+ Shutdown <= SmartShutdown && pmState < PM_STOP_BACKENDS)
+ {
+ /* The login monitor wants us to start a worker process. */
+ StartLMWorker();
+ }
+
if (CheckPostmasterSignal(PMSIGNAL_START_WALRECEIVER))
{
/* Startup Process wants us to start the walreceiver process. */
@@ -5864,6 +5919,16 @@
PostmasterStateMachine();
}
+ /*
+ * Postmaster send signal to login monitor, notifying login monitor to
+ * process failed login.
+ */
+ if (LoginMonitorPID != 0 &&
+ pmState == PM_RUN && CheckPostmasterSignal(PMSIGNAL_FAILED_LOGIN))
+ {
+ SendProcSignal(LoginMonitorPID, PROCSIG_FAILED_LOGIN, InvalidBackendId);
+ }
+
if (StartupPID != 0 &&
(pmState == PM_STARTUP || pmState == PM_RECOVERY ||
pmState == PM_HOT_STANDBY) &&
@@ -6175,6 +6240,91 @@
}
/*
+ * StartLMWorker
+ * Start a login monitor worker process.
+ *
+ * Like StartAutovacuumWorker, this function is here. As login monitor
+ * worker process is used like normal process, we set backend_type to
+ * NORMAL.
+ */
+static void
+StartLMWorker(void)
+{
+ Backend *bn;
+
+ /*
+ * If not in condition to run a process, don't try, but handle it like a
+ * fork failure. This does not normally happen, since the signal is only
+ * supposed to be sent by login monitor launcher when it's OK to do it,
+ * but we have to check to avoid race-condition problems during DB state
+ * changes.
+ */
+ if (canAcceptConnections(BACKEND_TYPE_NORMAL) == CAC_OK)
+ {
+ /*
+ * Compute the cancel key that will be assigned to this session. We
+ * probably don't need cancel keys for autovac workers, but we'd
+ * better have something random in the field to prevent unfriendly
+ * people from sending cancels to them。
+ */
+ if (!RandomCancelKey(&MyCancelKey))
+ {
+ ereport(LOG,
+ (errcode(ERRCODE_INTERNAL_ERROR),
+ errmsg("could not generate random cancel key")));
+ return;
+ }
+
+ bn = (Backend *) malloc(sizeof(Backend));
+ if (bn)
+ {
+ bn->cancel_key = MyCancelKey;
+
+ /* Login monitor workers are not dead_end and need a child slot */
+ bn->dead_end = false;
+ bn->child_slot = MyPMChildSlot = AssignPostmasterChildSlot();
+ bn->bgworker_notify = false;
+
+ bn->pid = StartLoginMonitorWorker();
+ if (bn->pid > 0)
+ {
+ bn->bkend_type = BACKEND_TYPE_NORMAL;
+ dlist_push_head(&BackendList, &bn->elem);
+#ifdef EXEC_BACKEND
+ ShmemBackendArrayAdd(bn);
+#endif
+
+ /* all OK */
+ return;
+ }
+
+ /*
+ * fork failed, fall through to report -- actual error message was
+ * logged by StartLoginMonitorWorker
+ */
+ (void) ReleasePostmasterChildSlot(bn->child_slot);
+ free(bn);
+ }
+ else
+ ereport(LOG,
+ (errcode(ERRCODE_OUT_OF_MEMORY),
+ errmsg("out of memory")));
+ }
+
+ /*
+ * Notify failure to the launcher by latch, if it's running. (If it's not,
+ * we might not even be connected to shared memory, so don't try to call
+ * LoginMonitorWorkerFailed.) Note that we also need to notify it so that
+ * it responds to the condition, but we don't do that here, instead waiting
+ * for ServerLoop to do it.
+ */
+ if (LoginMonitorPID != 0)
+ {
+ LoginMonitorWorkerFailed();
+ }
+}
+
+/*
* MaybeStartWalReceiver
* Start the WAL receiver process, if not running and our state allows.
*
@@ -6258,7 +6408,7 @@
MaxLivePostmasterChildren(void)
{
return 2 * (MaxConnections + autovacuum_max_workers + 1 +
- max_wal_senders + max_worker_processes);
+ login_monitor_max_processes /* Login Monitor */ + max_wal_senders + max_worker_processes);
}
/*
diff --git a/src/backend/storage/ipc/ipci.c b/src/backend/storage/ipc/ipci.c
index 0ea4e82..11324d6 100644
--- a/src/backend/storage/ipc/ipci.c
+++ b/src/backend/storage/ipc/ipci.c
@@ -39,6 +39,7 @@
#include "postmaster/bgwriter.h"
#include "postmaster/postmaster.h"
#include "postmaster/fts.h"
+#include "postmaster/loginmonitor.h"
#include "replication/logicallauncher.h"
#include "replication/origin.h"
#include "replication/slot.h"
@@ -185,6 +186,7 @@
size = add_size(size, ProcSignalShmemSize());
size = add_size(size, CheckpointerShmemSize());
size = add_size(size, AutoVacuumShmemSize());
+ size = add_size(size, LoginMonitorShmemSize());
size = add_size(size, ReplicationSlotsShmemSize());
size = add_size(size, ReplicationOriginShmemSize());
size = add_size(size, WalSndShmemSize());
@@ -365,6 +367,7 @@
ProcSignalShmemInit();
CheckpointerShmemInit();
AutoVacuumShmemInit();
+ LoginMonitorShmemInit();
ReplicationSlotsShmemInit();
ReplicationOriginShmemInit();
WalSndShmemInit();
diff --git a/src/backend/storage/ipc/procsignal.c b/src/backend/storage/ipc/procsignal.c
index 191f9a5..669b546 100644
--- a/src/backend/storage/ipc/procsignal.c
+++ b/src/backend/storage/ipc/procsignal.c
@@ -22,6 +22,7 @@
#include "commands/async.h"
#include "miscadmin.h"
#include "pgstat.h"
+#include "postmaster/loginmonitor.h"
#include "replication/walsender.h"
#include "storage/condition_variable.h"
#include "storage/ipc.h"
@@ -704,6 +705,9 @@
if (CheckProcSignal(PROCSIG_RESOURCE_GROUP_MOVE_QUERY))
HandleMoveResourceGroup();
+ if (CheckProcSignal(PROCSIG_FAILED_LOGIN))
+ HandleLoginFailed();
+
SetLatch(MyLatch);
errno = save_errno;
diff --git a/src/backend/storage/lmgr/lwlocknames.txt b/src/backend/storage/lmgr/lwlocknames.txt
index c83f158..13d8980 100644
--- a/src/backend/storage/lmgr/lwlocknames.txt
+++ b/src/backend/storage/lmgr/lwlocknames.txt
@@ -71,3 +71,5 @@
CdbConfigCacheLock 62
KmgrFileLock 63
GpParallelDSMHashLock 64
+LoginFailedControlLock 65
+LoginFailedSharedMemoryLock 66
diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c
index a9a6699..768b585 100644
--- a/src/backend/storage/lmgr/proc.c
+++ b/src/backend/storage/lmgr/proc.c
@@ -46,6 +46,7 @@
#include "miscadmin.h"
#include "pgstat.h"
#include "postmaster/autovacuum.h"
+#include "postmaster/loginmonitor.h"
#include "replication/slot.h"
#include "replication/syncrep.h"
#include "replication/walsender.h"
@@ -203,6 +204,7 @@
ProcGlobal->spins_per_delay = DEFAULT_SPINS_PER_DELAY;
ProcGlobal->freeProcs = NULL;
ProcGlobal->autovacFreeProcs = NULL;
+ ProcGlobal->lmFreeProcs = NULL;
ProcGlobal->bgworkerFreeProcs = NULL;
ProcGlobal->walsenderFreeProcs = NULL;
ProcGlobal->startupProc = NULL;
@@ -291,7 +293,14 @@
ProcGlobal->autovacFreeProcs = &procs[i];
procs[i].procgloballist = &ProcGlobal->autovacFreeProcs;
}
- else if (i < MaxConnections + autovacuum_max_workers + 1 + max_worker_processes)
+ else if (i < MaxConnections + autovacuum_max_workers + 1 + login_monitor_max_processes)
+ {
+ /* PGPROC for login monitor, add to lmFreeProcs list */
+ procs[i].links.next = (SHM_QUEUE *) ProcGlobal->lmFreeProcs;
+ ProcGlobal->lmFreeProcs = &procs[i];
+ procs[i].procgloballist = &ProcGlobal->lmFreeProcs;
+ }
+ else if (i < MaxConnections + autovacuum_max_workers + 1 + login_monitor_max_processes + max_worker_processes)
{
/* PGPROC for bgworker, add to bgworkerFreeProcs list */
procs[i].links.next = (SHM_QUEUE *) ProcGlobal->bgworkerFreeProcs;
@@ -371,6 +380,8 @@
/* Decide which list should supply our PGPROC. */
if (IsAnyAutoVacuumProcess())
procgloballist = &ProcGlobal->autovacFreeProcs;
+ else if (IsAnyLoginMonitorProcess())
+ procgloballist = &ProcGlobal->lmFreeProcs;
else if (IsBackgroundWorker)
procgloballist = &ProcGlobal->bgworkerFreeProcs;
else if (am_walsender)
@@ -444,11 +455,13 @@
* cleaning up. (XXX autovac launcher currently doesn't participate in
* this; it probably should.)
*
+ * Like autovac launcher, login monitor doesn't participate in this.
+ *
* Ideally, we should create functions similar to IsAutoVacuumLauncherProcess()
* for ftsProber, etc who call InitProcess().
* But MyPMChildSlot helps to get away with it.
*/
- if (IsUnderPostmaster && !IsAutoVacuumLauncherProcess()
+ if (IsUnderPostmaster && !(IsAutoVacuumLauncherProcess() || IsLoginMonitorLauncherProcess())
&& MyPMChildSlot > 0)
MarkPostmasterChildActive();
@@ -1155,9 +1168,9 @@
/*
* This process is no longer present in shared memory in any meaningful
* way, so tell the postmaster we've cleaned up acceptably well. (XXX
- * autovac launcher should be included here someday)
+ * autovac launcher and login monitor should be included here someday)
*/
- if (IsUnderPostmaster && !IsAutoVacuumLauncherProcess()
+ if (IsUnderPostmaster && !IsAutoVacuumLauncherProcess() && !IsLoginMonitorLauncherProcess()
&& MyPMChildSlot > 0)
MarkPostmasterChildInactive();
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 6d5c8b5..998d3ef 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -79,6 +79,7 @@
#include "access/table.h"
#include "catalog/oid_dispatch.h"
+#include "catalog/pg_profile.h"
#include "cdb/cdbdisp_query.h"
#include "cdb/cdbendpoint.h"
#include "cdb/cdbvars.h"
@@ -232,13 +233,16 @@
case T_ViewStmt:
/* fallthrough */
/* GPDB specific commands */
+ case T_AlterProfileStmt:
case T_AlterQueueStmt:
case T_AlterResourceGroupStmt:
+ case T_CreateProfileStmt:
case T_CreateQueueStmt:
case T_CreateResourceGroupStmt:
case T_CreateTaskStmt:
case T_AlterTaskStmt:
case T_DropTaskStmt:
+ case T_DropProfileStmt:
case T_DropQueueStmt:
case T_DropResourceGroupStmt:
case T_DropWarehouseStmt:
@@ -1104,6 +1108,18 @@
DropRole((DropRoleStmt *) parsetree);
break;
+ case T_CreateProfileStmt:
+ CreateProfile(pstate, (CreateProfileStmt *) parsetree);
+ break;
+
+ case T_AlterProfileStmt:
+ AlterProfile((AlterProfileStmt *) parsetree);
+ break;
+
+ case T_DropProfileStmt:
+ DropProfile((DropProfileStmt *) parsetree);
+ break;
+
case T_ReassignOwnedStmt:
/* no event triggers for global objects */
ReassignOwnedObjects((ReassignOwnedStmt *) parsetree);
@@ -2744,6 +2760,9 @@
case OBJECT_ROLE:
tag = CMDTAG_ALTER_ROLE;
break;
+ case OBJECT_PROFILE:
+ tag = CMDTAG_ALTER_PROFILE;
+ break;
case OBJECT_ROUTINE:
tag = CMDTAG_ALTER_ROUTINE;
break;
@@ -3491,6 +3510,18 @@
tag = CMDTAG_DROP_ROLE;
break;
+ case T_CreateProfileStmt:
+ tag = CMDTAG_CREATE_PROFILE;
+ break;
+
+ case T_AlterProfileStmt:
+ tag = CMDTAG_ALTER_PROFILE;
+ break;
+
+ case T_DropProfileStmt:
+ tag = CMDTAG_DROP_PROFILE;
+ break;
+
case T_DropOwnedStmt:
tag = CMDTAG_DROP_OWNED;
break;
@@ -4123,6 +4154,18 @@
lev = LOGSTMT_DDL;
break;
+ case T_CreateProfileStmt:
+ lev = LOGSTMT_DDL;
+ break;
+
+ case T_AlterProfileStmt:
+ lev = LOGSTMT_DDL;
+ break;
+
+ case T_DropProfileStmt:
+ lev = LOGSTMT_DDL;
+ break;
+
case T_DropOwnedStmt:
lev = LOGSTMT_DDL;
break;
diff --git a/src/backend/utils/activity/wait_event.c b/src/backend/utils/activity/wait_event.c
index 17cad37..d041ac0 100644
--- a/src/backend/utils/activity/wait_event.c
+++ b/src/backend/utils/activity/wait_event.c
@@ -279,6 +279,9 @@
case WAIT_EVENT_GLOBAL_DEADLOCK_DETECTOR_MAIN:
event_name = "GlobalDeadLockDetectorMain";
break;
+ case WAIT_EVENT_LOGIN_MONITOR_LAUNCHER_MAIN:
+ event_name = "LoginMonitorLauncherMain";
+ break;
/* no default case, so that compiler will warn */
}
@@ -488,6 +491,9 @@
case WAIT_EVENT_SHAREINPUT_SCAN:
event_name = "ShareInputScan";
break;
+ case WAIT_EVENT_LOGINMONITOR_FINISH:
+ event_name = "LoginMonitorFinish";
+ break;
case WAIT_EVENT_DTX_RECOVERY:
event_name = "DtxRecovery";
/* no default case, so that compiler will warn */
diff --git a/src/backend/utils/cache/catcache.c b/src/backend/utils/cache/catcache.c
index a51e88e..60f643c 100644
--- a/src/backend/utils/cache/catcache.c
+++ b/src/backend/utils/cache/catcache.c
@@ -1113,6 +1113,8 @@
case AUTHOID:
case AUTHMEMMEMROLE:
case DATABASEOID:
+ case PROFILEID:
+ case PROFILENAME:
/*
* Protect authentication lookups occurring before relcache has
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 65d341c..851ee05 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -58,7 +58,9 @@
#include "catalog/pg_database.h"
#include "catalog/pg_namespace.h"
#include "catalog/pg_opclass.h"
+#include "catalog/pg_password_history.h"
#include "catalog/pg_proc.h"
+#include "catalog/pg_profile.h"
#include "catalog/pg_publication.h"
#include "catalog/pg_rewrite.h"
#include "catalog/pg_shseclabel.h"
@@ -128,6 +130,8 @@
static const FormData_pg_attribute Desc_pg_auth_members[Natts_pg_auth_members] = {Schema_pg_auth_members};
static const FormData_pg_attribute Desc_pg_auth_time_constraint_members[Natts_pg_auth_time_constraint] = {Schema_pg_auth_time_constraint};
static const FormData_pg_attribute Desc_pg_index[Natts_pg_index] = {Schema_pg_index};
+static const FormData_pg_attribute Desc_pg_password_history[Natts_pg_password_history] = {Schema_pg_password_history};
+static const FormData_pg_attribute Desc_pg_profile[Natts_pg_profile] = {Schema_pg_profile};
static const FormData_pg_attribute Desc_pg_shseclabel[Natts_pg_shseclabel] = {Schema_pg_shseclabel};
static const FormData_pg_attribute Desc_pg_subscription[Natts_pg_subscription] = {Schema_pg_subscription};
@@ -4096,6 +4100,10 @@
Natts_pg_database, Desc_pg_database);
formrdesc("pg_authid", AuthIdRelation_Rowtype_Id, true,
Natts_pg_authid, Desc_pg_authid);
+ formrdesc("pg_password_history", PasswordHistoryRelation_Rowtype_Id, true,
+ Natts_pg_password_history, Desc_pg_password_history);
+ formrdesc("pg_profile", ProfileRelation_Rowtype_Id, true,
+ Natts_pg_profile, Desc_pg_profile);
formrdesc("pg_auth_members", AuthMemRelation_Rowtype_Id, true,
Natts_pg_auth_members, Desc_pg_auth_members);
formrdesc("pg_shseclabel", SharedSecLabelRelation_Rowtype_Id, true,
@@ -4105,7 +4113,7 @@
formrdesc("pg_auth_time_constraint", AuthTimeConstraint_Rowtype_Id, true,
Natts_pg_auth_time_constraint, Desc_pg_auth_time_constraint_members);
-#define NUM_CRITICAL_SHARED_RELS 6 /* fix if you change list above */
+#define NUM_CRITICAL_SHARED_RELS 8 /* fix if you change list above */
}
MemoryContextSwitchTo(oldcxt);
@@ -4249,6 +4257,12 @@
AuthIdRelationId);
load_critical_index(AuthIdOidIndexId,
AuthIdRelationId);
+ load_critical_index(ProfilePrfnameIndexId,
+ ProfileRelationId);
+ load_critical_index(ProfileOidIndexId,
+ ProfileRelationId);
+ load_critical_index(ProfileVerifyFunctionIndexId,
+ ProfileRelationId);
load_critical_index(AuthMemMemRoleIndexId,
AuthMemRelationId);
load_critical_index(SharedSecLabelObjectIndexId,
@@ -4256,7 +4270,7 @@
load_critical_index(AuthTimeConstraintAuthIdIndexId,
AuthTimeConstraintRelationId);
-#define NUM_CRITICAL_SHARED_INDEXES 7 /* fix if you change list above */
+#define NUM_CRITICAL_SHARED_INDEXES 10 /* fix if you change list above */
criticalSharedRelcachesBuilt = true;
}
diff --git a/src/backend/utils/cache/syscache.c b/src/backend/utils/cache/syscache.c
index 558fb6c..3f39c50 100644
--- a/src/backend/utils/cache/syscache.c
+++ b/src/backend/utils/cache/syscache.c
@@ -51,6 +51,8 @@
#include "catalog/pg_operator.h"
#include "catalog/pg_opfamily.h"
#include "catalog/pg_partitioned_table.h"
+#include "catalog/pg_password_history.h"
+#include "catalog/pg_profile.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_publication.h"
#include "catalog/pg_publication_rel.h"
@@ -638,6 +640,28 @@
},
32
},
+ {ProfileRelationId, /* PROFILEID */
+ ProfileOidIndexId,
+ 1,
+ {
+ Anum_pg_profile_oid,
+ 0,
+ 0,
+ 0
+ },
+ 8
+ },
+ {ProfileRelationId, /* PROFILENAME */
+ ProfilePrfnameIndexId,
+ 1,
+ {
+ Anum_pg_profile_prfname,
+ 0,
+ 0,
+ 0
+ },
+ 8
+ },
{ProcedureRelationId, /* PROCNAMEARGSNSP */
ProcedureNameArgsNspIndexId,
3,
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index 5fafe83..b187da7 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -166,6 +166,7 @@
int VacuumCostPageDirty = 20;
int VacuumCostLimit = 200;
double VacuumCostDelay = 0;
+int login_monitor_max_processes = 2; /* login monitor launcher and 1 worker */
int64 VacuumPageHit = 0;
int64 VacuumPageMiss = 0;
diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c
index 9a006c2..f2344d0 100644
--- a/src/backend/utils/init/miscinit.c
+++ b/src/backend/utils/init/miscinit.c
@@ -38,6 +38,7 @@
#include "miscadmin.h"
#include "pgstat.h"
#include "postmaster/autovacuum.h"
+#include "postmaster/loginmonitor.h"
#include "postmaster/fts.h"
#include "postmaster/interrupt.h"
#include "postmaster/postmaster.h"
@@ -287,6 +288,12 @@
case B_LOGGER:
backendDesc = "logger";
break;
+ case B_LOGIN_MONITOR:
+ backendDesc = "login monitor";
+ break;
+ case B_LOGIN_MONITOR_WORKER:
+ backendDesc = "login monitor worker";
+ break;
}
return backendDesc;
@@ -820,9 +827,10 @@
{
/*
* This function should only be called in single-user mode, in autovacuum
- * workers, and in background workers.
+ * workers, login monitor, and in background workers.
*/
AssertState(!IsUnderPostmaster || IsAutoVacuumWorkerProcess() || IsBackgroundWorker
+ || IsAnyLoginMonitorProcess()
|| am_startup
|| (am_faulthandler && am_mirror)
|| (am_ftshandler && am_mirror));
diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c
index 7a8cace..f6980d1 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -30,13 +30,16 @@
#include "access/xlog.h"
#include "catalog/catalog.h"
#include "catalog/namespace.h"
+#include "catalog/objectaccess.h"
#include "catalog/pg_authid.h"
#include "catalog/pg_database.h"
#include "catalog/pg_db_role_setting.h"
#include "catalog/pg_tablespace.h"
#include "catalog/indexing.h"
#include "catalog/storage_tablespace.h"
+#include "catalog/pg_profile.h"
#include "commands/tablespace.h"
+#include "datatype/timestamp.h"
#include "libpq/auth.h"
#include "libpq/hba.h"
@@ -49,6 +52,7 @@
#include "miscadmin.h"
#include "pgstat.h"
#include "postmaster/autovacuum.h"
+#include "postmaster/loginmonitor.h"
#include "postmaster/fts.h"
#include "postmaster/postmaster.h"
#include "replication/walsender.h"
@@ -388,9 +392,10 @@
* a way to recover from disabling all access to all databases, for
* example "UPDATE pg_database SET datallowconn = false;".
*
- * We do not enforce them for autovacuum worker processes either.
+ * We do not enforce them for autovacuum worker and login monitor processes
+ * either.
*/
- if (IsUnderPostmaster && !IsAutoVacuumWorkerProcess())
+ if (IsUnderPostmaster && !IsAutoVacuumWorkerProcess() && !IsLoginMonitorWorkerProcess())
{
/*
* Check that the database is currently allowing connections.
@@ -572,9 +577,9 @@
{
Assert(MaxBackends == 0);
- /* the extra unit accounts for the autovacuum launcher */
+ /* the extra unit accounts for the autovacuum launcher and login monitor */
MaxBackends = MaxConnections + autovacuum_max_workers + 1 +
- max_worker_processes + max_wal_senders;
+ login_monitor_max_processes /* Login Monitor */ + max_worker_processes + max_wal_senders;
/* internal error because the values were all checked previously */
if (MaxBackends > MAX_BACKENDS)
@@ -787,8 +792,8 @@
*/
before_shmem_exit(ShutdownPostgres, 0);
- /* The autovacuum launcher is done here */
- if (IsAutoVacuumLauncherProcess())
+ /* The autovacuum launcher and login monitor launcher is done here */
+ if (IsAutoVacuumLauncherProcess() || IsLoginMonitorLauncherProcess())
{
/* report this backend in the PgBackendStatus array */
pgstat_bestart();
@@ -833,10 +838,11 @@
* Perform client authentication if necessary, then figure out our
* postgres user ID, and see if we are a superuser.
*
- * In standalone mode and in autovacuum worker processes, we use a fixed
- * ID, otherwise we figure it out from the authenticated user name.
+ * In standalone mode, login monitor and in autovacuum worker processes,
+ * we use a fixed ID, otherwise we figure it out from the authenticated
+ * user name.
*/
- if (bootstrap || IsAutoVacuumWorkerProcess())
+ if (bootstrap || IsAutoVacuumWorkerProcess() || IsLoginMonitorWorkerProcess())
{
InitializeSessionUserIdStandalone();
am_superuser = true;
@@ -1205,7 +1211,7 @@
* process_startup_options parses the GUC.
*/
if (gp_maintenance_mode && Gp_role == GP_ROLE_DISPATCH &&
- !(am_superuser && gp_maintenance_conn))
+ !(am_superuser && gp_maintenance_conn) && !IsLoginMonitorWorkerProcess())
ereport(FATAL,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("maintenance mode: connected by superuser only")));
@@ -1252,7 +1258,7 @@
* This is SKIPPED when the database is in bootstrap mode or
* Is not UnderPostmaster.
*/
- if (!bootstrap && IsUnderPostmaster)
+ if (!bootstrap && IsUnderPostmaster && !IsLoginMonitorWorkerProcess())
{
cdb_setup();
on_proc_exit( cdb_cleanup, 0 );
@@ -1291,9 +1297,183 @@
}
/*
- * Initialize resource manager.
+ * Initialize resource manager. Login Monitor doesn't need to do.
*/
- InitResManager();
+ if (!IsLoginMonitorWorkerProcess())
+ InitResManager();
+
+ if (enable_password_profile &&
+ !(bootstrap || IsBackgroundWorker || IsLoginMonitorWorkerProcess() ||
+ IsAutoVacuumWorkerProcess() ||
+ am_mirror || !IsUnderPostmaster))
+ {
+ Datum new_record[Natts_pg_authid];
+ bool new_record_nulls[Natts_pg_authid];
+ bool new_record_repl[Natts_pg_authid];
+ Relation pg_authid_rel;
+ Relation pg_profile_rel;
+ TupleDesc pg_authid_dsc;
+ HeapTuple auth_tuple;
+ HeapTuple profile_tuple;
+ HeapTuple new_tuple;
+ Form_pg_authid authidform;
+ Form_pg_profile profileform;
+ bool role_enable_profile;
+ bool account_status_isnull;
+ bool role_lock_date_isnull;
+ bool profile_isnull;
+ bool failed_attempts_isnull;
+ int16_t account_status;
+ TimestampTz role_lock_date;
+ int32 prf_password_lock_time;
+ int32 failed_login_attempts;
+ int32 prf_failed_login_attempts;
+ TimestampTz password_lock_time_usec;
+ Oid profileid;
+
+ /*
+ * Check whether the account is locked and update related catalog.
+ */
+ pg_authid_rel = table_open(AuthIdRelationId, AccessShareLock);
+ pg_authid_dsc = RelationGetDescr(pg_authid_rel);
+
+ auth_tuple = SearchSysCache1(AUTHNAME, CStringGetDatum(username));
+
+ if (!HeapTupleIsValid(auth_tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("user \"%s\" does not exist", username)));
+
+ authidform = (Form_pg_authid) GETSTRUCT(auth_tuple);
+ role_enable_profile = authidform->rolenableprofile;
+
+ /*
+ * Only when role is enable to use profile, we should process that when login successful.
+ */
+ if (role_enable_profile)
+ {
+ account_status = SysCacheGetAttr(AUTHNAME, auth_tuple,
+ Anum_pg_authid_rolaccountstatus, &account_status_isnull);
+ Assert(!account_status_isnull);
+
+ role_lock_date = SysCacheGetAttr(AUTHNAME, auth_tuple,
+ Anum_pg_authid_rollockdate, &role_lock_date_isnull);
+
+ profileid = SysCacheGetAttr(AUTHNAME, auth_tuple,
+ Anum_pg_authid_rolprofile, &profile_isnull);
+ Assert(!profile_isnull);
+
+ failed_login_attempts = SysCacheGetAttr(AUTHNAME, auth_tuple,
+ Anum_pg_authid_rolfailedlogins, &failed_attempts_isnull);
+ Assert(!failed_attempts_isnull);
+
+ /*
+ * If last login is successful, we don't need to update catalog table.
+ */
+ if (!(failed_login_attempts == 0 && account_status == ROLE_ACCOUNT_STATUS_OPEN))
+ {
+ /*
+ * In here, to avoid updating table concurrently which will cause
+ * connection failed when connect to database concurrently, we use
+ * self exclusive lock ShareUpdateExclusiveLock.
+ */
+ pg_authid_rel = table_open(AuthIdRelationId, ShareUpdateExclusiveLock);
+ pg_profile_rel = table_open(ProfileRelationId, AccessShareLock);
+ profile_tuple = SearchSysCache1(PROFILEID, ObjectIdGetDatum(profileid));
+
+ /*
+ * Build an updated tuple, perusing the information just obtained
+ */
+ MemSet(new_record, 0, sizeof(new_record));
+ MemSet(new_record_nulls, true, sizeof(new_record_nulls));
+ MemSet(new_record_repl, false, sizeof(new_record_repl));
+
+ if (!HeapTupleIsValid(profile_tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("profile \"%d\" does not exist", profileid)));
+ profileform = (Form_pg_profile) GETSTRUCT(profile_tuple);
+
+ /*
+ * Transform password_lock_time and failed_login_attempts to
+ * normal value if it's PROFILE_DEFAULT or PROFILE_UNLIMITED.
+ */
+ prf_password_lock_time = tranformProfileValueToNormal(profileform->prfpasswordlocktime,
+ Anum_pg_profile_prfpasswordlocktime);
+ prf_failed_login_attempts = tranformProfileValueToNormal(profileform->prffailedloginattempts,
+ Anum_pg_profile_prffailedloginattempts);
+
+ /*
+ * Transform lock time format from days to milliseconds
+ */
+ password_lock_time_usec = prf_password_lock_time * USECS_PER_HOUR;
+
+ if ((account_status == ROLE_ACCOUNT_STATUS_OPEN &&
+ failed_login_attempts < prf_failed_login_attempts) ||
+ (!role_lock_date_isnull && account_status == ROLE_ACCOUNT_STATUS_LOCKED_TIMED &&
+ role_lock_date + password_lock_time_usec < GetCurrentTimestamp()))
+ {
+ /* update pg_authid.rolfailedlogins to 0 */
+ new_record[Anum_pg_authid_rolfailedlogins - 1] = Int32GetDatum(0);
+ new_record_nulls[Anum_pg_authid_rolfailedlogins - 1] = false;
+ new_record_repl[Anum_pg_authid_rolfailedlogins - 1] = true;
+
+
+ if (account_status == ROLE_ACCOUNT_STATUS_LOCKED_TIMED)
+ {
+ /* update pg_authid.rolaccountstatus to 'OPEN' */
+ new_record[Anum_pg_authid_rolaccountstatus - 1] =
+ Int32GetDatum(ROLE_ACCOUNT_STATUS_OPEN);
+ new_record_nulls[Anum_pg_authid_rolaccountstatus - 1] = false;
+ new_record_repl[Anum_pg_authid_rolaccountstatus - 1] = true;
+ }
+
+ new_tuple = heap_modify_tuple(auth_tuple, pg_authid_dsc, new_record,
+ new_record_nulls, new_record_repl);
+ CatalogTupleUpdate(pg_authid_rel, &auth_tuple->t_self, new_tuple);
+
+ InvokeObjectPostAlterHook(AuthIdRelationId, profileid, 0);
+ }
+ else if (account_status == ROLE_ACCOUNT_STATUS_OPEN ||
+ account_status == ROLE_ACCOUNT_STATUS_LOCKED_TIMED)
+ {
+ /* Update pg_authid.rolaccountstatus to ROLE_ACCOUNT_STATUS_LOCKED.
+ * This will occur when ALTER PROFILE change failed_login_attempts.
+ */
+ if (account_status == ROLE_ACCOUNT_STATUS_OPEN)
+ {
+ new_record[Anum_pg_authid_rolaccountstatus - 1] =
+ Int32GetDatum(ROLE_ACCOUNT_STATUS_LOCKED);
+ new_record_nulls[Anum_pg_authid_rolaccountstatus - 1] =
+ false;
+ new_record_repl[Anum_pg_authid_rolaccountstatus - 1] =
+ true;
+
+ new_tuple = heap_modify_tuple(auth_tuple, pg_authid_dsc, new_record,
+ new_record_nulls, new_record_repl);
+ CatalogTupleUpdate(pg_authid_rel, &auth_tuple->t_self, new_tuple);
+
+ InvokeObjectPostAlterHook(AuthIdRelationId, profileid, 0);
+ }
+
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION),
+ errmsg("Can't login in role \"%s\", the account is locking.",
+ username)));
+ }
+
+ table_close(pg_profile_rel, NoLock);
+ table_close(pg_authid_rel, NoLock);
+ ReleaseSysCache(profile_tuple);
+ }
+ }
+
+ table_close(pg_authid_rel, NoLock);
+ ReleaseSysCache(auth_tuple);
+
+ CommitTransactionCommand();
+ return;
+ }
/* close the transaction we started above */
if (!bootstrap)
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index e9881d2..c6b413a 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -46,6 +46,7 @@
#include "access/xlog_internal.h"
#include "catalog/namespace.h"
#include "catalog/pg_authid.h"
+#include "catalog/pg_profile.h"
#include "catalog/storage.h"
#include "commands/async.h"
#include "commands/prepare.h"
@@ -74,6 +75,7 @@
#include "postmaster/bgworker_internals.h"
#include "postmaster/bgwriter.h"
#include "postmaster/fts.h"
+#include "postmaster/loginmonitor.h"
#include "postmaster/postmaster.h"
#include "postmaster/syslogger.h"
#include "postmaster/walwriter.h"
@@ -1276,6 +1278,16 @@
},
{
+ {"enable_password_profile", PGC_POSTMASTER, CONN_AUTH_SETTINGS,
+ gettext_noop("Use profile for password authentication security."),
+ NULL
+ },
+ &enable_password_profile,
+ true,
+ NULL, NULL, NULL
+ },
+
+ {
{"fsync", PGC_SIGHUP, WAL_SETTINGS,
gettext_noop("Forces synchronization of updates to disk."),
gettext_noop("The server will use the fsync() system call in several places to make "
@@ -12457,7 +12469,7 @@
static bool
check_maxconnections(int *newval, void **extra, GucSource source)
{
- if (*newval + autovacuum_max_workers + 1 +
+ if (*newval + autovacuum_max_workers + 1 + login_monitor_max_processes /* Login Monitor */ +
max_worker_processes + max_wal_senders > MAX_BACKENDS)
return false;
return true;
@@ -12475,7 +12487,7 @@
static bool
check_max_wal_senders(int *newval, void **extra, GucSource source)
{
- if (MaxConnections + autovacuum_max_workers + 1 +
+ if (MaxConnections + autovacuum_max_workers + 1 + login_monitor_max_processes /* Login Monitor */ +
max_worker_processes + *newval > MAX_BACKENDS)
return false;
return true;
@@ -12507,7 +12519,7 @@
static bool
check_max_worker_processes(int *newval, void **extra, GucSource source)
{
- if (MaxConnections + autovacuum_max_workers + 1 +
+ if (MaxConnections + autovacuum_max_workers + 1 + login_monitor_max_processes /* Login Monitor */ +
*newval + max_wal_senders > MAX_BACKENDS)
return false;
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 9b29fc7..a80f80a 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -282,6 +282,7 @@
static void set_info_version(void);
static void setup_schema(FILE *cmdfd);
static void setup_cdb_schema(FILE *cmdfd);
+static void setup_password_history(FILE *cmdfd);
static void load_plpgsql(FILE *cmdfd);
static void vacuum_db(FILE *cmdfd);
static void make_template0(FILE *cmdfd);
@@ -1679,6 +1680,8 @@
"INSERT INTO pg_depend SELECT 0,0,0, tableoid,oid,0, 'p' "
" FROM pg_foreign_server;\n\n",
"INSERT INTO pg_shdepend SELECT 0,0,0,0, tableoid,oid, 'p' "
+ " FROM pg_profile;\n\n",
+ "INSERT INTO pg_shdepend SELECT 0,0,0,0, tableoid,oid, 'p' "
" FROM pg_resgroup;\n\n",
"INSERT INTO pg_shdepend SELECT 0,0,0,0, tableoid,oid, 'p' "
" FROM pg_resourcetype;\n\n",
@@ -1974,6 +1977,26 @@
escape_quotes(features_file));
}
+/*
+ * set up the password history table
+ */
+static void
+setup_password_history(FILE *cmdfd)
+{
+ const char *const *line;
+ static const char *const pg_password_history_setup[] = {
+ /*
+ * The password history table shouldn't be readable except through views, to
+ * ensure passwords are not publicly visible.
+ */
+ "REVOKE ALL ON pg_password_history FROM public;\n\n",
+ NULL
+ };
+
+ for (line = pg_password_history_setup; *line != NULL; line++)
+ PG_CMD_PUTS(*line);
+}
+
static int
cmpstringp(const void *p1, const void *p2)
{
@@ -3208,6 +3231,8 @@
setup_run_file(cmdfd, dictionary_file);
+ setup_password_history(cmdfd);
+
setup_privileges(cmdfd);
setup_schema(cmdfd);
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index 0ccba3d..38e6d32 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -43,6 +43,8 @@
static void dropRoles(PGconn *conn);
static void dumpRoles(PGconn *conn);
static void dumpRoleMembership(PGconn *conn);
+static void dumpProfiles(PGconn *conn);
+static void dumpPasswordHistory(PGconn *conn);
static void dropTablespaces(PGconn *conn);
static void dumpTablespaces(PGconn *conn);
static void dropDBs(PGconn *conn);
@@ -96,8 +98,12 @@
static int on_conflict_do_nothing = 0;
static char role_catalog[10];
+static char profile_catalog[12];
+static char password_history_catalog[20];
#define PG_AUTHID "pg_authid"
#define PG_ROLES "pg_roles "
+#define PG_PROFILES "pg_profile"
+#define PG_PASSWORD_HISTORY "pg_password_history"
static FILE *OPF;
static char *filename = NULL;
@@ -457,6 +463,9 @@
else
sprintf(role_catalog, "%s", PG_AUTHID);
+ sprintf(profile_catalog, "%s", PG_PROFILES);
+ sprintf(password_history_catalog, "%s", PG_PASSWORD_HISTORY);
+
/* Add long options to the pg_dump argument list */
if (binary_upgrade)
appendPQExpBufferStr(pgdumpopts, " --binary-upgrade");
@@ -652,6 +661,9 @@
if (resource_groups)
dumpResGroups(conn);
+ /* Dump role profile */
+ dumpProfiles(conn);
+
/* Dump roles (users) */
dumpRoles(conn);
@@ -660,6 +672,9 @@
/* Dump role constraints */
dumpRoleConstraints(conn);
+
+ /* Dump role password history */
+ dumpPasswordHistory(conn);
}
/* Dump tablespaces */
@@ -1182,6 +1197,13 @@
i_rolvaliduntil,
i_rolreplication,
i_rolbypassrls,
+ i_rolenableprofile,
+ i_rolprofile,
+ i_rolaccountstatus,
+ i_rolfailedlogins,
+ i_rolpasswordsetat,
+ i_rollockdate,
+ i_rolpasswordexpire,
i_rolcomment,
i_rolqueuename = -1, /* keep compiler quiet */
i_rolgroupname = -1, /* keep compiler quiet */
@@ -1210,7 +1232,24 @@
*/
/* note: rolconfig is dumped later */
- if (server_version >= 90600)
+ if (server_version >= 140000)
+ {
+ printfPQExpBuffer(buf,
+ "SELECT %s.oid, rolname, rolsuper, rolinherit, "
+ "rolcreaterole, rolcreatedb, "
+ "rolcanlogin, rolconnlimit, rolpassword, "
+ "rolvaliduntil, rolreplication, rolbypassrls, "
+ "rolenableprofile, prfname, rolaccountstatus, rolfailedlogins, "
+ "rolpasswordsetat, rollockdate, rolpasswordexpire, "
+ "pg_catalog.shobj_description(%s.oid, '%s') as rolcomment, "
+ "rolname = current_user AS is_current_user "
+ " %s %s %s %s"
+ "FROM %s, %s "
+ "WHERE rolname !~ '^pg_' and %s.rolprofile = %s.oid "
+ "ORDER BY 2", role_catalog, role_catalog, role_catalog, resq_col, resgroup_col, extauth_col,
+ hdfs_col, role_catalog, profile_catalog, role_catalog, profile_catalog);
+ }
+ else if (server_version >= 90600)
printfPQExpBuffer(buf,
"SELECT oid, rolname, rolsuper, rolinherit, "
"rolcreaterole, rolcreatedb, "
@@ -1317,6 +1356,13 @@
i_rolvaliduntil = PQfnumber(res, "rolvaliduntil");
i_rolreplication = PQfnumber(res, "rolreplication");
i_rolbypassrls = PQfnumber(res, "rolbypassrls");
+ i_rolprofile = PQfnumber(res, "prfname");
+ i_rolenableprofile = PQfnumber(res, "rolenableprofile");
+ i_rolaccountstatus = PQfnumber(res, "rolaccountstatus");
+ i_rolfailedlogins = PQfnumber(res, "rolfailedlogins");
+ i_rolpasswordsetat = PQfnumber(res, "rolpasswordsetat");
+ i_rollockdate = PQfnumber(res, "rollockdate");
+ i_rolpasswordexpire = PQfnumber(res, "rolpasswordexpire");
i_rolcomment = PQfnumber(res, "rolcomment");
i_is_current_user = PQfnumber(res, "is_current_user");
@@ -1418,13 +1464,6 @@
appendPQExpBuffer(buf, " CONNECTION LIMIT %s",
PQgetvalue(res, i, i_rolconnlimit));
-
- if (!PQgetisnull(res, i, i_rolpassword) && !no_role_passwords)
- {
- appendPQExpBufferStr(buf, " PASSWORD ");
- appendStringLiteralConn(buf, PQgetvalue(res, i, i_rolpassword), conn);
- }
-
if (!PQgetisnull(res, i, i_rolvaliduntil))
appendPQExpBuffer(buf, " VALID UNTIL '%s'",
PQgetvalue(res, i, i_rolvaliduntil));
@@ -1484,6 +1523,48 @@
"ROLE", rolename,
buf);
+ appendPQExpBuffer(buf, "ALTER ROLE %s WITH ", fmtId(rolename));
+ appendPQExpBuffer(buf, "PROFILE %s; \n", fmtId(PQgetvalue(res, i, i_rolprofile)));
+
+ appendPQExpBuffer(buf, "SET allow_system_table_mods = true;\n");
+
+ appendPQExpBuffer(buf, "UPDATE pg_authid SET "
+ "rolenableprofile = \'%s\', "
+ "rolaccountstatus = %s, "
+ "rolfailedlogins = %s",
+ PQgetvalue(res, i, i_rolenableprofile),
+ PQgetvalue(res, i, i_rolaccountstatus),
+ PQgetvalue(res, i, i_rolfailedlogins));
+
+ if (!PQgetisnull(res, i, i_rolpassword) && !no_role_passwords)
+ {
+ appendPQExpBuffer(buf, ", rolpassword = \'%s\'",
+ PQgetvalue(res, i, i_rolpassword));
+ }
+
+ if (!PQgetisnull(res, i, i_rolpasswordsetat))
+ {
+ appendPQExpBuffer(buf, ", rolpasswordsetat = \'%s\'",
+ PQgetvalue(res, i, i_rolpasswordsetat));
+ }
+
+ if (!PQgetisnull(res, i, i_rollockdate))
+ {
+ appendPQExpBuffer(buf, ", rollockdate = \'%s\'",
+ PQgetvalue(res, i, i_rollockdate));
+ }
+
+ if (!PQgetisnull(res, i, i_rolpasswordexpire))
+ {
+ appendPQExpBuffer(buf, ", rolpasswordexpire = \'%s\'",
+ PQgetvalue(res, i, i_rolpasswordexpire));
+ }
+
+ appendPQExpBuffer(buf, " WHERE oid = %s; \n",
+ PQgetvalue(res, i, i_oid));
+
+ appendPQExpBuffer(buf, "RESET allow_system_table_mods;\n");
+
fprintf(OPF, "%s", buf->data);
}
@@ -1909,6 +1990,271 @@
}
/*
+ * Dump profiles
+ */
+static void
+dumpProfiles(PGconn *conn)
+{
+ PQExpBuffer buf = createPQExpBuffer();
+ PGresult *res;
+ int i_oid,
+ i_prfname,
+ i_prffailedloginattempts,
+ i_prfpasswordlocktime,
+ i_prfpasswordlifetime,
+ i_prfpasswordgracetime,
+ i_prfpasswordreusetime,
+ i_prfpasswordreusemax,
+ i_prfpasswordallowhashed,
+ i_prfpasswordverifyfuncdb,
+ i_prfpasswordverifyfunc,
+ i_prfcomment;
+ int i;
+
+ if (server_version < 90600)
+ return;
+
+ printfPQExpBuffer(buf,
+ "SELECT oid, prfname, prffailedloginattempts, "
+ "prfpasswordlocktime, prfpasswordlifetime, prfpasswordgracetime, "
+ "prfpasswordreusetime, prfpasswordreusemax, prfpasswordallowhashed, "
+ "prfpasswordverifyfuncdb, prfpasswordverifyfunc, "
+ "pg_catalog.shobj_description(oid, '%s') as prfcomment "
+ "FROM %s "
+ "ORDER BY 2", profile_catalog, profile_catalog);
+
+ res = executeQuery(conn, buf->data);
+
+ i_oid = PQfnumber(res, "oid");
+ i_prfname = PQfnumber(res, "prfname");
+ i_prffailedloginattempts = PQfnumber(res, "prffailedloginattempts");
+ i_prfpasswordlocktime = PQfnumber(res, "prfpasswordlocktime");
+ i_prfpasswordlifetime = PQfnumber(res, "prfpasswordlifetime");
+ i_prfpasswordgracetime = PQfnumber(res, "prfpasswordgracetime");
+ i_prfpasswordreusetime = PQfnumber(res, "prfpasswordreusetime");
+ i_prfpasswordreusemax = PQfnumber(res, "prfpasswordreusemax");
+ i_prfpasswordallowhashed = PQfnumber(res, "prfpasswordallowhashed");
+ i_prfpasswordverifyfuncdb = PQfnumber(res, "prfpasswordverifyfuncdb");
+ i_prfpasswordverifyfunc = PQfnumber(res, "prfpasswordverifyfunc");
+ i_prfcomment = PQfnumber(res, "prfcomment");
+
+ if (PQntuples(res) > 0)
+ fprintf(OPF, "--\n-- Profiles\n--\n\n");
+
+ for (i = 0; i < PQntuples(res); i++)
+ {
+ const char *prfname;
+ Oid profile_oid;
+
+ profile_oid = atooid(PQgetvalue(res, i, i_oid));
+ prfname = PQgetvalue(res, i, i_prfname);
+
+ resetPQExpBuffer(buf);
+
+ /*
+ * We dump CREATE PROFILE followed by ALTER PROFILE to ensure that the
+ * profile acquire the right properties even if it already exists(ie,
+ * it won't hurt for the CREATE to fail). Notice, for default profife,
+ * we don't need to CREATE PROFILE while ALTER PROFILE is still needed.
+ */
+ if (profile_oid != 10140 && !binary_upgrade)
+ appendPQExpBuffer(buf, "CREATE PROFILE %s;\n", fmtId(prfname));
+ appendPQExpBuffer(buf, "ALTER PROFILE %s LIMIT", fmtId(prfname));
+
+ if (profile_oid != 10140)
+ {
+ if (strcmp(PQgetvalue(res, i, i_prffailedloginattempts), "-1") != 0)
+ {
+ appendPQExpBuffer(buf, " FAILED_LOGIN_ATTEMPTS %s",
+ PQgetvalue(res, i, i_prffailedloginattempts));
+ }
+
+ if (strcmp(PQgetvalue(res, i, i_prfpasswordlocktime), "-1") != 0)
+ {
+ appendPQExpBuffer(buf, " PASSWORD_LOCK_TIME %s",
+ PQgetvalue(res, i, i_prfpasswordlocktime));
+ }
+
+ if (strcmp(PQgetvalue(res, i, i_prfpasswordlifetime), "-1") != 0)
+ {
+ appendPQExpBuffer(buf, " PASSWORD_LIFE_TIME %s",
+ PQgetvalue(res, i, i_prfpasswordlifetime));
+ }
+
+ if (strcmp(PQgetvalue(res, i, i_prfpasswordgracetime), "-1") != 0)
+ {
+ appendPQExpBuffer(buf, " PASSWORD_GRACE_TIME %s",
+ PQgetvalue(res, i, i_prfpasswordgracetime));
+ }
+
+ if (strcmp(PQgetvalue(res, i, i_prfpasswordreusetime), "-1") != 0)
+ {
+ appendPQExpBuffer(buf, " PASSWORD_REUSE_TIME %s",
+ PQgetvalue(res, i, i_prfpasswordreusetime));
+ }
+
+ if (strcmp(PQgetvalue(res, i, i_prfpasswordreusemax), "-1") != 0)
+ {
+ appendPQExpBuffer(buf, " PASSWORD_REUSE_MAX %s",
+ PQgetvalue(res, i, i_prfpasswordreusemax));
+ }
+
+ if (strcmp(PQgetvalue(res, i, i_prfpasswordallowhashed), "-1") != 0)
+ {
+ appendPQExpBuffer(buf, " PASSWORD_ALLOW_HASHED %s",
+ PQgetvalue(res, i, i_prfpasswordallowhashed));
+ }
+
+ if (!PQgetisnull(res, i, i_prfpasswordverifyfuncdb))
+ {
+ appendPQExpBuffer(buf, " PASSWORD_VERIFY_FUNCDB %s",
+ PQgetvalue(res, i, i_prfpasswordverifyfuncdb));
+ }
+
+ if (!PQgetisnull(res, i, i_prfpasswordverifyfunc))
+ {
+ appendPQExpBuffer(buf, " PASSWORD_VERIFY_FUNC %s",
+ PQgetvalue(res, i, i_prfpasswordverifyfunc));
+ }
+ }
+ else
+ {
+ if (strcmp(PQgetvalue(res, i, i_prffailedloginattempts), "-2") != 0)
+ {
+ appendPQExpBuffer(buf, " FAILED_LOGIN_ATTEMPTS %s",
+ PQgetvalue(res, i, i_prffailedloginattempts));
+ }
+
+ if (strcmp(PQgetvalue(res, i, i_prfpasswordlocktime), "-2") != 0)
+ {
+ appendPQExpBuffer(buf, " PASSWORD_LOCK_TIME %s",
+ PQgetvalue(res, i, i_prfpasswordlocktime));
+ }
+
+ if (strcmp(PQgetvalue(res, i, i_prfpasswordlifetime), "-2") != 0)
+ {
+ appendPQExpBuffer(buf, " PASSWORD_LIFE_TIME %s",
+ PQgetvalue(res, i, i_prfpasswordlifetime));
+ }
+
+ if (strcmp(PQgetvalue(res, i, i_prfpasswordgracetime), "-2") != 0)
+ {
+ appendPQExpBuffer(buf, " PASSWORD_GRACE_TIME %s",
+ PQgetvalue(res, i, i_prfpasswordgracetime));
+ }
+
+ if (strcmp(PQgetvalue(res, i, i_prfpasswordreusetime), "-2") != 0)
+ {
+ appendPQExpBuffer(buf, " PASSWORD_REUSE_TIME %s",
+ PQgetvalue(res, i, i_prfpasswordreusetime));
+ }
+
+ if (strcmp(PQgetvalue(res, i, i_prfpasswordreusemax), "-2") != 0)
+ {
+ appendPQExpBuffer(buf, " PASSWORD_REUSE_MAX %s",
+ PQgetvalue(res, i, i_prfpasswordreusemax));
+ }
+
+ if (strcmp(PQgetvalue(res, i, i_prfpasswordallowhashed), "1") != 0)
+ {
+ appendPQExpBuffer(buf, " PASSWORD_ALLOW_HASHED %s",
+ PQgetvalue(res, i, i_prfpasswordallowhashed));
+ }
+
+ if (!PQgetisnull(res, i, i_prfpasswordverifyfuncdb))
+ {
+ appendPQExpBuffer(buf, " PASSWORD_VERIFY_FUNCDB %s",
+ PQgetvalue(res, i, i_prfpasswordverifyfuncdb));
+ }
+
+ if (!PQgetisnull(res, i, i_prfpasswordverifyfunc))
+ {
+ appendPQExpBuffer(buf, " PASSWORD_VERIFY_FUNC %s",
+ PQgetvalue(res, i, i_prfpasswordverifyfunc));
+ }
+ }
+
+ appendPQExpBufferStr(buf, ";\n");
+
+ if (!no_comments && !PQgetisnull(res, i, i_prfcomment))
+ {
+ appendPQExpBuffer(buf, "COMMENT ON PROFILE %s IS ", fmtId(prfname));
+ appendStringLiteralConn(buf, PQgetvalue(res, i, i_prfcomment), conn);
+ appendPQExpBufferStr(buf, ";\n");
+ }
+
+ fprintf(OPF, "%s", buf->data);;
+ }
+
+ PQclear(res);
+
+ fprintf(OPF, "\n\n");
+
+ destroyPQExpBuffer(buf);
+}
+
+/*
+ * Dump role password history
+ */
+static void
+dumpPasswordHistory(PGconn *conn)
+{
+ PQExpBuffer buf = createPQExpBuffer();
+ PGresult *res;
+ int i_passhistroleid,
+ i_passhistpasswordsetat,
+ i_passhistpassword;
+ int i;
+
+ if (server_version < 90600)
+ return;
+
+ printfPQExpBuffer(buf,
+ "SELECT passhistroleid, passhistpasswordsetat, passhistpassword "
+ "FROM %s "
+ "ORDER BY 1, 2; \n", password_history_catalog);
+
+ res = executeQuery(conn, buf->data);
+
+ i_passhistroleid = PQfnumber(res, "passhistroleid");
+ i_passhistpasswordsetat = PQfnumber(res, "passhistpasswordsetat");
+ i_passhistpassword = PQfnumber(res, "passhistpassword");
+
+ resetPQExpBuffer(buf);
+
+ if (PQntuples(res) > 0)
+ {
+ fprintf(OPF, "--\n-- Password Histories\n--\n\n");
+ fprintf(OPF, "SET allow_system_table_mods = true;\n");
+ }
+
+
+ for (i = 0; i < PQntuples(res); i++)
+ {
+ appendPQExpBuffer(buf, "INSERT INTO pg_catalog.pg_password_history VALUES( "
+ "%s, \'%s\', \'%s\');\n",
+ PQgetvalue(res, i, i_passhistroleid),
+ PQgetvalue(res, i, i_passhistpasswordsetat),
+ PQgetvalue(res, i, i_passhistpassword));
+
+
+ fprintf(OPF, "%s", buf->data);
+
+ resetPQExpBuffer(buf);
+ }
+
+ appendPQExpBuffer(buf, "RESET allow_system_table_mods;\n");
+
+ fprintf(OPF, "%s", buf->data);
+
+ PQclear(res);
+
+ fprintf(OPF, "\n\n");
+
+ destroyPQExpBuffer(buf);
+}
+
+/*
* Find a list of database names that match the given patterns.
* See also expand_table_name_patterns() in pg_dump.c
*/
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 054f1a7..ac53161 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4688,6 +4688,11 @@
appendPQExpBufferStr(&buf, "\n, r.rolbypassrls");
}
+ if (isGPDB7000OrLater())
+ {
+ appendPQExpBufferStr(&buf, "\n, r.rolenableprofile");
+ }
+
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
if (!showSystem && !pattern)
@@ -4777,6 +4782,10 @@
if (strcmp(PQgetvalue(res, i, (verbose ? 11 : 10)), "t") == 0)
add_role_attribute(&buf, _("Bypass RLS"));
+ if (isGPDB7000OrLater())
+ if (strcmp(PQgetvalue(res, i, (verbose ? 15 : 14)), "t") == 0)
+ add_role_attribute(&buf, _("Enable Profile"));
+
conns = atoi(PQgetvalue(res, i, 6));
if (conns >= 0)
{
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 68f1353..b3a6a16 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -961,6 +961,10 @@
" FROM pg_catalog.pg_extension "\
" WHERE substring(pg_catalog.quote_ident(extname),1,%d)='%s'"
+#define Query_for_list_of_profiles \
+"SELECT pg_catalog.quote_ident(profilename) FROM pg_catalog.pg_profile "\
+" WHERE substring(pg_catalog.quote_ident(profilename),1,%d)='%s'"
+
#define Query_for_list_of_available_extensions \
" SELECT pg_catalog.quote_ident(name) "\
" FROM pg_catalog.pg_available_extensions "\
@@ -1113,6 +1117,7 @@
{"PARSER", Query_for_list_of_ts_parsers, NULL, NULL, THING_NO_SHOW},
{"POLICY", NULL, NULL, NULL},
{"PROCEDURE", NULL, NULL, Query_for_list_of_procedures},
+ {"PROFILE", Query_for_list_of_profiles},
{"PUBLICATION", NULL, Query_for_list_of_publications},
{"RESOURCE", NULL},
{"ROLE", Query_for_list_of_roles},
@@ -1670,6 +1675,12 @@
/* ALTER AGGREGATE,FUNCTION,PROCEDURE,ROUTINE <name> */
else if (Matches("ALTER", "AGGREGATE|FUNCTION|PROCEDURE|ROUTINE", MatchAny))
COMPLETE_WITH("(");
+ /* ALTER PROFILE <name> */
+ else if (Matches("ALTER", "PROFILE", MatchAny))
+ COMPLETE_WITH("LIMIT", "RENAME TO");
+ /* ALTER PROFILE <name> LIMIT */
+ else if (HeadMatches("ALTER", "PROFILE", MatchAny) && TailMatches("LIMIT"))
+ COMPLETE_WITH("FAILED_LOGIN_ATTEMPTS", "PASSWORD_REUSE_MAX", "PASSWORD_LOCK_TIME");
/* ALTER AGGREGATE <name> (...) */
else if (Matches("ALTER", "AGGREGATE", MatchAny, MatchAny))
{
@@ -1876,7 +1887,8 @@
"NOCREATEDB", "NOCREATEROLE", "NOINHERIT",
"NOLOGIN", "NOREPLICATION", "NOSUPERUSER", "PASSWORD",
"RENAME TO", "REPLICATION", "RESET", "SET", "SUPERUSER",
- "VALID UNTIL", "WITH");
+ "VALID UNTIL", "WITH", "PROFILE", "ENABLE PROFILE",
+ "DISABLE PROFILE", "ACCOUNT");
/* ALTER USER,ROLE <name> WITH */
else if (Matches("ALTER", "USER|ROLE", MatchAny, "WITH"))
@@ -1886,8 +1898,11 @@
"NOCREATEDB", "NOCREATEROLE", "NOINHERIT",
"NOLOGIN", "NOREPLICATION", "NOSUPERUSER", "PASSWORD",
"RENAME TO", "REPLICATION", "RESET", "SET", "SUPERUSER",
- "VALID UNTIL");
+ "VALID UNTIL", "PROFILE", "ENABLE PROFILE",
+ "DISABLE PROFILE", "ACCOUNT");
+ else if (Matches("ALTER", "USER|ROLE", MatchAny) && TailMatches("ACCOUNT"))
+ COMPLETE_WITH("LOCK", "UNLOCK");
/* ALTER DEFAULT PRIVILEGES */
else if (Matches("ALTER", "DEFAULT", "PRIVILEGES"))
COMPLETE_WITH("FOR ROLE", "IN SCHEMA");
@@ -2403,7 +2418,7 @@
"SCHEMA", "SEQUENCE", "STATISTICS", "SUBSCRIPTION",
"TABLE", "TYPE", "VIEW", "MATERIALIZED VIEW",
"COLUMN", "AGGREGATE", "FUNCTION",
- "PROCEDURE", "ROUTINE",
+ "PROCEDURE", "PROFILE", "ROUTINE",
"OPERATOR", "TRIGGER", "CONSTRAINT", "DOMAIN",
"LARGE OBJECT", "TABLESPACE", "TEXT SEARCH", "ROLE");
else if (Matches("COMMENT", "ON", "ACCESS", "METHOD"))
@@ -2993,7 +3008,8 @@
"NOCREATEDB", "NOCREATEROLE", "NOINHERIT",
"NOLOGIN", "NOREPLICATION", "NOSUPERUSER", "PASSWORD",
"REPLICATION", "ROLE", "SUPERUSER", "SYSID",
- "VALID UNTIL", "WITH");
+ "VALID UNTIL", "WITH", "PROFILE", "ENABLE PROFILE",
+ "DISABLE PROFILE", "ACCOUNT");
/* CREATE ROLE,USER,GROUP <name> WITH */
else if (Matches("CREATE", "ROLE|GROUP|USER", MatchAny, "WITH"))
@@ -3004,7 +3020,8 @@
"NOCREATEDB", "NOCREATEROLE", "NOINHERIT",
"NOLOGIN", "NOREPLICATION", "NOSUPERUSER", "PASSWORD",
"REPLICATION", "ROLE", "SUPERUSER", "SYSID",
- "VALID UNTIL");
+ "VALID UNTIL", "PROFILE", "ENABLE PROFILE",
+ "DISABLE PROFILE", "ACCOUNT");
/* complete CREATE ROLE,USER,GROUP <name> IN with ROLE,GROUP */
else if (Matches("CREATE", "ROLE|USER|GROUP", MatchAny, "IN"))
@@ -3018,6 +3035,13 @@
COMPLETE_WITH_LIST(list_CREATERESOURCEGROUP);
}
+
+ /* CREATE PROFILE <name> LIMIT */
+ else if (Matches("CREATE", "PROFILE", MatchAny))
+ COMPLETE_WITH("LIMIT");
+ else if (Matches("CREATE", "PROFILE", MatchAny, "LIMIT"))
+ COMPLETE_WITH("FAILED_LOGIN_ATTEMPTS", "PASSWORD_REUSE_MAX", "PASSWORD_LOCK_TIME");
+
/* CREATE/DROP RESOURCE GROUP */
else if (TailMatches("CREATE|DROP", "RESOURCE", "GROUP"))
COMPLETE_WITH_QUERY(Query_for_list_of_resgroups);
diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h
index 0df4b38..52e7825 100644
--- a/src/include/catalog/dependency.h
+++ b/src/include/catalog/dependency.h
@@ -73,6 +73,10 @@
* storage don't need this: they are protected by the existence of a physical
* file in the tablespace.)
*
+ * (f) a SHARED_DEPENDENCY_PROFILE entry means that the referenced object
+ * is a profile mentioned in a role object. The referenced object must be
+ * a pg_profile entry.
+ *
* SHARED_DEPENDENCY_INVALID is a value used as a parameter in internal
* routines, and is not valid in the catalog itself.
*/
@@ -83,6 +87,7 @@
SHARED_DEPENDENCY_ACL = 'a',
SHARED_DEPENDENCY_POLICY = 'r',
SHARED_DEPENDENCY_TABLESPACE = 't',
+ SHARED_DEPENDENCY_PROFILE = 'f',
SHARED_DEPENDENCY_INVALID = 0
} SharedDependencyType;
@@ -135,6 +140,8 @@
OCLASS_TRANSFORM, /* pg_transform */
/* GPDB additions */
+ OCLASS_PROFILE, /* pg_profile */
+ OCLASS_PASSWORDHISTORY, /* pg_password_history */
OCLASS_EXTPROTOCOL, /* pg_extprotocol */
OCLASS_TASK /* pg_task */
} ObjectClass;
@@ -278,4 +285,8 @@
const char *msg,
const char *hint);
+extern void recordProfileDependency(Oid roleId, Oid profileId);
+
+extern void changeProfileDependency(Oid roleId, Oid profileId);
+
#endif /* DEPENDENCY_H */
diff --git a/src/include/catalog/gp_indexing.h b/src/include/catalog/gp_indexing.h
index 642f695..2adacd3 100644
--- a/src/include/catalog/gp_indexing.h
+++ b/src/include/catalog/gp_indexing.h
@@ -22,6 +22,8 @@
#define AuthIdRolResQueueIndexId 6029
DECLARE_INDEX(pg_authid_rolresgroup_index, 6440, on pg_authid using btree(rolresgroup oid_ops));
#define AuthIdRolResGroupIndexId 6440
+DECLARE_INDEX(pg_authid_rolprofile_index, 6441, on pg_authid using btree(rolprofile oid_ops));
+#define AuthIdRolProfileIndexId 6441
DECLARE_INDEX(pg_auth_time_constraint_authid_index, 6449, on pg_auth_time_constraint using btree(authid oid_ops));
#define AuthTimeConstraintAuthIdIndexId 6449
diff --git a/src/include/catalog/oid_dispatch.h b/src/include/catalog/oid_dispatch.h
index 2baddd7..bd2fbc2 100644
--- a/src/include/catalog/oid_dispatch.h
+++ b/src/include/catalog/oid_dispatch.h
@@ -57,6 +57,8 @@
char *extname);
extern Oid GetNewOidForExtprotocol(Relation relation, Oid indexId, AttrNumber oidcolumn,
char *ptcname);
+extern Oid GetNewOidForProfile(Relation relation, Oid indexId, AttrNumber oidcolumn,
+ char *prfname);
extern Oid GetNewOidForForeignDataWrapper(Relation relation, Oid indexId, AttrNumber oidcolumn,
char *fdwname);
extern Oid GetNewOidForForeignServer(Relation relation, Oid indexId, AttrNumber oidcolumn,
diff --git a/src/include/catalog/pg_authid.h b/src/include/catalog/pg_authid.h
index 02bf0e9..484181c 100644
--- a/src/include/catalog/pg_authid.h
+++ b/src/include/catalog/pg_authid.h
@@ -42,6 +42,7 @@
bool rolreplication; /* role used for streaming replication */
bool rolbypassrls; /* bypasses row-level security? */
int32 rolconnlimit; /* max connections allowed (-1=no limit) */
+ bool rolenableprofile BKI_DEFAULT(f) BKI_FORCE_NOT_NULL; /* whether user can use profile */
/* remaining fields may be null; use heap_getattr to read them! */
#ifdef CATALOG_VARLEN /* variable-length fields start here */
@@ -52,6 +53,18 @@
* GP added fields
*/
+ Oid rolprofile BKI_DEFAULT(10140) BKI_FORCE_NOT_NULL; /* name of profile */
+
+ int16 rolaccountstatus BKI_DEFAULT(0) BKI_FORCE_NOT_NULL; /* status of account */
+
+ int32 rolfailedlogins BKI_DEFAULT(0) BKI_FORCE_NOT_NULL; /* number of failed logins */
+
+ timestamptz rolpasswordsetat BKI_DEFAULT(_null_); /* password set time, if any */
+
+ timestamptz rollockdate BKI_DEFAULT(_null_) BKI_FORCE_NULL; /* account lock time, if any */
+
+ timestamptz rolpasswordexpire BKI_DEFAULT(_null_) BKI_FORCE_NULL; /* account password expire time, if any */
+
/* ID of resource queue for this role */
Oid rolresqueue BKI_DEFAULT(6055);
@@ -69,6 +82,15 @@
#endif
} FormData_pg_authid;
+typedef enum
+{
+ ROLE_ACCOUNT_STATUS_OPEN,
+ ROLE_ACCOUNT_STATUS_LOCKED_TIMED,
+ ROLE_ACCOUNT_STATUS_LOCKED,
+ ROLE_ACCOUNT_STATUS_EXPIRED_GRACE,
+ ROLE_ACCOUNT_STATUS_EXPIRED
+} ROLE_ACCOUNT_STATUS;
+
/* GPDB added foreign key definitions for gpcheckcat. */
FOREIGN_KEY(rolresqueue REFERENCES pg_resqueue(oid));
FOREIGN_KEY(rolresgroup REFERENCES pg_resgroup(oid));
diff --git a/src/include/catalog/pg_password_history.h b/src/include/catalog/pg_password_history.h
new file mode 100644
index 0000000..b281296
--- /dev/null
+++ b/src/include/catalog/pg_password_history.h
@@ -0,0 +1,53 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_password_history.h
+ * definition of the "password history" system catalog (pg_password_history)
+ *
+ *
+ * Copyright (c) 2023, Cloudberry Database, HashData Technology Limited.
+ *
+ * src/include/catalog/pg_password_history.h
+ *
+ * NOTES
+ * The Catalog.pm module reads this file and derives schema
+ * information.
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef PG_PASSWORD_HISTORY_H
+#define PG_PASSWROD_HISTORY_H
+
+#include "catalog/genbki.h"
+#include "catalog/pg_password_history_d.h"
+
+/* ----------------
+ * pg_password_history definition. cpp turns this into
+ * typedef struct FormData_pg_password_history
+ * ----------------
+ */
+CATALOG(pg_password_history,10141,PasswordHistoryRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_OID(10142,PasswordHistoryRelation_Rowtype_Id) BKI_SCHEMA_MACRO
+{
+ Oid passhistroleid BKI_FORCE_NOT_NULL; /* oid of role */
+#ifdef CATALOG_VARLEN
+ timestamptz passhistpasswordsetat BKI_FORCE_NOT_NULL; /* password set time */
+ text passhistpassword BKI_FORCE_NOT_NULL; /* the history password */
+#endif
+} FormData_pg_password_history;
+
+/* ----------------
+ * Form_pg_password_history corresponds to a pointer to a tuple with
+ * the format of pg_password_history relation.
+ * ----------------
+ */
+typedef FormData_pg_password_history *Form_pg_password_history;
+
+DECLARE_TOAST(pg_password_history, 10143, 10144);
+#define PgPasswordHistoryToastTable 10143
+#define PgPasswordHistoryToastIndex 10144
+
+DECLARE_UNIQUE_INDEX(pg_password_history_role_password_index, 10145, on pg_password_history using btree(passhistroleid oid_ops, passhistpassword text_ops));
+#define PasswordHistoryRolePasswordIndexId 10145
+DECLARE_INDEX(pg_password_history_role_passwordsetat_index, 10146, on pg_password_history using btree(passhistroleid oid_ops, passhistpasswordsetat timestamptz_ops));
+#define PasswordHistoryRolePasswordsetatIndexId 10146
+
+#endif /* PG_PASSWORD_HISTORY_H */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index e72142f..63b4902 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12476,5 +12476,7 @@
{ oid => 7077, descr => 'Update gp_segment_configuration mode and status by dbid', proname => 'gp_update_segment_configuration_mode_status',
proisstrict => 'f', provolatile => 'v', proparallel => 'r', prorettype => 'int2', proargtypes => 'int4 char char', prosrc => 'gp_update_segment_configuration_mode_status'},
+{ oid => 7060, descr => 'get user account status',
+ proname => 'get_role_status', prorettype => 'cstring', proargtypes => 'cstring', prosrc => 'get_role_status' },
]
diff --git a/src/include/catalog/pg_profile.dat b/src/include/catalog/pg_profile.dat
new file mode 100644
index 0000000..d76b974
--- /dev/null
+++ b/src/include/catalog/pg_profile.dat
@@ -0,0 +1,28 @@
+#----------------------------------------------------------------------
+#
+# pg_profile.dat
+# Initial contents of the pg_profile system catalog.
+#
+# Copyright (c) 2023, Cloudberry Database, HashData Technology Limited.
+#
+# src/include/catalog/pg_profile.dat
+#
+#----------------------------------------------------------------------
+
+[
+
+# The C code typically refers to these roles using the #define symbols,
+# so make sure every entry has an oid_symbol value.
+
+# The bootstrap superuser is named POSTGRES according to this data and
+# according to BKI_DEFAULT entries in other catalogs. However, initdb
+# will replace that at database initialization time.
+
+{ oid => '10140', oid_symbol => 'DEFAULT_PROFILE',
+ descr => 'default profile',
+ prfname => 'pg_default', prffailedloginattempts => '-2', prfpasswordlocktime => '-2',
+ prfpasswordlifetime => '-2', prfpasswordgracetime => '-2', prfpasswordreusetime => '-2',
+ prfpasswordreusemax => '-2', prfpasswordallowhashed => '1', prfpasswordverifyfuncdb => '_null_',
+ prfpasswordverifyfunc => '_null_' }
+
+]
diff --git a/src/include/catalog/pg_profile.h b/src/include/catalog/pg_profile.h
new file mode 100644
index 0000000..8aba34f
--- /dev/null
+++ b/src/include/catalog/pg_profile.h
@@ -0,0 +1,78 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_profile.h
+ * definition of the "profile" system catalog (pg_profile)
+ *
+ *
+ * Copyright (c) 2023, Cloudberry Database, HashData Technology Limited.
+ *
+ * src/include/catalog/pg_profile.h
+ *
+ * NOTES
+ * The Catalog.pm module reads this file and derives schema
+ * information.
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef PG_PROFILE_H
+#define PG_PROFILE_H
+
+#include "catalog/genbki.h"
+#include "catalog/pg_profile_d.h"
+#include "parser/parse_node.h"
+
+/* ----------------
+ * pg_profile definition. cpp turns this into
+ * typedef struct FormData_pg_profile
+ * ----------------
+ */
+CATALOG(pg_profile,10135,ProfileRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_OID(10136,ProfileRelation_Rowtype_Id) BKI_SCHEMA_MACRO
+{
+ Oid oid BKI_FORCE_NOT_NULL; /* oid */
+ NameData prfname BKI_FORCE_NOT_NULL; /* name of profile */
+ int32 prffailedloginattempts BKI_DEFAULT(-1) BKI_FORCE_NOT_NULL; /* the number of failed login attempts */
+ int32 prfpasswordlocktime BKI_DEFAULT(-1) BKI_FORCE_NOT_NULL; /* the locking period */
+ int32 prfpasswordlifetime BKI_DEFAULT(-1) BKI_FORCE_NOT_NULL; /* the number of days that the current password is valid and usable */
+ int32 prfpasswordgracetime BKI_DEFAULT(-1) BKI_FORCE_NOT_NULL; /* the number of days an old password can still be used */
+ int32 prfpasswordreusetime BKI_DEFAULT(-1) BKI_FORCE_NOT_NULL; /* the number of days a user must wait before reusing a password */
+ int32 prfpasswordreusemax BKI_DEFAULT(-1) BKI_FORCE_NOT_NULL; /* the number of password changes that must occur before a password can be reused */
+ int32 prfpasswordallowhashed BKI_DEFAULT(-1) BKI_FORCE_NOT_NULL; /* whether an hash encrypted password is allowed to be used or not */
+ Oid prfpasswordverifyfuncdb BKI_FORCE_NULL; /* verify function for database when login */
+ Oid prfpasswordverifyfunc BKI_FORCE_NULL; /* verify function when login */
+} FormData_pg_profile;
+
+/* ----------------
+ * Form_pg_profile corresponds to a pointer to a tuple with
+ * the format of pg_profile relation.
+ * ----------------
+ */
+typedef FormData_pg_profile *Form_pg_profile;
+
+DECLARE_UNIQUE_INDEX(profile_name_index, 10137, on pg_profile using btree(prfname name_ops));
+#define ProfilePrfnameIndexId 10137
+DECLARE_UNIQUE_INDEX(profile_oid_index, 10138, on pg_profile using btree(oid oid_ops));
+#define ProfileOidIndexId 10138
+DECLARE_INDEX(profile_password_verify_function_index, 10139, on pg_profile using btree(prfpasswordverifyfuncdb oid_ops, prfpasswordverifyfunc oid_ops));
+#define ProfileVerifyFunctionIndexId 10139
+
+#define DefaultProfileOID 10140
+
+#define PROFILE_UNLIMITED -2
+#define PROFILE_DEFAULT -1
+#define PROFILE_MAX_VALID 9999
+
+extern char *
+ProfileGetNameByOid(Oid profileOid, bool noerr);
+extern Oid
+get_profile_oid(const char *prfname, bool missing_ok);
+extern ObjectAddress
+rename_profile(char *oldname, char *newname);
+extern Oid
+CreateProfile(ParseState *pstate, CreateProfileStmt *stmt);
+extern Oid
+AlterProfile(AlterProfileStmt *stmt);
+extern void
+DropProfile(DropProfileStmt *stmt);
+extern int32
+tranformProfileValueToNormal(int32 profile_val, int attoff);
+#endif /* PG_PROFILE_H */
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 07e375d..7447c3e 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -423,6 +423,8 @@
B_BG_WORKER,
B_BG_WRITER,
B_CHECKPOINTER,
+ B_LOGIN_MONITOR,
+ B_LOGIN_MONITOR_WORKER,
B_STARTUP,
B_WAL_RECEIVER,
B_WAL_SENDER,
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 0912902..e168fb8 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -445,6 +445,9 @@
T_CreateRoleStmt,
T_AlterRoleStmt,
T_DropRoleStmt,
+ T_CreateProfileStmt,
+ T_AlterProfileStmt,
+ T_DropProfileStmt,
T_CreateQueueStmt,
T_AlterQueueStmt,
T_DropQueueStmt,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 294e213..f2a7a52 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1931,6 +1931,7 @@
OBJECT_OPERATOR,
OBJECT_OPFAMILY,
OBJECT_POLICY,
+ OBJECT_PROFILE,
OBJECT_PROCEDURE,
OBJECT_PUBLICATION,
OBJECT_PUBLICATION_REL,
@@ -3130,6 +3131,27 @@
bool missing_ok; /* skip error if a role is missing? */
} DropRoleStmt;
+typedef struct CreateProfileStmt
+{
+ NodeTag type;
+ char *profile_name; /* profile name */
+ List *options; /* List of DefElem nodes */
+} CreateProfileStmt;
+
+typedef struct AlterProfileStmt
+{
+ NodeTag type;
+ char *profile_name; /* profile name */
+ List *options; /* List of DefElem nodes */
+} AlterProfileStmt;
+
+typedef struct DropProfileStmt
+{
+ NodeTag type;
+ List *profiles; /* List of profile name to remove */
+ bool missing_ok; /* skip error if a profile is missing? */
+} DropProfileStmt;
+
typedef struct DenyLoginPoint
{
NodeTag type;
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index a6e840f..a3fbbe7 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -29,6 +29,7 @@
PG_KEYWORD("abort", ABORT_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("absolute", ABSOLUTE_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("access", ACCESS, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("account", ACCOUNT, UNRESERVED_KEYWORD, BARE_LABEL) /* GPDB */
PG_KEYWORD("action", ACTION, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("active", ACTIVE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("add", ADD_P, UNRESERVED_KEYWORD, BARE_LABEL)
@@ -180,6 +181,7 @@
PG_KEYWORD("extension", EXTENSION, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("external", EXTERNAL, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("extract", EXTRACT, COL_NAME_KEYWORD, BARE_LABEL)
+PG_KEYWORD("failed_login_attempts", FAILED_LOGIN_ATTEMPTS, UNRESERVED_KEYWORD, BARE_LABEL) /* GPDB */
PG_KEYWORD("false", FALSE_P, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("family", FAMILY, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("fetch", FETCH, RESERVED_KEYWORD, AS_LABEL)
@@ -354,6 +356,8 @@
PG_KEYWORD("partitions", PARTITIONS, UNRESERVED_KEYWORD, BARE_LABEL) /* GPDB */
PG_KEYWORD("passing", PASSING, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("password", PASSWORD, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("password_lock_time", PASSWORD_LOCK_TIME, UNRESERVED_KEYWORD, BARE_LABEL) /* GPDB */
+PG_KEYWORD("password_reuse_max", PASSWORD_REUSE_MAX, UNRESERVED_KEYWORD, BARE_LABEL) /* GPDB */
PG_KEYWORD("percent", PERCENT, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("persistently", PERSISTENTLY, UNRESERVED_KEYWORD, BARE_LABEL) /* GPDB */
PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD, BARE_LABEL)
@@ -371,6 +375,7 @@
PG_KEYWORD("procedural", PROCEDURAL, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("procedure", PROCEDURE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("procedures", PROCEDURES, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("profile", PROFILE, UNRESERVED_KEYWORD, BARE_LABEL) /* GPDB */
PG_KEYWORD("program", PROGRAM, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("protocol", PROTOCOL, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("publication", PUBLICATION, UNRESERVED_KEYWORD, BARE_LABEL)
@@ -500,6 +505,7 @@
PG_KEYWORD("unique", UNIQUE, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("unknown", UNKNOWN, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("unlisten", UNLISTEN, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("unlock", UNLOCK_P, UNRESERVED_KEYWORD, BARE_LABEL) /* GPDB */
PG_KEYWORD("unlogged", UNLOGGED, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("until", UNTIL, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("update", UPDATE, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/include/postmaster/loginmonitor.h b/src/include/postmaster/loginmonitor.h
new file mode 100644
index 0000000..7834d89
--- /dev/null
+++ b/src/include/postmaster/loginmonitor.h
@@ -0,0 +1,36 @@
+/*-------------------------------------------------------------------------
+ *
+ * loginmonitor.h
+ * header file for integrated loginmonitor daemon
+ *
+ *
+ * Copyright (c) 2023, Cloudberry Database, HashData Technology Limited.
+ *
+ * src/include/postmaster/loginmonitor.h
+ *
+ * NOTES
+ * The Catalog.pm module reads this file and derives schema
+ * information.
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef LOGINMONITOR_H
+#define LOGINMONITOR_H
+
+#include "storage/block.h"
+
+extern int StartLoginMonitorLauncher(void);
+extern int StartLoginMonitorWorker(void);
+extern Size LoginMonitorShmemSize(void);
+extern void LoginMonitorShmemInit(void);
+extern void SendLoginFailedSignal(const char *curr_user_name);
+extern bool IsLoginMonitorWorkerProcess(void);
+extern bool IsLoginMonitorLauncherProcess(void);
+extern void HandleLoginFailed(void);
+extern void LoginMonitorWorkerFailed(void);
+
+#define IsAnyLoginMonitorProcess() \
+ (IsLoginMonitorLauncherProcess() || IsLoginMonitorWorkerProcess())
+
+extern int login_monitor_max_processes;
+#endif /* LOGINMONITOR_H */
diff --git a/src/include/postmaster/postmaster.h b/src/include/postmaster/postmaster.h
index 8d4d845..f9b48b3 100644
--- a/src/include/postmaster/postmaster.h
+++ b/src/include/postmaster/postmaster.h
@@ -32,6 +32,7 @@
extern char *bonjour_name;
extern bool restart_after_crash;
extern bool remove_temp_files_after_crash;
+extern bool enable_password_profile;
extern int terminal_fd;
diff --git a/src/include/storage/pmsignal.h b/src/include/storage/pmsignal.h
index b46abda..fab2192 100644
--- a/src/include/storage/pmsignal.h
+++ b/src/include/storage/pmsignal.h
@@ -45,6 +45,9 @@
PMSIGNAL_WAKEN_DTX_RECOVERY, /* wake up dtx recovery to abort dtx xacts */
PMSIGNAL_DTM_RECOVERED, /* distributed recovery completed */
+ PMSIGNAL_FAILED_LOGIN, /* send signal SIGUSR1 to login monitor launcher */
+ PMSIGNAL_START_LOGIN_MONITOR_WORKER, /* start a login monitor worker */
+
NUM_PMSIGNALS /* Must be last value of enum! */
} PMSignalReason;
diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h
index 59cabdf..dd6d712 100644
--- a/src/include/storage/proc.h
+++ b/src/include/storage/proc.h
@@ -414,6 +414,8 @@
PGPROC *freeProcs;
/* Head of list of autovacuum's free PGPROC structures */
PGPROC *autovacFreeProcs;
+ /* Head of list of login monitor free PGPROC structures */
+ PGPROC *lmFreeProcs;
/* Head of list of bgworker free PGPROC structures */
PGPROC *bgworkerFreeProcs;
/* Head of list of walsender free PGPROC structures */
diff --git a/src/include/storage/procsignal.h b/src/include/storage/procsignal.h
index 57862ae..0815460 100644
--- a/src/include/storage/procsignal.h
+++ b/src/include/storage/procsignal.h
@@ -47,6 +47,8 @@
PROCSIG_QUERY_FINISH, /* query finish */
PROCSIG_RESOURCE_GROUP_MOVE_QUERY, /* move query to a new resource group */
+ PROCSIG_FAILED_LOGIN, /* failed login */
+
NUM_PROCSIGNALS /* Must be last! */
} ProcSignalReason;
diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h
index 6146914..7428552 100644
--- a/src/include/tcop/cmdtaglist.h
+++ b/src/include/tcop/cmdtaglist.h
@@ -49,6 +49,7 @@
PG_CMDTAG(CMDTAG_ALTER_OPERATOR_FAMILY, "ALTER OPERATOR FAMILY", true, false, false)
PG_CMDTAG(CMDTAG_ALTER_POLICY, "ALTER POLICY", true, false, false)
PG_CMDTAG(CMDTAG_ALTER_PROCEDURE, "ALTER PROCEDURE", true, false, false)
+PG_CMDTAG(CMDTAG_ALTER_PROFILE, "ALTER PROFILE", false, false, false)
PG_CMDTAG(CMDTAG_ALTER_PROTOCOL, "ALTER PROTOCOL", true, false, false)
PG_CMDTAG(CMDTAG_ALTER_PUBLICATION, "ALTER PUBLICATION", true, false, false)
PG_CMDTAG(CMDTAG_ALTER_QUEUE, "ALTER QUEUE", false, false, false)
@@ -110,6 +111,7 @@
PG_CMDTAG(CMDTAG_CREATE_OPERATOR_FAMILY, "CREATE OPERATOR FAMILY", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_POLICY, "CREATE POLICY", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_PROCEDURE, "CREATE PROCEDURE", true, false, false)
+PG_CMDTAG(CMDTAG_CREATE_PROFILE, "CREATE PROFILE", false, false, false)
PG_CMDTAG(CMDTAG_CREATE_PROTOCOL, "CREATE PROTOCOL", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_PUBLICATION, "CREATE PUBLICATION", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_QUEUE, "CREATE QUEUE", false, false, false)
@@ -175,6 +177,7 @@
PG_CMDTAG(CMDTAG_DROP_OWNED, "DROP OWNED", true, false, false)
PG_CMDTAG(CMDTAG_DROP_POLICY, "DROP POLICY", true, false, false)
PG_CMDTAG(CMDTAG_DROP_PROCEDURE, "DROP PROCEDURE", true, false, false)
+PG_CMDTAG(CMDTAG_DROP_PROFILE, "DROP PROFILE", false, false, false)
PG_CMDTAG(CMDTAG_DROP_PROTOCOL, "DROP PROTOCOL", true, false, false)
PG_CMDTAG(CMDTAG_DROP_PUBLICATION, "DROP PUBLICATION", true, false, false)
diff --git a/src/include/utils/syscache.h b/src/include/utils/syscache.h
index c2ba8a8..f705c17 100644
--- a/src/include/utils/syscache.h
+++ b/src/include/utils/syscache.h
@@ -77,6 +77,8 @@
OPFAMILYAMNAMENSP,
OPFAMILYOID,
PARTRELID,
+ PROFILEID,
+ PROFILENAME,
PROCNAMEARGSNSP,
PROCOID,
PUBLICATIONNAME,
diff --git a/src/include/utils/unsync_guc_name.h b/src/include/utils/unsync_guc_name.h
index 67483db..d8593b5 100644
--- a/src/include/utils/unsync_guc_name.h
+++ b/src/include/utils/unsync_guc_name.h
@@ -144,6 +144,7 @@
"enable_partition_pruning",
"enable_partitionwise_aggregate",
"enable_partitionwise_join",
+ "enable_password_profile",
"enable_seqscan",
"enable_sort",
"enable_tidscan",
diff --git a/src/include/utils/wait_event.h b/src/include/utils/wait_event.h
index 295f487..863ed3a 100644
--- a/src/include/utils/wait_event.h
+++ b/src/include/utils/wait_event.h
@@ -61,6 +61,7 @@
#ifdef USE_INTERNAL_FTS
WAIT_EVENT_FTS_PROBE_MAIN,
#endif
+ WAIT_EVENT_LOGIN_MONITOR_LAUNCHER_MAIN,
WAIT_EVENT_GLOBAL_DEADLOCK_DETECTOR_MAIN
} WaitEventActivity;
@@ -145,7 +146,8 @@
,
WAIT_EVENT_DTX_RECOVERY,
WAIT_EVENT_SHAREINPUT_SCAN,
- WAIT_EVENT_INTERCONNECT
+ WAIT_EVENT_INTERCONNECT,
+ WAIT_EVENT_LOGINMONITOR_FINISH
} WaitEventIPC;
/* ----------
diff --git a/src/test/authentication/t/003_profile.pl b/src/test/authentication/t/003_profile.pl
new file mode 100644
index 0000000..33e0a7b
--- /dev/null
+++ b/src/test/authentication/t/003_profile.pl
@@ -0,0 +1,812 @@
+
+# Copyright (c) 2023, Cloudberry Database, HashData Technology Limited.
+
+# Test password profile feature.
+#
+# This test can only run with Unix-domain sockets.
+
+use strict;
+use warnings;
+use PostgresNode;
+use TestLib;
+use Test::More;
+if (!$use_unix_sockets)
+{
+ plan skip_all =>
+ "authentication tests cannot run without Unix-domain sockets";
+}
+else
+{
+ plan tests => 96;
+}
+
+# Delete pg_hba.conf from the given node, add a new entry to it
+# and then execute a reload to refresh it.
+sub reset_pg_hba
+{
+ my $node = shift;
+ my $hba_method = shift;
+
+ unlink($node->data_dir . '/pg_hba.conf');
+ $node->append_conf('pg_hba.conf', "local all all $hba_method");
+ $node->reload;
+ return;
+}
+
+# Test access for a single role, useful to wrap all tests into one.
+sub test_login
+{
+ local $Test::Builder::Level = $Test::Builder::Level + 1;
+
+ my $node = shift;
+ my $role = shift;
+ my $password = shift;
+ my $expected_res = shift;
+ my $status_string = 'failed';
+
+ $status_string = 'success' if ($expected_res eq 0);
+
+ my $connstr = "user=$role";
+ my $testname =
+ "authentication $status_string for role $role with password $password";
+
+ $ENV{"PGPASSWORD"} = $password;
+ if ($expected_res eq 0)
+ {
+ $node->connect_ok($connstr, $testname);
+ }
+ else
+ {
+ # No checks of the error message, only the status code.
+ $node->connect_fails($connstr, $testname);
+ }
+}
+
+# Initialize primary node. Force UTF-8 encoding, so that we can use non-ASCII
+# characters in the passwords below.
+my $node = get_new_node('primary');
+my ($ret, $stdout, $stderr);
+$node->init(extra => [ '--locale=C', '--encoding=UTF8' ]);
+$node->append_conf('postgresql.conf', "log_connections = on\n");
+$node->start;
+
+# Create test profiles.
+$node->safe_psql(
+ 'postgres',
+ "CREATE PROFILE myprofile1 LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1;
+CREATE PROFILE myprofile2 LIMIT FAILED_LOGIN_ATTEMPTS 2 PASSWORD_REUSE_MAX 2;
+CREATE PROFILE myprofile3 LIMIT FAILED_LOGIN_ATTEMPTS -1 PASSWORD_REUSE_MAX 0;
+CREATE PROFILE myprofile4 LIMIT FAILED_LOGIN_ATTEMPTS 1 PASSWORD_LOCK_TIME 2 PASSWORD_REUSE_MAX 1;",
+"");
+
+# Create test roles.
+$node->safe_psql(
+ 'postgres',
+ "SET password_encryption='scram-sha-256';
+SET client_encoding='utf8';
+CREATE USER profile_user1 LOGIN PASSWORD 'IX' ENABLE PROFILE;
+CREATE USER profile_user2 LOGIN PASSWORD 'a' DISABLE PROFILE;
+CREATE USER profile_user3 LOGIN PASSWORD E'\\xc2\\xaa' ENABLE PROFILE;
+CREATE USER profile_user4 LOGIN PASSWORD E'foo\\x07bar' DISABLE PROFILE;
+");
+
+
+# Test only super users have privileges of manipulating profile
+$node->safe_psql(
+ 'postgres',
+ "SET password_encryption='scram-sha-256';
+SET client_encoding='utf8';
+CREATE USER super_user SUPERUSER;
+CREATE USER normal_user;"
+);
+
+# Test CREATE PROFILE
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'super_user',
+ 'postgres',
+ "CREATE PROFILE test_profile1 LIMIT FAILED_LOGIN_ATTEMPTS 2;"
+);
+is($ret, 0, 'create profile succeed');
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'super_user',
+ 'postgres',
+ "CREATE PROFILE test_profile2 LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 2;"
+);
+is($ret, 0, 'create profile succeed');
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'normal_user',
+ 'postgres',
+ "CREATE PROFILE test_profile3 LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 2;"
+);
+is($ret, 3, 'non-super user can not create profile');
+like(
+ $stderr,
+ qr/permission denied to create profile, must be superuser/,
+ 'expected error from non-super user can not create profile'
+);
+
+# Test ALTER PROFILE
+$node->safe_psql(
+ 'postgres',
+ "ALTER PROFILE pg_default LIMIT FAILED_LOGIN_ATTEMPTS 4;"
+);
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'super_user',
+ 'postgres',
+ "ALTER PROFILE test_profile1 LIMIT FAILED_LOGIN_ATTEMPTS 3;"
+);
+is($ret, 0, 'alter profile succeed');
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'super_user',
+ 'postgres',
+ "ALTER PROFILE test_profile2 LIMIT FAILED_LOGIN_ATTEMPTS 2 PASSWORD_LOCK_TIME 1 PASSWORD_REUSE_MAX 2;"
+);
+is($ret, 0, 'alter profile succeed');
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'normal_user',
+ 'postgres',
+ "ALTER PROFILE test_profile1 LIMIT FAILED_LOGIN_ATTEMPTS 3;"
+);
+is($ret, 3, 'non-super user can not alter profile');
+like(
+ $stderr,
+ qr/permission denied to alter profile, must be superuser/,
+ 'expected error from non-super user can not alter profile'
+);
+
+# Test DROP PROFILE
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'super_user',
+ 'postgres',
+ "DROP PROFILE test_profile1;"
+);
+is($ret, 0, 'drop profile succeed');
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'normal_user',
+ 'postgres',
+ "DROP PROFILE test_profile2;"
+);
+is($ret, 3, 'non-super user can not drop profile');
+like(
+ $stderr,
+ qr/permission denied to drop profile, must be superuser/,
+ 'expected error from non-super user can not drop profile'
+);
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'super_user',
+ 'postgres',
+ "DROP PROFILE pg_default;"
+);
+is($ret, 3, 'can not drop default profile pg_default');
+like(
+ $stderr,
+ qr/Disallow to drop default profile/,
+ 'expected error from can not drop default profile pg_default'
+);
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'super_user',
+ 'postgres',
+ "DROP PROFILE test_profile2;"
+);
+is($ret, 0, 'drop profile succeed');
+
+# Test CREATE USER ... PROFILE
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'super_user',
+ 'postgres',
+ "CREATE USER test_user1 PROFILE myprofile1;"
+);
+is($ret, 0, 'create user ... profile succeed');
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'super_user',
+ 'postgres',
+ "CREATE USER test_user2 PROFILE myprofile2;"
+);
+is($ret, 0, 'create user ... profile succeed');
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'normal_user',
+ 'postgres',
+ "CREATE USER test_user3 PROFILE myprofile3;"
+);
+is($ret, 3, 'non-super user can not create user ... profile');
+like(
+ $stderr,
+ qr/must be superuser to create role attached to profile/,
+ 'expected error from non-super user can not create user ... profile'
+);
+
+# Test CREATE USER ... ACCOUNT LOCK/UNLOCK
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'super_user',
+ 'postgres',
+ "CREATE USER test_user4 ACCOUNT LOCK;"
+);
+is($ret, 0, 'create user ... account lock succeed');
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'super_user',
+ 'postgres',
+ "CREATE USER test_user5 ACCOUNT UNLOCK;"
+);
+is($ret, 0, 'create user ... account unlock succeed');
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'normal_user',
+ 'postgres',
+ "CREATE USER test_user6 ACCOUNT LOCK;"
+);
+is($ret, 3, 'non-super user can not create user ... account lock');
+like(
+ $stderr,
+ qr/must be superuser to create role account lock\/unlock/,
+ 'expected error from non-super user can not create user ... account lock'
+);
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'normal_user',
+ 'postgres',
+ "CREATE USER test_user7 ACCOUNT UNLOCK;"
+);
+is($ret, 3, 'non-super user can not create user ... account unlock');
+like(
+ $stderr,
+ qr/must be superuser to create role account lock\/unlock/,
+ 'expected error from non-super user can not create user ... account unlock'
+);
+
+# Test CREATE USER ... ENABLE/DISABLE PROFILE
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'super_user',
+ 'postgres',
+ "CREATE USER test_user8 ENABLE PROFILE PROFILE myprofile2;"
+);
+is($ret, 0, 'create user ... enable profile succeed');
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'super_user',
+ 'postgres',
+ "CREATE USER test_user9 DISABLE PROFILE;"
+);
+is($ret, 0, 'create user ... disable profile succeed');
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'normal_user',
+ 'postgres',
+ "CREATE USER test_user10 ENABLE PROFILE;"
+);
+is($ret, 3, 'non-super user can not create user ... enable profile');
+like(
+ $stderr,
+ qr/must be superuser to create role enable\/disable profile/,
+ 'expected error from non-super user can not create user ... enable profile'
+);
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'normal_user',
+ 'postgres',
+ "CREATE USER test_user11 DISABLE PROFILE;"
+);
+is($ret, 3, 'non-super user can not create user ... disable profile');
+like(
+ $stderr,
+ qr/must be superuser to create role enable\/disable profile/,
+ 'expected error from non-super user can not create user ... disable profile'
+);
+
+# Test ALTER USER ... PROFILE
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'super_user',
+ 'postgres',
+ "ALTER USER test_user1 PROFILE myprofile1;"
+);
+is($ret, 0, 'alter user ... profile succeed');
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'super_user',
+ 'postgres',
+ "ALTER USER test_user2 PROFILE myprofile2;"
+);
+is($ret, 0, 'alter user ... profile succeed');
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'normal_user',
+ 'postgres',
+ "ALTER USER test_user4 PROFILE myprofile4;"
+);
+is($ret, 3, 'non-super user can not alter user ... profile');
+like(
+ $stderr,
+ qr/must be superuser to alter role attached to profile/,
+ 'expected error from non-super user can not alter user ... profile'
+);
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'normal_user',
+ 'postgres',
+ "ALTER USER test_user4 PROFILE myprofile4;"
+);
+is($ret, 3, 'non-super user can not alter user ... profile');
+like(
+ $stderr,
+ qr/must be superuser to alter role attached to profile/,
+ 'expected error from non-super user can not alter user ... profile'
+);
+
+# Test ALTER USER ... ENABLE/DISABLE PROFILE
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'super_user',
+ 'postgres',
+ "ALTER USER test_user1 ENABLE PROFILE;"
+);
+is($ret, 0, 'alter user ... enable profile succeed');
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'super_user',
+ 'postgres',
+ "ALTER USER test_user2 DISABLE PROFILE;"
+);
+is($ret, 0, 'alter user ... disable profile succeed');
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'normal_user',
+ 'postgres',
+ "ALTER USER test_user4 ENABLE PROFILE;"
+);
+is($ret, 3, 'non-super user can not alter user ... enable profile');
+like(
+ $stderr,
+ qr/must be superuser to alter role enable\/disable profile/,
+ 'expected error from non-super user can not alter user ... enable profile'
+);
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'normal_user',
+ 'postgres',
+ "ALTER USER test_user5 DISABLE PROFILE;"
+);
+is($ret, 3, 'non-super user can not alter user ... disable profile');
+like(
+ $stderr,
+ qr/must be superuser to alter role enable\/disable profile/,
+ 'expected error from non-super user can not alter user ... disable profile'
+);
+
+# Test ALTER USER ... ACCOUNT
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'super_user',
+ 'postgres',
+ "ALTER USER test_user1 ACCOUNT LOCK;"
+);
+is($ret, 0, 'alter user ... account lock succeed');
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'super_user',
+ 'postgres',
+ "ALTER USER test_user2 ACCOUNT UNLOCK;"
+);
+is($ret, 0, 'alter user ... account unlock succeed');
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'normal_user',
+ 'postgres',
+ "ALTER USER test_user1 ACCOUNT LOCK;"
+);
+is($ret, 3, 'non-super user can not alter user ... account lock');
+like(
+ $stderr,
+ qr/must be superuser to alter role account lock\/unlock/,
+ 'expected error from non-super user can not alter user ... account lock'
+);
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'normal_user',
+ 'postgres',
+ "ALTER USER test_user1 ACCOUNT UNLOCK;"
+);
+is($ret, 3, 'non-super user can not alter user ... account unlock');
+like(
+ $stderr,
+ qr/must be superuser to alter role account lock\/unlock/,
+ 'expected error from non-super user can not alter user ... account unlock'
+);
+
+# Test DROP USER
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'super_user',
+ 'postgres',
+ "DROP USER test_user1;"
+);
+is($ret, 0, 'drop user succeed');
+
+# Test privileges of SELECT pg_authid, pg_profile, pg_password_history
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'super_user',
+ 'postgres',
+ "SELECT rolname, rolaccountstatus, rolfailedlogins, rolenableprofile
+ FROM pg_authid
+ WHERE rolname like '%test_user%';"
+);
+is($ret, 0, 'select pg_authid succeed');
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'normal_user',
+ 'postgres',
+ "SELECT rolname, rolaccountstatus, rolfailedlogins, rolenableprofile
+ FROM pg_authid
+ WHERE rolname like '%test_user%';"
+);
+is($ret, 3, 'non-super user can not select pg_authid');
+like(
+ $stderr,
+ qr/permission denied for table pg_authid/,
+ 'expected error from non-super user can not select pg_authid'
+);
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'super_user',
+ 'postgres',
+ "SELECT prfname, prffailedloginattempts, prfpasswordlocktime, prfpasswordreusemax
+ FROM pg_profile;"
+);
+is($ret, 0, 'select pg_profile succeed');
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'normal_user',
+ 'postgres',
+ "SELECT prfname, prffailedloginattempts, prfpasswordlocktime, prfpasswordreusemax
+ FROM pg_profile;"
+);
+is($ret, 0, 'select pg_profile succeed');
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'super_user',
+ 'postgres',
+ "SELECT COUNT(*)
+ FROM pg_password_history;"
+);
+is($ret, 0, 'select pg_profile succeed');
+
+($ret, $stdout, $stderr) =
+$node->role_psql(
+ 'normal_user',
+ 'postgres',
+ "SELECT COUNT(*)
+ FROM pg_password_history;"
+);
+is($ret, 3, 'non-super user can not select pg_password_history');
+like(
+ $stderr,
+ qr/permission denied for table pg_password_history/,
+ 'expected error from non-super user can not select pg_password_history'
+);
+
+# Test Login Successful
+$node->safe_psql(
+ 'postgres',
+ "ALTER USER profile_user1 PROFILE myprofile1 ENABLE PROFILE;
+ ALTER USER profile_user2 PROFILE myprofile2 ENABLE PROFILE;
+ ALTER USER profile_user3 PROFILE myprofile3 ENABLE PROFILE;
+ ALTER USER profile_user4 PROFILE myprofile4 ENABLE PROFILE;
+ ALTER USER super_user PROFILE pg_default ENABLE PROFILE PASSWORD 'a_nice_word';"
+);
+
+# Require password from now on.
+reset_pg_hba($node, 'scram-sha-256');
+
+# Test passwords work OK
+test_login($node, 'profile_user1', "I\xc2\xadX", 0);
+test_login($node, 'profile_user1', "\xe2\x85\xa8", 0);
+
+test_login($node, 'profile_user2', "a", 0);
+test_login($node, 'profile_user2', "\xc2\xaa", 0);
+
+test_login($node, 'profile_user3', "a", 0);
+test_login($node, 'profile_user3', "\xc2\xaa", 0);
+
+test_login($node, 'profile_user4', "foo\x07bar", 0);
+
+test_login($node, 'profile_user1', 'IX', 0);
+
+# Test Login Successful and Failed
+# Test Account Unlocked
+test_login($node, 'profile_user1', "random", 2);
+test_login($node, 'profile_user1', "random", 2);
+
+reset_pg_hba($node, 'trust');
+($ret, $stdout, $stderr) =
+$node->psql(
+ 'postgres',
+ "SELECT rolname, rolaccountstatus, rolfailedlogins, rolenableprofile
+ FROM pg_authid
+ WHERE rolname = 'profile_user1';"
+);
+like(
+ $stdout,
+ qr/profile_user1|0|2|t/,
+ 'expected results from select pg_authid'
+);
+
+reset_pg_hba($node, 'scram-sha-256');
+test_login($node, 'profile_user1', "IX", 0);
+
+reset_pg_hba($node, 'trust');
+($ret, $stdout, $stderr) =
+$node->psql(
+ 'postgres',
+ "SELECT rolname, rolaccountstatus, rolfailedlogins, rolenableprofile
+ FROM pg_authid
+ WHERE rolname = 'profile_user1';"
+);
+like(
+ $stdout,
+ qr/profile_user1|0|0|t/,
+ 'expected results from select pg_authid'
+);
+
+# Test Account Locked
+reset_pg_hba($node, 'scram-sha-256');
+test_login($node, 'profile_user2', "random", 2);
+test_login($node, 'profile_user2', "random", 2);
+
+reset_pg_hba($node, 'trust');
+($ret, $stdout, $stderr) =
+$node->psql(
+ 'postgres',
+ "SELECT rolname, rolaccountstatus, rolfailedlogins, rolenableprofile
+ FROM pg_authid
+ WHERE rolname = 'profile_user2';"
+);
+like(
+ $stdout,
+ qr/profile_user2|2|2|t/,
+ 'expected results from select pg_authid'
+);
+
+$node->safe_psql(
+ 'postgres',
+ "ALTER USER profile_user2 ACCOUNT UNLOCK"
+);
+($ret, $stdout, $stderr) =
+$node->psql(
+ 'postgres',
+ "SELECT rolname, rolaccountstatus, rolfailedlogins, rolenableprofile
+ FROM pg_authid
+ WHERE rolname = 'profile_user2';"
+);
+like(
+ $stdout,
+ qr/profile_user2|0|2|t/,
+ 'expected results from select pg_authid'
+);
+
+reset_pg_hba($node, 'scram-sha-256');
+test_login($node, 'profile_user2', "a", 0);
+
+reset_pg_hba($node, 'trust');
+($ret, $stdout, $stderr) =
+$node->psql(
+ 'postgres',
+ "SELECT rolname, rolaccountstatus, rolfailedlogins, rolenableprofile
+ FROM pg_authid
+ WHERE rolname = 'profile_user2';"
+);
+like(
+ $stdout,
+ qr/profile_user2|0|0|t/,
+ 'expected results from select pg_authid'
+);
+
+# Test Default Profile Value
+reset_pg_hba($node, 'scram-sha-256');
+
+test_login($node, 'profile_user3', "random", 2);
+test_login($node, 'profile_user3', "random", 2);
+test_login($node, 'profile_user3', "random", 2);
+
+reset_pg_hba($node, 'trust');
+($ret, $stdout, $stderr) =
+$node->psql(
+ 'postgres',
+ "SELECT rolname, rolaccountstatus, rolfailedlogins, rolenableprofile
+ FROM pg_authid
+ WHERE rolname = 'profile_user3';"
+);
+like(
+ $stdout,
+ qr/profile_user3|0|3|t/,
+ 'expected results from select pg_authid'
+);
+
+($ret, $stdout, $stderr) =
+$node->psql(
+ 'postgres',
+ "SELECT prfname, prffailedloginattempts
+ FROM pg_profile
+ WHERE prfname = 'myprofile3';"
+);
+like(
+ $stdout,
+ qr/myprofile3|-1/,
+ 'expected results from select pg_profile'
+);
+
+($ret, $stdout, $stderr) =
+$node->psql(
+ 'postgres',
+ "SELECT prfname, prffailedloginattempts
+ FROM pg_profile
+ WHERE prfname = 'pg_default';"
+);
+like(
+ $stdout,
+ qr/pg_default|4/,
+ 'expected results from select pg_profile'
+);
+
+$node->safe_psql(
+ 'postgres',
+ "ALTER PROFILE pg_default LIMIT FAILED_LOGIN_ATTEMPTS 2;"
+);
+
+reset_pg_hba($node, 'scram-sha-256');
+test_login($node, 'profile_user3', "a", 2);
+
+reset_pg_hba($node, 'trust');
+($ret, $stdout, $stderr) =
+$node->psql(
+ 'postgres',
+ "SELECT rolname, rolaccountstatus, rolfailedlogins, rolenableprofile
+ FROM pg_authid
+ WHERE rolname = 'profile_user3';"
+);
+like(
+ $stdout,
+ qr/profile_user3|2|3|t/,
+ 'expected results from select pg_authid'
+);
+
+# Test Alter User ... Enable/Disable Profile
+reset_pg_hba($node, 'scram-sha-256');
+test_login($node, 'profile_user4', "random", 2);
+test_login($node, 'profile_user4', "random", 2);
+
+reset_pg_hba($node, 'trust');
+($ret, $stdout, $stderr) =
+$node->psql(
+ 'postgres',
+ "SELECT rolname, rolaccountstatus, rolfailedlogins, rolenableprofile
+ FROM pg_authid
+ WHERE rolname = 'profile_user4';"
+);
+like(
+ $stdout,
+ qr/profile_user4|2|2|t/,
+ 'expected results from select pg_authid'
+);
+
+reset_pg_hba($node, 'scram-sha-256');
+test_login($node, 'profile_user4', "foo\x07bar", 2);
+
+reset_pg_hba($node, 'trust');
+$node->safe_psql(
+ 'postgres',
+ "ALTER USER profile_user4 DISABLE PROFILE;"
+);
+
+($ret, $stdout, $stderr) =
+$node->psql(
+ 'postgres',
+ "SELECT rolname, rolaccountstatus, rolfailedlogins, rolenableprofile
+ FROM pg_authid
+ WHERE rolname = 'profile_user4';"
+);
+like(
+ $stdout,
+ qr/profile_user4|2|2|f/,
+ 'expected results from select pg_authid'
+);
+
+reset_pg_hba($node, 'scram-sha-256');
+test_login($node, 'profile_user4', "foo\x07bar", 0);
+
+reset_pg_hba($node, 'trust');
+$node->safe_psql(
+ 'postgres',
+ "ALTER USER profile_user4 ENABLE PROFILE;"
+);
+
+reset_pg_hba($node, 'scram-sha-256');
+test_login($node, 'profile_user4', "foo\x07bar", 2);
+
+# Test Superuser Will Never Locked
+test_login($node, 'super_user', "random", 2);
+test_login($node, 'super_user', "random", 2);
+test_login($node, 'super_user', "random", 2);
+
+reset_pg_hba($node, 'trust');
+($ret, $stdout, $stderr) =
+$node->psql(
+ 'postgres',
+ "SELECT rolname, rolaccountstatus, rolfailedlogins, rolenableprofile
+ FROM pg_authid
+ WHERE rolname = 'super_user';"
+);
+like(
+ $stdout,
+ qr/super_user|0|0|t/,
+ 'expected results from select pg_authid'
+);
+
+reset_pg_hba($node, 'scram-sha-256');
+test_login($node, 'super_user', "a_nice_word", 0);
+
+# cleanup
+reset_pg_hba($node, 'trust');
+$node->safe_psql(
+ 'postgres',
+ "DROP USER test_user2;
+ DROP USER test_user4;
+ DROP USER test_user5;
+ DROP USER test_user8;
+ DROP USER test_user9;
+ DROP USER super_user;
+ DROP USER normal_user;
+ DROP USER profile_user1;
+ DROP USER profile_user2;
+ DROP USER profile_user3;
+ DROP USER profile_user4;
+ DROP PROFILE myprofile1;
+ DROP PROFILE myprofile2;
+ DROP PROFILE myprofile3;
+ DROP PROFILE myprofile4;
+ ALTER PROFILE pg_default LIMIT FAILED_LOGIN_ATTEMPTS -2 PASSWORD_LOCK_TIME -2 PASSWORD_REUSE_MAX -2;"
+);
\ No newline at end of file
diff --git a/src/test/perl/PostgresNode.pm b/src/test/perl/PostgresNode.pm
index 647d0b8..1e2d7a0 100644
--- a/src/test/perl/PostgresNode.pm
+++ b/src/test/perl/PostgresNode.pm
@@ -2085,6 +2085,163 @@
return @file_opts;
}
+# Test SQL in specific role
+sub role_psql
+{
+ my ($self, $role, $dbname, $sql, %params) = @_;
+
+ local %ENV = $self->_get_env();
+
+ my $stdout = $params{stdout};
+ my $stderr = $params{stderr};
+ my $replication = $params{replication};
+ my $timeout = undef;
+ my $timeout_exception = 'psql timed out';
+
+
+ local $ENV{PGOPTIONS} = '-c gp_role=utility';
+
+ # Build the connection string.
+ my $psql_connstr;
+ if (defined $params{connstr})
+ {
+ $psql_connstr = $params{connstr};
+ }
+ else
+ {
+ $psql_connstr = $self->connstr($dbname);
+ }
+ $psql_connstr .= defined $replication ? " replication=$replication" : "";
+
+ my @psql_params = (
+ $self->installed_command('psql'),
+ '-XAtq', '-U', $role, '-d', $psql_connstr, '-f', '-');
+
+
+ # If the caller wants an array and hasn't passed stdout/stderr
+ # references, allocate temporary ones to capture them so we
+ # can return them. Otherwise we won't redirect them at all.
+ if (wantarray)
+ {
+ if (!defined($stdout))
+ {
+ my $temp_stdout = "";
+ $stdout = \$temp_stdout;
+ }
+ if (!defined($stderr))
+ {
+ my $temp_stderr = "";
+ $stderr = \$temp_stderr;
+ }
+ }
+
+ $params{on_error_stop} = 1 unless defined $params{on_error_stop};
+ $params{on_error_die} = 0 unless defined $params{on_error_die};
+
+ push @psql_params, '-v', 'ON_ERROR_STOP=1' if $params{on_error_stop};
+ push @psql_params, @{ $params{extra_params} }
+ if defined $params{extra_params};
+
+ $timeout =
+ IPC::Run::timeout($params{timeout}, exception => $timeout_exception)
+ if (defined($params{timeout}));
+
+ ${ $params{timed_out} } = 0 if defined $params{timed_out};
+
+ # IPC::Run would otherwise append to existing contents:
+ $$stdout = "" if ref($stdout);
+ $$stderr = "" if ref($stderr);
+
+ my $ret;
+
+ # Run psql and capture any possible exceptions. If the exception is
+ # because of a timeout and the caller requested to handle that, just return
+ # and set the flag. Otherwise, and for any other exception, rethrow.
+ #
+ # For background, see
+ # https://metacpan.org/pod/release/ETHER/Try-Tiny-0.24/lib/Try/Tiny.pm
+ do
+ {
+ local $@;
+ eval {
+ my @ipcrun_opts = (\@psql_params, '<', \$sql);
+ push @ipcrun_opts, '>', $stdout if defined $stdout;
+ push @ipcrun_opts, '2>', $stderr if defined $stderr;
+ push @ipcrun_opts, $timeout if defined $timeout;
+
+ IPC::Run::run @ipcrun_opts;
+ $ret = $?;
+ };
+ my $exc_save = $@;
+ if ($exc_save)
+ {
+
+ # IPC::Run::run threw an exception. re-throw unless it's a
+ # timeout, which we'll handle by testing is_expired
+ die $exc_save
+ if (blessed($exc_save)
+ || $exc_save !~ /^\Q$timeout_exception\E/);
+
+ $ret = undef;
+
+ die "Got timeout exception '$exc_save' but timer not expired?!"
+ unless $timeout->is_expired;
+
+ if (defined($params{timed_out}))
+ {
+ ${ $params{timed_out} } = 1;
+ }
+ else
+ {
+ die "psql timed out: stderr: '$$stderr'\n"
+ . "while running '@psql_params'";
+ }
+ }
+ };
+
+ if (defined $$stdout)
+ {
+ chomp $$stdout;
+ }
+
+ if (defined $$stderr)
+ {
+ chomp $$stderr;
+ }
+
+ # See http://perldoc.perl.org/perlvar.html#%24CHILD_ERROR
+ # We don't use IPC::Run::Simple to limit dependencies.
+ #
+ # We always die on signal.
+ my $core = $ret & 128 ? " (core dumped)" : "";
+ die "psql exited with signal "
+ . ($ret & 127)
+ . "$core: '$$stderr' while running '@psql_params'"
+ if $ret & 127;
+ $ret = $ret >> 8;
+
+ if ($ret && $params{on_error_die})
+ {
+ die "psql error: stderr: '$$stderr'\nwhile running '@psql_params'"
+ if $ret == 1;
+ die "connection error: '$$stderr'\nwhile running '@psql_params'"
+ if $ret == 2;
+ die
+ "error running SQL: '$$stderr'\nwhile running '@psql_params' with sql '$sql'"
+ if $ret == 3;
+ die "psql returns $ret: '$$stderr'\nwhile running '@psql_params'";
+ }
+
+ if (wantarray)
+ {
+ return ($ret, $$stdout, $$stderr);
+ }
+ else
+ {
+ return $ret;
+ }
+}
+
=pod
=item $node->pgbench($opts, $stat, $out, $err, $name, $files, @args)
diff --git a/src/test/regress/expected/misc_sanity.out b/src/test/regress/expected/misc_sanity.out
index 4f7ef1d..7e1df68 100644
--- a/src/test/regress/expected/misc_sanity.out
+++ b/src/test/regress/expected/misc_sanity.out
@@ -161,7 +161,9 @@
pg_compression
pg_depend
pg_extprotocol
+ pg_password_history
pg_proc_callback
+ pg_profile
pg_resgroup
pg_resgroupcapability
pg_resourcetype
@@ -171,7 +173,7 @@
pg_stat_last_operation
pg_stat_last_shoperation
pg_type_encoding
-(23 rows)
+(25 rows)
-- system catalog unique indexes not wrapped in a constraint
-- (There should be none.)
diff --git a/src/test/regress/expected/misc_sanity_external_fts.out b/src/test/regress/expected/misc_sanity_external_fts.out
index 06af9c3..c5115a8 100644
--- a/src/test/regress/expected/misc_sanity_external_fts.out
+++ b/src/test/regress/expected/misc_sanity_external_fts.out
@@ -158,7 +158,9 @@
pg_compression
pg_depend
pg_extprotocol
+ pg_password_history
pg_proc_callback
+ pg_profile
pg_resgroup
pg_resgroupcapability
pg_resourcetype
@@ -168,7 +170,7 @@
pg_stat_last_operation
pg_stat_last_shoperation
pg_type_encoding
-(22 rows)
+(24 rows)
-- system catalog unique indexes not wrapped in a constraint
-- (There should be none.)
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index bf4635e..fbac24e 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -130,10 +130,10 @@
p1.pronargs != p2.pronargs);
oid | proname | oid | proname
------+-------------------+------+---------------------
- 6212 | int2_matrix_accum | 6214 | int8_matrix_accum
6212 | int2_matrix_accum | 6215 | float8_matrix_accum
- 6213 | int4_matrix_accum | 6214 | int8_matrix_accum
+ 6212 | int2_matrix_accum | 6214 | int8_matrix_accum
6213 | int4_matrix_accum | 6215 | float8_matrix_accum
+ 6213 | int4_matrix_accum | 6214 | int8_matrix_accum
(4 rows)
-- Look for uses of different type OIDs in the argument/result type fields
@@ -402,11 +402,12 @@
AND NOT EXISTS(SELECT 1 FROM pg_conversion WHERE conproc = p1.oid)
AND p1.oid != 'shell_in(cstring)'::regprocedure
ORDER BY 1;
- oid | proname
-------+--------------
+ oid | proname
+------+-----------------
2293 | cstring_out
2501 | cstring_send
-(2 rows)
+ 7060 | get_role_status
+(3 rows)
-- Likewise, look for functions that return cstring and aren't datatype output
-- functions nor typmod output functions.
@@ -419,11 +420,12 @@
AND NOT EXISTS(SELECT 1 FROM pg_type WHERE typmodout = p1.oid)
AND p1.oid != 'shell_out(void)'::regprocedure
ORDER BY 1;
- oid | proname
-------+--------------
+ oid | proname
+------+-----------------
2292 | cstring_in
2500 | cstring_recv
-(2 rows)
+ 7060 | get_role_status
+(3 rows)
-- Check for length inconsistencies between the various argument-info arrays.
SELECT p1.oid, p1.proname
diff --git a/src/test/regress/expected/profile.out b/src/test/regress/expected/profile.out
new file mode 100644
index 0000000..9541ced
--- /dev/null
+++ b/src/test/regress/expected/profile.out
@@ -0,0 +1,689 @@
+--
+-- Test for PROFILE
+--
+-- Display pg_stas_activity to check the login monitor process
+SELECT COUNT(*) FROM pg_stat_activity;
+ count
+-------
+ 8
+(1 row)
+
+-- Display pg_authid, pg_roles, pg_profile and pg_password_history catalog
+\d+ pg_authid;
+ Table "pg_catalog.pg_authid"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+-------------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
+ oid | oid | | not null | | plain | |
+ rolname | name | | not null | | plain | |
+ rolsuper | boolean | | not null | | plain | |
+ rolinherit | boolean | | not null | | plain | |
+ rolcreaterole | boolean | | not null | | plain | |
+ rolcreatedb | boolean | | not null | | plain | |
+ rolcanlogin | boolean | | not null | | plain | |
+ rolreplication | boolean | | not null | | plain | |
+ rolbypassrls | boolean | | not null | | plain | |
+ rolconnlimit | integer | | not null | | plain | |
+ rolenableprofile | boolean | | not null | | plain | |
+ rolpassword | text | C | | | extended | |
+ rolvaliduntil | timestamp with time zone | | | | plain | |
+ rolprofile | oid | | not null | | plain | |
+ rolaccountstatus | smallint | | not null | | plain | |
+ rolfailedlogins | integer | | not null | | plain | |
+ rolpasswordsetat | timestamp with time zone | | | | plain | |
+ rollockdate | timestamp with time zone | | | | plain | |
+ rolpasswordexpire | timestamp with time zone | | | | plain | |
+ rolresqueue | oid | | | | plain | |
+ rolcreaterextgpfd | boolean | | | | plain | |
+ rolcreaterexthttp | boolean | | | | plain | |
+ rolcreatewextgpfd | boolean | | | | plain | |
+ rolresgroup | oid | | | | plain | |
+Indexes:
+ "pg_authid_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
+ "pg_authid_rolname_index" UNIQUE CONSTRAINT, btree (rolname), tablespace "pg_global"
+ "pg_authid_rolprofile_index" btree (rolprofile), tablespace "pg_global"
+ "pg_authid_rolresgroup_index" btree (rolresgroup), tablespace "pg_global"
+ "pg_authid_rolresqueue_index" btree (rolresqueue), tablespace "pg_global"
+Tablespace: "pg_global"
+
+\d+ pg_roles;
+ View "pg_catalog.pg_roles"
+ Column | Type | Collation | Nullable | Default | Storage | Description
+-------------------+--------------------------+-----------+----------+---------+----------+-------------
+ rolname | name | | | | plain |
+ rolsuper | boolean | | | | plain |
+ rolinherit | boolean | | | | plain |
+ rolcreaterole | boolean | | | | plain |
+ rolcreatedb | boolean | | | | plain |
+ rolcanlogin | boolean | | | | plain |
+ rolreplication | boolean | | | | plain |
+ rolconnlimit | integer | | | | plain |
+ rolenableprofile | boolean | | | | plain |
+ rolprofile | name | | | | plain |
+ rolaccountstatus | smallint | | | | plain |
+ rolfailedlogins | integer | | | | plain |
+ rolpassword | text | | | | extended |
+ rolvaliduntil | timestamp with time zone | | | | plain |
+ rollockdate | timestamp with time zone | | | | plain |
+ rolpasswordexpire | timestamp with time zone | | | | plain |
+ rolbypassrls | boolean | | | | plain |
+ rolconfig | text[] | C | | | extended |
+ rolresqueue | oid | | | | plain |
+ oid | oid | | | | plain |
+ rolcreaterextgpfd | boolean | | | | plain |
+ rolcreaterexthttp | boolean | | | | plain |
+ rolcreatewextgpfd | boolean | | | | plain |
+ rolresgroup | oid | | | | plain |
+View definition:
+ SELECT pg_authid.rolname,
+ pg_authid.rolsuper,
+ pg_authid.rolinherit,
+ pg_authid.rolcreaterole,
+ pg_authid.rolcreatedb,
+ pg_authid.rolcanlogin,
+ pg_authid.rolreplication,
+ pg_authid.rolconnlimit,
+ pg_authid.rolenableprofile,
+ pg_profile.prfname AS rolprofile,
+ pg_authid.rolaccountstatus,
+ pg_authid.rolfailedlogins,
+ '********'::text AS rolpassword,
+ pg_authid.rolvaliduntil,
+ pg_authid.rollockdate,
+ pg_authid.rolpasswordexpire,
+ pg_authid.rolbypassrls,
+ s.setconfig AS rolconfig,
+ pg_authid.rolresqueue,
+ pg_authid.oid,
+ pg_authid.rolcreaterextgpfd,
+ pg_authid.rolcreaterexthttp,
+ pg_authid.rolcreatewextgpfd,
+ pg_authid.rolresgroup
+ FROM pg_profile,
+ pg_authid
+ LEFT JOIN pg_db_role_setting s ON pg_authid.oid = s.setrole AND s.setdatabase = 0::oid
+ WHERE pg_profile.oid = pg_authid.rolprofile;
+
+\d+ pg_profile;
+ Table "pg_catalog.pg_profile"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+-------------------------+---------+-----------+----------+---------+---------+--------------+-------------
+ oid | oid | | not null | | plain | |
+ prfname | name | | not null | | plain | |
+ prffailedloginattempts | integer | | not null | | plain | |
+ prfpasswordlocktime | integer | | not null | | plain | |
+ prfpasswordlifetime | integer | | not null | | plain | |
+ prfpasswordgracetime | integer | | not null | | plain | |
+ prfpasswordreusetime | integer | | not null | | plain | |
+ prfpasswordreusemax | integer | | not null | | plain | |
+ prfpasswordallowhashed | integer | | not null | | plain | |
+ prfpasswordverifyfuncdb | oid | | | | plain | |
+ prfpasswordverifyfunc | oid | | | | plain | |
+Indexes:
+ "profile_name_index" UNIQUE CONSTRAINT, btree (prfname), tablespace "pg_global"
+ "profile_oid_index" UNIQUE CONSTRAINT, btree (oid), tablespace "pg_global"
+ "profile_password_verify_function_index" btree (prfpasswordverifyfuncdb, prfpasswordverifyfunc), tablespace "pg_global"
+Tablespace: "pg_global"
+
+\d+ pg_password_history;
+ Table "pg_catalog.pg_password_history"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+-----------------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
+ passhistroleid | oid | | not null | | plain | |
+ passhistpasswordsetat | timestamp with time zone | | not null | | plain | |
+ passhistpassword | text | C | not null | | extended | |
+Indexes:
+ "pg_password_history_role_password_index" UNIQUE CONSTRAINT, btree (passhistroleid, passhistpassword), tablespace "pg_global"
+ "pg_password_history_role_passwordsetat_index" btree (passhistroleid, passhistpasswordsetat), tablespace "pg_global"
+Tablespace: "pg_global"
+
+SELECT rolname, prfname, rolaccountstatus, rolfailedlogins, rolenableprofile
+FROM pg_authid, pg_profile
+WHERE pg_authid.rolprofile = pg_profile.oid
+AND rolname like '%profile_user%';
+ rolname | prfname | rolaccountstatus | rolfailedlogins | rolenableprofile
+---------+---------+------------------+-----------------+------------------
+(0 rows)
+
+SELECT prfname, prffailedloginattempts, prfpasswordlocktime, prfpasswordreusemax
+FROM pg_profile;
+ prfname | prffailedloginattempts | prfpasswordlocktime | prfpasswordreusemax
+------------+------------------------+---------------------+---------------------
+ pg_default | -2 | -2 | -2
+(1 row)
+
+-- Test CREATE PROFILE
+CREATE PROFILE myprofile1;
+CREATE PROFILE myprofile2 LIMIT FAILED_LOGIN_ATTEMPTS -1 PASSWORD_LOCK_TIME -2;
+CREATE PROFILE myprofile3 LIMIT FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME 1;
+CREATE PROFILE myprofile4 LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 9999 PASSWORD_REUSE_MAX 3;
+CREATE PROFILE myprofile4; -- Failed for myprofile4 already exists
+ERROR: profile "myprofile4" already exists
+SELECT prfname, prffailedloginattempts, prfpasswordlocktime, prfpasswordreusemax
+FROM pg_profile;
+ prfname | prffailedloginattempts | prfpasswordlocktime | prfpasswordreusemax
+------------+------------------------+---------------------+---------------------
+ pg_default | -2 | -2 | -2
+ myprofile1 | -1 | -1 | -1
+ myprofile2 | -1 | -2 | -1
+ myprofile3 | 4 | 1 | -1
+ myprofile4 | 5 | 9999 | 3
+(5 rows)
+
+-- Failed for invalid parameters
+CREATE PROFILE myprofile5 LIMIT FAILED_LOGIN_ATTEMPTS -3;
+ERROR: invalid failed login attempts: -3
+CREATE PROFILE myprofile6 LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME -5;
+ERROR: invalid password lock time: -5
+CREATE PROFILE myprofile7 LIMIT FAILED_LOGIN_ATTEMPTS -2 PASSWORD_LOCK_TIME -1 PASSWORD_REUSE_MAX -9999;
+ERROR: invalid password reuse max: -9999
+CREATE PROFILE myprofile8 LIMIT FAILED_LOGIN_ATTEMPTS 10000;
+ERROR: invalid failed login attempts: 10000
+CREATE PROFILE myprofile9 LIMIT FAILED_LOGIN_ATTEMPTS 9999 PASSWORD_LOCK_TIME 10000;
+ERROR: invalid password lock time: 10000
+CREATE PROFILE myprofile10 LIMIT FAILED_LOGIN_ATTEMPTS 9999 PASSWORD_LOCK_TIME -1 PASSWORD_REUSE_MAX 99999;
+ERROR: invalid password reuse max: 99999
+CREATE PROFILE myprofile11 LIMIT FAILED_LOGIN_ATTEMPTS 9999 FAILED_LOGIN_ATTEMPTS 2;
+ERROR: conflicting or redundant options
+LINE 1: ...FILE myprofile11 LIMIT FAILED_LOGIN_ATTEMPTS 9999 FAILED_LOG...
+ ^
+CREATE PROFILE myprofile12 LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 4 PASSWORD_LOCK_TIME 3;
+ERROR: conflicting or redundant options
+LINE 1: ...IMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 4 PASSWORD_L...
+ ^
+CREATE PROFILE myprofile13 LIMIT FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME 3 PASSWORD_REUSE_MAX 2 PASSWORD_REUSE_MAX 2;
+ERROR: conflicting or redundant options
+LINE 1: ...EMPTS 4 PASSWORD_LOCK_TIME 3 PASSWORD_REUSE_MAX 2 PASSWORD_R...
+ ^
+-- Failed for syntax error
+CREATE PROFILE myprofile14 FAILED_LOGIN_ATTEMPTS 1;
+ERROR: syntax error at or near "FAILED_LOGIN_ATTEMPTS"
+LINE 1: CREATE PROFILE myprofile14 FAILED_LOGIN_ATTEMPTS 1;
+ ^
+CREATE PROFILE myprofile15 PASSWORD_LOCK_TIME -2;
+ERROR: syntax error at or near "PASSWORD_LOCK_TIME"
+LINE 1: CREATE PROFILE myprofile15 PASSWORD_LOCK_TIME -2;
+ ^
+CREATE PROFILE myprofile16 PASSWORD_RESUE_MAX -1;
+ERROR: syntax error at or near "PASSWORD_RESUE_MAX"
+LINE 1: CREATE PROFILE myprofile16 PASSWORD_RESUE_MAX -1;
+ ^
+CREATE PROFILE myprofile17 FAILED_LOGIN_ATTEMPTS 0;
+ERROR: syntax error at or near "FAILED_LOGIN_ATTEMPTS"
+LINE 1: CREATE PROFILE myprofile17 FAILED_LOGIN_ATTEMPTS 0;
+ ^
+SELECT prfname, prffailedloginattempts, prfpasswordlocktime, prfpasswordreusemax
+FROM pg_profile;
+ prfname | prffailedloginattempts | prfpasswordlocktime | prfpasswordreusemax
+------------+------------------------+---------------------+---------------------
+ pg_default | -2 | -2 | -2
+ myprofile1 | -1 | -1 | -1
+ myprofile2 | -1 | -2 | -1
+ myprofile3 | 4 | 1 | -1
+ myprofile4 | 5 | 9999 | 3
+(5 rows)
+
+-- Test CREATE USER ... PROFILE
+CREATE USER profile_user1 PROFILE test; -- failed
+NOTICE: resource queue required -- using default resource queue "pg_default"
+ERROR: profile "test" does not exist
+CREATE USER profile_user1 PROFILE pg_default;
+NOTICE: resource queue required -- using default resource queue "pg_default"
+CREATE USER profile_user2 PASSWORD 'a_nice_long_password_123';
+NOTICE: resource queue required -- using default resource queue "pg_default"
+CREATE USER profile_user3 PASSWORD 'a_nice_long_password_456' PROFILE myprofile3;
+NOTICE: resource queue required -- using default resource queue "pg_default"
+CREATE USER profile_user4 ACCOUNT LOCK PROFILE myprofile4;
+NOTICE: resource queue required -- using default resource queue "pg_default"
+SELECT rolname, prfname, rolaccountstatus, rolfailedlogins, rolenableprofile
+FROM pg_authid, pg_profile
+WHERE pg_authid.rolprofile = pg_profile.oid
+AND rolname like '%profile_user%';
+ rolname | prfname | rolaccountstatus | rolfailedlogins | rolenableprofile
+---------------+------------+------------------+-----------------+------------------
+ profile_user1 | pg_default | 0 | 0 | f
+ profile_user2 | pg_default | 0 | 0 | f
+ profile_user3 | myprofile3 | 0 | 0 | f
+ profile_user4 | myprofile4 | 2 | 0 | f
+(4 rows)
+
+-- Test CREATE USER ... ENABLE/DISABLE PROFILE
+CREATE USER profile_user5 ENABLE PROFILE PROFILE pg_default;
+NOTICE: resource queue required -- using default resource queue "pg_default"
+CREATE USER profile_user6 ENABLE PROFILE PROFILE; -- failed
+ERROR: syntax error at or near ";"
+LINE 1: CREATE USER profile_user6 ENABLE PROFILE PROFILE;
+ ^
+CREATE USER profile_user7 DISABLE PROFILE PROFILE pg_default;
+NOTICE: resource queue required -- using default resource queue "pg_default"
+CREATE USER profile_user8 DISABLE PROFILE PROFILE; -- failed
+ERROR: syntax error at or near ";"
+LINE 1: CREATE USER profile_user8 DISABLE PROFILE PROFILE;
+ ^
+CREATE USER profile_user9 SUPERUSER;
+SELECT rolname, prfname, rolaccountstatus, rolfailedlogins, rolenableprofile
+FROM pg_authid, pg_profile
+WHERE pg_authid.rolprofile = pg_profile.oid
+AND rolname like '%profile_user%';
+ rolname | prfname | rolaccountstatus | rolfailedlogins | rolenableprofile
+---------------+------------+------------------+-----------------+------------------
+ profile_user1 | pg_default | 0 | 0 | f
+ profile_user2 | pg_default | 0 | 0 | f
+ profile_user3 | myprofile3 | 0 | 0 | f
+ profile_user4 | myprofile4 | 2 | 0 | f
+ profile_user5 | pg_default | 0 | 0 | t
+ profile_user7 | pg_default | 0 | 0 | f
+ profile_user9 | pg_default | 0 | 0 | f
+(7 rows)
+
+-- Test ALTER PROFILE
+ALTER USER profile_user1 PROFILE myprofile1;
+ALTER USER profile_user2 PROFILE myprofile2;
+SELECT rolname, prfname, rolaccountstatus, rolfailedlogins, rolenableprofile
+FROM pg_authid, pg_profile
+WHERE pg_authid.rolprofile = pg_profile.oid
+AND rolname like '%profile_user%';
+ rolname | prfname | rolaccountstatus | rolfailedlogins | rolenableprofile
+---------------+------------+------------------+-----------------+------------------
+ profile_user3 | myprofile3 | 0 | 0 | f
+ profile_user4 | myprofile4 | 2 | 0 | f
+ profile_user5 | pg_default | 0 | 0 | t
+ profile_user7 | pg_default | 0 | 0 | f
+ profile_user9 | pg_default | 0 | 0 | f
+ profile_user1 | myprofile1 | 0 | 0 | f
+ profile_user2 | myprofile2 | 0 | 0 | f
+(7 rows)
+
+ALTER USER profile_user10 PROFILE myprofile2; -- failed
+ERROR: role "profile_user10" does not exist
+SELECT rolname, prfname, rolaccountstatus, rolfailedlogins, rolenableprofile
+FROM pg_authid, pg_profile
+WHERE pg_authid.rolprofile = pg_profile.oid
+AND rolname = 'profile_user9';
+ rolname | prfname | rolaccountstatus | rolfailedlogins | rolenableprofile
+---------------+------------+------------------+-----------------+------------------
+ profile_user9 | pg_default | 0 | 0 | f
+(1 row)
+
+ALTER USER profile_user9 PROFILE pg_default;
+SELECT rolname, prfname, rolaccountstatus, rolfailedlogins, rolenableprofile
+FROM pg_authid, pg_profile
+WHERE pg_authid.rolprofile = pg_profile.oid
+AND rolname = 'profile_user9';
+ rolname | prfname | rolaccountstatus | rolfailedlogins | rolenableprofile
+---------------+------------+------------------+-----------------+------------------
+ profile_user9 | pg_default | 0 | 0 | f
+(1 row)
+
+ALTER PROFILE myprofile1 LIMIT; -- OK
+ALTER PROFILE myprofile1 LIMIT PASSWORD_LOCK_TIME 1;
+ALTER PROFILE myprofile2 PASSWORD_LOCK_TIME 3; -- syntax error
+ERROR: syntax error at or near "PASSWORD_LOCK_TIME"
+LINE 1: ALTER PROFILE myprofile2 PASSWORD_LOCK_TIME 3;
+ ^
+ALTER PROFILE myprofile2 LIMIT PASSWORD_LOCK_TIME 3; -- OK
+ALTER PROFILE myprofile3 LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_REUSE_MAX 2;
+ALTER PROFILE myprofile3 LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_REUSE_MAX 2; -- ALTER PROFILE the same values
+ALTER PROFILE myprofile4 LIMIT PASSWORD_LOCK_TIME 10 PASSWORD_REUSE_MAX -1;
+ALTER PROFILE myprofile4 LIMIT FAILED_LOGIN_ATTEMPTS 9999 PASSWORD_LOCK_TIME 9999 PASSWORD_REUSE_MAX 9999;
+ALTER PROFILE myprofile4 LIMIT FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME 0 PASSWORD_REUSE_MAX 0;
+ALTER PROFILE myprofile5 LIMIT FAILED_LOGIN_ATTEMPTS 3;
+ERROR: profile "myprofile5" does not exist
+ALTER PROFILE pg_default LIMIT FAILED_LOGIN_ATTEMPTS 2 PASSWORD_LOCK_TIME 1 PASSWORD_REUSE_MAX 3;
+ALTER PROFILE myprofile1 LIMIT FAILED_LOGIN_ATTEMPTS 1 FAILED_LOGIN_ATTEMPTS 2;
+ERROR: conflicting or redundant options
+ALTER PROFILE myprofile2 LIMIT PASSWORD_LOCK_TIME 2 PASSWORD_LOCK_TIME 3;
+ERROR: conflicting or redundant options
+ALTER PROFILE myprofile3 LIMIT PASSWORD_REUSE_MAX -1 PASSWORD_REUSE_MAX -2;
+ERROR: conflicting or redundant options
+ALTER PROFILE myprofile1 LIMIT FAILED_LOGIN_ATTEMPTS 1 FAILED_LOGIN_ATTEMPTS 2;
+ERROR: conflicting or redundant options
+ALTER PROFILE myprofile2 LIMIT FAILED_LOGIN_ATTEMPTS -2 PASSWORD_LOCK_TIME 2 PASSWORD_LOCK_TIME -2;
+ERROR: conflicting or redundant options
+ALTER PROFILE myprofile3 LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME -1 PASSWORD_REUSE_MAX 2 PASSWORD_REUSE_MAX 2;
+ERROR: conflicting or redundant options
+-- Failed for pg_default value can not be -1
+ALTER PROFILE pg_default LIMIT FAILED_LOGIN_ATTEMPTS -1;
+ERROR: can't set failed login attempts to -1(DEFAULT) of pg_default
+ALTER PROFILE pg_default LIMIT PASSWORD_LOCK_TIME -1;
+ERROR: can't set password lock time to -1(DEFAULT) of pg_default
+ALTER PROFILE pg_default LIMIT PASSWORD_REUSE_MAX -1;
+ERROR: can't set password reuse max to -1(DEFAULT) of pg_default
+ALTER PROFILE pg_default LIMIT FAILED_LOGIN_ATTEMPTS 2 PASSWORD_LOCK_TIME 2 PASSWORD_REUSE_MAX -1;
+ERROR: can't set password reuse max to -1(DEFAULT) of pg_default
+SELECT prfname, prffailedloginattempts, prfpasswordlocktime, prfpasswordreusemax
+FROM pg_profile;
+ prfname | prffailedloginattempts | prfpasswordlocktime | prfpasswordreusemax
+------------+------------------------+---------------------+---------------------
+ myprofile1 | -1 | 1 | -1
+ myprofile2 | -1 | 3 | -1
+ myprofile3 | 3 | 1 | 2
+ myprofile4 | 4 | 0 | 0
+ pg_default | 2 | 1 | 3
+(5 rows)
+
+-- Test ALTER PROFILE ... RENAME TO
+ALTER PROFILE pg_default RENAME TO anyname; -- failed for pg_default profile can't be renamed
+ERROR: can't RENAME "pg_default" profile
+ALTER PROFILE myprofile1 RENAME TO myprofile2; -- failed for myprofile2 already exists
+ERROR: profile "myprofile2" already exists
+ALTER PROFILE myprofile1 RENAME TO pg_default; -- failed for pg_default already exists
+ERROR: profile "pg_default" already exists
+ALTER PROFILE myprofile1 RENAME TO tempname; -- OK
+ALTER PROFILE myprofile2 RENAME TO myprofile1; -- OK
+ALTER PROFILE myprofile5 RENAME TO tempname2; -- failed for myprofile5 doesn't exists
+ERROR: profile "myprofile5" does not exist
+SELECT prfname, prffailedloginattempts, prfpasswordlocktime, prfpasswordreusemax
+FROM pg_profile;
+ prfname | prffailedloginattempts | prfpasswordlocktime | prfpasswordreusemax
+------------+------------------------+---------------------+---------------------
+ myprofile3 | 3 | 1 | 2
+ myprofile4 | 4 | 0 | 0
+ pg_default | 2 | 1 | 3
+ tempname | -1 | 1 | -1
+ myprofile1 | -1 | 3 | -1
+(5 rows)
+
+ALTER PROFILE tempname RENAME TO myprofile2; -- OK
+SELECT prfname, prffailedloginattempts, prfpasswordlocktime, prfpasswordreusemax
+FROM pg_profile;
+ prfname | prffailedloginattempts | prfpasswordlocktime | prfpasswordreusemax
+------------+------------------------+---------------------+---------------------
+ myprofile3 | 3 | 1 | 2
+ myprofile4 | 4 | 0 | 0
+ pg_default | 2 | 1 | 3
+ myprofile1 | -1 | 3 | -1
+ myprofile2 | -1 | 1 | -1
+(5 rows)
+
+-- Failed for invalid parameters
+ALTER PROFILE myprofile1 LIMIT FAILED_LOGIN_ATTEMPTS 10000;
+ERROR: invalid failed login attempts: 10000
+ALTER PROFILE myprofile2 LIMIT FAILED_LOGIN_ATTEMPTS 9999 PASSWORD_LOCK_TIME 10000;
+ERROR: invalid password lock time: 10000
+ALTER PROFILE myprofile3 LIMIT FAILED_LOGIN_ATTEMPTS 9999 PASSWORD_LOCK_TIME 9999 PASSWORD_REUSE_MAX 10000;
+ERROR: invalid password reuse max: 10000
+ALTER PROFILE myprofile4 LIMIT FAILED_LOGIN_ATTEMPTS 0;
+ERROR: invalid failed login attempts: 0
+ALTER PROFILE myprofile4 LIMIT FAILED_LOGIN_ATTEMPTS 0 PASSWORD_LOCK_TIME 0 PASSWORD_REUSE_MAX 3;
+ERROR: invalid failed login attempts: 0
+ALTER PROFILE myprofile4 LIMIT FAILED_LOGIN_ATTEMPTS 9999 FAILED_LOGIN_ATTEMPTS 3;
+ERROR: conflicting or redundant options
+ALTER PROFILE myprofile4 LIMIT FAILED_LOGIN_ATTEMPTS 9999 PASSWORD_LOCK_TIME 1 PASSWORD_LOCK_TIME 2;
+ERROR: conflicting or redundant options
+ALTER PROFILE myprofile4 LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 3 PASSWORD_REUSE_MAX 4 PASSWORD_REUSE_MAX 3;
+ERROR: conflicting or redundant options
+SELECT prfname, prffailedloginattempts, prfpasswordlocktime, prfpasswordreusemax
+FROM pg_profile;
+ prfname | prffailedloginattempts | prfpasswordlocktime | prfpasswordreusemax
+------------+------------------------+---------------------+---------------------
+ myprofile3 | 3 | 1 | 2
+ myprofile4 | 4 | 0 | 0
+ pg_default | 2 | 1 | 3
+ myprofile1 | -1 | 3 | -1
+ myprofile2 | -1 | 1 | -1
+(5 rows)
+
+-- Failed for syntax error
+ALTER PROFILE myprofile1 FAILED_LOGIN_ATTEMPTS 5;
+ERROR: syntax error at or near "FAILED_LOGIN_ATTEMPTS"
+LINE 1: ALTER PROFILE myprofile1 FAILED_LOGIN_ATTEMPTS 5;
+ ^
+ALTER PROFILE myprofile2 PASSWORD_LOCK_TIME -2;
+ERROR: syntax error at or near "PASSWORD_LOCK_TIME"
+LINE 1: ALTER PROFILE myprofile2 PASSWORD_LOCK_TIME -2;
+ ^
+ALTER PROFILE myprofile3 PASSWORD_RESUE_MAX -1;
+ERROR: syntax error at or near "PASSWORD_RESUE_MAX"
+LINE 1: ALTER PROFILE myprofile3 PASSWORD_RESUE_MAX -1;
+ ^
+SELECT prfname, prffailedloginattempts, prfpasswordlocktime, prfpasswordreusemax
+FROM pg_profile;
+ prfname | prffailedloginattempts | prfpasswordlocktime | prfpasswordreusemax
+------------+------------------------+---------------------+---------------------
+ myprofile3 | 3 | 1 | 2
+ myprofile4 | 4 | 0 | 0
+ pg_default | 2 | 1 | 3
+ myprofile1 | -1 | 3 | -1
+ myprofile2 | -1 | 1 | -1
+(5 rows)
+
+DELETE FROM pg_profile; -- failed for catalog can't be deleted
+ERROR: permission denied: "pg_profile" is a system catalog
+SELECT prfname, prffailedloginattempts, prfpasswordlocktime, prfpasswordreusemax
+FROM pg_profile;
+ prfname | prffailedloginattempts | prfpasswordlocktime | prfpasswordreusemax
+------------+------------------------+---------------------+---------------------
+ myprofile3 | 3 | 1 | 2
+ myprofile4 | 4 | 0 | 0
+ pg_default | 2 | 1 | 3
+ myprofile1 | -1 | 3 | -1
+ myprofile2 | -1 | 1 | -1
+(5 rows)
+
+-- Test ALTER USER ... PROFILE
+ALTER USER profile_user2 PROFILE myprofile3;
+ALTER USER profile_user3 PROFILE myprofile2;
+ALTER USER profile_user1 PROFILE myprofile1;
+ALTER USER profile_user4 PROFILE myprofile4;
+ALTER USER profile_user9 PROFILE myprofile3;
+SELECT rolname, prfname, rolaccountstatus, rolfailedlogins, rolenableprofile
+FROM pg_authid, pg_profile
+WHERE pg_authid.rolprofile = pg_profile.oid
+AND rolname like '%profile_user%';
+ rolname | prfname | rolaccountstatus | rolfailedlogins | rolenableprofile
+---------------+------------+------------------+-----------------+------------------
+ profile_user5 | pg_default | 0 | 0 | t
+ profile_user7 | pg_default | 0 | 0 | f
+ profile_user2 | myprofile3 | 0 | 0 | f
+ profile_user3 | myprofile2 | 0 | 0 | f
+ profile_user1 | myprofile1 | 0 | 0 | f
+ profile_user4 | myprofile4 | 2 | 0 | f
+ profile_user9 | myprofile3 | 0 | 0 | f
+(7 rows)
+
+-- Test ALTER USER ... ENABLE/DISABLE PROFILE
+ALTER USER profile_user5 DISABLE PROFILE PROFILE myprofile3;
+ALTER USER profile_user5 ENABLE PROFILE PROFILE;
+ERROR: syntax error at or near ";"
+LINE 1: ALTER USER profile_user5 ENABLE PROFILE PROFILE;
+ ^
+ALTER USER profile_user7 ENABLE PROFILE PROFILE myprofile4;
+ALTER USER profile_user7 DISABLE PROFILE PROFILE;
+ERROR: syntax error at or near ";"
+LINE 1: ALTER USER profile_user7 DISABLE PROFILE PROFILE;
+ ^
+SELECT rolname, prfname, rolaccountstatus, rolfailedlogins, rolenableprofile
+FROM pg_authid, pg_profile
+WHERE pg_authid.rolprofile = pg_profile.oid
+AND rolname like '%profile_user%';
+ rolname | prfname | rolaccountstatus | rolfailedlogins | rolenableprofile
+---------------+------------+------------------+-----------------+------------------
+ profile_user2 | myprofile3 | 0 | 0 | f
+ profile_user3 | myprofile2 | 0 | 0 | f
+ profile_user1 | myprofile1 | 0 | 0 | f
+ profile_user4 | myprofile4 | 2 | 0 | f
+ profile_user9 | myprofile3 | 0 | 0 | f
+ profile_user5 | myprofile3 | 0 | 0 | f
+ profile_user7 | myprofile4 | 0 | 0 | t
+(7 rows)
+
+-- Test ALTER USER ... PASSWORD
+ALTER USER profile_user1 PASSWORD 'test';
+ALTER USER profile_user1 PASSWORD 'a_nice_long_password_123';
+ALTER USER profile_user1 PASSWORD 'a_new_password';
+ALTER USER profile_user1 PASSWORD 'test';
+ERROR: The new password should not be the same with latest 3 history password
+ALTER USER profile_user1 PASSWORD 'a_nice_long_password_123';
+ERROR: The new password should not be the same with latest 3 history password
+ALTER USER profile_user1 PASSWORD 'a_new_password';
+ERROR: The new password should not be the same with latest 3 history password
+ALTER USER profile_user1 PASSWORD 'ABCD';
+ALTER USER profile_user1 PASSWORD 'test';
+ALTER PROFILE pg_default LIMIT PASSWORD_REUSE_MAX 4;
+ALTER USER profile_user1 PASSWORD 'a_nice_long_password_123';
+ERROR: The new password should not be the same with latest 4 history password
+ALTER USER profile_user2 PASSWORD 'test2';
+ALTER USER profile_user2 PASSWORD 'a_bad_password';
+ALTER USER profile_user2 PASSWORD 'test2';
+ERROR: The new password should not be the same with latest 2 history password
+ALTER USER profile_user2 PASSWORD 'a_bad_password';
+ERROR: The new password should not be the same with latest 2 history password
+ALTER USER profile_user2 PASSWORD 'a_nice_password';
+ALTER USER profile_user2 PASSWORD 'a_bad_password';
+ERROR: The new password should not be the same with latest 2 history password
+ALTER USER profile_user2 PASSWORD 'test2';
+ALTER PROFILE myprofile3 LIMIT PASSWORD_REUSE_MAX 1;
+ALTER USER profile_user2 PASSWORD 'a_bad_password'; -- OK
+ALTER USER profile_user2 PASSWORD 'test2'; -- OK
+ALTER USER profile_user4 PASSWORD 'test3'; -- failed
+ERROR: can't alter user password for profile's password_reuse_max is zero.
+DELETE FROM pg_password_history; -- failed for catalog can't be deleted
+ERROR: permission denied: "pg_password_history" is a system catalog
+-- Test ALTER USER ... ACCOUNT LOCK/UNLOCK
+ALTER USER profile_user1 ACCOUNT LOCK;
+ALTER USER profile_user2 ACCOUNT UNLOCK;
+ALTER USER profile_user3 ACCOUNT LOCK;
+ALTER USER profile_user4 ACCOUNT UNLOCK;
+SELECT rolname, prfname, rolaccountstatus, rolfailedlogins, rolenableprofile
+FROM pg_authid, pg_profile
+WHERE pg_authid.rolprofile = pg_profile.oid
+AND rolname like '%profile_user%';
+ rolname | prfname | rolaccountstatus | rolfailedlogins | rolenableprofile
+---------------+------------+------------------+-----------------+------------------
+ profile_user9 | myprofile3 | 0 | 0 | f
+ profile_user5 | myprofile3 | 0 | 0 | f
+ profile_user7 | myprofile4 | 0 | 0 | t
+ profile_user1 | myprofile1 | 2 | 0 | f
+ profile_user2 | myprofile3 | 0 | 0 | f
+ profile_user3 | myprofile2 | 2 | 0 | f
+ profile_user4 | myprofile4 | 0 | 0 | f
+(7 rows)
+
+-- Test for get_role_status()
+SELECT get_role_status('profile_user1');
+ get_role_status
+-----------------
+ LOCKED
+(1 row)
+
+SELECT get_role_status('profile_user2');
+ get_role_status
+-----------------
+ OPEN
+(1 row)
+
+SELECT get_role_status('profile_user3');
+ get_role_status
+-----------------
+ LOCKED
+(1 row)
+
+SELECT get_role_status('profile_user4');
+ get_role_status
+-----------------
+ OPEN
+(1 row)
+
+SELECT get_role_status('profile_user5'); -- failed for user does not exist
+ get_role_status
+-----------------
+ OPEN
+(1 row)
+
+SELECT rolname, prfname, rolaccountstatus, rolfailedlogins, rolenableprofile
+FROM pg_authid, pg_profile
+WHERE pg_authid.rolprofile = pg_profile.oid
+AND rolname like '%profile_user%';
+ rolname | prfname | rolaccountstatus | rolfailedlogins | rolenableprofile
+---------------+------------+------------------+-----------------+------------------
+ profile_user9 | myprofile3 | 0 | 0 | f
+ profile_user5 | myprofile3 | 0 | 0 | f
+ profile_user7 | myprofile4 | 0 | 0 | t
+ profile_user1 | myprofile1 | 2 | 0 | f
+ profile_user2 | myprofile3 | 0 | 0 | f
+ profile_user3 | myprofile2 | 2 | 0 | f
+ profile_user4 | myprofile4 | 0 | 0 | f
+(7 rows)
+
+-- Test update pg_password_history
+UPDATE pg_password_history SET passhistpassword = 'random'; -- permission denied
+ERROR: permission denied: "pg_password_history" is a system catalog
+-- Test DROP PROFILE
+-- Failed for profile is using by user
+DROP PROFILE myprofile1;
+ERROR: profile "myprofile1" cannot be dropped because some objects depend on it
+DETAIL: profile of role profile_user1
+DROP PROFILE myprofile2;
+ERROR: profile "myprofile2" cannot be dropped because some objects depend on it
+DETAIL: profile of role profile_user3
+DROP PROFILE myprofile3;
+ERROR: profile "myprofile3" cannot be dropped because some objects depend on it
+DETAIL: profile of role profile_user2
+profile of role profile_user5
+profile of role profile_user9
+DROP PROFILE myprofile4;
+ERROR: profile "myprofile4" cannot be dropped because some objects depend on it
+DETAIL: profile of role profile_user4
+profile of role profile_user7
+DROP PROFILE pg_default; -- failed, can't drop pg_default profile
+ERROR: Disallow to drop default profile
+SELECT rolname, prfname, rolaccountstatus, rolfailedlogins, rolenableprofile
+FROM pg_authid, pg_profile
+WHERE pg_authid.rolprofile = pg_profile.oid
+AND rolname like '%profile_user%';
+ rolname | prfname | rolaccountstatus | rolfailedlogins | rolenableprofile
+---------------+------------+------------------+-----------------+------------------
+ profile_user9 | myprofile3 | 0 | 0 | f
+ profile_user5 | myprofile3 | 0 | 0 | f
+ profile_user7 | myprofile4 | 0 | 0 | t
+ profile_user1 | myprofile1 | 2 | 0 | f
+ profile_user2 | myprofile3 | 0 | 0 | f
+ profile_user3 | myprofile2 | 2 | 0 | f
+ profile_user4 | myprofile4 | 0 | 0 | f
+(7 rows)
+
+-- cleanup
+DROP USER profile_user1;
+DROP USER profile_user2;
+DROP USER profile_user3;
+DROP USER profile_user4;
+DROP USER profile_user5;
+DROP USER profile_user7;
+SELECT rolname, prfname, rolaccountstatus, rolfailedlogins, rolenableprofile
+FROM pg_authid, pg_profile
+WHERE pg_authid.rolprofile = pg_profile.oid
+AND rolname like '%profile_user%';
+ rolname | prfname | rolaccountstatus | rolfailedlogins | rolenableprofile
+---------------+------------+------------------+-----------------+------------------
+ profile_user9 | myprofile3 | 0 | 0 | f
+(1 row)
+
+-- Successful
+DROP PROFILE myprofile1, myprofile2;
+DROP PROFILE myprofile1; -- failed
+ERROR: profile "myprofile1" does not exist
+DROP PROFILE IF EXISTS myprofile2; -- OK
+NOTICE: profile "myprofile2" does not exist
+DROP PROFILE myprofile3; -- failed
+ERROR: profile "myprofile3" cannot be dropped because some objects depend on it
+DETAIL: profile of role profile_user9
+DROP PROFILE myprofile4, pg_default; -- failed
+ERROR: Disallow to drop default profile
+DROP PROFILE IF EXISTS myprofile4; -- OK
+SELECT prfname, prffailedloginattempts, prfpasswordlocktime, prfpasswordreusemax
+FROM pg_profile;
+ prfname | prffailedloginattempts | prfpasswordlocktime | prfpasswordreusemax
+------------+------------------------+---------------------+---------------------
+ pg_default | 2 | 1 | 4
+ myprofile3 | 3 | 1 | 1
+(2 rows)
+
+DROP USER profile_user9;
+DROP PROFILE myprofile3; -- OK
+SELECT prfname, prffailedloginattempts, prfpasswordlocktime, prfpasswordreusemax
+FROM pg_profile;
+ prfname | prffailedloginattempts | prfpasswordlocktime | prfpasswordreusemax
+------------+------------------------+---------------------+---------------------
+ pg_default | 2 | 1 | 4
+(1 row)
+
diff --git a/src/test/regress/expected/rangefuncs_cdb.out b/src/test/regress/expected/rangefuncs_cdb.out
index 296c7d2..31956c8 100644
--- a/src/test/regress/expected/rangefuncs_cdb.out
+++ b/src/test/regress/expected/rangefuncs_cdb.out
@@ -23,10 +23,11 @@
enable_partition_pruning | on
enable_partitionwise_aggregate | off
enable_partitionwise_join | off
+ enable_password_profile | on
enable_seqscan | on
enable_sort | on
enable_tidscan | on
-(21 rows)
+(22 rows)
-- start_ignore
create schema rangefuncs_cdb;
diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out
index d79ae94..e70e4f1 100644
--- a/src/test/regress/expected/sanity_check.out
+++ b/src/test/regress/expected/sanity_check.out
@@ -146,9 +146,11 @@
pg_operator|t
pg_opfamily|t
pg_partitioned_table|t
+pg_password_history|t
pg_policy|t
pg_proc|t
pg_proc_callback|t
+pg_profile|t
pg_publication|t
pg_publication_rel|t
pg_range|t
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 22a68a9..e1766ef 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -121,10 +121,11 @@
enable_partition_pruning | on
enable_partitionwise_aggregate | off
enable_partitionwise_join | off
+ enable_password_profile | on
enable_seqscan | on
enable_sort | on
enable_tidscan | on
-(21 rows)
+(22 rows)
-- Test that the pg_timezone_names and pg_timezone_abbrevs views are
-- more-or-less working. We can't test their contents in any great detail
diff --git a/src/test/regress/greenplum_schedule b/src/test/regress/greenplum_schedule
index c5bd9fd..9f5d4d4 100755
--- a/src/test/regress/greenplum_schedule
+++ b/src/test/regress/greenplum_schedule
@@ -298,4 +298,7 @@
# check correct error message when create extension error on segment
test: create_extension_fail
+# check profile feature
+test: profile
+
# end of tests
diff --git a/src/test/regress/sql/profile.sql b/src/test/regress/sql/profile.sql
new file mode 100644
index 0000000..1843514
--- /dev/null
+++ b/src/test/regress/sql/profile.sql
@@ -0,0 +1,286 @@
+--
+-- Test for PROFILE
+--
+
+-- Display pg_stas_activity to check the login monitor process
+SELECT COUNT(*) FROM pg_stat_activity;
+
+-- Display pg_authid, pg_roles, pg_profile and pg_password_history catalog
+\d+ pg_authid;
+\d+ pg_roles;
+\d+ pg_profile;
+\d+ pg_password_history;
+
+SELECT rolname, prfname, rolaccountstatus, rolfailedlogins, rolenableprofile
+FROM pg_authid, pg_profile
+WHERE pg_authid.rolprofile = pg_profile.oid
+AND rolname like '%profile_user%';
+SELECT prfname, prffailedloginattempts, prfpasswordlocktime, prfpasswordreusemax
+FROM pg_profile;
+
+-- Test CREATE PROFILE
+CREATE PROFILE myprofile1;
+CREATE PROFILE myprofile2 LIMIT FAILED_LOGIN_ATTEMPTS -1 PASSWORD_LOCK_TIME -2;
+CREATE PROFILE myprofile3 LIMIT FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME 1;
+CREATE PROFILE myprofile4 LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 9999 PASSWORD_REUSE_MAX 3;
+CREATE PROFILE myprofile4; -- Failed for myprofile4 already exists
+
+SELECT prfname, prffailedloginattempts, prfpasswordlocktime, prfpasswordreusemax
+FROM pg_profile;
+
+-- Failed for invalid parameters
+CREATE PROFILE myprofile5 LIMIT FAILED_LOGIN_ATTEMPTS -3;
+CREATE PROFILE myprofile6 LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME -5;
+CREATE PROFILE myprofile7 LIMIT FAILED_LOGIN_ATTEMPTS -2 PASSWORD_LOCK_TIME -1 PASSWORD_REUSE_MAX -9999;
+
+CREATE PROFILE myprofile8 LIMIT FAILED_LOGIN_ATTEMPTS 10000;
+CREATE PROFILE myprofile9 LIMIT FAILED_LOGIN_ATTEMPTS 9999 PASSWORD_LOCK_TIME 10000;
+CREATE PROFILE myprofile10 LIMIT FAILED_LOGIN_ATTEMPTS 9999 PASSWORD_LOCK_TIME -1 PASSWORD_REUSE_MAX 99999;
+
+CREATE PROFILE myprofile11 LIMIT FAILED_LOGIN_ATTEMPTS 9999 FAILED_LOGIN_ATTEMPTS 2;
+CREATE PROFILE myprofile12 LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 4 PASSWORD_LOCK_TIME 3;
+CREATE PROFILE myprofile13 LIMIT FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME 3 PASSWORD_REUSE_MAX 2 PASSWORD_REUSE_MAX 2;
+
+-- Failed for syntax error
+CREATE PROFILE myprofile14 FAILED_LOGIN_ATTEMPTS 1;
+CREATE PROFILE myprofile15 PASSWORD_LOCK_TIME -2;
+CREATE PROFILE myprofile16 PASSWORD_RESUE_MAX -1;
+CREATE PROFILE myprofile17 FAILED_LOGIN_ATTEMPTS 0;
+
+SELECT prfname, prffailedloginattempts, prfpasswordlocktime, prfpasswordreusemax
+FROM pg_profile;
+
+-- Test CREATE USER ... PROFILE
+CREATE USER profile_user1 PROFILE test; -- failed
+CREATE USER profile_user1 PROFILE pg_default;
+CREATE USER profile_user2 PASSWORD 'a_nice_long_password_123';
+CREATE USER profile_user3 PASSWORD 'a_nice_long_password_456' PROFILE myprofile3;
+CREATE USER profile_user4 ACCOUNT LOCK PROFILE myprofile4;
+
+SELECT rolname, prfname, rolaccountstatus, rolfailedlogins, rolenableprofile
+FROM pg_authid, pg_profile
+WHERE pg_authid.rolprofile = pg_profile.oid
+AND rolname like '%profile_user%';
+
+-- Test CREATE USER ... ENABLE/DISABLE PROFILE
+CREATE USER profile_user5 ENABLE PROFILE PROFILE pg_default;
+CREATE USER profile_user6 ENABLE PROFILE PROFILE; -- failed
+CREATE USER profile_user7 DISABLE PROFILE PROFILE pg_default;
+CREATE USER profile_user8 DISABLE PROFILE PROFILE; -- failed
+CREATE USER profile_user9 SUPERUSER;
+
+SELECT rolname, prfname, rolaccountstatus, rolfailedlogins, rolenableprofile
+FROM pg_authid, pg_profile
+WHERE pg_authid.rolprofile = pg_profile.oid
+AND rolname like '%profile_user%';
+
+-- Test ALTER PROFILE
+ALTER USER profile_user1 PROFILE myprofile1;
+ALTER USER profile_user2 PROFILE myprofile2;
+
+SELECT rolname, prfname, rolaccountstatus, rolfailedlogins, rolenableprofile
+FROM pg_authid, pg_profile
+WHERE pg_authid.rolprofile = pg_profile.oid
+AND rolname like '%profile_user%';
+
+ALTER USER profile_user10 PROFILE myprofile2; -- failed
+SELECT rolname, prfname, rolaccountstatus, rolfailedlogins, rolenableprofile
+FROM pg_authid, pg_profile
+WHERE pg_authid.rolprofile = pg_profile.oid
+AND rolname = 'profile_user9';
+
+ALTER USER profile_user9 PROFILE pg_default;
+SELECT rolname, prfname, rolaccountstatus, rolfailedlogins, rolenableprofile
+FROM pg_authid, pg_profile
+WHERE pg_authid.rolprofile = pg_profile.oid
+AND rolname = 'profile_user9';
+
+ALTER PROFILE myprofile1 LIMIT; -- OK
+ALTER PROFILE myprofile1 LIMIT PASSWORD_LOCK_TIME 1;
+ALTER PROFILE myprofile2 PASSWORD_LOCK_TIME 3; -- syntax error
+ALTER PROFILE myprofile2 LIMIT PASSWORD_LOCK_TIME 3; -- OK
+ALTER PROFILE myprofile3 LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_REUSE_MAX 2;
+ALTER PROFILE myprofile3 LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_REUSE_MAX 2; -- ALTER PROFILE the same values
+ALTER PROFILE myprofile4 LIMIT PASSWORD_LOCK_TIME 10 PASSWORD_REUSE_MAX -1;
+ALTER PROFILE myprofile4 LIMIT FAILED_LOGIN_ATTEMPTS 9999 PASSWORD_LOCK_TIME 9999 PASSWORD_REUSE_MAX 9999;
+ALTER PROFILE myprofile4 LIMIT FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME 0 PASSWORD_REUSE_MAX 0;
+ALTER PROFILE myprofile5 LIMIT FAILED_LOGIN_ATTEMPTS 3;
+ALTER PROFILE pg_default LIMIT FAILED_LOGIN_ATTEMPTS 2 PASSWORD_LOCK_TIME 1 PASSWORD_REUSE_MAX 3;
+
+ALTER PROFILE myprofile1 LIMIT FAILED_LOGIN_ATTEMPTS 1 FAILED_LOGIN_ATTEMPTS 2;
+ALTER PROFILE myprofile2 LIMIT PASSWORD_LOCK_TIME 2 PASSWORD_LOCK_TIME 3;
+ALTER PROFILE myprofile3 LIMIT PASSWORD_REUSE_MAX -1 PASSWORD_REUSE_MAX -2;
+ALTER PROFILE myprofile1 LIMIT FAILED_LOGIN_ATTEMPTS 1 FAILED_LOGIN_ATTEMPTS 2;
+ALTER PROFILE myprofile2 LIMIT FAILED_LOGIN_ATTEMPTS -2 PASSWORD_LOCK_TIME 2 PASSWORD_LOCK_TIME -2;
+ALTER PROFILE myprofile3 LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME -1 PASSWORD_REUSE_MAX 2 PASSWORD_REUSE_MAX 2;
+
+-- Failed for pg_default value can not be -1
+ALTER PROFILE pg_default LIMIT FAILED_LOGIN_ATTEMPTS -1;
+ALTER PROFILE pg_default LIMIT PASSWORD_LOCK_TIME -1;
+ALTER PROFILE pg_default LIMIT PASSWORD_REUSE_MAX -1;
+ALTER PROFILE pg_default LIMIT FAILED_LOGIN_ATTEMPTS 2 PASSWORD_LOCK_TIME 2 PASSWORD_REUSE_MAX -1;
+
+SELECT prfname, prffailedloginattempts, prfpasswordlocktime, prfpasswordreusemax
+FROM pg_profile;
+
+-- Test ALTER PROFILE ... RENAME TO
+ALTER PROFILE pg_default RENAME TO anyname; -- failed for pg_default profile can't be renamed
+ALTER PROFILE myprofile1 RENAME TO myprofile2; -- failed for myprofile2 already exists
+ALTER PROFILE myprofile1 RENAME TO pg_default; -- failed for pg_default already exists
+ALTER PROFILE myprofile1 RENAME TO tempname; -- OK
+ALTER PROFILE myprofile2 RENAME TO myprofile1; -- OK
+ALTER PROFILE myprofile5 RENAME TO tempname2; -- failed for myprofile5 doesn't exists
+
+SELECT prfname, prffailedloginattempts, prfpasswordlocktime, prfpasswordreusemax
+FROM pg_profile;
+
+ALTER PROFILE tempname RENAME TO myprofile2; -- OK
+
+SELECT prfname, prffailedloginattempts, prfpasswordlocktime, prfpasswordreusemax
+FROM pg_profile;
+
+-- Failed for invalid parameters
+ALTER PROFILE myprofile1 LIMIT FAILED_LOGIN_ATTEMPTS 10000;
+ALTER PROFILE myprofile2 LIMIT FAILED_LOGIN_ATTEMPTS 9999 PASSWORD_LOCK_TIME 10000;
+ALTER PROFILE myprofile3 LIMIT FAILED_LOGIN_ATTEMPTS 9999 PASSWORD_LOCK_TIME 9999 PASSWORD_REUSE_MAX 10000;
+ALTER PROFILE myprofile4 LIMIT FAILED_LOGIN_ATTEMPTS 0;
+ALTER PROFILE myprofile4 LIMIT FAILED_LOGIN_ATTEMPTS 0 PASSWORD_LOCK_TIME 0 PASSWORD_REUSE_MAX 3;
+
+ALTER PROFILE myprofile4 LIMIT FAILED_LOGIN_ATTEMPTS 9999 FAILED_LOGIN_ATTEMPTS 3;
+ALTER PROFILE myprofile4 LIMIT FAILED_LOGIN_ATTEMPTS 9999 PASSWORD_LOCK_TIME 1 PASSWORD_LOCK_TIME 2;
+ALTER PROFILE myprofile4 LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 3 PASSWORD_REUSE_MAX 4 PASSWORD_REUSE_MAX 3;
+
+SELECT prfname, prffailedloginattempts, prfpasswordlocktime, prfpasswordreusemax
+FROM pg_profile;
+
+-- Failed for syntax error
+ALTER PROFILE myprofile1 FAILED_LOGIN_ATTEMPTS 5;
+ALTER PROFILE myprofile2 PASSWORD_LOCK_TIME -2;
+ALTER PROFILE myprofile3 PASSWORD_RESUE_MAX -1;
+
+SELECT prfname, prffailedloginattempts, prfpasswordlocktime, prfpasswordreusemax
+FROM pg_profile;
+
+DELETE FROM pg_profile; -- failed for catalog can't be deleted
+
+SELECT prfname, prffailedloginattempts, prfpasswordlocktime, prfpasswordreusemax
+FROM pg_profile;
+
+-- Test ALTER USER ... PROFILE
+ALTER USER profile_user2 PROFILE myprofile3;
+ALTER USER profile_user3 PROFILE myprofile2;
+ALTER USER profile_user1 PROFILE myprofile1;
+ALTER USER profile_user4 PROFILE myprofile4;
+ALTER USER profile_user9 PROFILE myprofile3;
+
+SELECT rolname, prfname, rolaccountstatus, rolfailedlogins, rolenableprofile
+FROM pg_authid, pg_profile
+WHERE pg_authid.rolprofile = pg_profile.oid
+AND rolname like '%profile_user%';
+
+-- Test ALTER USER ... ENABLE/DISABLE PROFILE
+ALTER USER profile_user5 DISABLE PROFILE PROFILE myprofile3;
+ALTER USER profile_user5 ENABLE PROFILE PROFILE;
+ALTER USER profile_user7 ENABLE PROFILE PROFILE myprofile4;
+ALTER USER profile_user7 DISABLE PROFILE PROFILE;
+
+SELECT rolname, prfname, rolaccountstatus, rolfailedlogins, rolenableprofile
+FROM pg_authid, pg_profile
+WHERE pg_authid.rolprofile = pg_profile.oid
+AND rolname like '%profile_user%';
+
+-- Test ALTER USER ... PASSWORD
+ALTER USER profile_user1 PASSWORD 'test';
+ALTER USER profile_user1 PASSWORD 'a_nice_long_password_123';
+ALTER USER profile_user1 PASSWORD 'a_new_password';
+ALTER USER profile_user1 PASSWORD 'test';
+ALTER USER profile_user1 PASSWORD 'a_nice_long_password_123';
+ALTER USER profile_user1 PASSWORD 'a_new_password';
+ALTER USER profile_user1 PASSWORD 'ABCD';
+ALTER USER profile_user1 PASSWORD 'test';
+ALTER PROFILE pg_default LIMIT PASSWORD_REUSE_MAX 4;
+ALTER USER profile_user1 PASSWORD 'a_nice_long_password_123';
+
+ALTER USER profile_user2 PASSWORD 'test2';
+ALTER USER profile_user2 PASSWORD 'a_bad_password';
+ALTER USER profile_user2 PASSWORD 'test2';
+ALTER USER profile_user2 PASSWORD 'a_bad_password';
+ALTER USER profile_user2 PASSWORD 'a_nice_password';
+ALTER USER profile_user2 PASSWORD 'a_bad_password';
+ALTER USER profile_user2 PASSWORD 'test2';
+ALTER PROFILE myprofile3 LIMIT PASSWORD_REUSE_MAX 1;
+ALTER USER profile_user2 PASSWORD 'a_bad_password'; -- OK
+ALTER USER profile_user2 PASSWORD 'test2'; -- OK
+
+ALTER USER profile_user4 PASSWORD 'test3'; -- failed
+
+DELETE FROM pg_password_history; -- failed for catalog can't be deleted
+
+-- Test ALTER USER ... ACCOUNT LOCK/UNLOCK
+ALTER USER profile_user1 ACCOUNT LOCK;
+ALTER USER profile_user2 ACCOUNT UNLOCK;
+ALTER USER profile_user3 ACCOUNT LOCK;
+ALTER USER profile_user4 ACCOUNT UNLOCK;
+
+SELECT rolname, prfname, rolaccountstatus, rolfailedlogins, rolenableprofile
+FROM pg_authid, pg_profile
+WHERE pg_authid.rolprofile = pg_profile.oid
+AND rolname like '%profile_user%';
+
+-- Test for get_role_status()
+SELECT get_role_status('profile_user1');
+SELECT get_role_status('profile_user2');
+SELECT get_role_status('profile_user3');
+SELECT get_role_status('profile_user4');
+SELECT get_role_status('profile_user5'); -- failed for user does not exist
+
+SELECT rolname, prfname, rolaccountstatus, rolfailedlogins, rolenableprofile
+FROM pg_authid, pg_profile
+WHERE pg_authid.rolprofile = pg_profile.oid
+AND rolname like '%profile_user%';
+
+-- Test update pg_password_history
+UPDATE pg_password_history SET passhistpassword = 'random'; -- permission denied
+
+-- Test DROP PROFILE
+-- Failed for profile is using by user
+DROP PROFILE myprofile1;
+DROP PROFILE myprofile2;
+DROP PROFILE myprofile3;
+DROP PROFILE myprofile4;
+DROP PROFILE pg_default; -- failed, can't drop pg_default profile
+
+SELECT rolname, prfname, rolaccountstatus, rolfailedlogins, rolenableprofile
+FROM pg_authid, pg_profile
+WHERE pg_authid.rolprofile = pg_profile.oid
+AND rolname like '%profile_user%';
+
+-- cleanup
+DROP USER profile_user1;
+DROP USER profile_user2;
+DROP USER profile_user3;
+DROP USER profile_user4;
+DROP USER profile_user5;
+DROP USER profile_user7;
+
+SELECT rolname, prfname, rolaccountstatus, rolfailedlogins, rolenableprofile
+FROM pg_authid, pg_profile
+WHERE pg_authid.rolprofile = pg_profile.oid
+AND rolname like '%profile_user%';
+
+-- Successful
+DROP PROFILE myprofile1, myprofile2;
+DROP PROFILE myprofile1; -- failed
+DROP PROFILE IF EXISTS myprofile2; -- OK
+DROP PROFILE myprofile3; -- failed
+DROP PROFILE myprofile4, pg_default; -- failed
+DROP PROFILE IF EXISTS myprofile4; -- OK
+
+SELECT prfname, prffailedloginattempts, prfpasswordlocktime, prfpasswordreusemax
+FROM pg_profile;
+
+DROP USER profile_user9;
+DROP PROFILE myprofile3; -- OK
+
+SELECT prfname, prffailedloginattempts, prfpasswordlocktime, prfpasswordreusemax
+FROM pg_profile;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index d71d1ad..7a0216f 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -80,6 +80,7 @@
AlterOperatorStmt
AlterOwnerStmt
AlterPolicyStmt
+AlterProfileStmt
AlterPublicationStmt
AlterRoleSetStmt
AlterRoleStmt
@@ -472,6 +473,7 @@
CreateOpFamilyStmt
CreatePLangStmt
CreatePolicyStmt
+CreateProfileStmt
CreatePublicationStmt
CreateRangeStmt
CreateReplicationSlotCmd
@@ -566,6 +568,7 @@
DomainIOData
DropBehavior
DropOwnedStmt
+DropProfileStmt
DropReplicationSlotCmd
DropRoleStmt
DropStmt
