Forums

configuring postgres databases

Up to now I have configured postgres databases as described here: https://help.pythonanywhere.com/pages/PostgresGettingStarted

However for a database I created with: CREATE DATABASE targetdb WITH TEMPLATE sourcedb; I had to additionally do:

\c targetdb   
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public to user1;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public to user1;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public to user1;

i.e. the

GRANT ALL PRIVILEGES ON DATABASE targetdb TO user1 ;

was not sufficient. Any idea why?

If the template you're creating from has different permissions to the ones you want, then you would need to override them. The default template would not have them defined, so you wouldn't need to override them.

Thanks. Let me rephrase my question: After creating the new database, the following permission grant did not work:

GRANT ALL PRIVILEGES ON DATABASE targetdb TO user1 ;

I had to additionally grant the permissions on TABLES, SEQUENCES and FUNCTIONS. And I don't understand the reason. Any help?

The tables in the template you used (sourcedb) probably already had permissions on the objects in the database, so you needed to change them.

As a followup, here's a thread on Stackoverflow which touches the question: https://dba.stackexchange.com/questions/36870/permission-denied-in-postgres

In addition you might need to set / alter default privileges.

Just to clarify what's going on here on our side -- in your original post, you said that you were using this:

CREATE DATABASE targetdb WITH TEMPLATE sourcedb;

Am I right in thinking that sourcedb was one of your databases?

Yes, sourcedb was one of my databases.

Also additionally I created a new testuser2, and then did:

GRANT ALL PRIVILEGES ON DATABASE targetdb TO testuser2 ;

Then testuser2 could not access the db, and I also had to do the separate grants on the tables, sequences and functions.

Thanks!