How to grant privileges in PostgreSQL

Granting privileges in PostgreSQL keeps application access predictable while limiting damage from mistakes, compromised credentials, or overly-permissive roles. A least-privilege role design also makes audits and incident response far easier than untangling blanket grants later.

PostgreSQL authorization is role-based and enforced at multiple layers: database-level access (such as CONNECT), schema-level access (such as USAGE), and object-level access for tables, sequences, and functions. The GRANT statement updates access control lists (ACLs) on those objects, and object ownership remains separate from granted privileges.

Default privileges matter for long-lived databases because GRANT … ON ALL TABLES only covers existing objects. ALTER DEFAULT PRIVILEGES applies only to future objects and only for objects created by the role specified (or the role executing the command), so the “owner/migration” role must be targeted correctly. Managed PostgreSQL services typically lack an OS-level postgres user, so the same SQL applies but the connection method differs.

1. Grant database connection permission to the application role.

   $ sudo -u postgres psql -d postgres -c "GRANT CONNECT ON DATABASE appdb TO appuser;"
   GRANT

2. Grant schema usage permission to the application role.

   $ sudo -u postgres psql -d appdb -c "GRANT USAGE ON SCHEMA public TO appuser;"
   GRANT

   Granting CREATE on a schema allows object creation inside that schema, which is frequently broader than intended for a runtime role.

3. Grant required privileges on all existing tables in the schema to the application role.

   $ sudo -u postgres psql -d appdb -c "GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;"
   GRANT

4. Grant required privileges on all existing sequences in the schema to the application role.

   $ sudo -u postgres psql -d appdb -c "GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO appuser;"
   GRANT

   USAGE enables nextval(), SELECT enables currval(), and UPDATE enables setval() on sequences.

5. Grant execute permission on all existing functions in the schema when stored functions are used.

   $ sudo -u postgres psql -d appdb -c "GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO appuser;"
   GRANT

6. Set default privileges for future tables created by the object owner role.

   $ sudo -u postgres psql -d appdb -c "ALTER DEFAULT PRIVILEGES FOR ROLE appowner IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO appuser;"
   ALTER DEFAULT PRIVILEGES

   The role in FOR ROLE must match the role that creates tables in the schema, which is commonly a migration/owner role rather than the runtime role.

7. Set default privileges for future sequences created by the object owner role.

   $ sudo -u postgres psql -d appdb -c "ALTER DEFAULT PRIVILEGES FOR ROLE appowner IN SCHEMA public GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO appuser;"
   ALTER DEFAULT PRIVILEGES

8. Set default privileges for future functions created by the object owner role when stored functions are used.

   $ sudo -u postgres psql -d appdb -c "ALTER DEFAULT PRIVILEGES FOR ROLE appowner IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO appuser;"
   ALTER DEFAULT PRIVILEGES

9. List object privileges to confirm the role appears in access control lists.

   $ sudo -u postgres psql -d appdb -c "\dp"
                                                   Access privileges
    Schema |          Name           |       Type        |     Access privileges     | Column privileges | Policies 
   --------+-------------------------+-------------------+---------------------------+-------------------+----------
    public | events                  | partitioned table | postgres=arwdDxt/postgres+|                   | 
           |                         |                   | appuser=arwd/postgres     |                   | 
    public | orders                  | table             | appowner=arwdDxt/appowner+|                   | 
           |                         |                   | appuser=arwd/appowner     |                   | 
    public | orders_id_seq           | sequence          | appowner=rwU/appowner    +|                   | 
           |                         |                   | appuser=rwU/appowner      |                   | 
   ##### snipped #####

10. List default privileges to confirm future objects inherit the intended access.

    $ sudo -u postgres psql -d appdb -c "\ddp"
                  Default access privileges
      Owner   | Schema |   Type   |   Access privileges   
    ----------+--------+----------+-----------------------
     appowner | public | function | appuser=X/appowner
     appowner | public | sequence | appuser=rwU/appowner
     appowner | public | table    | appuser=arwd/appowner