In Pgadmin Portal, I need to create a user for readonly access and user not able to create a table also in public

240 views Asked by At

I used this query

\`-- Create a user with read-only access
CREATE USER read3 WITH PASSWORD 'password';

\-- Grant necessary privileges for read-only access
GRANT CONNECT ON DATABASE auditdb, connectordb, notificationdb TO read3;
GRANT USAGE ON SCHEMA public TO read3;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read3;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO read3;

\-- Revoke unnecessary privileges from the user
REVOKE CREATE, TEMPORARY ON DATABASE auditdb, connectordb, notificationdb FROM read3;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM read3;

\-- Prevent the user from creating tables in the public schema
ALTER DEFAULT PRIVILEGES IN SCHEMA public
REVOKE CREATE ON TABLES FROM PUBLIC;\`

But still facing error showing this error WARNING: no privileges were granted for "public"

ERROR: invalid privilege type CREATE for relation

SQL state: 0LP01

Can anyone help me out so that using query I am able to create user for read only access and user not able to delete or create any new table

0

There are 0 answers