Re: I'm in need of something that should be there

> Ralph Smith wrote:
>
> > And should be easier to find in the manual!
> >
> > I've looked in many related chapters of the 8.2 manual for a way to
> > find out
> > WHY a specific user has access to a database.
> >
> > Chapter 5    Data Definition
> > Chapter 18  Database Roles & Privileges
> > Chapter 20  Client Authorization
>
>
> > postgres=# select * from pg_roles;
> >   rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb |
> rolcatupdate | rolcanlogin | rolconnlimit | rolpassword |
> rolvaliduntil | rolconfig |  oid
> > ----------+----------+------------+---------------+-------------
> +--------------+-------------+--------------+-------------
> +---------------+-----------+-------
> > lines removed
> >   smithrn  | f        | f          | t             | t           |
> f            | t           |           -1 | ********    |
> infinity      |           | 16393
> >
> > This user can connect via his .pgpass or manually since he's in a
> > netID range that requires a password.
> > But he can create and drop tables in any database!!!
> >
> > Why is that?
> > How can I find out what he can do?
> > The GRANT and REVOKE sections say nothing about which pg_xxxx tables
> > to query, and I've been lookin'!
> >
> >
> > Thank you!
> >
> > Ralph Smith
> >
> > =====================
>
> http://www.postgresql.org/docs/8.3/interactive/sql-grant.html
>
> "Depending on the type of object, the initial default privileges might
> include granting some privileges to PUBLIC. The default is ...
> CONNECT
> privilege and TEMP table creation privilege for databases"
>
> http://www.postgresql.org/docs/8.3/interactive
> /ddl-schemas.html#DDL-SCHEMAS-PUBLIC
>
> Note that by default, everyone has CREATE and USAGE privileges on
> the schema
> public. This allows all users that are able to connect to a given
> database
> to create objects in its public schema. If you do not want to allow
> that,
> you can revoke that privilege:
>
> REVOKE CREATE ON SCHEMA public FROM PUBLIC;
====================
Ralph's followup.

So am I to assume that there is no way to query just what privs a user/
role has on an object, anything, from a DB to an index?


Thank you again,
Ralph Smith