Обсуждение: Permissions
Hello all, I've been trying to figure a few things lately but I could not so I'm asking on the list. I need to programmatically (through JDBC): 1. Figure out if the currently logged in user can create a schema 2. Figure out if a role has USAGE privileges on a schema 3. Figure out if a role has CREATE privileges on a schema 4. Figure out if a user can create a table in a schema I've been reading the online docs but I could nowhere see any examples of finding out that information through the information_schema schema. I was advised not to poke around with the pg_* schemas since they can change frequently. What I need to do is basically ensure that a user can create a schema, then create some tables in that schema and ensure that other users can access these later. Is is possible that a user can be given for example SELECT privileges to a table in a schema but he/she would not be able to access the table because they don't have USAGE privileges on the schema which the table belongs to? I have found out that I can retrieve all the necessary permissions for tables from the information_schema.table_privileges table with no problems but I'm puzzled about database & schema privileges. Kind regards, gamehack
Milen Dzhumerov <gamehack@gmail.com> writes: > I've been trying to figure a few things lately but I could not so I'm > asking on the list. I need to programmatically (through JDBC): > 1. Figure out if the currently logged in user can create a schema > 2. Figure out if a role has USAGE privileges on a schema > 3. Figure out if a role has CREATE privileges on a schema > 4. Figure out if a user can create a table in a schema > I've been reading the online docs but I could nowhere see any examples > of finding out that information through the information_schema schema. I > was advised not to poke around with the pg_* schemas since they can > change frequently. Check out the has_foo_privilege family of functions: http://www.postgresql.org/docs/8.1/static/functions-info.html > I can retrieve all the necessary permissions for tables from the > information_schema.table_privileges table with no problems but I'm > puzzled about database & schema privileges. I think that those privileges are PG extensions to the standard, which is why the standard information_schema views don't know about them ... regards, tom lane
Tom Lane wrote: > Milen Dzhumerov <gamehack@gmail.com> writes: > >> I've been trying to figure a few things lately but I could not so I'm >> asking on the list. I need to programmatically (through JDBC): >> > > >> 1. Figure out if the currently logged in user can create a schema >> 2. Figure out if a role has USAGE privileges on a schema >> 3. Figure out if a role has CREATE privileges on a schema >> 4. Figure out if a user can create a table in a schema >> > > >> I've been reading the online docs but I could nowhere see any examples >> of finding out that information through the information_schema schema. I >> was advised not to poke around with the pg_* schemas since they can >> change frequently. >> > > Check out the has_foo_privilege family of functions: > http://www.postgresql.org/docs/8.1/static/functions-info.html > > >> I can retrieve all the necessary permissions for tables from the >> information_schema.table_privileges table with no problems but I'm >> puzzled about database & schema privileges. >> > > I think that those privileges are PG extensions to the standard, which > is why the standard information_schema views don't know about them ... > > regards, tom lane > > Thanks a lot. That's all I needed. Kind regards, gamehack