permissions PostgreSQL 9.5

On Wed, 2016-08-10 at 13:33 +1200, Patrick B wrote:

hi guys,

just setting up a new DB using PostgreSQL 9.5.

I've created a new username for the code, called codeuser.

To give the username access to all the tables, views, etc I ran:

GRANT INSERT, SELECT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public
TO codeuser;

Is that ok? Is that enough? 

Thanks 

Patrick,

You also need to REVOKE INSERT/SELECT/UPDATE/DELETE ON SCHEMA public
FROM PUBLIC;

See section 5.8.4 in the docs.

You are much better off doing:-

CREATE ROLE codeuser . .. etc.

CREATE SCHEMA mynewapp AUTHORIZATION codeuser;

Then only codeuser can create tables etc. in that schema.
Set the search path in postgresql.conf to include the new schema.
You need to connect as codeuser.

HTH,
Rob

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general