Thread: Users API

Users API

From
Murray Cumming
Date:
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



Discovering privileges

From
Murray Cumming
Date:
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



Re: Discovering privileges

From
Bruno Wolff III
Date:
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.


Re: Discovering privileges

From
Michael Fuhr
Date:
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/


Re: Discovering privileges

From
Michael Fuhr
Date:
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/


Re: Discovering privileges

From
Murray Cumming
Date:
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



Re: Discovering privileges

From
Michael Fuhr
Date:
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/


per-database groups? (was Discovering privileges)

From
Murray Cumming
Date:
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



Re: per-database groups? (was Discovering privileges)

From
Michael Fuhr
Date:
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/


Re: per-database groups? (was Discovering privileges)

From
Murray Cumming
Date:
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