about img
blogger img

Corys posts image

UnderPaidLoveMonkis posts img

buchos posts img

scotts posts image

PostgreSQL's Archive

Granting PostgreSQL Database and Table Ownership / Privileges to a New User

Scott Rippee @ 8:10 pm Sunday, January 14th, 2007

I found the process of modifying database and table permissions a little tricky in PostgreSQL, mostly due to trying to use GRANT when I should have been using ALTER. The following are some tips.

 

Login to postgresql as a user who has superuser privileges:

CODE:
  1. psql -U username

Check out the users who already exist

CODE:
  1. \du

or a select from pg_users

SQL:
  1. SELECT * FROM pg_user;

Lets add a new user with the createuser shell command:

CODE:
  1. createuser -U currentuser -A -D -P newuser

This give us a new user with the name 'newuser' and password 'somepassword'. We specified a current user with sufficient privileges (superuser) using -U."

We can now create a new database with the owner being the newuser we had created:

CODE:
  1. createdb -U currentuser -O newuser mynewdb

Or using SQL:

SQL:
  1. CREATE DATABASE mynewdb OWNER newuser;

Here we used our super user account to add a new database that is owned by our 'newuser'. We can log back into postgresql with our new user and access our new database using "psql -U newuser mynewdb". Now we can SQL it up to our hearts desire, create new tables, alter tables, drop tables.


Now for granting this new user privileges and ownership of database that already has an existing owner. You may try using GRANT to give the new user the desired privileges to the existing tables using:

SQL:
  1. GRANT ALL ON tablename TO newuser;

However, you most likely want the ability to create new tables in the database and with the above can still be restricted access certain ALTER functionality for the table. For this access privileges of the database needs to be changed AND the tables have to be told they have a new OWNER:

Logged in as a super user and:

SQL:
  1. ALTER DATABASE mynewdb OWNER TO newuser;

Additionally you could just add privileges for a user to a database with GRANT. Here I grant all, but one could grant only specific access privileges:

SQL:
  1. GRANT ALL ON DATABASE mynewdb TO newuser;

And you can be logged in as the new user to the correct database to change the OWNER of the existing tables:

SQL:
  1. ALTER TABLE some_table OWNER TO newuser;

Lastly you can control host and user access to databases by editing postgresql/data/pg_hba.conf. The following will give access to our new user to our new database when trying to connect from the localhost:

CODE:
  1. # TYPE  DATABASE   USER         CIDR-ADDRESS     METHOD
  2. host     mynewdb     newuser     127.0.0.1/32        md5

Now we're all setup to add new users, modify the owner of existing databases and tables, and control database access. Happy Hacking