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

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