Any need of GRANT/REVOKE CREATE TABLE | POLICY | ETC
Hi All,
During the discussion of supporting multi tenancy with the help of
row level security, because of some problems of executing any
policy that was created by an unprivileged user [1]/messages/by-id/21902.1455052932@sss.pgh.pa.us.
To avoid that problem, If we have some kind of new mechanism to
GRANT/REVOKE only CREATE POLICY from all the unprivileged
users by providing other CREATE access to them.
I just want to know is there any other such requirements that if such
option is available, it would be good or not? I don't know whether
this option is possible or not? If any such requirements are present
other than POLICY, i would like to analyze and propose a patch for
the same.
[1]: /messages/by-id/21902.1455052932@sss.pgh.pa.us
Regards,
Hari Babu
Fujitsu Australia
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Jul 19, 2016 at 2:59 AM, Haribabu Kommi
<kommi.haribabu@gmail.com> wrote:
During the discussion of supporting multi tenancy with the help of
row level security, because of some problems of executing any
policy that was created by an unprivileged user [1].To avoid that problem, If we have some kind of new mechanism to
GRANT/REVOKE only CREATE POLICY from all the unprivileged
users by providing other CREATE access to them.I just want to know is there any other such requirements that if such
option is available, it would be good or not? I don't know whether
this option is possible or not? If any such requirements are present
other than POLICY, i would like to analyze and propose a patch for
the same.
Well, there are lots of things that users can do that might cause
security exposures for other users. I think it's fair to say that
PostgreSQL - like many other systems, really - is going to work best
when either all of the users trust each other or when they are
well-isolated from each other (i.e. in separate databases). If one
user can get another to execute code, then that second user can usurp
the privileges of the first user. CREATE POLICY provides one way of
accomplishing that, but it's not necessarily qualitatively different
from any other mechanisms that we've had for a long time: CREATE
TRIGGER, CREATE FUNCTION, CREATE AGGREGATE, CREATE OPERATOR, and
CREATE VIEW are all plausible ways of inducing another user to run
your evil, malicious code.
So I don't think that the right solution is to restrict CREATE POLICY
in particular. It might be useful to have some kind of general system
for limiting the ability of certain users to run certain commands, but
I'm not sure how much demand there really is for such a thing. I
think it's already possible using ProcessUtilityHook, if you're
willing to write a small amount of C code. Do our users want more?
Very possibly. Do I know exactly what they want? No, I don't.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Aug 2, 2016 at 3:25 AM, Robert Haas <robertmhaas@gmail.com> wrote:
On Tue, Jul 19, 2016 at 2:59 AM, Haribabu Kommi
<kommi.haribabu@gmail.com> wrote:During the discussion of supporting multi tenancy with the help of
row level security, because of some problems of executing any
policy that was created by an unprivileged user [1].To avoid that problem, If we have some kind of new mechanism to
GRANT/REVOKE only CREATE POLICY from all the unprivileged
users by providing other CREATE access to them.I just want to know is there any other such requirements that if such
option is available, it would be good or not? I don't know whether
this option is possible or not? If any such requirements are present
other than POLICY, i would like to analyze and propose a patch for
the same.Well, there are lots of things that users can do that might cause
security exposures for other users. I think it's fair to say that
PostgreSQL - like many other systems, really - is going to work best
when either all of the users trust each other or when they are
well-isolated from each other (i.e. in separate databases). If one
user can get another to execute code, then that second user can usurp
the privileges of the first user. CREATE POLICY provides one way of
accomplishing that, but it's not necessarily qualitatively different
from any other mechanisms that we've had for a long time: CREATE
TRIGGER, CREATE FUNCTION, CREATE AGGREGATE, CREATE OPERATOR, and
CREATE VIEW are all plausible ways of inducing another user to run
your evil, malicious code.
Thanks for your valuable opinion and details.
So I don't think that the right solution is to restrict CREATE POLICY
in particular. It might be useful to have some kind of general system
for limiting the ability of certain users to run certain commands, but
I'm not sure how much demand there really is for such a thing. I
think it's already possible using ProcessUtilityHook, if you're
willing to write a small amount of C code. Do our users want more?
Very possibly. Do I know exactly what they want? No, I don't.
Here I attached a POC patch based on ProcessUtilityHook to restrict the
CREATE TRIGGER, CREATE FUNCTION, CREATE AGGREGATE,
CREATE OPERATOR, CREATE VIEW and CREATE POLICY commands
from normal user.
If any one is interested in this approach to restrict some of the operations
from some specific users, this patch can be further enhanced.
Regards,
Hari Babu
Fujitsu Australia
Attachments:
restrict_create_commands_poc.patchapplication/octet-stream; name=restrict_create_commands_poc.patchDownload
diff --git a/contrib/restrict_create_commands/Makefile b/contrib/restrict_create_commands/Makefile
new file mode 100644
index 0000000..81c1d70
--- /dev/null
+++ b/contrib/restrict_create_commands/Makefile
@@ -0,0 +1,18 @@
+# contrib/restrict_create_commands/Makefile
+
+MODULE_big = restrict_create_commands
+OBJS = restrict_create_commands.o $(WIN32RES)
+PGFILEDESC = "restrict_create_commands - Extension to restrict specific create commands for normal user"
+
+LDFLAGS_SL += $(filter -lm, $(LIBS))
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/restrict_create_commands
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/restrict_create_commands/restrict_create_commands.c b/contrib/restrict_create_commands/restrict_create_commands.c
new file mode 100644
index 0000000..3dcf9b2
--- /dev/null
+++ b/contrib/restrict_create_commands/restrict_create_commands.c
@@ -0,0 +1,144 @@
+/*-------------------------------------------------------------------------
+ *
+ * restrict_create_commands.c
+ *
+ * Module to restrict some specific CREATE commands from normal
+ * users
+ *
+ * Copyright (c) 2016, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * contrib/restrict_create_commands/restrict_create_commands.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "fmgr.h"
+#include "miscadmin.h"
+#include "tcop/utility.h"
+
+PG_MODULE_MAGIC;
+
+/* Saved hook values in case of unload */
+static ProcessUtility_hook_type prev_ProcessUtility = NULL;
+
+bool enable_restrict_create_commands = false;
+
+/*---- Function declarations ----*/
+
+void _PG_init(void);
+void _PG_fini(void);
+
+static void restrict_ProcessUtility(Node *parseTree, const char *queryString,
+ ProcessUtilityContext context, ParamListInfo params,
+ DestReceiver *dest, char *completionTag);
+static void check_restricted_command(Node *parseTree);
+
+/*
+ * Module load callback
+ */
+void
+_PG_init(void)
+{
+ /*
+ * we have to be loaded via shared_preload_libraries. If not, fall out
+ * without hooking into any of the main system.
+ */
+ if (!process_shared_preload_libraries_in_progress)
+ return;
+
+ DefineCustomBoolVariable("restrict_create_commands.enable",
+ "Enable to restrict specific create commands from normal users.",
+ NULL,
+ &enable_restrict_create_commands,
+ false,
+ PGC_SIGHUP,
+ 0,
+ NULL,
+ NULL,
+ NULL);
+
+ /*
+ * Install hooks.
+ */
+ prev_ProcessUtility = ProcessUtility_hook;
+ ProcessUtility_hook = restrict_ProcessUtility;
+}
+
+/*
+ * Module unload callback
+ */
+void
+_PG_fini(void)
+{
+ /* Uninstall hooks. */
+ ProcessUtility_hook = prev_ProcessUtility;
+}
+
+
+/*
+ * ProcessUtility hook
+ */
+static void
+restrict_ProcessUtility(Node *parseTree, const char *queryString,
+ ProcessUtilityContext context, ParamListInfo params,
+ DestReceiver *dest, char *completionTag)
+{
+
+ if (superuser() || !enable_restrict_create_commands)
+ {
+ standard_ProcessUtility(parseTree, queryString,
+ context, params,
+ dest, completionTag);
+ return;
+ }
+
+ check_restricted_command(parseTree);
+
+ if (prev_ProcessUtility)
+ prev_ProcessUtility(parseTree, queryString,
+ context, params,
+ dest, completionTag);
+ else
+ standard_ProcessUtility(parseTree, queryString,
+ context, params,
+ dest, completionTag);
+ return;
+}
+
+/*
+ * check_restricted_command
+ *
+ * Restrict the following operations.
+ * CREATE FUNCTION, CREATE AGGREGATE, CREATE VIEW, CREATE OPERATOR
+ * CREATE TRIGGER, CREATE POLICY
+ */
+static void
+check_restricted_command(Node *parseTree)
+{
+ switch (nodeTag(parseTree))
+ {
+ case T_CreateFunctionStmt:
+ case T_CreateTrigStmt:
+ case T_CreatePolicyStmt:
+ case T_ViewStmt:
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("command operation is restricted")));
+ break;
+ case T_DefineStmt:
+ {
+ DefineStmt *n = (DefineStmt *)parseTree;
+ if ((n->kind == OBJECT_OPERATOR)
+ || (n->kind == OBJECT_AGGREGATE))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("command operation is restricted")));
+ break;
+ }
+
+ default:
+ break;
+ }
+}