Debian Question posted in
The oficial documentation can be found here.

sqlalchemy psycopg2.errors.InsufficientPrivilege: permission denied for relation <<table>> – Debian

I’ve read over 20 different questions with the same problem – and the suggested answers didn’t solve my problem. I’m still getting sqlalchemy psycopg2.errors.InsufficientPrivilege: permission denied for relation <<table>>

Environment: EC2, debian 8, postgresql, flask, sqlalchemy
my table in postgresql:

Create table Members(
id BIGSERIAL PRIMARY KEY,
joinDate TIMESTAMPTZ DEFAULT Now(),
password TEXT NOT NULL
);

directly in postgresql: INSERT INTO members (password) VALUES('123') RETURNING id; works perfect

I’ve granted my user all possible grants

grant all privileges on database <my_db> to <my_user>
grant all privileges on all table in schema public to <my_user>
grant all privileges on all relations in schema public to <my_user>

I’ve also created a .htaccess file under var/www/html with Header set Access-Control-Allow-Origin "*"

my table mapped with sqlalchemy member.py

from datetime import datetime
from sqlalchemy import create_engine, Column, String, Integer, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from marshmallow import Schema, fields
from sqlalchemy.schema import UniqueConstraint
import sqlalchemy as sa

Engine = create_engine('postgresql://<my_user>:<my_psw>@<my_db_url>/<my_dn_name>')
Session = sessionmaker(bind=Engine)
Base = declarative_base()

class MemberSchema(Schema):
    id = fields.Number()    
    password = fields.Str()

class Member(Base):
    __tablename__ = 'members'

    id = Column(Integer, primary_key=True)
    password = Column(String)

    def __init__(self, password):
        self.password = password

the code receiving the POST and doing the insert in test.py:

from flask import Flask, render_template, request,jsonify
from flask_cors import CORS, cross_origin
import logging

def register_member(password):
    session = Session()
    newMember = Member(password)
    session.add(newMember)
    session.commit()
    return newMember

app = Flask(__name__)
cors = CORS(app)
app.config['CORS_HEADERS'] = 'Content-Type'

@app.route("/register", methods=["GET", "POST"])
@cross_origin()    
def register():
    try:
        formData = request.form
        register_member(formData['password'])
        return jsonify({"message":"Regsitrated successfully!"})
    except Exception as ex:
        logging.debug(ex)

if __name__ == "__main__":
    app.run(host='0.0.0.0', port=1234, debug=True)

executing this code results in exception with these errors

DEBUG:root:(psycopg2.errors.InsufficientPrivilege) permission denied for relation members

[SQL: INSERT INTO members (password) VALUES (%(password)s) RETURNING members.id]
[parameters: {'password': '4444'}]

and

TypeError: The view function did not return a valid response. The function either returned None or ended without a return statement.

Any help would be appreciated

Tags:

4

Answers


  1. 0

    Not sure if you’re manually testing with psql or pgAdmin, but ensure you’re testing with the same account used in your code.

    Even if the user has access to a table/relation in the schema, they also need access to the schema itself:
    grant usage on schema public to <myuser>

    Also, I noticed use of BIGSERIAL which involves a sequence. You’ll probably need a grant usage on all sequences in schema public to <myuser>.

    Login or Signup to reply.
  2. 0

    Granting privileges to the user will not work, just create the tables with the same user whose credentials you’ve provided on the Flask/Django settings.

    This is how it worked for me, I know I’m late but hope this helps.

    Login or Signup to reply.
  3. 0

    It appears there are several layers of privileges in a PostgreSQL database. If one layer is missing the privilege, the query fails.

    1. Database: GRANT ALL PRIVILEGES ON DATABASE thedatabse TO theotheruser;
    2. Each schema
    3. Each table
    4. Each sequence

    for layers 2-4 it depends on the structure of the database but regardless of the structure it would involve quite a bit of typing and/or clicking to change privileges for each item in each of these layers or a complex query.

    Login or Signup to reply.
  4. 0

    This is the query i’ve generated using Grant Wizard in PgAdmin that finally worked for me to use the DB with psycopg2:

    GRANT ALL ON TABLE public.table1 TO <username>;

GRANT ALL ON TABLE public.table2 TO <username>;

GRANT ALL ON TABLE public.table3 TO <username>;

    You can try to install PgAdmin and use the same tool as well, it works fine.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search