* Tom Lane ([EMAIL PROTECTED]) wrote: > Stephen Frost <[EMAIL PROTECTED]> writes: > > Ok. Can I get some help defining what the New Truth will look like > > then? I understand users and groups pretty well but I'm not 100% sure > > about roles. > > So I'm envisioning something like [...] > It might be better to call this by some name other than "pg_role", [...] > It might be better to lose the rolmembers/roladmin columns and instead > represent membership in a separate table, roughly [...] > One thing that needs to be thought about before going too far is exactly > how ACL rights testing will work, particularly in the face of roles > being members of other roles. That is the one performance-critical > operation that uses these data structures, so we ought to design around > making it fast.
Alright, here's a patch against head which adds in the tables pg_authid
and pg_auth_members as described in your previous mail. I've gotten a
bit farther than this in terms of implementation but here's something
for people to comment on, if they'd like to.
I've been thinking about the performance issues some and have to admit
that I havn't really come to much of a solution. It seems to me that
there's two ways to come at the issue:
a) start from the user:
Search for useroid in pg_auth_members.member
For each returned role, search for that role in member column
Repeat until all roles the useroid is in have been found
[Note: This could possibly be done and stored per-user on connection,
but it would mean we'd have to have a mechanism to update it when
necessary, possibly instigated by the user, or just force them to
reconnect ala unix group membership]
Look through ACL list to see if the useroid has permission or if any
of the roles found do.
b) start from the ACL list:
Search for each roleoid in pg_auth_members.role
For each returned member, search for that member in role column
Upon member == useroid match is found check for permission, if
granted then stop, otherwise continue processing
Has the advantage that the search stops once it's been determined
that permission is there and doesn't require updating.
If we do the user-part-of-which-roles search upon connection I expect
'a' would be quite fast, obviously it has it's drawbacks though. If we
feel that's not acceptable then I'm thinking 'b' would probably be
faster given that the ACL list is probably generally small and we can
short-circuit. I'm afraid 'b' might still be too slow though, comments?
thoughts? better ideas?
Thanks,
Stephen
diff -uNr pgsql.1/src/include/catalog/pg_auth_members.h
pgsql.2/src/include/catalog/pg_auth_members.h
--- pgsql.1/src/include/catalog/pg_auth_members.h 1969-12-31
19:00:00.000000000 -0500
+++ pgsql.2/src/include/catalog/pg_auth_members.h 2005-01-27
23:24:17.000000000 -0500
@@ -0,0 +1,55 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_auth_members.h
+ * definition of the system "authorization identifier members" relation
(pg_auth_members)
+ * along with the relation's initial contents.
+ * pg_group is now a public accessible view on pg_authid and
pg_auth_members.
+ *
+ *
+ * Portions Copyright (c) 2005, PostgreSQL Global Development Group
+ *
+ * $Id$
+ *
+ * NOTES
+ * the genbki.sh script reads this file and generates .bki
+ * information from the DATA() statements.
+ *
+ * WHENEVER the definition for pg_auth_members changes, the
+ * view creation of pg_shadow/pg_group must be changed in
initdb.sh!
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef PG_AUTH_MEMBERS_H
+#define PG_AUTH_MEMBERS_H
+
+/* ----------------
+ * pg_auth_members definition. cpp turns this into
+ * typedef struct FormData_pg_auth_members
+ * ----------------
+ */
+CATALOG(pg_auth_members) BOOTSTRAP BKI_SHARED_RELATION BKI_WITHOUT_OIDS
+{
+ Oid role;
+ Oid member;
+ Oid grantor;
+ bool admin_option
+} FormData_pg_auth_members;
+
+/* ----------------
+ * Form_pg_authid corresponds to a pointer to a tuple with
+ * the format of pg_authid relation.
+ * ----------------
+ */
+typedef FormData_pg_auth_members *Form_pg_auth_members;
+
+/* ----------------
+ * compiler constants for pg_auth_members
+ * ----------------
+ */
+#define Natts_pg_auth_members 4
+#define Anum_pg_auth_members_role 1
+#define Anum_pg_auth_members_member 2
+#define Anum_pg_auth_members_grantor 3
+#define Anum_pg_auth_members_admin_option 4
+
+#endif /* PG_AUTH_MEMBERS_H */
diff -uNr pgsql.1/src/include/catalog/pg_authid.h
pgsql.2/src/include/catalog/pg_authid.h
--- pgsql.1/src/include/catalog/pg_authid.h 1969-12-31 19:00:00.000000000
-0500
+++ pgsql.2/src/include/catalog/pg_authid.h 2005-01-27 23:24:03.000000000
-0500
@@ -0,0 +1,81 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_authid.h
+ * definition of the system "authorization identifier" relation
(pg_authid)
+ * along with the relation's initial contents.
+ * pg_shadow is now a public accessible view on pg_authid.
+ * pg_group is now a public accessible view on pg_authid.
+ *
+ *
+ * Portions Copyright (c) 2005, PostgreSQL Global Development Group
+ *
+ * $Id$
+ *
+ * NOTES
+ * the genbki.sh script reads this file and generates .bki
+ * information from the DATA() statements.
+ *
+ * WHENEVER the definition for pg_authid changes, the
+ * view creation of pg_shadow/pg_group must be changed in
initdb.sh!
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef PG_AUTHID_H
+#define PG_AUTHID_H
+
+#include "utils/timestamp.h"
+
+/* ----------------
+ * pg_authid definition. cpp turns this into
+ * typedef struct FormData_pg_authid
+ * ----------------
+ */
+CATALOG(pg_authid) BOOTSTRAP BKI_SHARED_RELATION
+{
+ NameData rolname;
+ bool rolsuper; /* read this field via
superuser() only */
+ bool rolcreateuser;
+ bool rolcreatedb;
+ bool rolcatupdate;
+ bool rolcanlogin;
+
+ /* remaining fields may be null; use heap_getattr to read them! */
+ TimestampTz rolvaliduntil; /* actually abstime */
+ text rolpassword;
+ text rolconfig[1];
+} FormData_pg_authid;
+
+/* ----------------
+ * Form_pg_authid corresponds to a pointer to a tuple with
+ * the format of pg_authid relation.
+ * ----------------
+ */
+typedef FormData_pg_authid *Form_pg_authid;
+
+/* ----------------
+ * compiler constants for pg_authid
+ * ----------------
+ */
+#define Natts_pg_authid 9
+#define Anum_pg_authid_rolname 1
+#define Anum_pg_authid_rolsuper 2
+#define Anum_pg_authid_rolcreateuser 3
+#define Anum_pg_authid_rolcreatedb 4
+#define Anum_pg_authid_rolcatupdate 5
+#define Anum_pg_authid_rolcanlogin 6
+#define Anum_pg_authid_rolvaliduntil 7
+#define Anum_pg_authid_rolpassword 8
+#define Anum_pg_authid_rolconfig 9
+
+/* ----------------
+ * initial contents of pg_authid
+ *
+ * The uppercase quantities will be replaced at initdb time with
+ * user choices.
+ * ----------------
+ */
+DATA(insert OID = 142 ( "POSTGRES" t t t t t _null_ _null_ _null_ ));
+
+#define SUPERUSEROID 142
+
+#endif /* PG_AUTHID_H */
signature.asc
Description: Digital signature
