database

creating a database with encoding UTF-8 with a specified user as owner

grant permissions on databases and database objects

grant permission to connect to a database

grant all privileges on a database to a specified user (role). The ‘privileges’ keyword is optional in PostgreSQL but mandatory in strict SQL.

all priviledges may be a bit omnicidal. You might want to fine tune the priviledges.

The general form to grant priviledges to database objects is

The database objects used most often and the possible priviledges for their use are (I’m not going to use EBNF here):

table

grant

• select
• insert
• update
• delete
• truncate
• drop

on table table-name
on all tables in schema schema-name

sequence

grant

• usage
• select
• update
• drop

on sequence sequence-name
on all sequences in schema schema-name

note that in order to get the next value of a sequence, i.e. in JPA when persisting entities, you need the priviledge select.

database

grant

• create
• connect
• drop

on database database-name

schema

grant

• create
• usage

on schema schema-name