Search code examples
postgresqlprivileges

ERROR: syntax error at or near "FROM" when setting default privileges

I am trying to create a user, test, which only has INSERT privileges to a database, test2. This database is populated with tables with data inserted by the postgres user.

The user test should only be able to INSERT data into the database tables and nothing else. It should not be able to delete or alter any data currently in the database.

I attempted using the command ALTER DEFAULT PRIVILEGES FOR ROLE test GRANT INSERT ON TABLES FROM "test2"; but got an error

ERROR:  syntax error at or near "FROM"
LINE 1: ...T PRIVILEGES FOR ROLE test GRANT INSERT ON TABLES FROM "test...
                                                             ^

I have also attempted using the GRANT command standalone but seem to be getting an error about relations not existing.

Would appreciate any help.

Solution

  • It looks like you mean to. The syntax here (from the docs) is:

    ALTER DEFAULT PRIVILEGES
    [ FOR { ROLE | USER } target_role [, ...] ]
    [ IN SCHEMA schema_name [, ...] ]
    abbreviated_grant_or_revoke

where abbreviated_grant_or_revoke is one of:

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON TABLES
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

    so

    ALTER DEFAULT PRIVILEGES FOR ROLE test GRANT INSERT ON TABLES TO "test2";