22

I am new to postgresql and I would grateful if you could please advise on how-to resolve the following error..

I have issued the followed commands:

ip_spotlight-# REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA doc FROM PUBLIC ;
ip_spotlight-# REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA net FROM PUBLIC ;

ip_spotlight# GRANT USAGE ON SCHEMA doc TO netopsapp ;

ip_spotlight-# ALTER DEFAULT PRIVILEGES IN SCHEMA doc GRANT ALL ON TABLES TO netopsapp ;
ip_spotlight-# ALTER DEFAULT PRIVILEGES IN SCHEMA net GRANT ALL ON TABLES TO netopsapp ;

Below is a list of the privileges:

ip_spotlight# \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
 doc    | postgres |                      | 
 net    | postgres |                      | 
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         | 
(3 rows)

And the error is:

python3 -m pwiz --engine=postgresql --host=x.x.x.x --port=5432 --user=netopsapp --password  --schema=doc --tables=bgp_communities ip_spotlight
Password: 
Traceback (most recent call last):
  File "/usr/lib/python3.4/site-packages/peewee.py", line 3768, in execute_sql
    cursor.execute(sql, params or ())
psycopg2.ProgrammingError: permission denied for schema doc
LINE 1: SELECT * FROM "doc"."bgp_communities" LIMIT 1

Could you please advise on how to setup the privileges so that netopsapp user to have access to the tables defined in schema doc

PS: the first 2 commands were mentioned as best practice in the postgresql book

1
  • 1
    Are you trying to create a read-only user? May 16 '17 at 2:58
26

this solves it:

postgres=# \connect ip_spotlight
You are now connected to database "ip_spotlight" as user "postgres".
ip_spotlight=# GRANT USAGE ON SCHEMA doc,net TO netops ;
ip_spotlight=# GRANT USAGE ON SCHEMA doc,net TO netopsapp ;

ip_spotlight=# GRANT SELECT ON ALL TABLES IN SCHEMA doc,net TO netops ;
ip_spotlight=# GRANT SELECT ON ALL SEQUENCES IN SCHEMA doc,net TO netops ;
ip_spotlight-# GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA doc,net TO netops ;

ip_spotlight-# GRANT ALL ON ALL TABLES IN SCHEMA doc,net TO netopsapp ;
ip_spotlight-# GRANT ALL ON ALL SEQUENCES IN SCHEMA doc,net TO netopsapp ;
ip_spotlight-# GRANT ALL ON ALL FUNCTIONS IN SCHEMA doc,net TO netopsapp ;
1
3

You're not running the GRANT / GRANT ALL when connected to the right database. Perhaps you're confusing devel and production?

When you run \dn+ on ip_spotlight you can see that you have no permissions granted to a netopsapp. That should otherwise be listed there.

8
  • thanks, do you know how to fix it ?
    – nskalis
    May 15 '17 at 21:14
  • @NikosSkalis connect to the same database python3 is connecting to and issue the GRANT statements. May 15 '17 at 21:32
  • Thanks. if could post an answer explaining also what is the following command does : GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO some_user_; that would be great :)
    – nskalis
    May 15 '17 at 21:54
  • That grants specified function and the use of any operators that are implemented on top of the function for all functions in the schema public to the user some_user for whatever database you're connected to. May 15 '17 at 22:00
  • but what execute means and what functions means ?
    – nskalis
    May 15 '17 at 22:01
1

For those wondering about security of running GRANT [ALL] USAGE statement, here is what Postgres doc have to say:

For schemas, allows access to objects contained in the specified schema (assuming that the objects' own privilege requirements are also met). Essentially 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.

Also, after revoking this permission, existing backends might have statements that have previously performed this lookup, so this is not a completely secure way to prevent object access. For sequences, this privilege allows the use of the currval and nextval functions.

For servers, this privilege enables the grantee to create, alter, and drop his own user's user mappings associated with that server. Also, it enables the grantee to query the options of the server and associated user mappings.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.