CODEWITHSUNDEEP
postgresql
joesan
joesan

Reputation: 15375

Postgres ACL for Schemas

I'm not a DBA and I have got some questions around access controls for schemas. Let's say I have a Postgres server running a several databases. The admin user is postgres. I have another user tmpUser with which I could log in to the remote server using pgadmin3 client.

I now create a database called myDatabase which is by default owned by the postgres user. I then use my admin client to remotely log in to this myDatabase using the tmpUser account.

I now create a new schema inside this myDatabase called myDbSchema. I created a new role called myDbRole and did a grant usage, grant all on myDatabase, myDbSchema to the myDbRole.

The question now is how should I control access to this myDatabase. I tried to log in to the remote server using the tmpUser and when I tried to execute select * from myTable where myTable is a table in myDatabase, it came back with a permission denied sql message. So I changed the owner of the table to the tmpUser which I really do not want to!

Is there a guide or something on how I should go about creating and organizing roles with schemas in postgres?

Upvotes: 0

Views: 2697

Answers (1)

Patrick
Patrick

Reputation: 32179

It is not entirely clear what your problem is (for instance, what is role "myDbRole" for, is that a group role (NOLOGIN) or a user role (LOGIN)?) but in general you could follow this pattern of permission management:

  1. Create a specific role to own a database and all or most of the objects in it. This should be a group role (NOLOGIN) for security reasons. Do not use the postgres user; if you need to login as that role often to do regular database work, you are doing something wrong. Any superuser (or other user role that has that role granted to it) can "impersonate" that owner role using SET SESSION AUTHORIZATION to do necessary maintenance. In a production environment this should be hardly ever necessary; during development you might want to consider making the role with LOGIN permission for ease of use.
  2. The owner creates all the schemas, tables, views, functions, etc. that you need for your application. By default, all of those objects are only available to the database owner, with the exception of functions.
  3. Define a number of group role profiles, each having specific requirements of the database. You could have, for instance sales_staff, product_managers, accounting and senior_management for a company, or web_user, web_admin, app_developer and app_manager for a web site. The database owner then GRANTs access to the database (CONNECT), schemas (USAGE), tables, views and functions (EXECUTE), as needed. I usually REVOKE ALL ON FUNCTION x() TO public, for security reasons.
  4. Assign group role membership to user roles, as needed: GRANT sales_staff TO jane. The user roles should have LOGIN INHERIT such that they can log in and inherit the permission of group roles that they are a member of. That includes the permission to connect to a database and usage rights on schemas. Note that a single user role can have membership in multiple group roles.

Lastly, update your pg_hba.conf file to enable remote access to the database.

Upvotes: 1

Related Questions