Why is Postgres Superuser Required for Access to My Database?

Question

I have a common user, mygs_user, across three databases: mydatamodel, mygs, and Newdatabase (names changed to protect the innocent).

I've 'inherited' the first two databases, and have created a third (Newdatabase), whose security must closely replicate the original two--including using the same user-name and role.

The problem is while the two original databases do not have explicit privileges, the common user, mygs_user, can access them just fine. The third (Newdatabase) does not allow access unless I modify the common user to be a super-user, which obviously is not a best-practice.

Of course, I've tried setting the security settings of my NewDB to settings of the original ones with no dice.

Here are the two original databases:

mydatamodel

CREATE DATABASE mydatamodel
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = datamodel_tablespace
       LC_COLLATE = 'English, United States'
       LC_CTYPE = 'English, United States'
       CONNECTION LIMIT = -1;

mygs

CREATE DATABASE mygs
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = mygs_tablespace
       LC_COLLATE = 'English, United States'
       LC_CTYPE = 'English, United States'
       CONNECTION LIMIT = -1;

...and here is the new database:

CREATE DATABASE "NewDatabase"
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'English, United States'
       LC_CTYPE = 'English, United States'
       CONNECTION LIMIT = -1;
GRANT CONNECT, TEMPORARY ON DATABASE "NewDatabase" TO public;
GRANT ALL ON DATABASE "NewDatabase" TO postgres;
GRANT ALL ON DATABASE "NewDatabase" TO mygs_user;

The two original occupy separate tablespaces, while the new one doesn't:

CREATE TABLESPACE mygs_tablespace
  OWNER postgres
  LOCATION 'c:/MYGS_Database/PostgreSQL';



CREATE TABLESPACE datamodel_tablespace
  OWNER postgres
  LOCATION 'c:/MYGS_Database/MyDataModel';

This is the the commmon user:

CREATE ROLE mygs_user LOGIN
  SUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;
GRANT "MYGS" TO mygs_user;

...and the common role:

CREATE ROLE "MYGS"
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;

Here are the pg_hba.conf file contents:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# IPv4 local connections:
host    all         all         127.0.0.1/32            md5

How can I grant access to the common user for the third database without granting superuser privileges?

Because of GRANT CONNECT, TEMPORARY ON DATABASE "NewDatabase" TO public any user should be able to connect to it. Please provide the error message if that's not the case. — Daniel Vérité
– Daniel Vérité, Commented Jun 1, 2012 at 14:33
The error was "Can't connect to this relation, tablename". You're right, the access privileges were set at the table-level for the two original databases. — MAbraham1
– MAbraham1, Commented Jun 1, 2012 at 14:49

Tometzky · Accepted Answer · 2012-05-31 21:49:55Z

2

I think you forgot to allow connecting to this database in pg_hba.conf configuration file.

You should probably add there something like:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   Newdatabase     MYGS                                    md5

answered May 31, 2012 at 21:49

Tometzky

24.3k5 gold badges64 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MAbraham1 Over a year ago

Tometzky, Great thought! However, we already have open access configured in the pg_hba: host all all 127.0.0.1/32 md5

MAbraham1 Over a year ago

Tometzky, I've updated my question with the pg_hba.conf file.

MAbraham1 · Accepted Answer · 2012-06-01 15:00:37Z

Upon further inspection, the privileges were set to authorize the the "MYGS" role at the object-level, with no explicit privileges at the database level. The problem is resolved by the following code, for each object-type:

Tables:

ALTER TABLE tableNameHere OWNER TO postgres;
GRANT ALL ON TABLE dtm_tableNameHereTO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE tableNameHere TO "MYGS";

...functions:

ALTER FUNCTION functionNameHere(..) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION functionNameHere(..) TO postgres;
GRANT EXECUTE ON FUNCTION functionNameHere(..) TO "MYGS";

...and sequences:

ALTER TABLE sequenceNameHere OWNER TO postgres;
GRANT ALL ON TABLE sequenceNameHere TO postgres;
GRANT SELECT, UPDATE ON TABLE sequenceNameHere TO "MYGS";

Although, the postgres user is not necessary for the solution to work.

Collectives™ on Stack Overflow

Why is Postgres Superuser Required for Access to My Database?

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related