1

I made a post yesterday to fix the issues with my database code, and I had a few typos to fix and it works now. The only problem is I can only connect to my database as the default postgres user, when I want to connect as my created user in the file. I pictured the status below:

CREATE DATABASE equipment;
CREATE USER equipment WITH ENCRYPTED PASSWORD 'testing';
GRANT ALL PRIVILEGES ON DATABASE "equipment" to equipment;
GRANT USAGE ON SCHEMA public TO equipment;
GRANT CONNECT ON DATABASE equipment TO equipment;
ALTER USER equipment WITH SUPERUSER;
\c equipment equipment;


CREATE TABLE material (
          material_id     INTEGER NOT NULL,
          name            VARCHAR(128) NOT NULL,
          description     VARCHAR(256) NOT NULL,
          tensile_strength INTEGER NOT NULL,
          PRIMARY KEY     ( material_id )
);

CREATE TABLE costs (
          cost_id        INTEGER NOT NULL,
          material_id    INTEGER NOT NULL,
          price          DECIMAL(10,2) NOT NULL,
          CONSTRAINT fk_material_id FOREIGN KEY (material_id) REFERENCES material(material_id)
          PRIMARY KEY     ( cost_id )
);

CREATE TABLE usages (
          usage_id         INTEGER NOT NULL,
          cost_id          INTEGER NOT NULL,
          purpose          VARCHAR(256) NOT NULL,
          location_to_purchase  VARCHAR(256) NOT NULL,
          CONSTRAINT fk_cost_id FOREIGN KEY (cost_id) REFERENCES costs(cost_id)
          PRIMARY KEY ( usage_id )

);

GRANT ALL PRIVILEGES ON material, costs, usages TO equipment;

EDIT: can connect with equipment user after editing pg_hba.conf;

Content containing changes in the conf file:

# Database administrative login by Unix domain socket
local   all             postgres                                peer
local   all             equipment                               md5

After running this this is the output:

GRANT
GRANT
GRANT
ALTER ROLE
Password for user equipment:

Entering 'testing' allows me in (Is there an automatic way to enter this?)

BigB1225
  • 23
  • 4
  • Does you user have access to log in? Or just denied on the database itself? My guess is they don't have privileges to access anything from `localhost` or `127.0.0.1` - Can you show us your `CREATE USER` SQL ? – Zak Mar 22 '22 at 16:53
  • @Zak The CREATE USER statement is at the top of the file, and if I granted all privileges to the equipment user, shouldn't it be able to login? – BigB1225 Mar 22 '22 at 16:55
  • So what is the issue here? You cannot connect as user `equipment` to the database? Because your screenshots do not show that? – Mushroomator Mar 22 '22 at 16:56
  • @Mushroomator Yes that is the issue; I cannot connect to the database as the equipment user. I apologize if I am missing something, I just don't know what that is; I am quite new to this. – BigB1225 Mar 22 '22 at 16:57
  • In my code .. database name is in quotes .. Does that help? `GRANT ALL PRIVILEGES ON DATABASE "equipment" to equipment` ? – Zak Mar 22 '22 at 17:00
  • Also did you read through [THIS QUESTION](https://stackoverflow.com/questions/22483555/postgresql-give-all-permissions-to-a-user-on-a-postgresql-database) – Zak Mar 22 '22 at 17:03
  • Yes; it still is only connecting as user postgres. And the double quotes did not fix it either. – BigB1225 Mar 22 '22 at 17:10
  • 1) Do not use image for textual information. Copy and paste text to question 2) `USE` is not a Postgres command. 3) `\c equipment` will connect to `equipment` database as current user `postgres`. If you want to connect as `equipment` user then do `\c equipment equipment`. 4) Read the docs [psql] `\c or \connect [ -reuse-previous=on|off ] [ dbname [ username ] [ host ] [ port ] | conninfo ]` – Adrian Klaver Mar 22 '22 at 17:12
  • @AdrianKlaver I made this change and get FATAL: peer authentication failed for user "equipment" – BigB1225 Mar 22 '22 at 17:13
  • Then you need to modify [pg_hba.conf](https://www.postgresql.org/docs/current/auth-pg-hba-conf.html) and reload it. – Adrian Klaver Mar 22 '22 at 17:14
  • The problem with doing that is this file is getting submitted to a teacher, and wouldn't he have to make the same config file edit in order to run it? – BigB1225 Mar 22 '22 at 17:17
  • Depends is he using the same conf files? In any case connection access to the database is controlled by `pg_hba.conf`. You will need to at least look at it to see if there may be a way to access that does not require a change. If you post the text of the file to your question we maybe able to offer pointers. – Adrian Klaver Mar 22 '22 at 17:22
  • @AdrianKlaver this is everything I have in the top of it now CREATE DATABASE equipment; CREATE USER equipment WITH ENCRYPTED PASSWORD 'testing'; GRANT ALL PRIVILEGES ON DATABASE "equipment" to equipment; GRANT USAGE ON SCHEMA public TO equipment; GRANT CONNECT ON DATABASE equipment TO equipment; ALTER USER equipment WITH SUPERUSER; \c equipment equipment; – BigB1225 Mar 22 '22 at 17:24
  • 1) Do not put revisions in the comments, update your question with the changes. 2) The important part at this point is the contents of `pg_hba.conf`. That also needs to added **as text** to the question. – Adrian Klaver Mar 22 '22 at 17:27
  • @AdrianKlaver Thank you for your input and assistance. I've updated the question – BigB1225 Mar 22 '22 at 17:33
  • 1) Change `md5` to `trust`, that means no password needed. 2) On `\c` use `conninfo` as shown here [psql](https://www.postgresql.org/docs/current/app-psql.html) and here [connection string](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING) . 3) Use [.pgpass](https://www.postgresql.org/docs/14/libpq-pgpass.html) 4) Use [environment](https://www.postgresql.org/docs/current/libpq-envars.html) variables – Adrian Klaver Mar 22 '22 at 17:42

0 Answers0