How to allow user access to non-owned objects in Postgres?

Question

I am in the process of creating a pre-prod Postgres database, and want to ensure the accessing user has limited access.

I want to setup tables in such a way that the accessing user can't CREATE, DROP or ALTER objects. Currently the only way I can see it to change the ownership to another user, such as postgres and then somehow grant only the permissions needed:

 ALTER TABLE mytable SET OWNER TO postgres;
 REVOKE connect ON DATABASE mydatabase FROM PUBLIC;
 GRANT USAGE, SELECT, UPDATE ON ALL TABLES IN SCHEMA public TO dbuser;
 GRANT connect ON DATABASE mydatabase TO dbuser;

The issue is when I do the above I am unable to do a simple SELECT when I connect with the user the grant was applied to. For example:

 select * from mytable;

Yields:

ERROR: permission denied for relation mytable

How to get this right?
Additionally, should I use a named schema other than public?

Answer 1

1. Read the manual on GRANT and ALTER TABLE.

2. There is no USAGE privilege for tables. You would see an error message if you tried your GRANT command.

3. You need permissions on the schema, too. Here USAGE is right. But you certainly want to revoke CREATE.

4. Ownership for sequences that are owned by serial columns is changed to the new table owner automatically. But any other sequences you'll need to reown manually. Or you go a more aggressive route with REASSIGN OWNED.

5. You may need permissions on sequences, too. (If you grant INSERT or want them to use currval() or nextval() functions). Those are currently (v9.6) separate from table permissions.

6. I strongly suggest to bundle permissions in a group role and grant / revoke membership in that role to selected user roles.

Something along these lines, as superuser:

REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC; -- you may or may not want this
-- REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC; -- only for more existing permissions
-- REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM PUBLIC; -- only for more existing permissions
ALTER TABLE mytable OWNER TO postgres; -- or some admin role. best keep the superuser out of this
-- more tables? or even:
-- REASSIGN OWNED BY dbuser TO postgres; -- to reassign *all* owned objects the current DB
CREATE ROLE usr_permissions;
GRANT usr_permissions TO dbuser;
GRANT CONNECT ON DATABASE mydatabase TO usr_permissions;
GRANT USAGE ON SCHEMA public TO usr_permissions; -- if you revoked from PUBLIC
GRANT SELECT, UPDATE ON ALL TABLES IN SCHEMA public TO usr_permissions; -- DELETE, INSERT?
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO usr_permissions; -- if you also granted INSERT

Related:

• How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?
• Grant access to all tables of a database

Additionally, should I use a named schema other than public?

The schema public is in no way special. It only happens to be installed by default with permissions for PUBLIC and preset in the default search_path. Some DB admins even delete it. You can use it like any other schema. Depends on the complete situation. Related:

• Is it recommended to install extensions into pg_catalog schema?

• Matt Hampel: can't edit less than 6 characters, but the alter table owner query should be ALTER TABLE mytable OWNER TO postgres (no SET)
• Erwin Brandstetter: Thank you! Fixed.