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
On Tuesday, October 9, 2018, Thiemo Kellner <thiemo@gelassene-pferde.biz>
wrote:
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.
Basically lacking USAGE does not prevent someone from knowing objects
within the schema exist, it just prevents queries from referencing them as
named objects.
To carry out actions on objects one needs the specific grant like select
anyway. I do not see the point of usage privilege.
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.
David J.