2

I've tried just about everything to get a user permissions for some relations in postgres. Here's what I've tried:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myuser;
ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO myuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO myuser;

I've changed my pg_hba.cnf to this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident

I'm sure these commands do something if you fiddle around with them right, but why don't any of these commands do anything anywhere close to what they say?

I'm using python:

>>> conn = psycopg2.connect(host="localhost", port="5432", dbname="mydb",user="myuser", password="mypassword")
>>> cur = conn.cursor()
>>> cur.execute('select count(*) from mytable')

This is the error I'm getting:

>>> cur.execute('select count(*) from public.mytable')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
psycopg2.ProgrammingError: permission denied for relation mytable

I also tried: 

ALTER DATABASE mydb OWNER TO myuser;

And a restart, of course.

moooeeeep
  • 31,622
  • 22
  • 98
  • 187
Alexander Kleinhans
  • 5,950
  • 10
  • 55
  • 111
  • What command do you execute before you get the error? – Draco Ater Mar 02 '17 at 07:43
  • Is the table in schema `public`? Can you access it as user `myuser` when you connect with `psql`? – Laurenz Albe Mar 02 '17 at 07:53
  • Stating the obvious: is `mytable` in the schema public? Or is it maybe in a different schema on the search_path? –  Mar 02 '17 at 07:53
  • It might not be in the schema public. How would I get it into there? When I do `\l`, it's a database that I created from the shell postgres default user which I has do `sudo su postgresql` to get into... Also not sure about the difference between a schema and a database in psql... – Alexander Kleinhans Mar 02 '17 at 07:56
  • Actually, my table is `public.mytable` so I believe it's in the public schema. – Alexander Kleinhans Mar 02 '17 at 08:02
  • Please provide the full error message. psycopg2 produces an exception and a traceback, right? Also, when the `connect()` succeeds, the `pg_hba.conf` should not be relevant for solving this problem. – moooeeeep Mar 02 '17 at 08:13
  • Are you a superuser? Does another user own the database and the table? Did your grants and database alterings succeed? Are there any privileges revoked for your user? – moooeeeep Mar 02 '17 at 08:23
  • I was the user `postgres` when creating the tables. `myuser` when accessing from python. Thanks again for the help though. I really appreciate it. – Alexander Kleinhans Mar 02 '17 at 08:29

3 Answers3

1

Did you assigned OWNER myuser in mydb? It's a simple way to solve your problem:

CREATE DATABASE mydb OWNER myuser;

José T. Lin
  • 149
  • 6
1

So per this answer, I was able to get access by granting it individually for a table, which I guess I'll have to do with each table.

Still not sure why this worked.

GRANT ALL PRIVILEGES ON TABLE mytable TO myuser;

and these didn't.

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myuser;
ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO myuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO myuser;
Community
  • 1
  • 1
Alexander Kleinhans
  • 5,950
  • 10
  • 55
  • 111
  • The first statement would have given you the necessary permissions. I suspect that you issued these statements *before* you created the table. Then the first statement would not have any effect, since it only affects tables that already exist. But the third statement should have taken care of tables created in the future - except if the user who issued it was a different user than the one who ran `CREATE TABLE` (in that case you'd have needed the clause `FOR USER someuser` with the user who runs `CREATE TABLE`). That is the only explanation I can come up with. – Laurenz Albe Mar 02 '17 at 08:39
  • What version of postgreSQL is your server? – moooeeeep Mar 02 '17 at 09:49
1

There seem to be a few variations of ALTERT DEFAULT PRIVILEGES around. In my case

ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO myuser;

did not work. I had to include the schema as well:

-- change PUBLIC to appropriate schema
ALTER DEFAULT PRIVILEGES IN SCHEMA PUBLIC GRANT ALL ON TABLES TO myuser;
user783836
  • 3,099
  • 2
  • 29
  • 34