1

I am new to PostgreSQL and am having a bit of trouble finding any information on how to grant a user permissions on the database that they own. I have read a few sources that says the owner has all permissions, but it doesn't seem to work for me. I still had to grant permissions in order to run a select statement.

I have also read quite a few things that say that PostgreSQL simply doesn't support granting to multiple tables in a database at once, so I'm hoping that they are just out-of-date.

Here is what I have done so far:

# Create the user
createuser -SDRPE demo

# Create the database
createdb demo -O demo

# Import schema
psql -f myfile.sql demo

# Grant Permissions
???

Can somebody tell me the best way to go about this? Currently, I am writing all of this inside of a Rake task, so I could technically retrieve all of the tables in the database and iterate through them, but it feels like an awful clunky solution. It seems like I am missing something. Any thoughts/comments/links would be greatly appreciated.

Edit 1: As a quick note, all of these commands are being run as root which is a superuser in the database with the IDENT SAMEUSER authentication specified in pg_hba.conf in case that makes any difference.

Topher Fangio
  • 279
  • 3
  • 13

3 Answers3

1

Some examples might be:

pretty much everything: grant all privileges on database demo to demo;

grant some simple selects: grant select on table foo to demo;

more than select: grant select, insert, update, delete on table foo to demo;

More info here: http://www.postgresql.org/docs/8.3/static/sql-grant.html

user6373
  • 174
  • 4
1

Can you try

psql dbname username < filename.sql

So you would use

psql demo demo < myfile.sql

I am not sure where your sql file is going because you are not specifying the db in your script.

JosephL
  • 126
  • 2
  • Updated code to show that it is going into the demo database. Does it matter what user I use to import it? – Topher Fangio Nov 11 '09 at 14:44
  • I think the user is important. Try `psql -f myfile.sql -d demo -u demo`. This should run the commands as the demo user which should mean the objects created will be owned by the demo user. – JosephL Nov 11 '09 at 19:47
  • @JoesephL - Thanks, I'll give it a try and comment. – Topher Fangio Nov 11 '09 at 21:22
  • @JoesephL - Thanks, it worked! Now the only problem is that it requires me to type the password for every myfile.sql that I import (which is only 3 at the moment, but it's still a lot of user interaction). Any idea how to change that? – Topher Fangio Nov 11 '09 at 21:30
  • 1
    You could use a `.pgpass` file in your users home dirctory. Not sure how this would work as you are running the script as root but connecting as demo using `ident`. I would try putting the file in the demo users home directory. See http://www.postgresql.org/docs/8.1/interactive/libpq-pgpass.html – JosephL Nov 12 '09 at 01:12
0

If all you want to do is grant the user 'demo' the ability to read/write/modify the database 'demo', you are already done, because the owner of the database has all necessary privileges by default.

If you need more specific privileges for more than a single user, you can CREATE USER a new user and GRANT privileges to one or more objects.

For non-local access to the database I strongly recommend adjusting your pg_hba.conf

Jan Jungnickel
  • 964
  • 6
  • 9
  • Unfortunately, as my question attempted to state, what I have done is NOT granting permissions on the demo table for the demo user. I can't do a simple select statement on the table until I have executed `grant all on table_name to demo`. – Topher Fangio Nov 11 '09 at 14:42