First, you have to be able to connect to the database in order to run queries. This can be achieved by
REVOKE CONNECT ON DATABASE your_database FROM PUBLIC; GRANT CONNECT ON DATABASE database_name TO user_name;
REVOKE is necessary because
The key word PUBLIC indicates that the privileges are to be granted to
all roles, including those that might be created later. PUBLIC can be
thought of as an implicitly defined group that always includes all
roles. Any particular role will have the sum of privileges granted
directly to it, privileges granted to any role it is presently a
member of, and privileges granted to PUBLIC.
If you really want to restrict your user to DML statements, then you have a little more to do:
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO user_name;
These assume that you will have only one schema (which is named ‘public’ by default).
As Jack Douglas pointed out, the above only gives the privileges for the already existing tables. To achieve the same for future tables, you have to define default privileges:
ALTER DEFAULT PRIVILEGES FOR ROLE some_role -- Alternatively "FOR USER" IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO user_name;
some_role is a role that creates the tables, while
user_name is the one who gets the privileges. Defining this, you have to be logged in as
some_role or a member of it.
And, finally, you have to do the same for the sequences (thanks to PlaidFan for pointing it out) – here it is the
USAGE privilege that you need.
assuming you want to give them all privileges – do this:
grant all privileges on database dbname to dbuser;
dbname is the name of your database and
dbuser is the name of the user.
Granting all privileges to all tables within the database is achieved with
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema_name> TO <username>; GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema_name> TO <username>;