How to add permission to custom role in postgres to create index

Question

I am currently creating a role for a particular database using the below sql script executed as master user (postgres).

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 ?

must be owner of table mytable, make this role the owner of the objects and your problem is solved. — Frank Heikens
– Frank Heikens, Commented Jul 26, 2024 at 16:10

Laurenz Albe · Accepted Answer · 2024-07-29 06:27:23Z

1

Only the table owner, a member of the table owner (that is allowed to SET ROLE to the table owner) or a superuser can create an index on a table.

The owner of a table is the user who created it. So make sure that you are using only a single user to create and drop objects.

edited Jul 29, 2024 at 6:27

answered Jul 26, 2024 at 17:01

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Subhajit Over a year ago

Thanks for your response @laurenz . Can you tell how can I set the custom role as owner for all tables in the database that are existing and future tables?

Collectives™ on Stack Overflow

How to add permission to custom role in postgres to create index

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related