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

Ralph Smith wrote:

>> 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
>
You could also query information_schema.table_privileges for information
about table grants.

http://www.postgresql.org/docs/8.3/interactive/infoschema-table-privileges.html