Postgres create database user with grant access to schema only

0
votes

I have a database with a template_schema.I cloned this template schema and created a database user with password. I need to provide access to cloned schema only, for the created user.

SELECT clone_schema('my_template_schema','john_smith_gmail_com');
CREATE USER john_smith_gmail_com WITH PASSWORD 'mypassword';

Upto this Ok. Then I need to grant access to this user for this cloned schema(john_smith_gmail_com) only

Method :1 I tried to revoke all privileges on all tables of cloned schema(john_smith_gmail_com) for the user and grant select to the user. But my question is, can this user get SELECT access on other schema tables?

REVOKE ALL ON ALL TABLES IN SCHEMA john_smith_gmail_com FROM john_smith_gmail_com;
GRANT SELECT ON ALL TABLES IN SCHEMA john_smith_gmail_com TO john_smith_gmail_com;

Method :2

Create a role with only SELECT access and assign or grant this role to newly created user. If I do this, for which schema I grant access,because I clone schema dynamically?

Which method is best one?

1
postgresql

1 Answers

2
votes

From postgresql version 9.0 and forward, the best way is probably to use ALTER DEFAULT PRIVILEGES.

...the default privileges for any object type normally grant all grantable permissions to the object owner, and may grant some privileges to PUBLIC as well. However, this behavior can be changed by altering the global default privileges with ALTER DEFAULT PRIVILEGES.

So if all users like "john_smith_gmail_com" should only have SELECT access to tables in "their own" schema, after creating the schema and user, you can run:

ALTER DEFAULT PRIVILEGES IN SCHEMA john_smith_gmail_com GRANT SELECT ON TABLES TO john_smith_gmail_com;