2

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();
    }
Art
  • 231
  • 1
  • 2
  • 7

3 Answers3

0

ALTER USER user1 WITH SUPERUSER; makes your user a superuser with all privileges. However, you can still allocate some other privileges separately like CREATEUSER, CREATEROLE, LOGIN and so on. Follow the official documentation here: https://www.postgresql.org/docs/9.5/static/sql-alteruser.html

You'll have to first login with a privileged user to give these permissions to secondary user and it is postgres by default.

UPDATE Well if all the permissions seem to be fine, in order to skip mentioning id field in your insert query, try changing the data type of the id field to SERIAL. After that, may be your initial query INSERT INTO formats (format) VALUES ('some value'); will work.

Pragun
  • 1,123
  • 8
  • 20
  • I tried that earlier - no success. I did the same again. ALTER USER user1 WITH SUPERUSER; then (to make sure) GRANT ALL PRIVILEGES ON formats TO user1; - it is the same – Art Nov 04 '16 at 02:31
  • what does `\du` result for privileges on `user1`? – Pragun Nov 04 '16 at 02:35
  • Superuser, create role, create db – Art Nov 04 '16 at 02:41
  • well that seems pretty fine then. You'll have to manually insert a random id from your query. Whenever an application interacts with PostgreSQL using and ORM, the primary key fields are automatically generated. However, to my experience, whenever you write a hardcoded query like that, the database asks for the primary key field as well. So why not try with a dummy primary key field for the id? – Pragun Nov 04 '16 at 02:44
0

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?

Hambone
  • 15,600
  • 8
  • 46
  • 69
  • Try/catch throwing External component has thrown an exception when the user is not superuser. I think it is from Npgsql and usually (from my observations) it happens when something is wrong with the command. The other strange thing is after changing privileges to superuser several times it started working. I mean now if the user is superuser - it works, otherwise it does not work. But how about if I do not want the user to be able to delete data? – Art Nov 04 '16 at 03:47
  • You absolutely should be able to do that with a normal user. It would be chaos if we had to grant every user superuser rights just to do selective rights on selective tables. Something is totally not adding up. Can you add your relevant C# to your question and clarify what changes when it works or does not? – Hambone Nov 04 '16 at 03:49
  • And explicitly adding the schema didn't help? – Hambone Nov 04 '16 at 03:50
  • And one more thing. user1 without superuser privileges works fine on tables with primary key = string. Very strange – Art Nov 04 '16 at 03:50
  • Please, see my code in my first post. adding schema does not make change. I used dbName.public.formats - is this all right? – Art Nov 04 '16 at 04:02
  • I downgraded user1 to normal user, then when I use psql to insert I get "permission denied for sequence formats_format_id_seq". I also tried insert into formats (format_id, format) VALUES (nextval('formats_format_id_seq'), 'bla') - "permission denied for sequence formats_format_id_seq". Seems like an issue with permissions – Art Nov 04 '16 at 04:14
0

I think I found the answer. I need to GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO user1;

GRANT USAGE - For sequences, this privilege allows the use of the currval and nextval functions.

source: ERROR: permission denied for sequence cities_id_seq using Postgres

Community
  • 1
  • 1
Art
  • 231
  • 1
  • 2
  • 7