1

I have a database named somedb owned by user someuser. I have revoked all privileges from public to this database.

I want to grant connect privileges to anotheruser, but I don't want anotheruser to be able to query any of the non-system tables (i.e. any table created by someuser) in its public schema. Is that possible? Or do I need to move all the tables in the public schema to a different schema?

When I GRANT CONNECT ON DATABASE somedb TO anotheruser; and connect to database somedb as user anotheruser, I can query all the tables in somedb.

I tried the following, but it didn't work:

REVOKE ALL ON somedb.public.sometable FROM anotheruser;

ALTER DEFAULT PRIVILEGES FOR USER anotheruser IN SCHEMA public
   REVOKE ALL ON TABLES FROM anotheruser;
ALTER DEFAULT PRIVILEGES FOR USER anotheruser IN SCHEMA public
   REVOKE ALL ON SEQUENCES FROM anotheruser;

It might be useful to know that anotheruser has SELECT and other privileges in a different database on the same server.

1

OK, I think I figured it out. I had to change the public schema's owner to be the owner of the database and then revoke all privileges on the public schema from public, like this:

ALTER SCHEMA public OWNER TO someuser;

REVOKE ALL ON SCHEMA public FROM PUBLIC;

I found this web page helpful.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.