CODEWITHSUNDEEP
amazon-web-servicesamazon-redshift
user9807549
user9807549

Reputation:

check user access on schema postgresql

I have looked into the documentation for GRANT Found here(https://docs.aws.amazon.com/redshift/latest/dg/r_GRANT.html) and I was trying to see if there is a built-in function that can let me look at what level of accessibility I have in databases. Of course there is:

\dp and \dp mytablename

But this does not show what my account has access to. I would like to see ALL the schemas I have access to or user. Can anyone tell me if there is a command that can check my level of access in Postgres/Redshift (whether I have CREATE, USAGE privileges)? And if so, what would that command be? And same for the database (whether I have CREATE, TEMP, TEMPORARY privileges)

Upvotes: 0

Views: 12249

Answers (1)

Z4-tier
Z4-tier

Reputation: 7988

Make sure you are familiar with the model that Postgres uses for users/roles: from the Postgres documentation

PostgreSQL manages database access permissions using the concept of roles. A role can be thought of as either a database user, or a group of database users, depending on how the role is set up. Roles can own database objects (for example, tables and functions) and can assign privileges on those objects to other roles to control who has access to which objects

So, you should be thinking in terms of "which roles is user X assigned, and to which objects do those roles have access (and what type of access)?" while keeping in mind that user X is also a role.

Also keep in mind that "schema" and "database" are namespaces, not objects. Tables and columns are the objects. Asking about schema/database rights is not going to be very useful because those are not the objects to which privileges are applied.

Try this experiment:

  1. Set up a test database, and create a new schema. Add a test table to the schema, and create a test user.
  2. Look at the content of pg_namespace, table_privileges, role_column_grants, column_privileges, role_table_grants.
  3. Grant USAGE on the schema to the test user. Look at those tables again.
  4. Grant SELECT to the table, and look at the same tables one more time.

You should notice that granting USAGE only added the test user to a namespace. Granting select should have applied permissions to the actual database object (the table).

You are probably looking for something like this:

select * from information_schema.role_table_grants

and possibly the other role_ tables:

information_schema.role_column_grants
information_schema.role_routine_grants
information_schema.role_table_grants
information_schema.role_udt_grants
information_schema.role_usage_grants

Upvotes: 4

Related Questions