Postgresql – User cannot create schema in PostgresSQL database

alter-databasepermissionspostgresql

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?

Best Answer

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

Related Solutions

Postgresql – Why is a new user allowed to create a table

When you create a new database, any role is allowed to create objects in the public schema. To remove this possibility, you may issue immediately after the database creation:

REVOKE ALL ON schema public FROM public;

Edit: after the above command, only a superuser may create new objects inside the public schema, which is not practical. Assuming a non-superuser foo_user should be granted this privilege, this should be done with:

GRANT ALL ON schema public TO foo_user;

To know what ALL means for a schema, we must refer to GRANT in the doc, (in PG 9.2 there are no less than 14 forms of GRANT statements that apply to different things...). It appears that for a schema it means CREATE and USAGE.

On the other hand, GRANT ALL PRIVILEGES ON DATABASE... will grant CONNECT and CREATE and TEMP, but CREATE in this context relates to schemas, not permanent tables.

Regarding this error: ERROR: no schema has been selected to create in, it happens when trying to create an object without schema qualification (as in create table foo(...)) while lacking the permission to create it in any schema of the search_path.

Postgresql – How to create a role that cannot create or alter a table

The privilege to create tables is granted to new roles automatically. You need to REVOKE the role's CREATE privilege on the schema (not the database):

REVOKE CREATE ON SCHEMA myschema FROM manager;

Per documentation on GRANT:

CREATE

For databases, allows new schemas to be created within the database.

For schemas, allows new objects to be created within the schema. To rename an existing object, you must own the object and have this privilege for the containing schema.

For tablespaces, allows tables, indexes, and temporary files to be created within the tablespace, and allows databases to be created that have the tablespace as their default tablespace. (Note that revoking this privilege will not alter the placement of existing objects.)

And REVOKE all direct privileges any role might have in respective schemas. By default, plain roles only have privileges for the schema public and schemas they create themselves.

Best Answer

Related Solutions

Postgresql – Why is a new user allowed to create a table

Postgresql – How to create a role that cannot create or alter a table

Related Question