Re: [GENERAL] Permissions for Web App

On 10/13/2017 11:21 AM, David G. Johnston wrote: On Fri, Oct 13, 2017 at 11:03 AM, Igal @ Lucee.org >wrote: You mean that if I execute the ALTER DEFAULT command above as user `postgres` then only tables created by user `postgres` will give default privileges to

Re: [GENERAL] Permissions for Web App

On Fri, Oct 13, 2017 at 11:03 AM, Igal @ Lucee.org wrote: > You mean that if I execute the ALTER DEFAULT command above as user > `postgres` then only tables created by user `postgres` will give default > privileges to role `webapp`? > ​Yes. "​You can change default privileges only for objects t

Re: [GENERAL] Permissions for Web App

Stephen, On 10/10/2017 6:14 AM, Stephen Frost wrote: For future reference and for the benefit of others, the command that I ran is:   ALTER DEFAULT PRIVILEGES IN SCHEMA public     GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO webapp; Note that DEFAULT PRIVILEGES apply to a specific rol

Re: [GENERAL] Permissions for Web App

Greetings, * Igal @ Lucee.org (i...@lucee.org) wrote: > It worked, thanks! Be sure to check that you're really getting what you want here. > For future reference and for the benefit of others, the command that > I ran is: > >   ALTER DEFAULT PRIVILEGES IN SCHEMA public >     GRANT SELECT, INSER

Re: [GENERAL] Permissions for Web App

On 10/9/2017 10:51 AM, David G. Johnston wrote: On Mon, Oct 9, 2017 at 9:44 AM, Igal @ Lucee.org >wrote: But I want to give that role permissions on future tables since I add new tables and drop/recreate current ones. ​ALTER DEFAULT PRIVILEGES​ ​https://www.post

Re: [GENERAL] Permissions for Web App

On Mon, Oct 9, 2017 at 9:44 AM, Igal @ Lucee.org wrote: > But I want to give that role permissions on future tables since I add new > tables and drop/recreate current ones. > ​ALTER DEFAULT PRIVILEGES​ ​https://www.postgresql.org/docs/9.6/static/sql-alterdefaultprivileges.html David J. ​

[GENERAL] Permissions for Web App

Hello, I created a role named `webapp` as follows:   CREATE ROLE webapp WITH LOGIN PASSWORD 'changeme'; While in development, I want to give that role permissions on all tables in schema public.  So far I've been using the following command, which works on existing tables:   GRANT ALL PRIVI

Re: [GENERAL] Permissions pg_dump / import

Patrick B writes: > I'm doing a pg_dump and a pg_restore on the same command, using different > usernames and databases names.: > ... > But I'm getting some permissions errors: > could not execute query: ERROR: role "devel" does not exist If that's from > REVOKE ALL ON SCHEMA public FROM devel;

Re: [GENERAL] Permissions pg_dump / import

Hi guys, I'm doing a pg_dump and a pg_restore on the same command, using different usernames and databases names.: pg_dump --format=custom -v --no-password --no-owner --username=teste1 > --dbname=test1 --host=11.11.11.11 | pg_restore -v --schema=public > --no-password --no-owner --username=maste

Re: [GENERAL] Permissions pg_dump / import

2016-08-18 10:30 GMT+12:00 Ilya Kazakevich : > >> Owned by: public.accounts.id > > This is not owner but table this sequence depends on. See > http://stackoverflow.com/questions/6941043/get-table- > and-column-owning-a-sequence > > > > Use query provided on SO to get real owner > > > > > Thanks g

Re: [GENERAL] Permissions pg_dump / import

<http://www.jetbrains.com/> The Drive to Develop From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Patrick B Sent: Thursday, August 18, 2016 1:17 AM To: pgsql-general Subject: [GENERAL] Permissions pg_dump / import Hi guys, I'm runn

Re: [GENERAL] Permissions pg_dump / import

Check out the --no-owner and/or --no-acl flags when performing the dump. These eliminate the statements that set and/or alter ownership of database objects. For use in a test server where the username of the test-server database is different than the username on the production server *and* where y

[GENERAL] Permissions pg_dump / import

Hi guys, I'm running a pg_dump and then importing the dump into a test server. I'm using PostgreSQL 9.5. *pg_dump:* > pg_dump --dbname=prod1 --host= servername --port=5432 --username=user1 -v > -f test1_NEW.sql *Steps into the new database (test1):* > CREATE SCHEMA public; > GRANT ALL ON SCHEM

