PostgreSQL: Permission Denied For Sequence Posts_post_id_seq

Contents hide

1 PostgreSQL: permission denied for sequence posts_post_id_seq

Problem Description:

I’m new to PostgreSQL, and I’m testing a NodeJS/React app on cPanel.

I’m able to read data from the PostgresSQL database, but when I try to insert data into it I get the error "permission denied for sequence posts_post_id_seq". posts is the table name and post_id is the PK.

In node, I establish the connection to the database like so:

postgresql://${process.env.DB_USER_PROD}:${process.env.DB_PASSWORD_PROD}@${process.env.DB_HOST_PROD}/${process.env.DB_NAME_PROD}

I have checked every value in the string above and it is correct; as stated above, I can fetch data from Node using SELECT. DB_USER_PROD value is lemon_admin and in the screenshot below of phpPgAdmin I see that this user have all privileges

the server code for inserting is this:

app.post('/api/postsdb', async (req, res) => {
   
    const {title, content, author} = req.body
    try {
        const postQuery = 'INSERT INTO posts(title, author, content) VALUES($1, $2, $3)'
        await pool.query(postQuery, [title, author, content])
       
        return res.send({status: 'ok', msg:'alrigth'})
    } catch (error) {
       
        return  res.send({status: 'failed', msg: error.message})
    }   
})

This works locally with Postbird.

Since I am new to PG, it’s very possible that Im missig something

I only have access to phpPgAdmin. I dont have SSH access.

How can I grant permission for this user to not only SELECT, but also to INSERT, UPDATE and DELETE? I have to do it from phpPgAdmin, please.

Thanks

Solution – 1

To grant INSERT, UPDATE, DELETE use:

GRANT UPDATE, INSERT, DELETE ON posts TO username;

But it looks like table has some column which is fed by sequence. And user needs to have permission to use that sequence.

GRANT USAGE, SELECT ON SEQUENCE posts_post_id_seq TO username;

Also note that none of the above code has schema, do not forget to add or select before running it.

Rate this post