User cannot create schema in PostgresSQL database
5
votes
2
answers
19411
views
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?
Asked by Sergei
(151 rep)
Nov 21, 2015, 10:41 PM
Last activity: Sep 12, 2017, 05:57 AM
Last activity: Sep 12, 2017, 05:57 AM