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

SUPER Eric! Very explanatory!

Thank you!

Ralph Smith
=====================

On Mar 6, 2008, at 10:17 AM, Erik Jones wrote:

>
> On Mar 6, 2008, at 11:52 AM, 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?
>
> Well, the different database objects have the permissions that have
> been granted to them in columns in the catalog tables,
> pg_database.datacl, pg_class.relacl, and pg_proc.procacl for
> databases, relations, and functions, respectively. That gives
> postgres the ability to answer the question "Does this user have a
> given access permission for this object?" So, what you could do is
> much the same: for a given object, search through its *acl entry and
> determine if the given role is there with the pertinent permission,
> or if any group* roles in which the given role has membership does.
> Note for group role memberships that if the given role was not
> created with the INHERIT keyword then they won't have the group role
> permissions directly but, given that they do have the ability to
> change to the given group role, for your purposes, you could
> probably consider that a yes. Also, note that you'd need to follow
> the role memberships up any role "chains", for example where role
> John is in role Billing which is in role Admin or some such.
>
> * Here I use the term group simply to denote a role in which other
> roles have membership.
>
>
> Erik Jones
>
> DBA | Emma®
> erik(at)myemma(dot)com
> 800.595.4401 or 615.292.5888
> 615.292.0777 (fax)
>
> Emma helps organizations everywhere communicate & market in style.
> Visit us online at http://www.myemma.com
>
>
>