500 Error adding a user to group


(Jared Needell) #1

Getting this error when adding a user to a large group

Looks to be an issue with the database


(Rafael dos Santos Silva) #2

Did you mess with the database directly before, or did an import?

This is happening because the postgresql table sequence is behind the already used serials.


(Jared Needell) #3

I made a PSQL script to import users into group from an Active Directory group.

I ran the script to auto-increment the unique id by looking up the highest unique id value.


(Jared Needell) #4

How do you reset the sequence or make the script work to not cause this error?

I tried rebuilding the app but that didn’t fix i t.


(Mittineague) #5

I don’t know what script you used, but

the id field is “serial” - i.e. in MySQL terminology "auto increment"
That is,you do not need or want to INSERT specific values into them.
http://www.postgresql.org/docs/8.2/static/sql-insert.html

If you need to maintain specific ids for some reason (though I can’t think of any reason to offhand) you should do an UPDATE not an INSERT
http://www.postgresql.org/docs/9.5/static/sql-update.html

Don’t confuse “id” with “group_id” or “user_id”

group_users
id    serial    primary key 
group_id    integer    fkey groups 
user_id    integer    fkey users 
created_at    timestamp 
updated_at    timestamp 
owner    boolean    default false 
notification_level    integer    default 2

(Jared Needell) #6

Here’s the snippet from the script that imported users into groups directly into PSQL

  $id = (Get-ODBC-Data -query "SELECT MAX(id) FROM group_users").max +1
  $query = "INSERT INTO group_users (id, group_id, user_id, created_at, updated_at, owner, notification_level) VALUES ('"+ $id +"','" + $group_id + "','" + $user_id + "','" + (Get-Date -Format "yyyy-MM-dd HH:mm:ss.fffff") + "','" + (Get-Date -Format "yyyy-MM-dd HH:mm:ss.fffff") + "','f','2')"

(Mittineague) #7

Dollar sign variables? Is that PHP?
Concatenating with “+” instead of “.”?

Anyway, for serial fields you want to insert DEFAULT - not in quotes.
Same for integer and boolean fields - not in quotes.

i.e. only the timestamp should be in quotes


(Jared Needell) #8

PowerShell, the script works. I can still add and remove users from group via PSQL. But I get an Error 500 trying to do so from admin.

https://social.technet.microsoft.com/Forums/office/en-US/5a4a63f4-c22a-4cc5-8b7b-3cfd773a8a35/powershell-odbc-postgresql-insert-query?forum=winserverpowershell


(Rafael dos Santos Silva) #9

When inserting into a postgresql serial column you just omit the column on the insert statement and postgresql sets it automatically and updates the sequence accordingly.

If you insert the value manually, this happens.

To fix the mess:

SELECT setval('foo', 42);
-- where Foo is the sequence name and 42 the max id on the table. 

(Jared Needell) #10

If I take out the INSERT for ID, I’ll get this error

ERROR: permission denied for sequence group_users_id_seq
********** Error **********

ERROR: permission denied for sequence group_users_id_seq
SQL state: 42501


(Jared Needell) #11

If I run the INSERT command as a superuser, the INSERT command completes successfully.


(Jared Needell) #12

OK I think I’m good

GRANT USAGE, SELECT ON SEQUENCE group_users_id_seq TO mydbuser;