Re: [GENERAL] permissions PostgreSQL 9.5

On Wed, 2016-08-10 at 13:33 +1200, Patrick B wrote: > hi guys, > > > just setting up a new DB using PostgreSQL 9.5. > > > I've created a new username for the code, called codeuser. > > To give the username access to all the tables, views, etc I ran: > > > GRANT INSERT, SELECT, UPDATE, DELETE

[GENERAL] permissions PostgreSQL 9.5

hi guys, just setting up a new DB using PostgreSQL 9.5. I've created a new username for the code, called codeuser. To give the username access to all the tables, views, etc I ran: GRANT INSERT, SELECT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO > codeuser; Is that ok? Is that enough?

Re: [GENERAL] Permissions, "soft read failure" - wishful thinking?

On Mon, 14 Dec 2015 09:55:02 -0800, Benjamin Smith wrote: >Is there a way to set PG field-level read permissions so that a deny doesn't >cause the query to bomb, but the fields for which permission is denied to be >nullified? How about using encryption? pgp_sym_decrypt() returns null if any

Re: [GENERAL] Permissions, "soft read failure" - wishful thinking?

Benjamin, * Benjamin Smith (li...@benjamindsmith.com) wrote: > Is there a way to set PG field-level read permissions so that a deny doesn't > cause the query to bomb, but the fields for which permission is denied to be > nullified? Not directly, no. One approach would be to create views which

Re: [GENERAL] Permissions, "soft read failure" - wishful thinking?

On Monday, December 14, 2015, Jack Christensen wrote: > On 12/14/2015 11:55 AM, Benjamin Smith wrote: > >> Is there a way to set PG field-level read permissions so that a deny >> doesn't >> cause the query to bomb, but the fields for which permission is denied to >> be >> nullified? >> >> In our

Re: [GENERAL] Permissions, "soft read failure" - wishful thinking?

On Monday, December 14, 2015, Benjamin Smith wrote: > On Monday, December 14, 2015 05:25:16 PM Adrian Klaver wrote: > > > FOLLOWUP QUESTION: is there a way to ask the query planner what > > > tables/fields were output in a database result? > > > > Just dawned on me, are you asking if EXPLAIN can

Re: [GENERAL] Permissions, "soft read failure" - wishful thinking?

On 12/14/2015 11:55 AM, Benjamin Smith wrote: Is there a way to set PG field-level read permissions so that a deny doesn't cause the query to bomb, but the fields for which permission is denied to be nullified? In our web-based app, we have a request to implement granular permissions: table/fiel

Re: [GENERAL] Permissions, "soft read failure" - wishful thinking?

On Monday, December 14, 2015 05:25:16 PM Adrian Klaver wrote: > > FOLLOWUP QUESTION: is there a way to ask the query planner what > > tables/fields were output in a database result? > > Just dawned on me, are you asking if EXPLAIN can output more detailed > information? Ha ha, in another post, I

Re: [GENERAL] Permissions, "soft read failure" - wishful thinking?

On Monday, December 14, 2015 05:20:52 PM Adrian Klaver wrote: > > FOLLOWUP QUESTION: is there a way to ask the query planner what > > tables/fields were output in a database result? > > I am not following, that would be in the query output would it not? A > more detailed explanation of what you w

Re: [GENERAL] Permissions, "soft read failure" - wishful thinking?

On 12/14/2015 04:22 PM, Benjamin Smith wrote: On Monday, December 14, 2015 01:02:00 PM you wrote: On 12/14/2015 09:55 AM, Benjamin Smith wrote: Is there a way to set PG field-level read permissions so that a deny doesn't cause the query to bomb, but the fields for which permission is denied to

Re: [GENERAL] Permissions, "soft read failure" - wishful thinking?

On 12/14/2015 04:22 PM, Benjamin Smith wrote: Ccing list On Monday, December 14, 2015 01:02:00 PM you wrote: On 12/14/2015 09:55 AM, Benjamin Smith wrote: Is there a way to set PG field-level read permissions so that a deny doesn't cause the query to bomb, but the fields for which permission is

Re: [GENERAL] Permissions, "soft read failure" - wishful thinking?

On Mon, 2015-12-14 at 09:55 -0800, Benjamin Smith wrote: > Is there a way to set PG field-level read permissions so that a deny > doesn't > cause the query to bomb, but the fields for which permission is > denied to be > nullified? > > In our web-based app, we have a request to implement granul

Re: [GENERAL] Permissions, "soft read failure" - wishful thinking?

On 12/14/2015 09:55 AM, Benjamin Smith wrote: Is there a way to set PG field-level read permissions so that a deny doesn't cause the query to bomb, but the fields for which permission is denied to be nullified? In our web-based app, we have a request to implement granular permissions: table/fiel

Re: [GENERAL] Permissions, "soft read failure" - wishful thinking?

On Mon, Dec 14, 2015 at 10:55 AM, Benjamin Smith wrote: > Is there a way to set PG field-level read permissions so that a deny > doesn't > cause the query to bomb, but the fields for which permission is denied to > be > nullified? > > In our web-based app, we have a request to implement granular

Re: [GENERAL] Permissions, "soft read failure" - wishful thinking?

On Mon, Dec 14, 2015 at 09:55:02AM -0800, Benjamin Smith wrote: > Is that there is a way to let the query succeed, but nullify any fields where > read permissions fail? What about real field values begin NULL ? Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73B

[GENERAL] Permissions, "soft read failure" - wishful thinking?

Is there a way to set PG field-level read permissions so that a deny doesn't cause the query to bomb, but the fields for which permission is denied to be nullified? In our web-based app, we have a request to implement granular permissions: table/field level permissions. EG: userX can't read cu

Re: [GENERAL] Permissions on large objects - db backup and restore

On 4/3/2013 5:57 PM, Tom Lane wrote: $ pg_restore -? ... -O, --no-owner skip restoration of object ownership ... So there you have it. pg_restore just restored all the objects (blobs and otherwise) as owned by the user running it. I should think you'd have had issues with other things besides

Re: [GENERAL] Permissions on large objects - db backup and restore

