PostgreSQL Role Table Grants – Differences Between Users

postgresql

I have two users [UserA, UserB] — UserA is an existing user. I just created UserB and gave it GRANT ALL PRIVILEGES.

But when I try to see privileges by doing

select * from information_schema.role_table_grants

UserA sees a lot more rows than UserB in the public schema.

For example

UserA can see all the rows with grantee in "admin_group", but UserB can't.
UserA and UserB can see all the rows with grantee in "user_group".

This is confusing because I did not do anything special to "user_group" (that I know of). What additional permission do I have to give to UserB so it can see all rows in information_schema.role_table_grants.

Best Answer

One possible cause is that UserA belongs to "admin_group" and that UserB does not belong to "admin_group". Please post output of following statements run by a superuser from psql CLI:

\du userA
\du userB
select * from information_schema.role_table_grants where grantee='user_group';
select * from information_schema.role_table_grants where grantee='admin_group';

Related Solutions

PostgreSQL, How to keep only one schema

The documentation of GRANT says the following:

CREATE

For databases, allows new schemas to be created within the database.

For schemas, allows new objects to be created within the schema. To rename an 
existing object, you must own the object and have this privilege for the containing schema.

So let's simply try this. I created a user for this purpose, and if you look at it, it has the CREATEDB attribute, just for clarifying which CREATE privilege refers to what.

CREATE USER schematest WITH LOGIN CREATEDB PASSWORD 'secure';

REVOKE CREATE ON DATABASE access_test FROM schematest;

GRANT CREATE ON SCHEMA public TO schematest;

These result in the following behaviour:

CREATE SCHEMA other_schema;

ERROR: permission denied for database access_test
SQL state: 42501

So far so good: the user cannot create a new schema. Let's see if it can create objects in the public schema:

CREATE public.simple_table(id integer);

Query returned successfully with no result in 181 ms.

Hooray!

Postgres Monitoring – Creating a User to Monitor Database Activity

At this time, only superusers can see others' activity details in pg_stat_activity.

PostgreSQL could use a finer-grained rights model, where you can GRANT the MONITOR_QUERIES right to a user, for example. But right now it doesn't have one, and quite a few things are superuser-only. This is one of them.

Best Answer

Related Solutions

PostgreSQL, How to keep only one schema

Postgres Monitoring – Creating a User to Monitor Database Activity

Related Question