0

I'm using PHP to query a PostgreSQL database (PHP5, PostgreSQL 9.3.9). I have an existing database that has an owner. I'm trying to make a secondary user have full access to the database to be used by PHP. When this user is set SUPERUSER it can query the database. Otherwise he cannot. I granted all privileges to the user with the postgres superuser for the database in question with:

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO phpuser;

And when I look at the database list, phpuser=CTc is listed on Access Privileges. The user also has a password set.

What am I missing? I want this user to access the database with full privileges through PHP.

silicongreensix
  • 23
  • 5

1 Answers1

1

You should not only grant privileges on the database (CTc means the user can connect to the database and create new and temp databases), but also on the database objects.

GRANT ROLE

The absolutely easiest way to do this is to grant the owner role to the phpuser role:

GRANT owner_role TO phpuser;

This may, however, introduce all manner of security problems and is not generally recommended for any scenario unless you are very confident about your network security.

GRANT privilege

Now for the hard way.

To begin with, your phpuser most likely does not have to create new or temporary database, so revoke these privileges:

REVOKE CREATE, TEMP ON DATABASE mydatabase FROM phpuser;

If you have tables or other objects in schemas other than public, grant access to those schemas (you need to be connected to database mydatabase when you issue these commands):

GRANT USAGE ON SCHEMA myschema TO phpuser;

Now you can grant the privileges to all tables, sequences, functions, etc inside the schemas, so for each schema, including public, do:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO phpuser;
GRANT ALL ON ALL SEQUENCES IN SCHEMA myschema TO phpuser;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA myschema TO phpuser;

If you have some more exotic objects, use the appropriate command. Views are included in the table grant and indexes are automatically included with the tables.

Network access

Note that the phpuser role should also be permitted access in the pg_hba.conf file. There should be a line like (modify to match your network configuration):

host mydatabase phpuser 127.0.0.1/32 md5
Patrick
  • 29,357
  • 6
  • 62
  • 90
  • A little clarification: Instead of using the GRANT ALL PRIVILEGES, should I just GRANT CONNECT? – silicongreensix Jul 16 '15 at 01:28
  • Yes, `GRANT CONNECT ON DATABASE mydatabase TO phpuser` is enough to gain access to the database objects. But since you already granted all privileges on the database, you can revoke those privileges that are not required. Grant as few privileges as you need to make your application work. – Patrick Jul 16 '15 at 03:01