PostgreSQL – Can’t Alter Default Privileges

permissionspostgresql

I followed the steps below.

  1. I installed PostgreSQL.
  2. I logged in as postgres via (sudo -i -u postgres)
  3. I created new user new_user via shell script createuser --interactive
  4. I granted that user permission to create databases.
  5. I created database: createdb new_db.

Now I want to give new_user privileges be able to perform all default DML operations on tables created by himself (command bellow taken from this question), so I typed:

postgres=# ALTER DEFAULT PRIVILEGES FOR new_user IN SCHEMA 
    public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO new_user;

But got an error:

ERROR:  syntax error at or near "new_user"
LINE 1: ALTER DEFAULT PRIVILEGES FOR new_user IN SCHEMA public GRANT...

I have no idea why this command is not working.

Best Answer

A key word is missing. It must be:

ALTER DEFAULT PRIVILEGES FOR ROLE new_user IN SCHEMA public GRANT...

The documentation:

ALTER DEFAULT PRIVILEGES
    [ FOR { ROLE | USER } target_role [, ...] ]

One of both ({ ROLE | USER }) is required.

Note that the particular command does not make any sense to begin with because (per documentation):

There is no need to grant privileges to the owner of an object (usually the user that created it), as the owner has all privileges by default.

If you want that to grant permissions for objects that a different user created, like postgres, then use:

And you probably wouldn't want to restrict that to a particular schema.

ALTER DEFAULT PRIVILEGES FOR ROLE postgres
GRANT ALL ON TABLES TO new_user;

And you typically want to grant privileges on sequences as well

ALTER DEFAULT PRIVILEGES FOR ROLE postgres
GRANT ALL ON SEQUENCES TO new_user;
Related Question