Re: I'm in need of something that should be there
От | Ralph Smith |
---|---|
Тема | Re: I'm in need of something that should be there |
Дата | |
Msg-id | 3928D952-3731-42B5-B5A7-94CF04F05268@washington.edu обсуждение исходный текст |
Ответ на | Re: I'm in need of something that should be there (Erik Jones <erik@myemma.com>) |
Список | pgsql-general |
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@myemma.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 > > >
В списке pgsql-general по дате отправления: