Sorry, real newbie question about connecting to a database

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Sorry, real newbie question about connecting to a database

stan-9
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?


--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                                                -- Benjamin Franklin


Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
stan <[hidden email]> writes:
> 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"

This means that you've set pg_hba.conf to specify "peer" authentication,
which by default only lets you connect as a PG role named the
same as your OS account.  (This is about are-you-who-you-say-you-are;
whether who-you-say-you-are has privilege to connect is independent.)

You could use some other auth method, or you could set up a mapping
file that says you (stan) are allowed to connect as "employee".

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

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

Adrian Klaver-4
In reply to this post by stan-9
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
[hidden email]