User cannot create schema in PostgresSQL database

I’m trying to setup a “deployment” user which can create and alter tables on an existing database in addition to selecting, updating, inserting and deleting records.

Here is what I’ve tried so far:

-- Create deployment user
CREATE ROLE deploy_user WITH LOGIN PASSWORD 'deploy_user';

-- Grant connect and create
GRANT CONNECT, CREATE ON DATABASE my_database TO deploy_user;

-- Grant create schema privilege
ALTER ROLE deploy_user CREATEDB;

-- Change db owner to deployment user
ALTER DATABASE my_database OWNER TO deploy_user;

-- Grant CRUD operations
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, UPDATE, DELETE ON TABLES TO deploy_user;

None of the above grants work. What I end up with is a user which can login but that’s it. I cannot select, insert, update, delete on any tables. I cannot make changes to the schema either.

Can anybody help out?

You need to provide at least CREATE permission in schema:

GRANT CREATE ON SCHEMA public TO deploy_user;

or ALL:

GRANT ALL PRIVILEGES ON SCHEMA public TO deploy_user;

See manual at http://www.postgresql.org/docs/9.4/static/sql-grant.html

This command doesn’t work because it grants privileges on the tables of the schema public.

GRANT ALL PRIVILEGES ON SCHEMA public TO deploy_user;

A user can create a schema = he must have privileges on the DataBases

--ACCESS BD
REVOKE CONNECT ON DATABASE nova FROM PUBLIC;
GRANT  CONNECT ON DATABASE nova  TO user;

--ACCESS SCHEMA
REVOKE ALL     ON SCHEMA public FROM PUBLIC;
GRANT  USAGE   ON SCHEMA public  TO user;

--ACCESS TABLES
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC ;
GRANT SELECT                         ON ALL TABLES IN SCHEMA public TO read_only ;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write ;
GRANT ALL                            ON ALL TABLES IN SCHEMA public TO admin ;

For more detail : https://stackoverflow.com/questions/17338621/what-grant-usage-on-schema-exactly-do/28849656#28849656