David Wall writes: > On 4/3/2013 3:14 PM, Tom Lane wrote: >> A 9.2->9.2 dump and restore certainly should preserve large object >> ownership (and permissions, if you've set any). In a quick check I do >> see "ALTER LARGE OBJECT nnn OWNER TO ..." commands in pg_dump's output >> for such a case.

Re: [GENERAL] Permissions on large objects - db backup and restore

On 4/3/2013 3:14 PM, Tom Lane wrote: A 9.2->9.2 dump and restore certainly should preserve large object ownership (and permissions, if you've set any). In a quick check I do see "ALTER LARGE OBJECT nnn OWNER TO ..." commands in pg_dump's output for such a case. Are you sure this is really the

Re: [GENERAL] Permissions on large objects - db backup and restore

David Wall writes: > When we upgraded from PG 8.4 to PG 9.2, we ran into a permissions issue > with large objects as discussed here: > http://postgresql.1045698.n5.nabble.com/Large-Object-permissions-lost-in-transfer-td4281604.html > The basic solution was to do an ALTER LARGE OBJECT and set th

[GENERAL] Permissions on large objects - db backup and restore

When we upgraded from PG 8.4 to PG 9.2, we ran into a permissions issue with large objects as discussed here: http://postgresql.1045698.n5.nabble.com/Large-Object-permissions-lost-in-transfer-td4281604.html The basic solution was to do an ALTER LARGE OBJECT and set the OWNER TO using a script

Re: [GENERAL] permissions / ACLs made easier?

On Mon, Jun 29, 2009 at 1:01 PM, Jeff Davis wrote: > > > CREATE USER read_only_user > GRANT (SELECT ON TABLE, USAGE ON SCHEMA) FROM admin_user; > > "read_only_user" would automatically have SELECT privileges on any table > that admin_user has SELECT privileges on, and automatically have USAGE >

[GENERAL] permissions / ACLs made easier?

This idea is meant as an alternative to MySQL-style "GRANT ... *" or other similar permissions schemes. I posted a similar message on -hackers here: http://archives.postgresql.org/pgsql-hackers/2009-06/msg01393.php I'm posting on -general now to get some feedback from potential users to see if i

Re: [GENERAL] permissions

On Apr 21, 2007, at 9:53 PM, Tom Allison wrote: I am not sure how the permissions work anymore. What I want to do is create a database and have a user create all the tables and rules. I created the database from user postgres. I then set the owner of the database to my userid my userid crea

[GENERAL] permissions

I am not sure how the permissions work anymore. What I want to do is create a database and have a user create all the tables and rules. I created the database from user postgres. I then set the owner of the database to my userid my userid created a number of tables and rules. I then tried to

Re: [GENERAL] Permissions to connect to postgres database

You can turn on connection logging by setting the following in postgresql.conf file:log_connections = onlog_disconnections = onand this will log all the connections and disconnections to your database server in the db server log files. Thanks,-- Shoaib MirEnterpriseDB (www.enterprisedb.com)On 7/27/

Re: [GENERAL] Permissions to connect to postgres database

Jasbinder Bali wrote: How do i turn on the connection logging and then how do you actually check it? Log settings are in your postgresql.conf file and are detailed in this part of the manual: http://www.postgresql.org/docs/8.1/static/runtime-config-logging.html Where your logfiles are stored

Re: [GENERAL] Permissions to connect to postgres database

How do i turn on the connection logging and then how do you actually check it?On 7/27/06, Richard Huxton wrote:Jasbinder Bali wrote:> Hi,> I have a database in postgres and i've given trusted permissions to all > making required changes> in pg_hba.conf.>> Now, I'm trying to conn

Re: [GENERAL] Permissions to connect to postgres database

Jasbinder Bali wrote: Hi, I have a database in postgres and i've given trusted permissions to all making required changes in pg_hba.conf. Now, I'm trying to connect to the database using ECPG. Unfortunately after EXEC SQL CONNECT dbxyz gives me error -402 that means i don't have permissions to t

[GENERAL] Permissions to connect to postgres database

Hi,I have a database in postgres and i've given trusted permissions to all making required changesin pg_hba.conf.Now, I'm trying to connect to the database using ECPG. Unfortunately afterEXEC SQL CONNECT dbxyz gives me error -402 that means i don't have permissions to the database.I'm running this

Re: [GENERAL] Permissions required for service user account _after_ installation

> The "Silent install of PostgreSQL on Windows" document states > that "The service user needs Log on as a service, Log on > locally and Access this computer from the network." If the > PostgreSQL database will only be accessed via localhost, are > these permissions still required after the in

[GENERAL] Permissions required for service user account _after_ installation

The "Silent install of PostgreSQL on Windows" document states that "The service user needs Log on as a service, Log on locally and Access this computer from the network." If the PostgreSQL database will only be accessed via localhost, are these permissions still required after the installation has

[GENERAL] Permissions on implicit SERIAL datatype seqeuences

Hi, I see there's been some discussion about cascading GRANTS to implicitly created sequences. Regardless, a heads-up in the documentation could be a nice thing to have, noting that permissions will have to be created (or not) for the implicit seqeuences. I _should_ know better, but just got bit by

Re: [GENERAL] Permissions issue?

On Wed, 2003-12-17 at 15:25, Tom Lane wrote: > Christopher Murtagh <[EMAIL PROTECTED]> writes: > > Am I missing something obvious? > > The permissions were granted to PUBLIC, not to newuser, and so the > REVOKE doesn't do anything. You'd need to revoke rights from PUBLIC and > then grant them bac

Re: [GENERAL] Permissions issue?

On Wed, 17 Dec 2003, Christopher Murtagh wrote: > On Wed, 2003-12-17 at 13:54, Stephan Szabo wrote: > > I think you probably want to revoke create on the public schema. Create on > > databases controls the creation of schemas. > > From the grant page: > > Hrm, thanks for the reply. I tried that

Re: [GENERAL] Permissions issue?

Christopher Murtagh <[EMAIL PROTECTED]> writes: > Am I missing something obvious? The permissions were granted to PUBLIC, not to newuser, and so the REVOKE doesn't do anything. You'd need to revoke rights from PUBLIC and then grant them back to whomever should have them.

Re: [GENERAL] Permissions issue?

On Wed, 2003-12-17 at 13:54, Stephan Szabo wrote: > I think you probably want to revoke create on the public schema. Create on > databases controls the creation of schemas. > From the grant page: Hrm, thanks for the reply. I tried that too. Here's what I got (below). Am I missing something obviou

Re: [GENERAL] Permissions issue?

On Wed, 17 Dec 2003, Christopher Murtagh wrote: > Greetings, > > I'm trying to create a user without create privileges and I don't seem > to be able to do it. I could be clueless, but after my revoke > statements, the new user still seems to be able to create dbs, and then > have full privileges

Re: [GENERAL] Permissions issue?

On Wed, 2003-12-17 at 13:20, Christopher Murtagh wrote: > I'm trying to create a user without create privileges and I don't > seem to be able to do it. I could be clueless, but after my revoke > statements, the new user still seems to be able to create dbs, and > then have full privileges on them.

[GENERAL] permissions question

Suppose we have table A and table B. When action is performed on table A (for example INSERT), some action (for example UPDATE) through the trigger is done on table B. If user had permission on both tables (GRANT INSERT on table A and GRANT UPDATE on table B)- everything is good. But in that way

[GENERAL] Permissions & Ownership (BUG?)

Hi all, Having had a play with GRANT, REVOKE and database ownership, a couple of things have come to my attention. I'm pretty sure I've covered everywhere these may be explained in the documentation, but please send me off in the right direction if I've missed something: 1) There doesn't seem to

Re: [GENERAL] Permissions and views.

=?iso-8859-1?q?Mart=EDn=20Marqu=E9s?= <[EMAIL PROTECTED]> writes: >> It is said that RULES are executed with rule's owner permissions, so how >> is it possible that different users are getting different results? > This is not true. Rules are not executed with owner permission. Yes they are. If

Re: [GENERAL] Permissions and views.

Dariusz Pietrzak <[EMAIL PROTECTED]> writes: > but select oid FROM "jednostka" where "brcd" = 222; works fine. for > update clause makes the difference. What PG version is this? regards, tom lane ---(end of broadcast)--- T

Re: [GENERAL] permissions on databases

On Tue, 6 Feb 2001, Tressens Lionel wrote: > I am the super user of my DBMS and if create a database for a simple user, > I do : > > => create database formyuser; > > But If this user connects to the database and tries to create a table, he > has no rights. I must (the super user) create the tabl

[GENERAL] permissions on databases

Hi all, I am the super user of my DBMS and if create a database for a simple user, I do : => create database formyuser; But If this user connects to the database and tries to create a table, he has no rights. I must (the super user) create the table for him and then give him all the permissions

[GENERAL] permissions on user-defined functions

I am new to PostgreSQL... I've created a table (table1) and database user (dbuser). dbuser has only SELECT permission on table1. Next I created a function insert_table1 that inserts a new row into table1. I want to give dbuser the ability to invoke the insert_table1 function without granting dbu

[GENERAL] permissions on databases vs. permissions on tables

ok, so i know how to use GRANT/REVOKE to assign permission levels on tables. however, it eludes me on how to do it on a database level. i poked about in the manuals and nothing jumped out and said "here's how you do it". for example, i have a database called "samples". in "samples", i have va

[GENERAL] Permissions on databases (not on tables)

Colleagues. Could you please tell me how I can prevent a user from creating tables in a database belonging to another user? I login as user1, create a database, then logout, connect to the database as user2 and create tables in it. Is this behavior by design? And how can I prevent this from happ

RE: [GENERAL] permissions & foreign keys

That's cleared that up, thanks! Tamsin -Original Message- From: Jan Wieck [mailto:[EMAIL PROTECTED]] Sent: 04 September 2000 15:50 To: Tamsin Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] permissions & foreign keys Tamsin wrote: > > I don't really see wh

Re: [GENERAL] permissions & foreign keys

Tamsin wrote: > > I don't really see why it wants to update feedback_type? Can anyone tell me > what I'm doing wrong, or will I just have to grant update on feedback_type > (and all other tables referenced by FKs)? > It doesn't want to update it. It just does the SELECT ... FOR UPDATE to

[GENERAL] permissions & foreign keys

Hi, I am having some problems setting up security on my database: I have a table 'feedback', with a foriegn key to a table 'feedback_type' (tables at end). I want to give the user all permissions on feedback, but to only be able to select the possible values from the feedback_type table. So, I

[GENERAL] Permissions on pg_dump/pg_dumpall files

For those people using pg_dump and pg_dumpall, are you making sure your dump files are not world-readable? -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + C

[GENERAL] permissions ..

hello, I was trying the to revoke and grant permissions to the users on the tables, from the psql prompt. I appears that the revoke and grant functions donot do the needful. Could someone help me, as to how the permissions can be manipulated. Thanks, Reshma.