postgresql user cannot write into tables with primary key

Question

I am developing an application to write and read data into/from PostgresSql (C# + Npgsql). There is a table called "formats" with 2 columns: format_id (serial, pk), format (text, not null). SQL command that I use:

INSERT INTO formats (format) VALUES ('some value')

(I am not specifying the primary key as the db should generate it).

There is a user called user1. I granted SELECT, UPDATE, INSERT to user1 on formats. When I use admin's credentials (postgres+pass) the application works fine, when I use user1+password it does not insert any data and no error messages. Despite this user1+password works fine on other test tables without primary keys as serial.

Tried lots of things including drop and recreation of the table, user1, debugging and etc. Any ideas what could be wrong? Is something with privileges?

Thanks in advance.

EDITED: here is my method:

public void WriteInto2FieldTable (string tblName, List<Tuple<string, string>> fieldValuePair)
    {
        CreateConnection();

        using (connection)
        {
            //do staff here
            connection.Open();

            using (var command = new NpgsqlCommand())
            {
                command.Connection = connection;
                //string value;
                //string field;

                foreach (Tuple<string, string> fvp in fieldValuePair)
                {
                    // Insert data
                    //command.CommandText = string.Format("INSERT INTO {0} (format_id, {1}) VALUES (nextval('formats_format_id_seq'),'{2}')", tblName, fvp.Item1, fvp.Item2);
                    command.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES ('{2}')", tblName, fvp.Item1, fvp.Item2);

                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                }

            }
        }

        //Remove FiledValuePairs from list

        fieldValuePair.Clear();
    }

Hambone Hambone · Accepted Answer · 2016-11-04T02:41:27

The primary key totally shouldn't matter to Postgres or to NpgSql, especially on an insert.

These are guesses, but two thoughts:

It might be the default schema of the admin id is different from that of user1. Explicitly declare the schema:

insert into <schema>.formats (format) values ('foo')

This is really a shot in the dark, but if that doesn't work, manually invoke the sequence:

insert into <schema>.formats (format_id, format) values
(nextval('formats_format_id_seq'), 'foo')

Also, when you run ExecuteNonQuery() in C#, what does it return? A "1" would mean it was successful, and a 0 would mean it was successful at doing nothing. Do you have a try/catch, and is it passing successfully?

postgresql user cannot write into tables with primary key

3 Answers