Thread: Users API
Hi. I would like to create a GUI to administer postgres users and their access rights. Is there any API for this, or do I really need to mess with the system tables directly? -- Murray Cumming murrayc@murrayc.com www.murrayc.com www.openismus.com
I'm using the pg_user and pg_group system tables to discover the list of users, and the groups to which they belong. But how can I discover the privileges that these users and groups have for the various tables. Is this also in one of the system tables? -- Murray Cumming murrayc@murrayc.com www.murrayc.com www.openismus.com
On Sun, Mar 20, 2005 at 15:50:30 +0100, Murray Cumming <murrayc@murrayc.com> wrote: > I'm using the pg_user and pg_group system tables to discover the list of > users, and the groups to which they belong. > > But how can I discover the privileges that these users and groups have > for the various tables. Is this also in one of the system tables? In recent (7.4+ I think) versions of Postgres there are functions provided for testing access to tables. Look at table 9-40 in section 9.19 of the version 8 manual.
On Sun, Mar 20, 2005 at 03:50:30PM +0100, Murray Cumming wrote: > I'm using the pg_user and pg_group system tables to discover the list of > users, and the groups to which they belong. > > But how can I discover the privileges that these users and groups have > for the various tables. Is this also in one of the system tables? You could look at pg_class.relacl, but the "Access Privilege Inquiry Functions" (has_table_privilege(), etc.) might be easier to use. http://www.postgresql.org/docs/8.0/interactive/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Sun, Mar 20, 2005 at 09:52:30AM -0600, Bruno Wolff III wrote: > In recent (7.4+ I think) versions of Postgres there are functions provided > for testing access to tables. According to the Release Notes, has_table_privilege() was introduced in 7.2. The other privilege-testing functions appear to have been introduced in 7.3 (except has_tablespace_privilege(), which is new in 8.0). -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Sun, 2005-03-20 at 08:42 -0700, Michael Fuhr wrote: > On Sun, Mar 20, 2005 at 03:50:30PM +0100, Murray Cumming wrote: > > > I'm using the pg_user and pg_group system tables to discover the list of > > users, and the groups to which they belong. > > > > But how can I discover the privileges that these users and groups have > > for the various tables. Is this also in one of the system tables? > > You could look at pg_class.relacl, I can't make much sense of that so far. Ca you give any clues on how that works? > but the "Access Privilege Inquiry > Functions" (has_table_privilege(), etc.) might be easier to use. > > http://www.postgresql.org/docs/8.0/interactive/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE Interesting. Is there anything like that for a group instead of a user? I'm most interested in privileges of a group. Thanks. -- Murray Cumming murrayc@murrayc.com www.murrayc.com www.openismus.com
On Sun, Mar 20, 2005 at 05:45:29PM +0100, Murray Cumming wrote: > On Sun, 2005-03-20 at 08:42 -0700, Michael Fuhr wrote: > > > > You could look at pg_class.relacl, > > I can't make much sense of that so far. Ca you give any clues on how > that works? See "Notes" in the GRANT documentation: http://www.postgresql.org/docs/8.0/interactive/sql-grant.html > > but the "Access Privilege Inquiry > > Functions" (has_table_privilege(), etc.) might be easier to use. > > > > http://www.postgresql.org/docs/8.0/interactive/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE > > Interesting. Is there anything like that for a group instead of a user? > I'm most interested in privileges of a group. Hmmm...not sure. I suppose you could create a user that belongs only to a particular group and check that user's privileges. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Sun, 2005-03-20 at 10:02 -0700, Michael Fuhr wrote: > On Sun, Mar 20, 2005 at 05:45:29PM +0100, Murray Cumming wrote: > > On Sun, 2005-03-20 at 08:42 -0700, Michael Fuhr wrote: > > > > > > You could look at pg_class.relacl, > > > > I can't make much sense of that so far. Ca you give any clues on how > > that works? > > See "Notes" in the GRANT documentation: > > http://www.postgresql.org/docs/8.0/interactive/sql-grant.html I notice that users and groups are per-cluster rather than per-database. Is there any way to create users and groups that are not available to other databases on the same cluster? I would expect databases to be completely independent, regardless of whether they are available from the same host and port. This seems like it should be theoretically possible - after all, you can not even connect to postgres unless you specify a database in the connection. -- Murray Cumming murrayc@murrayc.com www.murrayc.com www.openismus.com
On Mon, Mar 21, 2005 at 07:38:00PM +0100, Murray Cumming wrote: > I notice that users and groups are per-cluster rather than per-database. > Is there any way to create users and groups that are not available to > other databases on the same cluster? What do you mean by "available"? You can use pg_hba.conf to control which users can connect to which databases, if that's what you're after. Or maybe db_user_namespace will do what you want: http://www.postgresql.org/docs/8.0/interactive/runtime-config.html#GUC-DB-USER-NAMESPACE -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Mon, 2005-03-21 at 14:13 -0700, Michael Fuhr wrote: > On Mon, Mar 21, 2005 at 07:38:00PM +0100, Murray Cumming wrote: > > > I notice that users and groups are per-cluster rather than per-database. > > Is there any way to create users and groups that are not available to > > other databases on the same cluster? > > What do you mean by "available"? You can use pg_hba.conf to control > which users can connect to which databases, if that's what you're > after. Or maybe db_user_namespace will do what you want: > > http://www.postgresql.org/docs/8.0/interactive/runtime-config.html#GUC-DB-USER-NAMESPACE Thanks. That looks like what I want. -- Murray Cumming murrayc@murrayc.com www.murrayc.com www.openismus.com