3

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?

1
  • 1
    I have rolled back your edit. If you want to document what you did to solve your problem, please do so in an answer, thank you. – Paul White Sep 12 '17 at 5:57
2

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

1
  • GRANT ALL PRIVILEGES ON SCHEMA public TO deploy_user; This won't work as mentioned in the question above, ned to give grant on the database. – technazi Oct 1 '20 at 20:31
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.