Обсуждение: permissions
Is there an easy way to give role x blanket access to the objects in a database? I started with a default Debian setup of 8.4, which gives each OS user passwordless login to their own databases if you setup a pg user with the same name. Edited pg_hba.conf to trust all users for the database of interest, rossmail. Owner is ross. As OS user Debian-exim, login to mydb. This works, but I get "permission denied" when I tried to access the table. As postgres, grant all privileges on database rossmail to "Debian-exim"; This didn't help. Perhaps it's relevant that I got a syntax error unless I quoted Debian-exim, I assume because of the dash. My theory is that I would need to grant privileges individually for each table, index, .... Is that theory right? Is there a command to do it all at once? I though grant ... database... was that command, but it didn't work. At this point I backed up and had OS user Debian-exim connect to the database as PG user ross. That worked, albeit with minimal security. Although my immediate problem is solved, I'd like to understand what's going on. Ross
On Wed, May 30, 2012 at 11:26 PM, Ross Boylan <ross@biostat.ucsf.edu> wrote: > Is there an easy way to give role x blanket access to the objects in a > database? As of 9.0, you can use: GRANT SELECT ON ALL TABLES IN SCHEMA ... TO ... ; If you have many schemas, it should be easy to create a PL/pgSQL function to iterate over them and run the above GRANT for each. If you're stuck on 8.4, you might have to resort to a PL/pgSQL function to iterate over the tables in your database and run the necessary GRANTs. Another strategy worth mentioning is to figure out which tables should be readable by all users in the database, and use GRANT SELECT ON ... TO PUBLIC; on those tables. > My theory is that I would need to grant privileges individually for each > table, index, .... > > Is that theory right? Is there a command to do it all at once? I > though grant ... database... was that command, but it didn't work. Granting, say, ALL PRIVILEGES on a database essentially allows the specified user to create *new* objects in that database, but does not affect the privileges on objects in the database created by other users. Josh
On 5/31/2012 12:49 PM, Josh Kupershmidt wrote: > On Thu, May 31, 2012 at 12:25 PM, Ross Boylan<ross@biostat.ucsf.edu> wrote: >> Thanks for the info. I am on 8.4. >> I didn't realize plpgsql let you convert variables (i.e., results of a >> select to get table names) into command text. > Yup, you just have to remember to use the EXECUTE command in your > PL/pgSQL function when you are constructing dynamic SQL. Here's an > example relevant to your scenario: > http://archives.postgresql.org/pgsql-general/2010-01/msg00871.php > > Josh I thought the tip was worth preserving for the list, along with my thanks, so I'm sending it to the list. I hadn't been thinking of execute; I've got too much SAS macros on the brain. Ross