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(at)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(dot)klaver(at)aklaver(dot)com