Thread: Privilege mess?

Hi all

I installed pglogger (https://sourceforge.net/projects/pglogger/) and  
try to insert into the "level" table as user "act" but it fails  
claiming insufficient privileges even though insert is granted to  
public (see below). What am I missing?

Kind regards Thiemo


thiemo @ thiemos-toshi ~/external_projects/pg-scd-code/trunk :-( %  
psql -U act                                                             
                                      psql (10.5 (Debian  
10.5-1.pgdg90+1))
Type "help" for help.

act=> insert into logger.LEVEL (
act(>     SCOPE,
act(>     LEVEL
act(> ) values (
act(>     'inline_code_block',
act(>     'INFO'
act(> );
ERROR:  permission denied for schema logger
LINE 1: insert into logger.LEVEL (
                     ^
act=> SELECT grantor, grantee, table_catalog, table_schema,  
table_name, privilege_type
act->   FROM information_schema.table_privileges
act->  WHERE grantor = 'logger'
act->    AND table_schema = 'logger'
act->    AND table_name = 'level';
  grantor | grantee | table_catalog | table_schema | table_name |  
privilege_type
---------+---------+---------------+--------------+------------+----------------
  logger  | PUBLIC  | act           | logger       | level      | INSERT
  logger  | PUBLIC  | act           | logger       | level      | SELECT
  logger  | PUBLIC  | act           | logger       | level      | UPDATE
  logger  | PUBLIC  | act           | logger       | level      | DELETE
(4 rows)

## Thiemo Kellner (thiemo@gelassene-pferde.biz):

> I installed pglogger (https://sourceforge.net/projects/pglogger/) and
> try to insert into the "level" table as user "act" but it fails
> claiming insufficient privileges even though insert is granted to
> public (see below). What am I missing?

Schema privileges.

> ERROR:  permission denied for schema logger
> LINE 1: insert into logger.LEVEL (

It says "permission denied for schema", so this is not about table
privileges (GRANT INSERT/UPDATE/... ON TABLE ...), but about schema
provileges. I'd guess you miss USAGE on schema logger.
See https://www.postgresql.org/docs/current/static/sql-grant.html

AFAIK those privileges are not found in information_schema, you'll
have to use pg_catalog for that.

Regards,
Christoph

-- 
Spare Space.

Quoting Christoph Moench-Tegeder <cmt@burggraben.net>:

> Schema privileges.
> provileges. I'd guess you miss USAGE on schema logger.

Thanks for the hint. I did not know about a usage grant coming from  
Oracle, so I completely ignored the possibility of the absence of a  
different grant. I just read the documentation but I cannot get grip  
on the sense behind it:

'For schemas, ... this allows the grantee to “look up” objects within  
the schema. Without this permission, it is still possible to see the  
object names, e.g. by querying the system tables.'

Does it not say you do not need the usage privilege as you can query  
the data catalog anyway to get the object's details? And in deed,  
DBeaver queries the details of the object without the usage privilege.

To carry out actions on objects one needs the specific grant like  
select anyway. I do not see the point of usage privilege.

I would be grateful I some could shed some more light for me.

Kind regards Thiemo

Does it not say you do not need the usage privilege as you can query the data catalog anyway to get the object's details? And in deed, DBeaver queries the details of the object without the usage privilege.

To carry out actions on objects one needs the specific grant like select anyway. I do not see the point of usage privilege.

  Quoting "David G. Johnston" <david.g.johnston@gmail.com>:

> Layers of security.  But yes it is generally sufficient enough to  
> simply allow usage on scheme without much
> thought while ensuring contained objects are sufficiently secured.

Thanks :-)