Re: Sorry, real newbie question about connecting to a database

On 8/19/19 7:44 AM, stan wrote:
> I am developinng an appliction usig Postgresql 11, installed on a UBUTU
> 18.14 machine.
> 
> I ahve vreated a new database to do some testing on restricting access of
> specific users/roles to certain data. I have done the following:
> 
> REVOKE ALL ON DATABASE pertest FROM employee;
> GRANT CONNECT ON DATABASE pertest TO employee;
> 
> and I have verifed tht the user employee does exst, I have also doen a few
> more GRABTs to allow specific acces. But I cannot conect, or swith to user
> employee:
> 
> stan@smokey:/etc/postgresql/11/main$ psql -U employee
> psql: FATAL:  Peer authentication failed for user "employee"
> 
> stan=> \l
> List of databases
>   Name    |  Owner   | Encoding | Collate | Ctype  |   Access privileges
> --------+----------+----------+---------+---------+-----------------------
> pertest   | stan     | UTF8     | C.UTF-8
>                                  | C.UTF-8 | =Tc/stan             +
>                     |         | stan=CTc/stan
>                                             
>                              employee=CTc/stan
> 
> Sorrry cut and paste mangled that.
> 
> What am I failing to do here?
> 
> 

Tom has spelled out the specific issue. The generic issue is that 
security in Postgres is a multi-layer process that involves many moving 
parts. You will save yourself a lot of do overs by looking at the 
relevant documentation. Starting roughly from outside in:

Server connection:

https://www.postgresql.org/docs/11/runtime-config-connection.html

Client authentication(the pg_hba.conf Tom referred to):

https://www.postgresql.org/docs/11/client-authentication.html

Database roles(users):

https://www.postgresql.org/docs/11/user-manag.html

Role/user permissions:

https://www.postgresql.org/docs/11/sql-grant.html

Finer grained permissions(row level security):

https://www.postgresql.org/docs/11/ddl-rowsecurity.html


The above is intimidating and not something that will be fully 
understood in a single reading(or in my case multiple readings:)). Still 
a passing familiarity with the concepts will make your life easier.


-- 
Adrian Klaver
adrian.klaver@aklaver.com