New answers tagged

1

There is a simple and a complicated way. The complicated way is to join with pg_roles and get the user name from there. But since PostgreSQL hackers don't want to type more than necessary, they came up with something else: Internally, every object is not identified by its name (which is mutable) but by its “object ID”. This is the number that ...


1

You say "It seems that every table in the database is listed here and they all have update, delete, insert, and select". If you have not explicitly verified that insert, update, and delete permissions have been provided on every object via this role; these permissions may be missing on some objects. The role [db_datawriter] guarantees that insert, update, ...


0

The issue brought up here is easily resolved by using SECURITY DEFINER. However, it's worth noting that code that runs in procedures and functions marked as SECURITY DEFINER cannot utilize transaction support (such as COMMIT or ROLLBACK).


0

I don't think it makes any sense to define users and roles anywhere else than in admin database. If you define them somewhere else then likely your user/role management get unmanageable. From db.createRole(): Except for roles created in the admin database, a role can only include privileges that apply to its database and can only inherit from other ...


0

You cannot grant a password-protected Role to another Role. Consider this (which doesn't work): create role ROLE_1 identified by abc123 ; create role ROLE_2 identified by bcd234 ; grant ROLE_2 to ROLE_1 ; set ROLE ROLE_1 identified by abc123 ; Question: How do you provide the password for ROLE_2? It has to have one, because it's password-...


3

You can group several privileges into one Role. It is simpler to grant roles rather than dealing with a bunch of privileges. In a multi-tier architecture this might be no so important. You may have a user admin, backup, http - that's it. However, when your database is used by many (human) users distributed over several departments with different ...


Top 50 recent answers are included