I am currently creating a role for a particular database using the below sql script executed as master user (postgres).
<code>CREATE USER customrole WITH PASSWORD 'mypassword';
-- removed CREATE permissions from the public schema from all roles
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
-- removed all permissions from the database from all users
REVOKE ALL ON DATABASE mydb FROM PUBLIC;
-- added connect permission on the database to role
GRANT CONNECT ON DATABASE mydb TO customrole;
-- added usage permission on public schema to the customrole
GRANT USAGE ON SCHEMA public TO customrole;
-- added create permission on the public schema to the custom role
GRANT CREATE ON SCHEMA public to customrole;
-- added create permission on the database to custom role
GRANT CREATE ON DATABASE mydb to customrole;
-- added 'all permissions' to all existing tables in the public schema to new role
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO customrole;
-- Set default privileges for the postgres user in the public schema
ALTER DEFAULT PRIVILEGES FOR USER masteruser IN SCHEMA public
GRANT ALL PRIVILEGES ON TABLES TO customrole;
<code>CREATE USER customrole WITH PASSWORD 'mypassword';
-- removed CREATE permissions from the public schema from all roles
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
-- removed all permissions from the database from all users
REVOKE ALL ON DATABASE mydb FROM PUBLIC;
-- added connect permission on the database to role
GRANT CONNECT ON DATABASE mydb TO customrole;
-- added usage permission on public schema to the customrole
GRANT USAGE ON SCHEMA public TO customrole;
-- added create permission on the public schema to the custom role
GRANT CREATE ON SCHEMA public to customrole;
-- added create permission on the database to custom role
GRANT CREATE ON DATABASE mydb to customrole;
-- added 'all permissions' to all existing tables in the public schema to new role
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO customrole;
-- Set default privileges for the postgres user in the public schema
ALTER DEFAULT PRIVILEGES FOR USER masteruser IN SCHEMA public
GRANT ALL PRIVILEGES ON TABLES TO customrole;
</code>
CREATE USER customrole WITH PASSWORD 'mypassword';
-- removed CREATE permissions from the public schema from all roles
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
-- removed all permissions from the database from all users
REVOKE ALL ON DATABASE mydb FROM PUBLIC;
-- added connect permission on the database to role
GRANT CONNECT ON DATABASE mydb TO customrole;
-- added usage permission on public schema to the customrole
GRANT USAGE ON SCHEMA public TO customrole;
-- added create permission on the public schema to the custom role
GRANT CREATE ON SCHEMA public to customrole;
-- added create permission on the database to custom role
GRANT CREATE ON DATABASE mydb to customrole;
-- added 'all permissions' to all existing tables in the public schema to new role
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO customrole;
-- Set default privileges for the postgres user in the public schema
ALTER DEFAULT PRIVILEGES FOR USER masteruser IN SCHEMA public
GRANT ALL PRIVILEGES ON TABLES TO customrole;
Now I want to create index on the existing table using the role. For example : create index my_idx on mytable (mycolumn);
My finding is as below :
Execute as master user : ALTER TABLE "mydb".public.mytable OWNER TO "customrole";
Execute as customrole : create index my_idx on mytable (mycolumn);
I would get this error if I don’t perform the alter table command as shown above .
Error : SQL Error [42501]: ERROR: must be owner of table mytable
Question : Is there any sql command which will allow the role (not the master role) to create index in existing and future tables ?