Re: [GENERAL] 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 5Data 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_ 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® [EMAIL PROTECTED] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 5Data 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_ 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] I'm in need of something that should be there
Ralph Smith wrote: 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, obviously you can see what permissions an object has - \dp from psql. See the manual for details. Run psql with -E if you want to see the SQL that's being run. I don't know of a shortcut to see what's been granted/revoked from a particular role. Hmm - given inheritence of permissions I'm not sure that's be the most useful approach anyway. If you want to test a particular privilege, you can use the system functions of course. http://www.postgresql.org/docs/8.3/static/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] 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 5Data 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_ 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® [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] 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 5Data 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_ 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 5Data 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_ 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; ---(end of broadcast)--- TIP 6: explain analyze is your friend
