0

I have been through numerous other posts (to name only a few) but still stuck. The configuration is simple enough that I'll detail everything, though I think only a few of the following are relevant:

Running psql as user postgres on Ubuntu 16.04, I've created database qedadmin with 3 tables: networks, devices, and settings; there's also a sequence relation networks_networkid_seq owned by networks.networkId.

I am writing a python script to run on the same server using psycopg2 which needs to execute a SELECT on the settings table. Many examples show scripts connecting as user 'postgres', and indeed this works perfectly for me. However, I suppose it's better to use a less-privileged user for this sort of thing, so I created a user qedserver with a password.

Using this new user and password and localhost in the psycopg2 connection string, I can successfully get a connection and a cursor (and if I use incorrect user or password values, the connection fails, so I know the defined user and password and the authentication from python are all working). However, all of my attempts to execute a SELECT statement on table settings are returning code 42501: permission denied for relation settings.

I initially granted user qedserver only SELECT privileges, and only on table settings:

GRANT SELECT ON settings TO qedserver;

Since that did not work, I've gradually escalated privileges to the point that now user qedserver has ALL PRIVILEGES on all 3 tables, on the sequence relation, and on the database:

GRANT ALL PRIVILEGES ON settings TO qedserver;
GRANT ALL PRIVILEGES ON devices TO qedserver;
GRANT ALL PRIVILEGES ON networks TO qedserver;
GRANT ALL PRIVILEGES ON networks_networkid_seq TO qedserver;
GRANT ALL PRIVILEGES ON DATABASE qedadmin TO qedserver;

but I am still getting "permission denied for relation settings".

To be clear, changing just the connection string in my python script from one for user postgres to one for user qedserver makes the difference between success and failure, so I am not providing python code because I think it's irrelevant (but I can do so if you think it would help).

What am I missing?

Edited to add: there is no linux user named qedserver; I don't think there needs to be but perhaps I'm wrong about that? (further edit: I just did this experiment and it made no difference.)

Updates: Per @klin's comment and link, I can see that all of the privileges have been successfully granted: qedserver has privileges arwdDxt on the networks, devices, and settings tables, and rwU privileges on the networks_networkid_seq sequence relation; and \l reports Access Privileges on the qedadmin database of =Tc/postgres + postgres=CTc/postgres + qedserver=CTc/postgres.

I have also edited the config file (/etc/postgresql/10/main/postgresql.conf on my system) to set log_error_verbosity=VERBOSE and sent a SIGHUP to the postgresql process to re-read the config file. This added another line to the error log (/var/log/postgresql/postgresql-10-main.log on my system) for each failed attempt; now the log shows (the new line is the middle one):

qedserver@qedadmin ERROR:  42501: permission denied for relation settings
qedserver@qedadmin LOCATION:  aclcheck_error, aclchk.c:3410
qedserver@qedadmin STATEMENT:  SELECT * FROM settings WHERE deviceId = 10020;

What else can I look at or try to make headway?

Editing 4 months later to add: this was all for a new project for which I thought it would be advantageous to use postgresql for a few reasons; hitting this problem so early in development and being unable to resolve it over several days, I gave up and went with mysql instead. Not exactly a "solution" to the OP so I'm not adding it as an answer...

GISmatters
  • 431
  • 8
  • 20
  • Basically this should be enough: `grant select on settings to qedserver;` It seems that for some reason the `grant` commands have no effect. Use the query described in [this post](https://stackoverflow.com/a/39820032/1995738) to check whether the user actually has privileges granted. – klin Oct 21 '18 at 17:44
  • I need more time to absorb that post and make sure I understand it, but doing a quick test of that query minus the WHERE clause returns nothing at all... If it turns out to be the case that `GRANT` is having no effect, what would my options be at that point? – GISmatters Oct 21 '18 at 18:05
  • The query without the `WHERE` clause should return at least one row per each relation, so something is really wrong. Did you run it in `psql`? What tool do you use for plain sql? – klin Oct 21 '18 at 18:14
  • Yeah, I forgot a terminal semicolon after the paste! I'm getting results now... will take me some moments to look it all over... – GISmatters Oct 21 '18 at 18:15
  • WHERE s[1] = 'qedserver' yields 4 rows: 1 for each table w/ privileges arwdDxt and 1 for the sequence relation w/ privileges rwU; nothing for the db itself... would the db privileges show up in this query w/ that WHERE clause? It appears all the table privs are fine, so unless it's db privs I don't know where to look next. – GISmatters Oct 21 '18 at 18:58
  • It's really puzzling, it's hard to say anything. I've found this: [Postgres permission denied](https://www.postgresql.org/message-id/873174.35146.qm@web54409.mail.yahoo.com) – klin Oct 21 '18 at 20:58

0 Answers0